Submitted by Dan Wagemann, Proven Practices Team
This article 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.
Although the techniques outlined in this document may also apply to IBM Cognos 8.4 and IBM Cognos 8.4.1. The technique was validated using IBM Cognos 10.
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.
A calculated measure query calculation named Running-Total is inserted into the crosstab next to the Cost column. This calculated query calculation consists of the following expression:
total([Cost] within setIf this report is executed within IBM Cognos Viewer, the IBM Cognos Viewer displays a crosstab with the Outlet as rows along with the Cost measure and Running-Total calculated measure as columns. The Running-Total adds 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.The Running Total Calculation Dissected
The following section provides a breakdown of the Running Total calculation.
The currentMember provides the context of the given row. Loosely translated in this means at each row.
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
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]