Aggregating Data

Posted on 12/11/2023

Aggregating Data

In any good report, you may want to display summary, or aggregate, data. Aggregating data involves performing a calculation on a set of values and returning a single value. For example, you may want to calculate the total number of hours worked by all employees in a department. In this case, you would add up all the hours worked by each employee and return a single value.

In BIRT Studio, reports can be aggregated at the table and group level. For example, you could group by Employee Full Name and Paycode Name, then aggregate on each group, but also the table. This would provide you with the total hours worked by each employee, as well as the total hours worked by each employee for each paycode. And at the table level, it would give you the total hours worked by all employees.

How To Aggregate Data

To aggregate data in a report, first select the desired column that will be aggregated. In the table bar, click the icon. This will open the Aggregation Editor. Alternatively, you can right click the column, and select Aggregation

The following sections review terms discussed in Navigating BIRT Studio. If you are unfamiliar with these terms, please review that article before continuing.

Applying Aggregations by Group in Design View

Aggregating by group assumes that you have an existing group to aggregate on. If you do not know how to create a group, please see the Grouping Data article.

To aggregate by group, while in design view and with the Aggregation Window open, check the group option in the Aggregate on section (true by default). Then select whether you want to place the aggregation in the header or footer of the group. Select a function to use for the aggregation in the Select Function dropdown. An optional Label can be entered that will prefix the aggregation. Click OK when done to save the aggregation.

Applying Aggregations by Group in Preview View

Aggregating by group in preview view has additional options not present in design view. However, there are some things to note when applying aggregations in preview view.

• In order to edit in preview view, the report must be under 200 pages
• Changes applied in preview view must be saved in preview view, and the entire unpublished report reloaded to see the changes
• Aggregations in preview view are bolded by default (can be changed in design view later)
• The group aggregated on can be selected in preview view, whereas in design view the aggregation applies to every group and cannot be selected
• Aggregations at the group level in preview view apply their own “aggregation row” in either the header or footer of the group. This behavior cannot be changed.
• Aggregations made in preview view cannot be removed from design view. They must be removed in preview view.

To aggregate by group while in preview view, right click on the column header and select Aggregation. Alternatively, with a column selected, you can click on the three-dots menu and select the Aggregation option. Either will open the Aggregation Window. It will look different than before.

You’ll notice that this window looks slightly different than the one before in that it allows you to select the group level with a dropdown. The dropdown provides the column key of every group in the report. See the Data Dictionary for more information on column keys.

From this dropdown, select the group you want to aggregate on and whether you would like the aggregation to appear in the header or footer. As mentioned above, aggregations in preview view will add their own row to the report. If header is selected, the aggregation will appear just under the group header. If footer is selected, the aggregation will appear just before the next group header. An optional Label can be entered. Click OK when done to save the aggregation.

By Table

Aggregating at the table lavel is the same in either design or preview view. From the aggregation window, check the table level option (true by default). Click OK when done to save the aggregation.

Adding Multiple Aggregations

To add multiple aggregations, click the Add Aggregation link at the bottom of the Aggregation window. This will add another aggregation on the same selected column. You can add as many aggregations as you want. Each aggregation can be applied to a different group or the table. Each aggregation can also be placed in the header or footer of the group. If adding multiple aggregations with different functions, it may be a good idea to add a label to differentiate what each aggregation is.

Removing Aggregations

If you need to remove an existing aggregation, open the Aggregation window and click the Delete Aggregation link. This will remove that specific aggregation from the report. If multiple aggregations on the same column are applied, the others will not be removed.