Implementing relative time prompt

[Dineshpullagura]

Hello,

I am using cube as a data source for my report. In my report i am using a value prompt which displays the following options 1 day, 1 week, one month, six months, 1 year, 2 years and LTD.

Currently i am using static choices as use values and using a case statement in detail filter to filter the data. But, using detailed filters is not a good approach while working with cubes.

I tried to use slicers by changing the use values of my prompt to 'level unique names'  like

Use Value                                                                                   
[CUBE_NAME].[DIMENSION].[HIERARCHY].[LEVEL]                           

I am stuck at what should be the slicer expression, so as to filter the data as per the relative time selected by the user.

Please suggest.

Thanks for your time.

[MFGF]

Hello,

I am using cube as a data source for my report. In my report i am using a value prompt which displays the following options 1 day, 1 week, one month, six months, 1 year, 2 years and LTD.

Currently i am using static choices as use values and using a case statement in detail filter to filter the data. But, using detailed filters is not a good approach while working with cubes.

I tried to use slicers by changing the use values of my prompt to 'level unique names'  like

Use Value                                                                                   
[CUBE_NAME].[DIMENSION].[HIERARCHY].[LEVEL]                           

I am stuck at what should be the slicer expression, so as to filter the data as per the relative time selected by the user.

Please suggest.

Thanks for your time.

Can you tell us how to determine which day member to show if the user picks the "1 day" option? The same applies for the other options too - which week/month/half year/year etc should be used?

MF.

[Dineshpullagura]

Hi,

I apologize for the confusion. By 1 day i mean current day -1 day, same as with week ,month and year.

1 week= data for last one week(current day- 7 days)
1 month= data for last one month  (current day - last month number of days)

Thanks

[MFGF]

Hi,

I apologize for the confusion. By 1 day i mean current day -1 day, same as with week ,month and year.

1 week= data for last one week(current day- 7 days)
1 month= data for last one month  (current day - last month number of days)

Thanks

Ok - can you tell us what the MUN (Member Unique Name) for a Day member looks like (You can see it if you right-click a day member and select Properties)? We will need the same for a month member, and for the members of the other levels you want to select...

MF.

[Dineshpullagura]

Please find the MUNs below.

Year MUN  --    [CUBE].[Date].[YMDH].[Year]->:[RO].[Date].[YMDH].[All].[2014]
Month MUN --   [CUBE].[Date].[YMDH].[Month]->:[RO].[Date].[YMDH].[All].[2014].[201406]
Week MUN  -- [CUBE].[Date].[YMDH].[Week]->:[RO].[Date].[YMDH].[All].[2014].[201401].[20140131004055?20140131631]
Day MUN --  [CUBE].[Date].[YMDH].[Day]->:[RO].[Date].[YMDH].[All].[2014].[201401].[20140103234045?2014010363].[352]
Hour MUN  -- [CUBE].[Date].[YMDH].[Hour]->:[RO].[Date].[YMDH].[All].[2014].[201401].[20140103234045?2014010363].[352].[352]


Thank you