Minimal Standard Analytical Tables in Tryton: Current Capabilities and Possible Evolution

Feature Ideas
1

Hello,

I am evaluating Tryton from the perspective of accounting, tax analysis, and ERP reporting.

From what I understand, Tryton already provides a solid foundation with:

  • list/tree views

  • form views

  • search and filtering

  • grouped data presentation in standard views

  • graph views

  • board views

  • accounting reports such as General Ledger, Trial Balance, Balance Sheet, Income Statement, and Aged Balance

  • drill-down from financial reports into accounts, parties, and move lines

So Tryton already has an excellent transactional and reporting base. However, it seems that it does not yet provide a built-in interactive analytical table layer similar to what some modern ERP systems offer through pivot tables, spreadsheet-style analysis, or flexible multidimensional reporting.

My main interest is not necessarily a full spreadsheet inside Tryton at the start, but rather a minimal standard analytical table framework that could later evolve into something richer.

What I mean by this is a native or semi-native way to support tables with features such as:

  • grouping by dimensions such as period, account, party, tax, product, warehouse, project, document type, etc.

  • subtotal and total rows

  • expandable / collapsible grouped lines

  • drill-down from summary rows into underlying records

  • configurable columns and measures

  • comparison columns (current period vs previous period, budget vs actual, etc.)

  • reusable table definitions for common ERP analysis

  • export-friendly structure that preserves analytical meaning

In my opinion, even a minimal first step would already be very valuable if Tryton had a standard mechanism for predefined analytical tables, especially for:

  • accounting analysis

  • tax analysis

  • sales and purchase summaries

  • stock movement summaries

  • receivable/payable analysis

  • management dashboards based on tabular aggregates

I also found older discussions suggesting that aggregated-data reporting is a known need, but that a completely generic pivot tool may not be the ideal UX. That makes sense to me. Perhaps the best direction is not “full free-form pivot first”, but a structured analytical table engine with predefined dimensions and measures that can later be extended.

So my questions are:

  1. Is there already a recommended Tryton approach for building such minimal standard analytical tables?

  2. Are there existing modules or community efforts in this direction?

  3. From the core team’s perspective, would this be considered a useful evolution of Tryton’s reporting layer?

  4. What would be the best architectural path:

    • custom wizard + Python aggregation logic

    • dedicated model + table view

    • graph/board extensions

    • custom SAO widget

    • external BI integration only

  5. If Tryton were to evolve in this area, what would be the most realistic “minimum viable” feature set?

My goal is to understand both:

  • what Tryton already has today, and

  • what should be added so that it can cover the standard analytical table expectations users often have from modern ERP systems.

I would be very interested in guidance, architectural recommendations, or feedback on whether this direction fits Tryton’s philosophy.

Thank you.

1 Like
2

We are generating all the reporting using Metabase as external BI tool. In order to achieve that we connect it directly to the PostgreSQL database. As tryton has a lot of tables, we create several SQL views on the database, where we can add calculated fields and also denormalize information for reporting.

Our users are very happy with this aproach, as they can create:

  • Pivot tables
  • Several graphs for seeing information
  • Dashboards
  • Alerts

There are some caveheats and details to improve but you can get a very interessting results with this aproach. You can achieve similar results with any external BI tool.

3

You may be interested in the babi module.

There, you can create:

  • SQL Tables which are computed with a cron job whenever you want
  • SQL Views
  • Tables based on data from tryton using Python expressions. This allows you to explore information that Tryton does not store in the database and which would require you to reimplement if you use an external BI tool.

Tables can depend on other tables which the system can recompute in cascade. It supports on a per-table user permissions.

They can be exported directly to Excel or navigated with a built-in Pivot Table implementation. I recommend checking out the latest changes in version 7.8 which still requires some polishing but are more user friendly. You can add row or column dimensions, measures and properties. You can also store some predefined Pivot Tables.

You can also configure those tables to request the user some parameters before the information is computed. It also supports warnings that users can handle (there’s a Tryton menu entry for the warnings pending to be managed), and it can send e-mails for those warnings to the users or external parties. E-mails can contain the content of the table or a pivot table.

It also supports dashboards (which can be viewed was a new tab in sao) with 15 different types of charts which can be easily created from the aforementioned tables.

It may be useful to search for the rest of “babi_reports_*” modules we have in github which incorporate quite a few python expressions and filters to be quickly used.

Hope it helps!

4

For now such requirements are solved with ModelSQL based on table_query, context model, an multiple action on menu entry and “tree open” keyword.
An example is the sale reporting which has quite good result.
But it has some usability drawbacks like having to re-open the menu entry to change the “grouping”, opening new tab for drill-down, calculation of invisible columns, only sum aggregate on selected rows.

