Date filtering and slicing in Dimensional Data source

[ravi.ahuja]

Hello All,

I have a report which is based of a Powerplay cube.

I have a requirement where a user wants to filter the data based on the time dimension, the user wants to filter data in two ways,
1. Based on the from date and to date selected by the user in the tree prompt which is based of the time dimension, we call this as absolute time filter
2. Based on the relative time category like Last week, Next week, last month, year to date, this is a value prompt control containing static choices,we call this as relative time filter

Note:The relative categories mentioned above is created in the powerplay cube.

The user wants both the above given filtering as optional.

So I have a radio button which asks the user the filter criteria(Absolute/Relative/None) and based on the user selection i conditionally show the tree prompt or value prompt.

The following is the hierarchy of my time dimension
Time Dimension.Time Hierarchy.[Year].[Month].[Week].[Day]
Time Dimension.Last Week
Time Dimension.Last Month

I have used slicers for the relative category filtering using the methodolgy given below.
I have created static choices with the use value as Member Unique name
and the slicer expression as
#prompt('Parameter1', 'memberuniquename', '[RootMember]', '')#
where Parameter 1 is the name of the parameter for relative category prompt
Memberuniquename says that the prompt type is MUN
and Root Member is basically a data item created to default the root members in case the user does not select the relative category prompt.

My problem is I have to also filter/slice data based on the from and to date selected by the user in the tree prompt.

I created a detail filter for the same as
[Cube].[Time Dimension].[Time Hierarchy].[Day] between ?fromdate? and ?todate?
this works fine if I dont have a slicer. I believe that the filters should not be used when dealing with DMR models.

I am trying to do a filter using the below expression but dont know how do i make the prompt optional

filter(
[Time Dimension].[My Hierarchy].[Date],
roleValue('_businessKey', [Time Dimension].[My Hierarchy].[Day]) >= #sq(prompt('Fromdate', 'Date'))# and
roleValue('_businessKey', [Time Dimension].[My Hierarchy].[Day])) <= #sq(prompt('Todate', 'Date'))#
)


Can someone suggest how do i bring everything in a slicer ?

Please suggest where am I going wrong.


Thank You.