Let me first start by saying this is not a how to or even necessarily the right way to do things with Dimensional Data in Report Studio, rather some options to explore learned from hours spent cursing at the computer trying to get the seemingly ‘simple’ things to work in crosstabs. You know, things like adding two columns together, calculate a variance, or filter out crosstab rows with no data. I run into these issues most when I’m building financial reports where we’re comparing multiple forecast/budget versions across specific spans of time.

​

----The Tuple

​

Love me some Tuple! Whenever I need to pick some data point seemingly out of thin air… the answer usually lies in the form of a tuple. Basically, the tuple function allows you to pinpoint cross sections of dimensions. Note: it only works with data MEMBERS!
Sample: tuple([2009],[Budget])

----Calculating Totals/Variance
For those occasions when clicking the nice little ‘Total’ icon isn’t working for you… don’t be afraid to explore the currentMeasure function and build the total yourself. It will really help you start to understand what the crosstab is trying to do when you click the ‘total’ icon and help you create more robust crosstabs. Some sample syntax:
Sample: total(currentMeasure within set[Forecast]).
If your Forecast is nested in the crosstab you might want to try using within detail.
Sample: total(currentMeasure within detail[Forecast]).
This same logic can be extended to help compute variance.
Sample: total(currentMeasure within detail [Budget]) – total(currentMeasure within detail [Actual])

----Filter crosstab rows.
I recently created a report where I had to show Hours worked by employee by project for the entire company. Many projects had employees that didn’t have any worked hours allocated to a project for a particular time period… yet these employees were still showing up in the report. Naturally, the report ballooned to well over 100 pages. To fix this, I embeded the filter function in a new data item to help filter out the unwanted rows.
Sample: filter([Employee],tuple([2009],[Actual Hours]) >0 or tuple([2009],[Actual/Fcst]) >0)

As a side note to this, in Cognos 8.4 there are options to filter out rows with nulls and zeros, however, I know most of you are still 8.3 or earlier versions. So until you’re on board with 8.4, hopefully this will help you stay afloat!

If you liked this blog on Crosstabs Dimensional Data in Report Studio, then check out this blog on what is Cognos TM1 performance modeler by clicking here.