BIRT provides a wide range of functions in the Total class that support aggregate calculations over sets of data rows. These functions are custom JavaScript functions that BIRT defines. As with native JavaScript functions, you must use the correct capitalization when you type the function name, as shown in Table 13-1. For detailed information about each aggregate function, see Scripting Reference in BIRT’s online help.

Where you place aggregate data is essential to getting the correct results. For aggregate calculations, such as Total.sum( ), Total.ave( ), Total.max( ), and Total.mode( ), which process a set of data rows and return one value, you typically insert the aggregate data in the following places in a table:

You can place this type of aggregate data in a table’s detail row, but the data would not make much sense, because the same aggregate value would appear repeatedly for every row in the group. On the other hand, you typically insert aggregate calculations, such as Total.runningSum( ), Total.movingAve( ), Total.percentRank( ), and Total.Rank( ), in the detail row of a table. These functions process a set of data rows and return a different value for each row.

The report in Figure 13-3 groups data rows by customer, then by order ID. It displays totals for each order, totals for each customer, and a grand total of all sales. At the detail level, the report displays the running total for each line item.

Figure 13-3. Report showing totals for groups and running totals for detail row

To display the aggregate data as shown in the preceding report example, complete the following steps:

Figure 13-4 shows the report design.

Figure 13-4. Aggregate calculations in a report design

This section describes the expressions that you use to calculate the subtotals and totals that appear in the report example in the preceding section. As with all dynamic data, you define the aggregate expressions in a column binding. Then you insert a report element that uses the column binding.

Notice that the group subtotals and report total are calculated with the same basic aggregate expression, Total.sum(row["Extended_Price"]). When you create multiple groups and place aggregate expressions in the headers or footers of various group levels, BIRT automatically calculates totals for the corresponding groups.

Figure 13-5 shows the column bindings defined for the table that contains all the report data. The Aggregate On values indicate the level at which aggregate calculations apply. The ALL value indicates that the aggregate calculation is applied to all rows in the table. The Orders value indicates that the aggregate calculation is applied to rows in the Orders group. The N/A value indicates that aggregation is not applicable, because the expression is not an aggregate expression.

Figure 13-5. Column bindings

Typically, you supply only a data set field (or a column binding that refers to a data set field) as an argument to an aggregate function, as shown in the following examples:

//Return the highest payment amount
Total.max(row["paymentAmount"])

//Return the lowest score
Total.min(row["score"])
//Return the average price
Total.ave(row["price"])

//Return the number of unique order IDs
Total.countDistinct(row["orderID"])

//Return the first date in the data set
Total.first(row["paymentDate"])

//Return the last name in the data set
Total.last(row["customerName"])

The exception is Total.count( ), which does not take a data set field as an argument. Total.count( ) returns the number of rows in a group or in the entire report, depending on where you place it.

If you use the expression builder to access the aggregate functions, these functions are listed under BIRT Functions—Total, as shown in Figure 13-6.

Figure 13-6. Aggregate functions in the expression builder

How to construct an aggregate expression in the expression builder

When you calculate aggregate data, you can specify a filter condition to use to determine which rows are factored in the calculation. For example, you can exclude rows with missing or null credit limit values when you calculate an average credit limit or include only deposit transactions when you calculate the sum of transactions.

To specify a filter condition when you aggregate data, specify a filter expression that evaluates to true or false. Include the filter expression as an argument to the aggregate function. You specify the filter expression either as the second or third argument, depending on the function. The exception is Total.count( ), where the filter expression is the first argument, because this function does not reference a data set field.

The following expressions are examples of aggregate expressions that include a filter condition:

   Total.countDistinct(row["country"])

   Total.countDistinct(row["country"], row["country"] != null)

As the example report in “Placing aggregate data,” earlier in this chapter shows, you typically insert an aggregate expression in the header or footer of a group to calculate and show aggregate information for that particular group. For example, to calculate the sum of orders for each customer, you insert an expression, such as Total.sum(row[“extendedPrice”]), in the header of the customer group to display the information at the beginning of each customer group or in the footer to display the information at the end of each customer group.

