IBM Cognos Proven Practices: Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-Total

Product(s): IBM Cognos 10; Area of Interest: Reporting

A description of how the dimensional function periodsToDate can be used outside of a time dimension to calculate a running total against on online analytical processing (OLAP) data source.

Share:

Business Analytics Proven Practices Team, Business Analytics Proven Practices Team, IBM

Business Analytics Proven Practices Team



24 May 2012 (First published 13 May 2011)

Also available in Russian

Introduction

Purpose

This document describes how the dimensional function periodsToDate can be used outside of a time dimension to calculate a running total against on online analytical processing (OLAP) data source.

Applicability

Although the techniques outlined in this document may also apply to IBM Cognos 8.4 and IBM Cognos 8.4.1. The document was validated using:

  • IBM Cognos 10

Caveats

This technique was created as a proof of concept. Any implementation of any part of this technique will need to be thoroughly tested before being applied in any type of production environment.


The periodsToDate Function

The periodsToDate function Returns a set of sibling members from the same level as a given member, as constrained by a specified level.

It locates the ancestor of "member" at "level", and returns that ancestor's descendants at the same level as "member", up to and including "member".

Syntax:

periodsToDate ( level , member )

Example:

periodsToDate([great_outdoors_company].[Years].[Years].[Year], [2004/Mar] )

result: returns the value for [2004/Jan], [2004/Feb], [2004/Mar]

Although typically used with the time dimension, the periodsToDate function can also be used to calculate an OLAP running total.


The Running Total Calculation

For this example, a base crosstab was created against an IBM Cognos Power Cube. The crosstab consists of Outlet on the row edge and the Cost measure on the column edge. The Cost measure has been formatted as currency to two decimal places. When executed in the IBM Cognos Viewer, the report displays as the following image.

Figure 1 IBM Cognos Viewer displaying a simple crosstab with Outlet on the row edge and the Cost measure on the column edge
Figure 1 IBM Cognos Viewer displaying a simple crosstab with Outlet on the row edge and the Cost measure on the column edge

A calculated measure query calculation named Running-Total is inserted into crosstab next to the Cost column. This calculated query calculation consists of the following expression:

total([Cost] within set
  periodsToDate([great_outdoors_company].[State].[State].[State],
  currentMember([great_outdoors_company].[State].[State])))

If this report is executed within IBM Cognos Viewer, the IBM Cognos Viewer will now display a crosstab with the Outlet as rows along with the Cost measure and Running-Total calculated measure as columns. The Running-Total will add up all the costs up to each of the Outlet rows. This is illustrated by the following image.

Figure 2 IBM Cognos Viewer display the original crosstab with the Running Total calculation
Figure 2 IBM Cognos Viewer display the original crosstab with the Running Total calculation

The Running Total Calculation Dissected

The following section provides an inside out breakdown of the Running Total calculation.

currentMember([great_outdoors_company].[State].[State])

The currentMember provides the context of the given row. Loosely translated in this means at each row.

periodsToDate([great_outdoors_company].[State].[State].[State],
  currentMember([great_outdoors_company].[State].[State]))

The periodsToDate function coupled with the currentMember provides a cumulative set at each row. In this case, at row 1 the set would be [San Diego]. Row two would be [San Diego] and [San Jose].

total([Cost] within set 
  periodsToDate([great_outdoors_company].[State].[State].[State],
  currentMember([great_outdoors_company].[State].[State])))

The total function within set adds the measure for each of the cumulative sets. In this example the behind the scenes total calculation for row 1 would be total([Cost] within set [San Diego]. As before row two would add the next member to the set making the calculation total[Cost] within set [San Diego],[San Jose].

The Running Total Calculation with Parent Child Hierarchies

In some cases the OLAP data sources will have parent child hierarchies which do not visually expose the levels. In these cases the calculation can be adapted to the following expression which obtains the levels using the levels function.

total([Cost] within set
  periodsToDate(levels([great_outdoors_company].[State].[State],0),
  currentMember([great_outdoors_company].[State].[State])))

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics, Information Management
ArticleID=658179
ArticleTitle=IBM Cognos Proven Practices: Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-Total
publish-date=05242012