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
Original Message:
Sent: 1/21/2022 7:45:00 AM
From: Cristian Serb
Subject: PAW Column Headers
Hi there,
Is there any future plan to include static named headers for the columns in PAW exploration views ? For example, when using two stacked dimensions on columns, e.g. Year and Month, sometimes we can use MDX subsets to build the column area, e.g.:
Year - bring prior year and current year using MDX subset
=====
2021
2022
Month - bring Q1 months by using MDX subset
=====================================
OCT
NOV
DEC
Now, when trying to use Create Calculation function we all know this is using static member references, so next year the calculation is lost because new column headers are to be displayed according to the MDX subset.
Ideally, PAW should keep static reference to these elements (potentially put a label instead of the real element name) and use those when creating PAW calculations.
For now, the workaround we found is to edit the MDX query and use WITH MEMBER expression in MDX to create the static reference. This works, but requires a lot of coordination between what we create in PAW vs what we edit using MDX editor.
Thanks,
Cristian
------------------------------
Cristian Serb
------------------------------
#PlanningAnalyticswithWatson