Summary Functions

This section describes the summary functions that are available in IBM® Cognos® Report Studio. Some functions, such as Custom, are available only when you click the summarize button aggregate icon

on the toolbar. Additional summary functions are available in the expression editor.

Automatic Summary

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.

Note: 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.

Note: 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.

Object	Behavior
Level	`Count distinct` is used. A warning appears when you validate the report.
Member set	`Count distinct` is used. A warning appears when you validate the report.
Attribute	Not supported. An error is returned when you run the report.
Measure	Supported.

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.

Object	Behavior
Level	`Count distinct` is used. A warning appears when you validate the report.
Member set	`Count distinct` is used. A warning appears when you validate the report.
Attribute	Supported.
Measure	Supported.

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.

Object	Behavior
Level	Supported.
Member set	Supported.
Attribute	Not supported. An error is returned when you run the report.
Measure	Not supported. An error is returned when you run the report.

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.

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.

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.