Some can be fixed within the current design and tools like:

But the dynamic “grouping” is probably not resolvable with the ModelSQL which has predefined columns and requires a unique ID.
So I think in order to provide such feature, we will need to create a new kind of object that provides the needed API for a client view which can display as a table/tree dynamic result but also expose the available grouping dimensions, the possible aggregate (not every aggregate on any column has meaning). In some way the view would be a kind of display for SQL result and the new object should be a merge of the reporting Mixin currently used.

5

I am a practitioner with several years of experience in accounting analytics and the tax field, and I am also very interested in the development of ERP systems.

I have recently been learning more about Tryton, and I genuinely like its philosophy: the structured design, the clean business logic, and the strong focus on consistency.

I expect that in the future I may also share other ideas on the forum, especially from the perspective of accounting, tax analysis, and ERP reporting.

Regarding this topic, I completely agree with your assessment. It seems that the current ModelSQL abstraction is not the best fit for dynamic grouping, especially because it relies on predefined columns and stable physical record identifiers.

Instead of aiming immediately for a fully free-form pivot builder, I wonder whether a more incremental native analytical layer could be a realistic direction.

For example, a dedicated analytical/reporting object could define:

  • dimensions: such as period, account, party, tax, product, warehouse

  • measures: such as amount, quantity, tax amount, balance

  • aggregates: such as sum, count, average

  • drill-down mapping: from a summary row to the underlying source records

On top of that, the client could provide a grouped table view with:

  • configurable grouping

  • subtotal and total rows

  • expand/collapse for grouped lines

  • drill-down to source records

  • export support

From a business perspective, this could later support predefined analytical tables such as:

  • Sales by Period / Customer / Product

  • Purchase Summary

  • General Ledger Summary

  • Trial Balance by Period

  • Tax Summary

  • Stock Movement Summary

  • Receivable / Payable Aging Summary

Even a small MVP with fixed report definitions, configurable grouping, basic aggregates, and drill-down would already be very valuable for daily accounting and tax analysis.

Would such a dedicated analytical result object be a realistic architectural direction for Tryton core in the future?

6

My proposal is not about a any “free-form pivot builder”. I do not think it is the job of the user to construct the view but only to select what he wants to see such as the grouping sequence of pre-defined dimension, the sequence of pre-defined aggregated columns.

This is what I was talking about.

I think this is deduced from the grouping sequence.

I’m not sure what you mean but if it is a view to display the result of the “reporting” object then it is what I call a “new view”.

Well for me it makes sense only to replace the existing “reporting” models, the other reports especially the financial are good enough with the current design.

Yes as long as it fits well with the override mechanism and it generates performent SQL queries.
So basically if it can replace the “reporting” mixin design for a simpler definition.

7

The question is: Why invest a lot of development capacities into a transactional system, if you want analytical processing? So tryton fits best, for processes and transactions - but flexible reports needs a lot of development, if you want to fulfill the architectural patterns.

From our point of view, it depends on the requirements, and we believe that the separation between ERP and analytics makes perfect sense. In particular, the issues of analysis flexibility, time-to-market, and results presentation are good arguments in favor of this.

We have used various analytics tools with Tryton in the past. Based on our experience, we have evaluated and productively used a wide variety of tools: Qlikview, Pentaho with Saiku, and Apache Superset.

Each of these tools has its advantages, but Apache Superset has since become our favorite and successor to Pentaho. The development of reporting templates and the modern interface are particularly advantageous here.

All of the above requirements can be implemented extremely quickly and with virtually no coding. We would set up our “reporting container” based on Apache Superset, install the standard template if desired, customize users and permissions, and establish the connection to Tryton. That’s it.

1 Like
8

But for me, it is important that Tryton comes with standard analytic reporting out of the box. This is the reason that we have already some of them for sales, purchases, stock etc.
Every users can not rely on having an external tool that need developer to setup correctly and expose a correct initial configuration. And those “flexible” tools have all the same flaw, they can produce very bad SQL requests if they are not correctly used. And they also require to duplicate the access control.

So for me your position is not from the point of view of an end user but more from an integrator which can combine multiple tools.

9

I understand well that external BI tools exist for dynamic analytical tables and dashboards. In our case, I believe I do not really need Apache Superset, and I would use Metabase instead, because it seems closer to my practical goals and more suitable for everyday business analysis.

At the same time, I still believe that a minimal native analytical layer should exist in Tryton. I do not think that would go against Tryton’s philosophy. It would simply provide a standard analytical foundation out of the box, while more advanced analytics could remain the role of external BI tools.

I am also convinced that this would make Tryton more attractive to professionals from fields like accounting, taxation, and business analysis, who often need solid analytical tools in daily work but are not necessarily developers.