Summarizing Data Dimensionally

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

The summary options that you can use depend on the type of data source that you are using. If you are querying an OLAP data source, all measure values that appear in reports are pre-summarized because the data source contains rolled up values. The type of summary that is used is specified in the data source itself. As a result, we recommend that you use the Automatic summary when creating dimensional style reports. This ensures that the report always uses the type of summary that the data modeler specified in the data source.

For example, the modeler may have specified that the rollup for revenue is total and the rollup for stock prices is average.

If you use other types of summaries with dimensional style reports, you may encounter unexpected results.

You can also add summaries, which are supported for any data source. Summaries 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.

Figure 1. Crosstab showing automatic summaries for rows and columns
a crosstab showing revenue by product line by year with a total column and a total row

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

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 a dimensional 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 summarized also depends on the type of data that you are summarizing. Summary rules are applied differently to facts, identifiers, and attributes. For example, if you summarize a data item that represents part numbers, the only summary 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.

For information about how null or missing values are handled in summaries, see Null (Missing) Values in Calculations and Summaries.

Limitations on Measure Rollups

For all OLAP data sources except IBM® Cognos® PowerCube and Microsoft SQL Server 2005 Analysis Services (SSAS), aggregation and re-aggregation are supported only for calculations and measures that use the following rollups: Sum (Total), Maximum, Minimum, First, Last, and Count.

All other types of rollup either fail or return error cells, which typically appear as two dash characters (--).

This problem occurs in, but is not limited to, the following:

  • footers
  • aggregate function
  • context filters that select more than one member of a hierarchy that is used elsewhere on the report

Summarizing Sets

When working with Microsoft SQL Server Analysis Services (SSAS) data sources, we recommend that you not summarize values for sets which contain members that are descendants of other members in the same set. If you do so, SSAS double-counts values for automatic summaries and all data sources double-counts values for explicit summaries.

Parent topic: Dimensional Reporting Style
Related concepts:
Limitations When Summarizing Measures in DMR Data Sources