[RS] Identifying Current Period From an OLAP Source

[MDXpressor]

Hi All,
I promised long ago to provide a calc that would dynamically identify the current period (without using prompting or building any special Time heirarchies on the cube).  First a disclaimer:  I have not tested this against anything other than Cognos Powercubes, and certainly not against a ROLAP source.  As well it should be said that this will actually pick up the last period with data, and not necissarily the Current Period.  However since typical cubes are built using historical records, this solution should work in most cases.

The other important factor in all of this is to ensure that the calc returns a member and not a set as most Relative Time calcs require a single member to start from.

We need to use several nested calculations to perform this task. 1st we need to filter our data to only those months with active data (i.e. no unpopulated periods since a cube will normally create all the periods for a fiscal year as soon as that fiscal year has any data against it at all).

filter([Cube].[Date Dimension].[Date Heirarchy].[Date Level], [Measure] <>0)

This filter obviously reduces the set of members to only those who have measures against them.  Next we want to ensure that we are only getting the last member; we accomplish this using the TAIL function:

tail(filter([Cube].[Date Dimension].[Date Heirarchy].[Date Level], [Measure] <>0),1)

So now we have a set of 1 member which was the last member to have Measures against it.  Now we need to isolate that member from the set and provide to the query as a member.  Use the ITEM function for this, remember that the ITEM funtion is zero based, so to get the 1st member from a set, you must set the index to 0 not 1:

item(tail(filter([Cube].[Date Dimension].[Date Heirarchy].[Date Level], [Measure] <>0),1),0)

Have a crack at this calculation, let me know how it works for you and your clients.  Too complicated?  Got a cleaner solution?  Let's discuss!

[blom0344]

I am not at all familiar with Cognos cubes (having worked with analysis services 2000 and offline cubes before) and remember I added time-hierarchies by using a special time table.
Can you comment on what type of report could benefit from our construction? Is it a means to restrict data to the last period?

[MDXpressor]

Actually, the point of this is to identify the Current Period as a member (instead of a set) without having to prompt the user for it.  This allows you to be able to perform calculations against Current Period using other functions such as ParallelPeriod, or PrevMember, NextMember, PeriodsToDate...

Using the Relative Time heirarchies (Current Month, QTD, YTD, ...) still have their place, especially outside of Report Studio where you don't have the luxury of utilizing the OLAP functions. 

[Andrei I]

Unfortunately Cognos does not support OLAP functions with DMR :-(

===============================================
Cannot Specify the Order of Members When Defining a Level  In the current release, you cannot specify the order of the members when defining a level for a regular dimension on a relational data source. This limitation results in the following functions returning members who are dependent on the sort order in which the data was retrieved:

firstFromSet

remainderSet

head

item

lag

lead

nextMember

prevMember

lastPeriods

closingPeriod

openingPeriod

parallelPeriod

periodsToDate

subset

cousin

firstChild

firstSibling

lastChild

lastSibling

We recommend that you do not use these functions when using dimensionally modeled relational data sources. To ensure that report authors do not use these functions, use Quality of Service to exclude them from the functions list.

491332

Topic from: Cognos 8 Business Intelligence 8 Version 8.2 Readme