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:
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.
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 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:
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.
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.
Click the item to which to add a summary.
Click the aggregate button 
and click
a summary type .
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 .
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.
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.
From the Tools menu, click Options.
Click the Report tab.
Click Aggregation mode and select
an aggregation mode .
In crosstabs, aggregated values are calculated using one of the
following aggregate expressions:
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.
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.
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.
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 .
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.
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
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.
Adds all existing values and then divides by the count of existing
values.
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 .
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.
|
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.
|
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.
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.
|
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.
Summarizes data based on an expression that you define.
Selects the largest existing value.
Returns the median value of the selected data item.
Selects the smallest existing value.
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.
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.
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.
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:
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.
Adds all existing values.
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.
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.
|
None specified, or none | Unsupported | None |
average (avg) | Average | Average |
count | Count | Count |
count distinct | Count Distinct | Count distinct |
maximum (max) | Maximum | Maximum |
minimum (min) | Minimum | Minimum |
median | Median | Median |
standard deviation (stddev) | Standard Deviation | Standard Deviation |
sum | Sum | Total |
variance (var) | Variance | Variance |
count non zero | Count Non-Zero | Automatic |
average non zero | unknown | Automatic |
external | unknown | Automatic |
any | unknown | Automatic |
calculated | unknown | Automatic |
unknown | unknown | Automatic |
first_period | unknown | Automatic |
last_period | unknown | Automatic |
The following summary functions exist only in either Framework
Manager or Report Studio. There is no corresponding summary function
in data sources.
|
Automatic | Automatic |
Calculated | Calculated |
No corresponding summary function | Summarize |
No corresponding summary function | Not Applicable |
