Hi Fred,
The way we handle dynamic reports at this moment is:
1. Create a static exploration view in our PAW book, with elements like Year=2022, Month=DEC, etc. So nothing MDX based at this point
2. Use targeted column/row selections where needed
3. Convert the static view in a dynamic one by using Edit MDX button on the view. This opens up the query editor
4. Add WITH MEMBER syntax at the top of the query and use a Control cube where we retrieve all these dynamic members from:
WITH
MEMBER [Year].[This Year] AS StrToMember("[Year].["+[Control].([Control].[Current Year],[Control Measures].[String])+"]")
MEMBER [Year].[Prior Year] AS StrToMember("[Year].["+[Control].([Control].[Current Year],[Control Measures].[String])+"].PrevMember")
MEMBER [Month].[Current Month] AS StrToMember("[Month].["+[Control].([Control].[Current Month],[Control Measures].[String])+"]")
SELECT ... ON 0,
... ON 1
FROM ...
WHERE ...
5. Use Find and Replace to actually replace [Year].[2022] with [Year].[This Year] in our MDX query. Same thing for Month.
Note: The rest of the MDX query remains the same as it was generated by the MDX Editor in PAW.
6. Create other PAW Calculations using the new dynamic references. You can build them in PAW directly, without a need to edit the MDX query, so just use Create Calculation option after selecting the columns you need to build the calculations for. For example, [This Year[-[Prior Year] can be done on the UI itself, no need to get to the MDX editor again.
Hope this helps !
------------------------------
Cristian Serb
------------------------------
Original Message:
Sent: Mon January 24, 2022 02:49 AM
From: frederic MALLAVAL
Subject: PAW Column Headers
Hi
We are all looking for this to have dynamic reports and views across the time !!!
Could you share an example of your with member subset ?
Regards
Fred
Envoyé de mon iPhone