The Aggregate Function

Reporting Services supports an Aggregate function to retrieve aggregated values directly from the data provider if the provider supports this feature. The Aggregate function is especially useful with retrieving data from Analysis Services because it may be difficult if not impossible to recreate aggregated values on the report, such as when the cube script overwrites the server aggregates.

It’s easy to use the Aggregate function. Just replace the default Sum() aggregate function with Aggregate() in the group subtotals. When you do this, the Analysis Services provider brings in additional rows that include the server aggregates which the report displays in the group subtotals.

Recently, I came across a report that would return the following exception after attempting to use Aggregate().

The Value expression for the text box ”<textbox.’ uses Aggregate with an invalid scope parameter.

I couldn’t find anything wrong with the way Aggregate() is used because it was in the right place on a crosstab report. Robert Bruckner from the Reporting Services team helped me understand the Aggregate() function limitations:

The Aggregate function can only be used if the current scope and all parent group scopes are all based on simple field references (e.g. =Fields!Accounts.Value), or constants. The Aggregate function also cannot be used in detail rows.

In my case, the report was using a parent-child Analysis Services hierarchy. To show the data recursively, the tablix Details group was set to group on =Fields!Account.UniqueName. This is an extended property that the SSAS provider supports and it’s not a simple reference. Changing the grouping to =Fields!Account.Value resolved the issue.