# 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:

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

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.

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