Filter a Time Hierarchy by Period/Month Prompt

[cs95aam]

Hello,

I have a report which has a master detail relationship. The master query has the following set:

set(
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Current Month] ,
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Prior Month],
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[YTD],
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Full Year])

This is currently displaying several crosstabs based on the Current Month and Prior Month as set in the cube. These values change each month based on the finalised month. However, now they would like to do this based on the month or period of their choice. So this would need to be as follows:

[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Current Month] <------- the period of their choice entered in a prompt
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Prior Month] <----- the period prior to their entered period
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[YTD],
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Full Year]

Our cube has a hierarchy of Relative Time which contains members Prior Month, Current Month, YTD, Full Year and then they have child members. So for example, Prior Month is currently 201506, Current Month is 201507 and YTD is from 201501...201507 and Full Year contains 201501...201512.  I'm using Full Year in a value prompt to capture the user's prompt choice. However, how do I modify this master query to match the user's choice and allow the master detail to display values for the appropriate periods, YTD and Full Year?

Thanks in advance.

[MFGF]

Hello,

I have a report which has a master detail relationship. The master query has the following set:

set(
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Current Month] ,
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Prior Month],
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[YTD],
[Profit & Loss Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Full Year])

This is currently displaying several crosstabs based on the Current Month and Prior Month as set in the cube. These values change each month based on the finalised month. However, now they would like to do this based on the month or period of their choice. So this would need to be as follows:

[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Current Month] <------- the period of their choice entered in a prompt
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Prior Month] <----- the period prior to their entered period
[Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[YTD],
[Profit & Loss Cube].[Time].[Relative Time]->:[PC].[@MEMBER].[Full Year]

Our cube has a hierarchy of Relative Time which contains members Prior Month, Current Month, YTD, Full Year and then they have child members. So for example, Prior Month is currently 201506, Current Month is 201507 and YTD is from 201501...201507 and Full Year contains 201501...201512.  I'm using Full Year in a value prompt to capture the user's prompt choice. However, how do I modify this master query to match the user's choice and allow the master detail to display values for the appropriate periods, YTD and Full Year?

Thanks in advance.

Hi,

If you are going to prompt for a period rather than using the current month defined in the cube, you would need to use the main time hierarchy rather than the relative time members.

eg

[Your month level from the main time hierarchy] -> ?Desired Month?
prevMember([Your month level from the main time hierarchy] -> ?Desired Month?)
aggregate(currentMeasure within set periodsToDate ([Your year level from the main time hierarchy],[Your month level from the main time hierarchy] -> ?m?))
aggregate(currentMeasure within set descendants(ancestor([Your month level from the main time hierarchy] -> ?m?,[Your year level from the main time hierarchy]),[Your month level from the main time hierarchy]))

Cheers!

MF.

[cs95aam]

Thank you very much. I'll give that a go.