Aggregate Function (Reporting Services)
Returns a custom aggregate of the specified expression, as defined by the data provider.
Syntax
Aggregate(expression, scope)
Parameters
expression
The expression on which to perform the aggregation. The expression must be a simple field reference.scope
(String) The name of a dataset, group, or data region that contains the report items to which to apply the aggregate function. If scope is not specified, the current scope is used. For more information about the scope parameter, see Using Built-in Report and Aggregate Functions in Expressions (Reporting Services) and Calculating Totals and Other Aggregates (Reporting Services).
Return Type
Return type is determined by the data provider. Returns Nothing if the data provider does not support this function or data is not available.
Remarks
The Aggregate function provides a way to support data provider features. For example, the SQL Server Analysis Services data processing extension uses a data provider that retrieves flattened rowsets from an MDX query. Some rows in the result set can contain aggregate values calculated on the data source server. These are known as server aggregates. To view server aggregates in the graphical query designer for Analysis Services, you can use the Show Aggregate button on the toolbar. For more information, see Analysis Services MDX Query Designer User Interface.
When you display the combination of aggregate and detail dataset values on detail rows of a Tablix data region, server aggregates would not typically be included because they are not detail data. However, you may want to display all values retrieved for the dataset and customize the way aggregate data is calculated and displayed.
Reporting Services detects the use of the Aggregate function in expressions in your report in order to determine whether to display server aggregates on detail rows. If you include Aggregate in an expression in a data region, server aggregates can only appear on group total or grand total rows, not on detail rows. If you want to display server aggregates on detail rows, do not use the Aggregate function.
You can change this default behavior by changing the value of the Interpret subtotals as details option on the Dataset Properties dialog box. When this option is set to True, all data, including server aggregates, appears as detail data. When set to False, server aggregates appear as totals. The setting for this property affects all data regions that are linked to this dataset.
Note
All containing groups for the report item that references Aggregate must have simple field references for their group expressions, for example, [FieldName]. You cannot use Aggregate in a data region that uses complex group expressions. For the SQL ServerAnalysis Services data processing extension, your query must include MDX fields of type LevelProperty (not MemberProperty) to support aggregation using the Aggregate function.
Comparing the Aggregate and Sum Functions
The Aggregate function differs from numeric aggregate functions like Sum in that the Aggregate function returns a value that is calculated by the data provider or data processing extension. Numeric aggregate functions like Sum return a value that is calculated by the report processor on a set of data from the dataset that is determined by the scope parameter. For more information, see in the aggregate functions listed in Using Built-in Report and Aggregate Functions in Expressions (Reporting Services).
Example
The following code example shows an expression that retrieves a server aggregate for the field LineTotal. The expression is added to a cell in a row that belongs to the group GroupbyOrder.
=Aggregate(Fields!LineTotal.Value, "GroupbyOrder")