Total currentMeasure within set with a time interval

[RubenvdLinden]

Running Cognos 10.1.1 fixpack 2.

I have a crosstab with weeks and dates on rows, products on columns and two measures nested under products (realisation and planning). Both measures are integers and represent minutes.

I have created automatic row totals for weeks and the report.
I have also manually created 2 members to calculate the daily totals for realization and planning (outer columns):

Code Select Expand

total(currentMeasure within set [Measure name])

This works fine. Everything is calculated correctly.

Now, the business users would like to see a time format (e.g. 6:20) instead of the total amount of minutes (380 for 6:20), so I changed the integer measures like this:

Code Select Expand

cast([Measure name]; interval minute)

I also changed the data formatting to time interval Time, H:MM.

When I run the report, the measures and automatic totals are displayed correctly.
However, the manually created members for the daily totals are all wrong.

I tried changing the solve order to calculate the members last, but this doesn't seem to fix the problem.

Your help will be appreciated.

[MDXpressor]

total (currentMeasure... will use whatever measure is in the current intersection to calculate

within set... specifies the categories by which to total the data

So, not knowing what you've called your set of dates for comparison I will assume you've got a set call [Months]

Try:

Code Select Expand

total(currentMeasure within set [Months])

or to avoid the creation of 'set' data item, you can create your set on the fly:

Code Select Expand

total (currentMeasure within set set([Month1], [Month2])) 

[RubenvdLinden]

The measures are nested columns under Product.

Code Select Expand

total(currentMeasure within set [Measure name]; [Product])

did the trick. I do think it's a bit odd that the integer representation of the measure DID work without [Product].

Thanks for your help!

[MDXpressor]

Code Select Expand

total(currentMeasure within set [b][Measure name][/b]; [Product])

The [Measure name] portion of your statement is redundant at best.  You've covered the applicable measure by stating 'currentMeasure'.  As it moves through the intersections in your Measure columns, each column is isolated to one measure:  currentMeasure.  currentMeasure is dynamic, and picks up whatever measure is in the intersection being calculated. 

What made it work was providing categories from the Product dimension for which it can total.

[RubenvdLinden]

Thank you for this clear explanation!