Summarizing Data Dimensionally

Summarize data in your reports to obtain totals, averages, aggregates, 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 aggregate that is used is specified in the data source itself. As a result, we recommend that you use the Aggregate 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 summary aggregation, which is supported for any data source. It specifies 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 summary aggregates only summarize the data that is visible on that page of the report.

You can specify 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 aggregate button in the toolbar

• aggregation properties for a data item

• the aggregation mode

• the solve order of calculations

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.

Limitations on Measure Rollups

For all OLAP data sources except 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.

Add a Simple Summary

You can add simple summaries in a report by using the aggregate button. This button provides a subset of the summary functions available in Report Studio.

The aggregate button sets the rollup aggregate property for the data item to the selected summary aggregate, and places the data item into an appropriate footer. A footer is created for each set, hierarchy, or level.

For information about adding a rolling or moving average, see Rolling and Moving Averages.

Steps

1. Click the item to which to add a summary.

2. Click the aggregate button  and click a summary type .

3. To change the summary label, do the following:

   • Click the label.

   • In the Properties pane, under Text Source, set the Source Type property to the source type to define the label.

     For example, set it as Data Item Value to produce a dynamic label for the summary based on data item values.

   • Set the property below Source Type to specify the label.

     This property depends on the source type you chose. For example, if you chose Data Item Value as the source type, set the Data Item Value property to the data item to use to define the label.

In crosstabs and charts, the summary appears as a node .

Tips

• To change a summary, select it and, in the Properties pane, under Data Item, click Rollup Aggregate Function and choose a different function.

• In crosstabs, you can add multiple summaries at the same level. For example, you have a crosstab with Product line as rows, Order year as columns, and Revenue as the measure. For Product line, you can add the Total summary as a header, which will total all revenue for each order year. You can then add the Average summary as a footer, which will give the average revenue of all product lines for each order year.

Specify the Aggregation Mode

If you are working with a dimensional or a dimensionally-modeled relational (DMR) data source, you must specify the aggregation mode . The aggregation mode sets the aggregation clause to be used in the data item expression.

Steps

1. From the Tools menu, click Options.

2. Click the Report tab.

3. Click Aggregation mode and select an aggregation mode .

Aggregating Values in Crosstabs

In crosstabs, aggregated values are calculated using one of the following aggregate expressions:

