Quaterly, YTD data using dimensional model

[pooja]

Hi all,

We are building the crosstab report with below requirements-

prompt selection - ? Select_Month?

report columns:

1st col- [Actual] data for prompt selected month

2nd col- [Budget] data for prompt selected month

3rd col- [Actual] Quarterly data for prompt selected month. Ex- if we select in between Jan-March then Q1. If we select in between April-June then Q2......and so on.....

4th Col- YTD data for data for prompt selected month

Please help how to achieve this. We are using dimensional model.

Much appreciate your help on this.


P

[MFGF]

Hi,

You would probably use a default measure of [Actual] in your crosstab

The first column would be a query calculation:

[your month level] -> ?Your month parameter?

The second column would be a tuple of budget and the month:

tuple( [your month level] -> ?Your month parameter?, [Budget] )

Assuming you have a time hierarchy where the selected month has a parent at the quarter level, for the third column you simply need to use a parent() function to get the quarter member the month belongs to.

eg

parent( [your month level] -> ?Your month parameter? )

To get the year-to-date you would use the periodsToDate function to grab all the months up to and including the chosen month within the year parent, and you would aggregate your default measure measure across these months:

aggregate( currentMeasure within set periodsToDate( [your Year level], [your month level] -> ?Your month parameter? ) )

Cheers!

MF.