Aggregates are calculations performed on a group of data rows. For example, counting the number of rows in a group is an aggregate calculation. In Exago, this is done by using an aggregate formula in the Group Footer or Report Footer section of a report.
Supported database aggregate functions are AggCount(), AggDistinctCount(), AggSum(), AggAvg(), AggMax(), and AggMin().
Exago calculates aggregates by individually pulling each detail row from the data source, and then applying a formula. For small reports this is usually acceptable, but in reports with large numbers of rows, where you don't need to see the row data, performance can be reduced.
Most databases have the ability to calculate aggregates themselves. This tends to be much faster than performing the calculations in memory. The Database Aggregation feature allows Exago to push these calculations to the database. This allows Exago to retrieve only the summary records from the database instead of all the individual data rows. This can mean a significant performance boost especially for reports containing large amounts of data.
Enabling Database Aggregation
NoteDatabase Aggregation is enabled by default in Exago v2019.1+.
Database Aggregation can be enabled (or disabled) by either:
- changing the Admin Console's General > Database Settings > Aggregate and Group in Database setting
- by editing the
<aggregateandgroupindatabase>node in the config file.
A value of True enables Database Aggregation, False disables it.
Warning:You must see the following section to ensure that aggregates for One-to-Many joins are calculated correctly.
Identifying One-to-Many Joins
All One-to-Many joins must be identified as such in the application configuration. Exago calculates aggregates based on the unique key relationship between categories. But some databases calculate aggregates by simply counting the rows. This means Exago must know which joins are one-to-many, so that it can decide whether it is acceptable to push the calculation to the database, or whether it must do the calculation in the application.
NoteThe Data Source Metadata Discovery tool cannot identify whether joins are One-to-Many. By default it sets all joins as One-to-One. If you used this tool to autofill your Objects and Joins, you must take these precautions before enabling Database Aggregation.
Admin Console
To set your joins using the Admin Console, expand Data > Joins, and then double-click on the Join, or select the Join and click the Edit 
icon. This will open the Joins tab.
In the Relation Type drop-down menu, select One To Many. Then click Apply or Okay to save your changes. Do this for each relevant Join.
Config File
To set joins by editing the config file, open the file in a text or XML editor. Each join is a <join> element. In each relevant join element, locate the <relation_type> field and change the value from "11" (one-to-one) to "1M" (one-to-many). Save the config and restart the web server.
Exceptions
Database Aggregation will not work on every report. In the following cases aggregation occurs in memory even if Database Aggregation is enabled:
- ExpressViews (pre-v2017.3)
- Reports with Execution Caching enabled (v2017.3.19+)
- Reports with more than one data source
- Reports with an aggregation on the ‘One’ side of a One-to-Many join
- Reports with a visible Detail section or cells referencing a hidden Detail section
- Reports with Formula Sorts (pre-v2019.1.0) or Custom Columns
- Reports containing an
AggDistinctCount()formula (pre-v2019.1.3) - Reports containing a CrossTab Report
- Aggregate formulas with a non-data field argument, such as another formula
AggAvg()needs to be in the innermost group if there are multiple groups or a group and a report footer- Reports with one or more Interactive Filters, Column Sorts, Group Min/Max Filters, Top N Filters, Linked Report Formulas or Transformed Vertical Tables