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

Comments

Content series:

This content is part # of # in the series: IBM Cognos Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Cognos Proven Practices

Stay tuned for additional content in this series.

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])))

Downloadable resources


Comments

Sign in or register to add and subscribe to comments.

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