Relative time prompt using slicer

[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 Last 24 hours, Last 1 week, Last one month, Last six months, Last 1 year,Last 2 years and LTD.

I am trying to implement this using a slicer. My prompt displays these values to the user and uses the level names as use values.

For Example

Display Value                         Use Value
Last 1 month          [CUBE_NAME].[DIMENSION].[HIERARCHY].[WEEK_LEVEL]
Last 3 months         [CUBE_NAME].[DIMENSION].[HIERARCHY].[MONTH_LEVEL]
Last  2 years           [CUBE_NAME].[DIMENSION].[HIERARCHY].[YEAR_LEVEL]


These are the MUNs for the level members.

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]


Any suggestions on how to implement a slicer for relative time prompt.

Thanks for your time.

[bdbits]

Since you have a dimensional data source, you should use some of the dimensional functions like lag(), lead(), lastPeriods(), etc.

[Dineshpullagura]

Hi

Thanks for the reply. I will try using those functions.

I am stuck at the point where, how to make the slicer choose the level dynamically according to the relative time range selected by the user from the prompt.

My prompt displays these values to the user and uses the level names as use values.

Display Value                         Use Value
Last 1 month          [CUBE_NAME].[DIMENSION].[HIERARCHY].[WEEK_LEVEL]
Last 3 months         [CUBE_NAME].[DIMENSION].[HIERARCHY].[MONTH_LEVEL]
Last  2 years           [CUBE_NAME].[DIMENSION].[HIERARCHY].[YEAR_LEVEL]

I am using the following slicer expression(Tree Prompt) to make the slicer choose the level dynamically.

#prompt('Prompt Name', 'memberuniquename', '', '','[Cube].[Dimension].[Hierarchy]')#


But the expression is throwing the following error.

XQE-PLN-0019
Need to resolve prompts.
XQE-PLN-0107 Invalid format for prompt 'TimeRange'. Expected format is unknown.

Please suggest, where to set the data type for the prompt and whether my approach is correct.

Thanks for your time.

[bdbits]

Maybe I can share something I have done... Say you have a time dimension and want to filter on year. Create a normal prompt based on the year level in your time dimension, call it pYear. Then add a data item and create an expression like:
item(#prompt('pYear','memberuniquename')#,0)

When you run the report, you will select a member from the prompt. The MUN will be passed into this expression, effectively slicing your data on that dim to the one member. You can of course get more elaborate with the expression and prompt macro, and create sets to include multiple members.

If you are referencing a MUN, you will need to go all the way down to the member. For example, here is a slightly anonymized example from a cube I have with a time dim:
[Cube].[All Time].[All Time].[Fiscal Year]->:[PC].[@MEMBER].[FY-2015]

[Dineshpullagura]

Hi,

Thanks a lot. I appreciate your time.