Summarize data and create calculations - IBM Cognos

IBM Cognos Business Insight Advanced provides a range of summarization functions and calculations that can be applied to reports to help advanced business users get the best insight from their data.

Summarization

IBM Cognos Business Insight Advanced provides the following summarization functions to users:

  • Total
  • Count
  • Average
  • Minimum
  • Maximum

The first calculation applied depends on what is set on the data model. Nonetheless, despite it, users can create new summarized columns and rows using these functions if it makes sense to them.

To apply a summarization function, follow these steps:

  1. Click one member of a set or data item (depending on the type of data source that you use) to create a summarization column or row.
  2. On the top toolbar, click the Summarize icon.
  3. Click the desired summarization function .
  4. Inserting a summarization column
    Inserting a summarization column

    After you follow these steps, a new column is created on the right side of the crosstab with the Average title

    Report after an Average summarization function is applied
    Report after an Average summarization function is applied

Create reports using a dimensional data source

Reports that are based on a dimensional data source always apply summarization to sets. If the user does not turn on the Create Sets for Members option in the Insertable Objects pane the user is unable to insert additional summarization columns and rows.

In the case of relational data sources, the summarization functions are applied to the nodes of the crosstabs, charts, and to the entire list.

Create Sets for Members option

Create Sets for Members option

Calculation

IBM Cognos Business Insight Advanced provides several calculations for users. Several of the calculations that are available for relational and dimensional data sources differ.

Calculations available for relational data sources

When using a relational data source, IBM Cognos Business Insight Advanced allows users to deal easily with strings and remove blank spaces (remove trailing spaces), as well as truncate blank spaces based on a number of characters (First ? characters, Last ? characters).

If the columns or rows are measure items, users can apply a range of calculations, such as addition, subtraction, multiplication, and division of a column, Absolute,Round,Round up, Round down, percentage,and difference. Several of these calculations require a number value if the calculation involves one measure only.The number can be provided when the user clicks the Custom option.

List report showing truncate, percent, and round calculations

List report showing truncate, percent, and round calculations

Crosstab report showing addition calculation

Crosstab report showing addition calculation

Calculations available for dimensional data sources

When using dimensional data sources, IBM Cognos Business Insight Advanced provides a capability to perform calculations across rows and columns, which is an advantage for the dimensional approach. Users can calculate the percent of the difference between the first product line in terms of revenue and all other product lines easily.

If the columns or rows are a measure, users can apply a range of calculations, such as addition, subtraction, multiplication, and division of a column, percentage, and difference. Several of these calculations need a number value, which is set when the user clicks the Custom option.

When using dimensional data sources, IBM Cognos Business Insight Advanced does not display the calculations for string.

Figures show examples of how to work with the calculations.

Revenue percent of Base (Revenue, Personal Accessories) calculation

Revenue percent of Base (Revenue, Personal Accessories) calculation

Percent difference (Planned revenue, Revenue), division, and subtraction calculations

Percent difference (Planned revenue, Revenue), division, and subtraction calculations

Example

Lynn Cope, an Advanced Business User of the Great Outdoors company, wants to make a quick comparison between the top product type in the Personal Accessories product line and the other product types in the same line using a dimensional data source.

To create a percent of base calculation, she follows these steps:

  1. Create a simple report that groups Revenue by Product types under the
    Personal Accessories product line using a crosstab.
  2. Simple report
    Simple report

  3. Click the Revenue measure.
  4. On the top toolbar, click the Sort icon.
  5. Click Descending
  6. Sorting the Revenue column in descending order
    Sorting the Revenue column in descending order

  7. Click the Revenue measure and press Ctrl.
  8. Click the first member of the Rows area (Eyewear), right-click one of the selected items, click Calculate, and then click % of Base (Revenue,Eyewear),
  9. Creating a percent of Base calculation
    Creating a percent of Base calculation

    After performing these steps, Lynn can see the percent of the Revenue of all product types against the Eyewear revenue

    Percent of Revenue of all types against Eyewear type Revenue
    Percent of Revenue of all types against Eyewear type Revenue


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Cognos Topics