Use Summary Functions in Data Item Expressions

You can use summary functions in data item expressions. The summary functions in the expression editor that have the same name as the summary functions available through the Detail aggregation and Summary aggregation properties operate the same way. For example, in a relational report, setting the Detail aggregation property to Total is the same as changing the expression of the data item to total ( [Revenue] ).

In general, report maintenance is easier if the Detail aggregation and Summary aggregation properties are used rather than adding summary functions to data item expressions. Use summary functions in expressions if the required summary is not supported as an aggregate or rollup aggregate property or if the complexity of the expression cannot be managed using the data item properties. Add a summary function to a data item expression if one of the following conditions applies:

• The underlying data source is relational, and you want to use database vendor-specific summary functions.
• You want to use summary functions that require more than one parameter, such as percentile.
• You require summary expressions that are not available in the aggregation properties, such as a for clause. You should use for clauses only in relational style reports (list reports).

For example, your report uses Product line, Product type, and Quantity. You want to calculate the percentage of the parent quantity that is represented by each product type. This requires you to compare the total quantity for each product type to the total quantity for the parent product line. In other words, your expression requires summaries at different group levels. You use summary functions, such as aggregate, in the data item expression with a for clause to specify the group level as follows:

[Quantity] / aggregate ([Quantity] for [Product line])

In some cases, you may need a summary that is different from the default. For example, if the default summary for Quantity is total, you can calculate the average as follows:

[Quantity] / average ( aggregate ([Quantity]) for [Product line])

The inner summary gives you the expected Quantity values at the Product Type level, which are then averaged to the Product Line level. This two-stage aggregation is required for OLAP data sources and recommended for relational data sources also.

Why is this necessary? Here's what would happen if you simply averaged Quantity, as follows:

[Quantity] / average([Quantity] for [Product line])

The denominator is now computed as the average of the detail Quantity rows in the underlying fact table. Since these values are much smaller than the pre-summarized Quantity values you see in the report, the resulting percentage is far too big, and not what we wanted.