Hi all,
I am trying to create a cube where it is possible to choose a period and get the YTD-value or Running-total 12 for that particular period next to the periods actual values so to say. As far as I know the YTD and other custom calculations in the time period dimension is only relative to the current period. What is needed is a report on this cube that shows a crosstab with for example revenue, budget, ytd, running-total 12 on the columns for any period chosen in a prompt page. I would be glad if this is possible to do in a smart manner in the cube instead of in the database.
How would you sort this out?
thanks in advance
It depends what you are using to build the report. If you are using Cognos 8 Report Studio, then this is fairly straightforward in the report. As your 'current' date is prompted for, there is no way to build this into the cube though.
MF.
Hello,
Tagging along. I have the same requirement as well. The report studio report uses a powerplay cube. What function can I use to display, let's say, three months before the year-month that was prompted?
Since this uses a powerplay cube, I should be able to use a dimensional function I presume?
Thanks for your help.
Hi Arvind,
Yes - a dimensional expression will be the easiest and most efficient way to do this. If you want to see the prompted-for period and the three immediately preceding periods, use an expression such as:
lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?)
Regards,
MF.
Hi,
this is great! thanks. Also how do i get the measure for that period?
For example if I want to show total_sales_amount for that period what is the syntax should i use?
Thanks again
Arvind.
this is what I got so far, but does not seem to return the result that I am looking for:
tuple(parallelPeriod ([PCG QOB Homeowners Cube].[Calendar].[Calendar].[Month],-1,[PCG QOB Homeowners Cube].[Calendar].[Calendar].[Month]->?Month?), [Inforce Premium])
Quote from: Arvind on 22 Apr 2010 09:24:40 AM
Hi,
this is great! thanks. Also how do i get the measure for that period?
For example if I want to show total_sales_amount for that period what is the syntax should i use?
Thanks again
Arvind.
How about:
aggregate(currentMeasure within set lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?))
or
aggregate([Sales Amount Measure] within set lastPeriods(4,[Your Period Level from the Time Hierarchy] -> ?Period?))
Depends if Sales Amount Measure is the only default measure, or whether you want to do this for multiple measures.
Regards,
MF.
MF:
Worked like a charm. thank you for being such a big help.
Arvind.
You're welcome! :)