Before it can be used in reports, data from the data packages needs to be structured into tables. See Table library for in-depth documentation on tables and table handling components.
Although there are no definitive rules on how to structure the data, it is useful to consider three different types of tables:
Base tables
Base tables are built directly from the data packages, typically using the Table Builder component.
Table Builder takes three main inputs:
- A node from which the table data should be read. Set this to the data package.
- A member type from which to read the nodes that will make up the table rows. Set this to the index member type, such as All or a tag.
- A view which defines the columns for the table.
To stop the base tables and the tables on which they depend constantly recalculating, Table Builder does not automatically recalculate the table when the data package changes. Instead, use the Delayed Recalculation component to manage recalculation of the base tables. This will ensure that the base tables are up-to-date, but limit the number of times that they are recalculated if there are lots of changes to the data packages. Delayed Recalculation takes a number of parameters:
- A delay which indicates how long to wait before running the recalculation after detecting changes.
- A period which should elapse before recalculations can be rerun.
- The source data package, in Depends on.
- The target base table, in Recalculate.
For convenience, you can combine multiple small recalculations into a single delayed recalculation. For large tables it may be more efficient to use different delayed recalculation nodes.
Intermediate tables
Typically the base tables need to be manipulated before they contain exactly the data to be shown to the user. This manipulation may need a number of steps, each involving the creation of a new table. A number of standard table manipulation components can be used:
- Filter Table creates a table from the rows and columns of another table, like a SQL SELECT statement.
- Merge Table creates a table by merging two other tables, like a SQL JOIN or UNION statement. For convenience, this also contains filter functionality.
- Aggregator Table creates a table which summarises another table, by grouping and applying column functions such as sum() and count().
Each of these also has a ..Type version that pre sets the rules for the manipulation in the node type. These are more suitable when building a solution for distribution, as they prevent the user from modifying the manipulation rules.
- Filter Table Type creates a type for creating a pre-set table filter.
- Merge Table Type creates a type for creating a pre-set table merge.
- Aggregator Table Type creates a type for creating a pre-set table aggregator.
Result tables
The last level of tables contains the data that will be used as the direct basis for the user reports. They are built using the same tools as the intermediate tables, the only difference is that they are intended for final consumption.
Results tables should not contain data that you don't want to show to users. For example, it may be necessary to turn links to simple text (using the Filter Table component) or calculate sums and averages (using the Aggregator Table component).