If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

aggregate currentMeasure and a calculated expression

Started by RubenvdLinden, 13 Nov 2010 05:33:42 AM

Previous topic - Next topic

RubenvdLinden

My boss needs a budget report for 2011.

The accounts he wants to see are in level 3 to 5 of a dimension hierarchy, beneath the level 2 member 'Profits and Losses'. I made a crosstab row dataitem 'T-account':

descendants (Profits and Losses]; [Cube Financial Budgets].[T-account].[T-account].[T-account 6]; before)

I set the dataitem to display as hierarchized. This works fine. Next, I made crosstab column dataitems for 2011 and 2010 and inserted 'Budget amount' and 'Budget fte' as nested measures (column items beneath 2011 and 2010). Finally, I inserted a row for the totals: aggregate(currentMeasure within detail).
So far so good.

However, my boss also wants to see a column with the expected actual results for 2010, based on the YTD results until a selected month extrapolated to 12 months. I used the periodsToDate function to determine the YTD, then I created a dataitem 'Expected result':

total([Cube Financial Budgets].[Measures].[Actual cost] within set [YTD]) /
count([Cube Financial Budgets].[Measures].[Actual cost] within set [YTD]) * 12

This column dataitem works fine as a measure in the detail row, but it returns strange results in the totals row (aggregate(currentMeasure within detail)). I tried to set the Rollup Aggregate Function to Total, but this returns 3 times the expected results (I suspect that's a total for each level in the hierarchy).

What am I missing? Any help would be appreciated.