Summarizing Values in Crosstabs

In crosstabs, summaries are calculated using two summary expressions.

The expressions used to summarize data are:

aggregate ([measure within set [data item]) summarizes the member values from the data source within the current content.
aggregate ([measure within detail [data item]) summarizes the lowest level of details in the report.

The expression that is used to calculate a summary is controlled by the summary’s Use Set Aggregation property. For example, in the following crosstab, if you specified Total as the summary, the following aggregate expressions are produced when the Use Set Aggregation is set to Yes and No respectively:

a crosstab showing revenue by quarter and month with a total row

Total ([Revenue] within set [Quarter])
This expression totals the quarter values from the data source at the intersecting product line. At the bottom right corner, it totals the aggregate over all product lines for each quarter.
Total ([Revenue] within detail [Quarter])
This expression totals the month values visible in the report at the intersecting product line. At the bottom right corner, it totals all of the intersecting month - product line values visible in the report.

In simple cases, the members and values visible in the report and the aggregate rules in the report are the same as those in the data source and all of these expressions produce the same results.

For example, for the quarter and month values, if you are totaling the values for all months in all quarters, it makes no difference whether the visible values, the values in the cube, or the month and quarter values are used. The result is the same.

a crosstab showing the revenue by quarter and month with a summary row

Tip: The Total Revenue by Country or Region sample report in the GO Data Warehouse (query) package includes a totalsummary function. For more information about The Sample Outdoors Company samples, see Sample Reports and Packages.

Different results appear when you start filtering, changing aggregation types, or using set expressions or unions.

For example, the following crosstab shows the quantity of products sold across all product types for each product line. The bottom of the crosstab has two summary values that show the average quantity of products sold by product line.

a crosstab showing the quantity by product line and product type with two average rows: within detail and within set

Each summary value uses a different expression that is set by the value specified for the Use Set Aggregation property.

Average (Product line) - within detail
This summary is the average of the detail values in the crosstab.
Average (Product line) - within set
This summary is the average of the product type values rolled up into sets at the Product line level. The values are obtained from the data source. If filters or slicers exist, the values are recomputed using the aggregation rules defined in the data source.

In most cases, you should use the within detail expression because the results are easiest to understand and the same as the results for footers in a grouped list report. The within set expression should be reserved for reports with a purely dimensional focus, such as when there are no detail or summary filters defined in the report.