• aggregate ([measure within set [data item]) aggregates the member values from the data source within the current content.

• aggregate ([measure within detail [data item]) aggregates the lowest level of details in the report.

• aggregate ([measure within aggregate [data item]) aggregates each level of details in the report.

You decide which aggregate expression is used by setting the aggregation mode .

For example, in the following crosstab, if you specified Total as the summary, these aggregate expressions are produced for each aggregation mode:

• 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.

• Total ([Revenue] within aggregate [Quarter])

  This expression totals the month values visible in the report at the intersecting product line into quarters. At the bottom right corner, it does the same, but starting with the aggregate over all product lines for each month.

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.

Tip: The Total Revenue by Country sample report in the GO Data Warehouse (query) package includes a total summary function. For more information about The Great 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 three summary values that show the average quantity of products sold by product line.

Each summary value uses a different aggregation mode that is indicated in the summary name.

• Average(Product line) - within detail

  This summary is the average of the detail values in the crosstab.

• Average(Product line) - within aggregate

  For each product line, the average of the detail values is calculated. This is equivalent to applying the average aggregation function to the Product type column. This summary is the average of the product type averages.

• 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 aggregation mode because the results are easiest to understand and the same as the results for footers in a grouped list report. In more complex cases, you may consider the within aggregate aggregation mode. The within set aggregation mode should be reserved for reports with a purely dimensional focus such as when there are no detail or summary filters defined in the report.

Aggregating Member Sets

When you work with dimensional data sources and you aggregate member sets, if an explicit summary function such as Total is used and the set contains duplicates, the result is double-counted. If you are using an OLAP data source, the result produced for the automatic summary function depends on the data source.

For example, the product line rows below were defined using the expression

union([Product line], [Camping Equipment], ALL)

where [Product line] is the level that contains Camping Equipment.

For IBM Cognos PowerCubes, Aggregate(Product line) is the sum of the product lines excluding duplicates. For more information about how the aggregate function is processed, see Summary Functions.

Limitations When Summarizing Measures in Dimensionally-modeled Relational (DMR) Data Sources

There are limitations when summarizing DMR measures and semi-additive measures in crosstabs using the aggregation function count distinct, median, standard-deviation, or variance. The following limitations can produce empty or error cells when the report is run:

• The aggregation function must apply to all members of a level or all children of a member.

• To use OLAP functions in detail filters that are applied to a dimension that is not in the report, or is at a level below what is being reported, only the functions children, level, members, roleValue, and rootMembers will work.

• You cannot define detail filters that reference one or more measures and are set to After auto aggregation.

• You cannot define context filters that have more than one member from a dimension that does not appear in the report.

• Context filters that have more than one member from a dimension that appears in the report produce errors in all cells that are ancestors of the slicer members.

• If a crosstab has a row that is a set of members from one dimension (dimension A) and another row that is a set of members from another dimension (dimension B), and a context filter containing members from dimension A is defined, error cells are produced in the row that contains members from dimension B.

• If a context filter contains members from a dimension, and a crosstab has a row that is a set of members from a higher level than the slicer members, error cells are produced for that row.

• Error cells are produced when drilling down on a crosstab that has two nested levels.

If you do not consider these limitations in a calculation, the report may return inaccurate results.

If there is a non-measure calculation that returns a constant or contains a summary function, and the calculation has a lower solve order than the measure being aggregated, error cells are returned for the aggregated measure.

In list reports, error cells are produced as a result of these limitations if the list uses an OLAP function other than children, filter, level, members, roleValue, and rootMembers.

Summary Functions

This section describes the summary functions that are available in Report Studio. Some functions, such as Custom, are available only when you click the aggregate button  on the toolbar. Additional summary functions are available in the Expression Editor .

Aggregate

In lists, sets the summary function to Automatic. In crosstabs, sets the Rollup Aggregate Function to Automatic. The Aggregate function uses the summary rules for Automatic.

Automatic

Depending on the type of data item, applies the function None, Summarize, or Calculated based on the context in which the data item appears.

Calculated is applied if the underlying data source is OLAP. It is also applied if the data item expression

• contains a summary function

• is an if-then-else or case expression that contains a reference to at least a modeled measure in its condition

• contains a reference to a model calculation or to a measure that has the Regular Aggregate property set to a value other than Unsupported

• contains a reference to at least one data item that has the Rollup Aggregate Function property set to a value other than None

If the underlying data source is relational and if the data item expression contains no summary functions and a reference to at least one fact that has the Regular Aggregate property set to a value other than Unsupported, Summarize is applied.

If the underlying data source is OLAP and if the data item expression contains no summary functions and a reference to at least one fact that has the Regular Aggregate property set to a value other than Unsupported, Calculated is applied.

If the underlying data source is SAP BW, reports containing aggregations and summaries run more efficiently if the aggregation applied to a query item matches the aggregation rule for the underlying key figure on the SAP BW server. In Report Studio, the easiest way to accomplish this is to change the value of the Aggregate Function property to Automatic.

In all other contexts, None is applied.

For relational and dimensionally modeled relational (DMR) data sources, if this function is applied to a data item expression that has the average function, weighted averages are computed based on the lowest level detail values in the data source.

This is the default function.

Average

Adds all existing values and then divides by the count of existing values.

Calculated

Specifies that all the terms within the expression for a data item are aggregated according to their own rollup rules, and then the results of those aggregations are computed within the overall expression.

For example, a list contains the data item Quantity with the Aggregation property set to Total. You add a query calculation named Quantity Calculated. You define its expression as Quantity + 100 and you set its Aggregation property to Calculated. When you run the report, the values for Quantity Calculated are computed by first adding all values for Quantity and then adding 100 to each value.

In crosstabs, this function overrides any solve orders that are specified .

Count

Counts all existing values.

If the underlying data source is OLAP, Count behaves as follows if it is specified in the Rollup Aggregate Function property for a data item.

If the underlying data source is dimensionally modeled relational (DMR), Count behaves as follows if it is specified in the Aggregate Function or Rollup Aggregate Function property for a data item.

If you add the Count summary to a non-fact column in a list and then group the column, the column will not be grouped when you run the report. To resolve this issue, group the column first before adding the Count summary.

Count Distinct

Returns the total number of unique non-null records.

If the underlying data source is OLAP, Count Distinct behaves as follows if it is specified in the Rollup Aggregate Function property for a data item.

If the underlying data source is dimensionally modeled relational (DMR), Count Distinct is supported for levels, member sets, attributes, and measures when it is specified in the Aggregate Function or Rollup Aggregate Function property for a data item.

Custom

Summarizes data based on an expression that you define.

Maximum

Selects the largest existing value.

Median

Returns the median value of the selected data item.

Minimum

Selects the smallest existing value.

None

Does not aggregate values.

If the underlying data source is relational, the data item is grouped when the query property Auto Group & Summarize is set to Yes.

Not Applicable

This function is available only for the Aggregate Function property. It specifies that the aggregate attribute is ignored. For example, the calculation will be applied after the data is aggregated.

This function differs from the Calculated function, which applies aggregation to the rows and then performs the calculation.

For example, for the average([Quantity])+5 expression, when the Aggregate Function property is set to Not Applicable, five is added to the average of the single Quantity value. When the function is set to Calculated, five is added to the average of a set of numbers.

This setting should not be applied to a simple model reference.

This setting is relevant only for reports that are upgraded from IBM Cognos ReportNet^® 1.x.

Standard Deviation

Returns the standard deviation of the selected data item.

From a mathematical perspective, this function is not useful for small numbers of items and is not supported if the query property Auto Group & Summarize is set to Yes.

Summarize

Aggregates data based on model or data type information. This function can be thought of as a calculate and then aggregate rule. Data is aggregated using the following rules:

If the underlying data source type is relational and the data item or calculation is a reference to a single fact query item in the model that has the Regular Aggregate property set to Sum, Maximum, Minimum, Average, or Count, aggregate data using this function. Otherwise, data is aggregated according to the data type of the data item as follows:

• Total is applied for numeric and interval values.

• Maximum is applied for date, time, and date-time values.

• Count is applied for everything else.

The underlying data source type can be dimensional and the data item or calculation a reference to a single item in the model. In this case, if the Regular Aggregate property of the model item is not unknown, the summary function that corresponds to the Regular Aggregate property is applied. If the Regular Aggregate property is unknown, set the function to Calculated.

For example, a list contains the data item Quantity with the Aggregation property set to Average. You add a query calculation named Quantity Summarize. You define its expression as Quantity + 100 and you set its Aggregation property to Summarize. When you run the report, the values for Quantity Summarize are computed by first adding 100 to each value for Quantity and then calculating the total. The total is calculated because Quantity contains numeric values and the expression for Quantity Summarize is not a simple data item reference. If Quantity Summarize is defined as Quantity, the function Average is applied to each value.

Total

Adds all existing values.

Variance

Returns the variance of the selected data item.

Note: From a mathematical perspective, this function is not useful for small numbers of items and is not supported if the query property Auto Group & Summarize is set to Yes.

Mapping Aggregation Functions From Data Sources to Framework Manager and Report Studio

The summary functions available in Framework Manager and Report Studio reflect summary functions supported by relational and dimensional data sources. The following table shows how summary functions in data sources are mapped to Framework Manager and Report Studio.

The following summary functions exist only in either Framework Manager or Report Studio. There is no corresponding summary function in data sources.