Hello Cognoise,
How can we show a dynamic 'date' column in the crosstab report (Cube based) based on a user date selection from the date prompt (single select)?
Example:
User Selection -> Date Prompt (single select)
Dynamic Date MTD
Product 1 123 123
Product 2 123 123
Product 3 123 123
Thanks in advance
Your crosstab looks like it has 4 data items.
1. The measure in the corner
2. Product level in the rows.
3. Date
4. Month to date.
The date is the easy bit. You simply create a prompt macro in your data item that points to the correct date member. How to do that is simple, but depends on what your date members look like.
Let's assume it looks like:
[Cube].[Date dim].[Date Hierarchy].[Day Level]:->[20170101]
that being January 1, 2017.
#'[Cube].[Date dim].[Date Hierarchy].[Day Level]:->['+ timestampMask(prompt('Date','date')+'T00:00:00Z','yyyymmdd') + ']'#
The MTD member is more interesting. Does that need to be based on the date the user selects?
If so, you can do something like:
member(
total(currentMeasure within set periodsToDate([Cube].[Date dim].[Date Hierarchy].[Month Level],[Date]))
,'MTD','MTD',[Cube].[Date dim].[Date Hierarchy])
It takes the Date member, finds all of the periods before it on the month level (so a YTD member would use the year level), totals them, and returns it as a member in the date hierarchy.
Hi Paul,
Fantastic ! Thanks a lot for your solution. (as always genius solution ;D)
I've incorporated a similar solution but in a different way. I've created a relative time hierarchy which includes Day, MTD, Prior MTD, QTD, Prior QTD, YTD and Prior YTD. I dragged all the aforementioned members onto the column of the crosstab. For dynamic date, I used the same hierarchy by implementing the prompt macros (similar to the solution which you've provided). The report runs like super sonic :)
Thanks again