SQL Server Reporting Services: How to do an aggregate, SUM(Field), and display it as a % of the total for a Table or Matrix column

  • Article

SQL Server Reporting Services: How to do an aggregate, SUM(Field), and display it as a % of the total for a Table or Matrix column

To do this we have to take advantage of the fact that the SUM function, and indeed all aggregate functions, have an optional scope parameter

=Sum(Fields!A.Value) / Sum(Fields!A.Value, "Scope")
Then, on the textbox set the formatcode property e.g. to P1 (to get a display pattern of "xx.x %")

From Books On Line:

Scope
Each aggregate function uses the Scope parameter, which defines the scope in which the aggregate function is performed. A valid scope is the name of a grouping, dataset, or data region. Only groupings or data regions that directly or indirectly contain the expression can be used as a scope. For expressions within data regions, Scope is optional for all aggregate functions. If you omit the Scope parameter, the scope of the aggregate is the innermost data region or grouping to which the report item belongs. Specifying a scope of Nothing sets the scope to the outermost data region to which the report item belongs.

For expressions outside of data regions, Scope refers to a dataset. If a report contains more than one dataset, Scope is required. If a report contains only one dataset and Scope is omitted, the scope is set to the dataset. You cannot specify the Nothing keyword for report items outside of a data region.

You cannot use the Scope parameter in page headers or footers.

Thanks to Robert Bruckner for this tip