Dynamic Date Column in Crosstab report

[Ravisha]

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

[CognosPaul]

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:

Code Select Expand

[Cube].[Date dim].[Date Hierarchy].[Day Level]:->[20170101]
that being January 1, 2017.

Code Select Expand

#'[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:

Code Select Expand


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.

[Ravisha]

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