Summarizing Data Relationally

Summarize data in your reports to obtain totals, averages, and so on.

In a report, you can add both detail and summary aggregation. Detail aggregation, which is supported only for relational data sources, specifies how a data item is totaled at the lowest level in a report. In lists, detail aggregation specifies how the values that appear in the rows are totaled. In crosstabs, detail aggregation specifies how the values in the cells are totaled. For example, detail aggregation for a measure like Revenue might be Total in both lists and crosstabs. In the following list report, this means that the values you see for the Revenue column represent the total revenue for each product type.

Figure 1. A list showing product line, product type, and revenue

Summaries, which are supported for all data sources, specify how data items are totaled in the headers and footers of a list and in the total rows and columns of a crosstab. For list reports, these summaries only summarize the data that is visible on that page of the report.

You can specify detail and summary aggregation in different ways by using any of the following:

aggregation properties that are specified in the model
the Auto Group & Summarize property
the summarize button in the toolbar
aggregation properties for a data item
summary functions in data item expressions
the solve order of calculations

Tip: When you add a summary by clicking the summarize button in the toolbar, a data item is created in the query for the summary. In lists, the data item that is created is called a list summary. In crosstabs and charts, the data item that is created is called an edge summary.

Limitation

If a summary is applied to a report that contains binary large object (BLOB) data, such as images or multimedia objects, you cannot also perform grouping or sorting.

The Type of Data

How data is aggregated also depends on the type of data that you are aggregating. Aggregation rules are applied differently to facts, identifiers, and attributes. For example, if you aggregate a data item that represents part numbers, the only aggregate rules that apply are count, count distinct, count non-zero, maximum, and minimum. For information about how to determine the type of data that a data item represents, see Add Relational Data to a Report and Add Dimensional Data to a Report.