Scope of an Aggregate Function

The Scope parameter defines the scope in which the aggregate function is performed. A valid scope could be

• A string name of data set.

• String name of containing grouping.

• String name of containing data region. Within the data region, Scope is optional for all aggregate functions except RowNumber() .

• Omitted, when optional. In this case, the scope of the aggregate is the innermost data region or grouping to which the report item belongs. If the item is outside of a data region and there is a single data set, Scope refers to this single data set. If there are multiple data sets, Scope cannot be omitted. In the case of multiple data sets, SSRS provides an explicit reference if the designer drags and drops a field outside of the data region.

• A keyword set to Nothing . This is only allowed inside of data regions . In this case, the scope of the aggregate is the outermost data region or grouping to which the report item belongs.

Tip

Scope is a context-sensitive string, thus Dataset1 and dataset1 are different Scope values.

If the containing group or data region is not used as scope, then SSRS throws an error: "The Value expression for the <Item> '<Item Name>' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set."

The Scope parameter cannot be used in page headers or footers.

For RowNumber to generate expected results, the expression containing this function has to be in the "detail" row and not on the "summary" row like a group header or footer.

Table and Matrix report items can have multiple defined scopes. Table can have multiple row scopes. Table with a single group has three scopes: entire data set, entire Table for detail rows, and one for a group. Matrix can have multiple scopes defined on rows and columns . Because Matrix requires at least one row and at least one column group, it has at least four scopes: entire data set, Matrix for details, scope, and one for each of the groups.