Sometimes, however, you might want to display an aggregate value from a different group. The report shown in Figure 13-8 displays the grand total (the sum of all customer orders) in each customer group.

Figure 13-8. Report displaying a grand total for each customer

The Total.sum(row[“extendedPrice”]) expression in the customer group calculates the sum of extended prices over all data rows in the customer group only. The overall total, however, is calculated over all the data rows in the report’s data set.

To display the overall total in the customer group header, you must specify the group for which you want an aggregate value. You accomplish this task in either of the following ways:

Similarly, if you want to display the customer total in the order ID group, you can accomplish the task using either of the following ways:

If you use the second method, you can supply different types of values for the third argument. For example, you can specify the group index instead of the group name. In our example, the Customers group is the first group, so its index is 1. An index value of 0 gets the overall total. For example, the following expressions return the same results:

Total.sum(row["extendedPrice"], null, "Customers")
Total.sum(row["extendedPrice"], null, 1)

The following expressions also return the same results:

Total.sum(row["extendedPrice"], null, "overall")
Total.sum(row["extendedPrice"], null, 0)

The ability to access the aggregate value from any group of data in the report is useful. Not only can you display the values, you can use them in calculations that require aggregate values from multiple groups. Percentage calculations, for example, require a subtotal and an overall total. Information about calculating percentages is in the following section.

Some percentage calculations require aggregate values from two different groups of data. For example, a report displays each regional sales total as a percentage of the total national sales. To calculate this aggregate data for each region, two totals are required:

Figure 13-9 shows an example of a report that displays sales data that is grouped by state, then by product. The report shows two percentage calculations:

Figure 13-9. Percentage calculations in a grouped report

Figure 13-10 shows the report design.

Figure 13-10. Percentage calculations in a report design

Total.sum(row["extendedPrice"])/
   Total.sum(row["extendedPrice"], null, "State")

Total_Product: Total.sum(row["extendedPrice"])
Aggregate On Product

Total_State: Total.sum(row["extendedPrice"])
Aggregate On State

Product_Percent: row["Total_Product"]/row["Total_State"]
Aggregate On N/A

Total.sum(row["extendedPrice"])/
   Total.sum(row["extendedPrice"], null, "overall")

Total_State: Total.sum(row["extendedPrice"])
Aggregate On State

Total_All: Total.sum(row["extendedPrice"])
Aggregate On ALL

State_Percent: row["Total_State"]/row["Total_All"]
Aggregate On N/A

Reports typically display both detail and aggregate data. A summary report is a report that shows only aggregate data. Summary reports, such as the top ten products or sales totals by state, provide key information at a glance and are easy to create.

When you create a report that contains data in detail rows and aggregate data in header or footer rows, you can change such a report to a summary report by hiding the contents in the detail rows. To hide these contents, you can use any of the following techniques:

The first and second techniques provide the flexibility of maintaining two versions of a report. The third technique, on the other hand, results in a summary report only. To change the report to display details, you would have to add the data back to the detail row.

Take a closer look at the report design in Figure 13-10. The report contains data only in the header and footer rows, and almost all the data elements display totals. Rather than display individual sales records in the detail rows, the report shows only the sales total for each product.

To create a top n or bottom n summary report, insert the aggregate data in the header or footer row. Then create a filter for the group that contains the data, and use a filter condition, as shown in the following examples:

//Show only the top ten orders
Total.sum(row["extendedPrice"]) Top n 10

//Show only orders in the top ten percent
Total.sum(row["extendedPrice"]) Top Percent 10

//Show only the lowest five orders
Total.sum(row["extendedPrice"]) Bottom n 5

//Show only orders in the bottom one percent
Total.sum(row["extendedPrice"]) Bottom Percent 1

Figure 13-11 shows a top ten report. Figure 13-12 shows the report design.

Figure 13-11. Top ten report

Figure 13-12. Top ten report design

The data elements that display the sales representative names and their sales totals are in a group header row. This report groups sales data rows (not shown in the report) by sales representatives. To display only the top ten sales representatives, a filter is in the group definition, as shown in Figure 13-13.

Figure 13-13. Top ten filter, defined in the group