Scribd
Upload
25 views6 pages

Page561 PDF

ibm cognos proven practice

Uploaded by

Naveen Reddy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
25 views6 pages

Page561 PDF

ibm cognos proven practice

Uploaded by

Naveen Reddy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

IBM Cognos Proven Practices: IBM Cognos BI Using

Dimensional Functions to Determine Current Period


Nature of Document: Tip or Technique; Product(s): IBM Cognos
Report Studio; Area of Interest: Reporting
Jon Appelbergh
Technical Architect
IBM

02 May 2011

A technique using dimensional functions to determine the current period for a multidimensional
data source, OLAP or Dimensionally Modeled Relational (DMR), where the current period has
not been defined in the data source itself.
View more content in this series

Introduction
Purpose

This document describes a technique using dimensional functions to determine the current period
for a multidimensional data source, OLAP or Dimensionally Modeled Relational (DMR), where the
current period has not been defined in the data source itself.

Applicability
This technique uses standard Dimensional Functions available within Report Studio. The
examples in this document were tested against IBM Cognos BI versions 8.4.x, and 10.1 using the
Sales and Marketing sample PowerCube distributed with the product.

Exclusions and Exceptions


This technique applies to multidimensional data sources only (OLAP and DMR) and not to
packages that contain strictly relational sources.

Overview
Report Authors are often faced with OLAP or Dimensionally Modeled Relational (DMR) data
sources where the current period is not defined within the data source. Reports may require the
use of the current period. The method for determining the current period varies depending on how
the cube was built or the DMR was populated.
Copyright IBM Corporation 2011
IBM Cognos Proven Practices: IBM Cognos BI Using
Dimensional Functions to Determine Current Period

Trademarks
Page 1 of 6

developerWorks

ibm.com/developerWorks/

If the data source's time dimension ends at the current period, then an author can simply use
the closingPeriod dimensional function to obtain the last period for any given level in the time
hierarchy. If the data source was built with a time dimension that has future periods, then
determining the current period can be accomplished with a filter if the data contains a measure that
has data that only goes up to the current period.
This document will illustrate these two scenarios.

Example 1 Find Current Period using closingPeriod Function


This example may be used for multidimensional data sources where the current period is the last
period that exists in the Time Dimension.
If this is the case, the dimensional function called closingPeriod may be used.
For example, the last member of the Year level in the Time dimension is 2007, as shown in
Illustration 1.

Illustration 1: Sales and Marketing (cube) showing the year 2007 as the last
member of the Year level in the Time dimension

The closingPeriod function can be used to return the year 2007 if you provide the Year level as the
argument for the function as seen below and in Illustration 2.
closingPeriod([sales_and_marketing].[Time].[Time].[Year])

IBM Cognos Proven Practices: IBM Cognos BI Using


Dimensional Functions to Determine Current Period

Page 2 of 6

ibm.com/developerWorks/

developerWorks

Illustration 2: Data Item Expression dialog showing closingPeriod function


with Year level

The result of this function returns the 2007 member from the Year level as shown in Illustration 3.

Illustration 3: Cognos Viewer showing the 2007 member returned from the
Year level of the Time dimension

This function can be used for any level within the dimension. For example, you can use the Month
level as shown below.
closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])

In this example, when the Month level is used, the 2007/Dec member is returned as seen in
Illustration 4.

Illustration 4: Cognos Viewer showing the 2007/Dec member returned from the
Month level of the Time dimension

Example 2 Find Current Period by Filtering on Measure Data


If the OLAP or DMR data source has been populated with time periods into the future (e.g. end of
year or future years), then the calculation of current period is more complicated. However, it can
still be determined by finding the latest period that has data for a given measure.
IBM Cognos Proven Practices: IBM Cognos BI Using
Dimensional Functions to Determine Current Period

Page 3 of 6

developerWorks

ibm.com/developerWorks/

For example, the Sales and Marketing (cube) sample has members in the Month level from
January to December for the year 2007, but only has revenue values up to July of 2007 as seen in
Illustration 5.

Illustration 5: Crosstab report showing Revenue values ending in July of 2007,


but months up to December 2007

In this example, July of 2007 would be considered the current period.


You can create a Current Period data item that uses the following expression to determine the
current period based on the Revenue measure.
item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),
tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time]))
is not null), 1), 0)

The result of the Current Period data item in this example returns the 2007/Jul member as seen in
Illustration 6.

Illustration 6: Crosstab report showing only data for the 2007/Jul member

The individual parts of this expression are described below:


1. Get the set of members from the Time dimension at the Month level.
members([sales_and_marketing].[Time].[Time].[Month])

2. Filter the set of Months to find the members that have data for a measure you know only goes
up to the current period, in this case Revenue.
filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue],
currentMember([sales_and_marketing].[Time].[Time])) is not null)

This allows you to display any measure or measures you wish in the report regardless if they
go beyond the current period (sales targets for example). You could also use currentMeasure
in the expression, but you would need to ensure that it is the default measure of the crosstab
or chart report and that the data for that measure does not go beyond the current period.
3. Get the last month that has data. The tail function returns a member set. In this case only one
member is returned since the integer argument is 1.
tail(filter(members([sales_and_marketing].[Time].[Time].[Month]), tuple([Revenue],
currentMember([sales_and_marketing].[Time].[Time])) is not null), 1)
IBM Cognos Proven Practices: IBM Cognos BI Using
Dimensional Functions to Determine Current Period

Page 4 of 6

ibm.com/developerWorks/

developerWorks

4. Because the desired result is a single member, the item function is used passing 0 as its
index value argument to return only the current period member.
item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),
tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time])) is not null),
1), 0)

Returning a member is important if you want to use this data item in another dimensional
function. For example, you may want to display the current period and the same period from
the previous year using the parallelPeriod function. In this case, your Current Period data item
must return a member since the parallelPeriod function expects a member as an argument as
shown below.
parallelPeriod([sales_and_marketing].[Time].[Time].[Year], 1, [Current Period])

IBM Cognos Proven Practices: IBM Cognos BI Using


Dimensional Functions to Determine Current Period

Page 5 of 6

developerWorks

ibm.com/developerWorks/

About the author


Jon Appelbergh
Jon Appelbergh is a Technical Architect in the FSS practice of BAO. He has 28 years
experience in the IT field in a variety of capacities and industries. For the past 9 years
Jon has been developing Cognos solutions for customers in Finance, Insurance,
Pharmaceuticals and the US Army.
Copyright IBM Corporation 2011
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)

IBM Cognos Proven Practices: IBM Cognos BI Using


Dimensional Functions to Determine Current Period

Page 6 of 6

You might also like