Ironside Tech Tip: Leveraging Time and Aggregate Functions in Dimensional Reporting to Create Low Maintenance Cognos Reports

Over the years I have successfully used the ancestor(), parallelPeriods(), periodToDate(), aggregate(), and set() dimensional functions to develop robust and durable dimensional reporting solutions in IBM Cognos BI.

I had the opportunity to go to lunch the other day with one of my clients from a few years ago and they told me that the profit and loss reports that I wrote for them were still in use. These reports used no year or month members (directly) and required little if any maintenance over the last two years, despite daily refreshes of the source PowerCube.

If you have ever needed (within a multi-dimensional model) a comparison of current year to a past year, or past year to date with current year to date, or other combinations, here is a solution that I have used successfully. Please note that these functions and techniques will work with any Cognos multi-dimensional source, including PowerCubes, TM1, or DMR.

Assumptions: The consumer of this article should have an intermediate understanding of dimensional reporting, models, and Report Studio, including how to select a crosstab and the basic use of query calculations.

Here are the comparisons we will derive:

• Two years in the past, totaled
• Previous year, totaled
• Previous year to date, totaled
• Current year to date, totaled

We’ll build the following sample report beginning with the 2012 column, which is back two years from the current year 2014:

The prompt:The report output:
Two years prior query calculation:

Features of this formula:
• The prompt provides a month value from the Time dimension for determining the starting point for our period reporting.
• The prompt value selected satisfies the first parameter for parallelPeriod with a lag or offset of two. It could be a positive or a negative depending on the need.
• Ancestor bumps us up to the Year level. Please note the 2 offset, which will navigate us up to the Year level an skips over the Qtr.
Previous year query calculation:
In the previous year calculation, we use a similar formula except we only need a one year off set.

The output: Now, let’s leverage what we have done with parallelPeriod by adding year to date capability utilizing the periodsToDate() function. We’ll also introduce the aggregate function.

• PeriodsToDate allows us to capture only periods that have happened.
• Parallel periods, as before, determine the offset for the number of years you wish to navigate to. In this case 1.
• The aggregate function allows us to total whatever measure(s) is in scope. We will take a deeper dive in the next section.

Anatomy of the aggregate() function:

Members who’s intersections with measures that will be totaled.

• Current measure is the default measure in this report. This can also be used as an outer column edge with measures nested beneath it, to calculate more than one measure at a time.
• Set is a dimensional function used to determine the periods (months) we want to total.
• PeriodToDate provides the months that will make up the members in the set.
• As in previous example, parallelPeriod is used to jump to another year.
• A prompt is used to set the starting point of our date calculations.

Current year calculation:

• No parallel period is needed as we are only concerned with the current year.
• Aggregate, currentMeasure, within set logic is used to allow for summing of the periodsTodate set.

Finished report output:
As you can see from the examples, these formulas can open up many possibilities for dimensional reporting. They are especially useful in P&L reporting and financial reporting in general. And because we are not using any members directly, we can put this method to use and it will not require much if any maintenance when the multi-dimensional source changes over time.

All of the functions described in this article can be found on the IBM website:
http://www.ibm.com/developerworks/data/library/cognos/page82.html