Hello,

I'm starting a new thread on the latest "Global variable" feature released with Planning Analytics Workspace 2.0.106/2.1.13

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=wnibr2-use-parameterized-mdx-queries-in-planning-analytics-workspace-books

Previously you could create and insert variables in a View widget but the scope was limited to a single widget. Now the variables can be defined "globally" and be used in multiple widgets across the book. I'll update my initial example below. It's quite a long read but I hope it's going to illustrate how powerful that feature is

Here's a simple example of a parameterized view, yet very difficult to build without MDX variables. Let's say you need to build a report that can compare two combination of Scenario/Year with a calculation that makes the difference. It's a simple enough view to create in PAW but it gets complicated as soon as you have to make things dynamic.

Here, there's no way to make that calculation dynamic. Once created, the "Var vs-1" stays hardcoded on "FY 2004 Forecast/2004" - "FY 2003 Budget/2003". There's also an issue with the prompting of Scenario and Year: we have an asymmetric view that takes in columns "Scenario 1 / Year 1" and "Scenario 2 /Year 2 & Var vs-1". Once that asymmetric view is set, you can't change members through the selectors without breaking the asymmetry. If you change one member, you'll go back to a symmetric view that makes the cartesian product of Scenario and Year

The only way you could create this report prior to MDX variables was to build a selection cube but that comes with caveats:

• It's quite difficult to develop: inserting a reference to a cell value into a given view creates huge unmanageable MDX queries
• Performance is sub-par for obvious reasons: the system has to calculate "sub-views" on the lookup cube before starting to compute the main view
• Selections are reduced to picklist: there's no way to select a set containing multiple members and you lose the benefits of a full set editor (searching members, using sets etc...)

Now let's build that report with MDX variables. First we'll create multiple selector widgets for all items that we want parameterize (2 widgets for versions and 2 widgets for year). For each of these widgets, we'll assign a synchronization group that will act as a unique ID for referencing them in variables. Furthermore, assigning different groups allows them to behave independently from each other (remember 2 widgets belonging on the same group act as the same object)

Then we'll go to Dashboard properties => Parameters => Create and manage global parameters

In there we'll create the global variables we intend to use

and we'll insert them in the MDX query of the view

and now you have a dynamic report that lets you freely compare any Scenario/Year

Now let's take the example further. The business team has two new requirements:

1. Selecting versions and years is too cumbersome, they'd like a drop down list with two display mode: current year forecast vs-1 or next year budget vs-1
2. The chart of accounts contains indicators that aren't consolidated in the total, some business users find that unintuitive and the business team would like to filter them out but still allowing user to select whatever they want

To meet the first new requirement, we'll create a Reporting mode dimension and we'll insert a selector widget in the book. The key takeaway here is that you can use a dimension that doesn't belong to the cube you wish to interact with.

We'll first link that new widget to the variation calculation:

And then we'll link the columns:

And now your report is driven by a single prompt

Now for the second requirement, we'll use a weight attribute to help identify the un-consolidated accounts

We'll define the variable not as a slicer but as a Set to capture the entire selection made by the user

We'll then set a filter in the view to get rid of accounts with a weight <> 0 in the set created by the user

So that if a 0 weight account is present in the selection

It's going to get filtered out in the view

------------------------------
Paul C
------------------------------

Many thanks Paul. Very useful and easy to follow with the explanations.

------------------------------
Wim Gielis
Senior Consultant
Aexis International
Sint-Stevens-Woluwe
+32496225001
------------------------------

Original Message:
Sent: Thu August 14, 2025 09:30 AM
From: Paul C
Subject: PAW - Global MDX variables

Hello,

I'm starting a new thread on the latest "Global variable" feature released with Planning Analytics Workspace 2.0.106/2.1.13

https://www.ibm.com/docs/en/planning-analytics/2.1.0?topic=wnibr2-use-parameterized-mdx-queries-in-planning-analytics-workspace-books

Previously you could create and insert variables in a View widget but the scope was limited to a single widget. Now the variables can be defined "globally" and be used in multiple widgets across the book. I'll update my initial example below. It's quite a long read but I hope it's going to illustrate how powerful that feature is

Here's a simple example of a parameterized view, yet very difficult to build without MDX variables. Let's say you need to build a report that can compare two combination of Scenario/Year with a calculation that makes the difference. It's a simple enough view to create in PAW but it gets complicated as soon as you have to make things dynamic.

Here, there's no way to make that calculation dynamic. Once created, the "Var vs-1" stays hardcoded on "FY 2004 Forecast/2004" - "FY 2003 Budget/2003". There's also an issue with the prompting of Scenario and Year: we have an asymmetric view that takes in columns "Scenario 1 / Year 1" and "Scenario 2 /Year 2 & Var vs-1". Once that asymmetric view is set, you can't change members through the selectors without breaking the asymmetry. If you change one member, you'll go back to a symmetric view that makes the cartesian product of Scenario and Year

The only way you could create this report prior to MDX variables was to build a selection cube but that comes with caveats:

• It's quite difficult to develop: inserting a reference to a cell value into a given view creates huge unmanageable MDX queries
• Performance is sub-par for obvious reasons: the system has to calculate "sub-views" on the lookup cube before starting to compute the main view
• Selections are reduced to picklist: there's no way to select a set containing multiple members and you lose the benefits of a full set editor (searching members, using sets etc...)

Now let's build that report with MDX variables. First we'll create multiple selector widgets for all items that we want parameterize (2 widgets for versions and 2 widgets for year). For each of these widgets, we'll assign a synchronization group that will act as a unique ID for referencing them in variables. Furthermore, assigning different groups allows them to behave independently from each other (remember 2 widgets belonging on the same group act as the same object)

Then we'll go to Dashboard properties => Parameters => Create and manage global parameters

In there we'll create the global variables we intend to use

and we'll insert them in the MDX query of the view

and now you have a dynamic report that lets you freely compare any Scenario/Year

Now let's take the example further. The business team has two new requirements:

1. Selecting versions and years is too cumbersome, they'd like a drop down list with two display mode: current year forecast vs-1 or next year budget vs-1
2. The chart of accounts contains indicators that aren't consolidated in the total, some business users find that unintuitive and the business team would like to filter them out but still allowing user to select whatever they want

To meet the first new requirement, we'll create a Reporting mode dimension and we'll insert a selector widget in the book. The key takeaway here is that you can use a dimension that doesn't belong to the cube you wish to interact with.

We'll first link that new widget to the variation calculation:

And then we'll link the columns:

And now your report is driven by a single prompt

Now for the second requirement, we'll use a weight attribute to help identify the un-consolidated accounts

We'll define the variable not as a slicer but as a Set to capture the entire selection made by the user

We'll then set a filter in the view to get rid of accounts with a weight <> 0 in the set created by the user

So that if a 0 weight account is present in the selection

It's going to get filtered out in the view

------------------------------
Paul C
------------------------------