Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

Jitesh

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

Jitesh

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

I've made the test by substituting the <0> variable by a hardcoded set and it works so yes I expect that you can fully impact a cubeview with a dimension that doesn't belong to the underlying cube. It will work as long as these dimensions belong the same instance

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hey Paul,

Thanks again for taking the time to explain. The concept of having a global variable that can be used across any MDX statement will be great.

It's the population of that variable that I'm hoping to understand in a bit more depth (if possible at this stage.) I'm imagining:

• A global variable section at the dashboard properties level of the book. 
• Each variable created can then be assigned a synchronisation group.
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available.
• When a user selects an element of that broadcasted dimension, the global variable is updated.
• From there, we can use your example above.

Is that how you see it working?

Cheers,
George

I've made the test by substituting the <0> variable by a hardcoded set and it works so yes I expect that you can fully impact a cubeview with a dimension that doesn't belong to the underlying cube. It will work as long as these dimensions belong the same instance

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Here's my comments as far as I know

• A global variable section at the dashboard properties level of the book => there will be some kind of Global variables manager (at dashboard level), similar to the sync group manager
• Each variable created can then be assigned a synchronisation group. => Actually each Variable will have to be sourced from a synchronisation group dimension (but they can be applied anywhere irrelevant of the synchronisation group settings). It's like in excel, you create a "named range" (a kind of excel variable) that has to be sourced from a cell. Here you need a source and a source is a sync group
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available. => I would expect that to be the case yes
• When a user selects an element of that broadcasted dimension, the global variable is updated. => yes and also all widgets that rely on it
• From there, we can use your example above.

Hey Paul,

Thanks again for taking the time to explain. The concept of having a global variable that can be used across any MDX statement will be great.

It's the population of that variable that I'm hoping to understand in a bit more depth (if possible at this stage.) I'm imagining:

• A global variable section at the dashboard properties level of the book. 
• Each variable created can then be assigned a synchronisation group.
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available.
• When a user selects an element of that broadcasted dimension, the global variable is updated.
• From there, we can use your example above.

Is that how you see it working?

Cheers,
George

I've made the test by substituting the <0> variable by a hardcoded set and it works so yes I expect that you can fully impact a cubeview with a dimension that doesn't belong to the underlying cube. It will work as long as these dimensions belong the same instance

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Here's my comments as far as I know

• A global variable section at the dashboard properties level of the book => there will be some kind of Global variables manager (at dashboard level), similar to the sync group manager
• Each variable created can then be assigned a synchronisation group. => Actually each Variable will have to be sourced from a synchronisation group dimension (but they can be applied anywhere irrelevant of the synchronisation group settings). It's like in excel, you create a "named range" (a kind of excel variable) that has to be sourced from a cell. Here you need a source and a source is a sync group
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available. => I would expect that to be the case yes
• When a user selects an element of that broadcasted dimension, the global variable is updated. => yes and also all widgets that rely on it
• From there, we can use your example above.

Hey Paul,

Thanks again for taking the time to explain. The concept of having a global variable that can be used across any MDX statement will be great.

It's the population of that variable that I'm hoping to understand in a bit more depth (if possible at this stage.) I'm imagining:

• A global variable section at the dashboard properties level of the book. 
• Each variable created can then be assigned a synchronisation group.
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available.
• When a user selects an element of that broadcasted dimension, the global variable is updated.
• From there, we can use your example above.

Is that how you see it working?

Cheers,
George

I've made the test by substituting the <0> variable by a hardcoded set and it works so yes I expect that you can fully impact a cubeview with a dimension that doesn't belong to the underlying cube. It will work as long as these dimensions belong the same instance

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hi Paul - I've just installed 104 local and can't see the Global Variables at the dashboard level. Is there any update of when this might be getting released?

Hello,

Here's my comments as far as I know

• A global variable section at the dashboard properties level of the book => there will be some kind of Global variables manager (at dashboard level), similar to the sync group manager
• Each variable created can then be assigned a synchronisation group. => Actually each Variable will have to be sourced from a synchronisation group dimension (but they can be applied anywhere irrelevant of the synchronisation group settings). It's like in excel, you create a "named range" (a kind of excel variable) that has to be sourced from a cell. Here you need a source and a source is a sync group
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available. => I would expect that to be the case yes
• When a user selects an element of that broadcasted dimension, the global variable is updated. => yes and also all widgets that rely on it
• From there, we can use your example above.

Hey Paul,

Thanks again for taking the time to explain. The concept of having a global variable that can be used across any MDX statement will be great.

It's the population of that variable that I'm hoping to understand in a bit more depth (if possible at this stage.) I'm imagining:

• A global variable section at the dashboard properties level of the book. 
• Each variable created can then be assigned a synchronisation group.
• When type=member is used, the dimension drop down will have all broadcasting dimensions of that sync group available.
• When a user selects an element of that broadcasted dimension, the global variable is updated.
• From there, we can use your example above.

Is that how you see it working?

Cheers,
George

I've made the test by substituting the <0> variable by a hardcoded set and it works so yes I expect that you can fully impact a cubeview with a dimension that doesn't belong to the underlying cube. It will work as long as these dimensions belong the same instance

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Thanks Paul for the insights.

I had greatly used this feature by retrieving a period-attribute from a version dimension via <0>.Properties("Period_YTD"). Unfortunately this does not seem to not work anymore in PAW 2.0.104.

I receive an error like "error (recursion)". Does anyone else also have this issue?

------------------------------
Grischa Rehmer

Original Message:
Sent: Tue February 25, 2025 04:44 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

That should be possible in the future. I know the developement team is working on "Global variables" as a next step. Currently these variables are "attached" and "restricted" to a given view. The idea with Global variables is to create variables that can be used everywhere. This has a couple of implications:

• You can use a variable from a dimension that is not part of the view: ie a dimension from a 1rst view/cube can drive the selection of a 2nd view/cube (and they might be different cubes)
• You can have several variables (from several selector widgets) for a given dimension: ie you don't have to use the multiple selectors in the example above, you can just create several selectors for a given dimension (and thus get several variables with different values)

It's also on the roadmap to bring variables to TI buttons parameters and Set editor widget. With these features, you should gain an excel-like freedom of modelisation in books.

In the case of Product Category and Product SKU, I expect that you can link both with global variables in the following manner:

1. Create a "Slicer Variable" for Product Category (called <0>)
2. In the view with Product SKU, transform that "Product Category" member into a Product SKU member: STRTOMEMBER("[Product SKU].[Product SKU].[" + <0>.PROPERTIES("MEMBER_NAME") + "]")

I actually wonder if the same would work for a set, the expression would have to contain a reference to a current member that the view on Product SKU shouldn't have access to. It's something we should try when that feature is released but I don't think that would work

GENERATE( <0> , {STRTOMEMBER("[Product SKU].[Product SKU].[" + [Product Category].[Product Category].CURRENTMEMBER.PROPERTIES("MEMBER_NAME") + "]")})

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

Original Message:
Sent: Mon February 24, 2025 05:21 PM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

------------------------------
Jitesh Parmar

Original Message:
Sent: Mon February 24, 2025 07:01 AM
From: Paul C
Subject: PAW 2.0.101 - MDX Template a practical use case

Hello,

Sure here's a simple one, 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 build this report prior 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 unmanageble 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 several 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Thank for that write up Paul, much appreciated. 

I have managed to successfully replicate this on v11 on Cloud. However, trying to do the same on IBM PA aaS on v12, the reference to variables works fine, the asymmetric display of the variables works fine, but then defining a calculation (e.g. the variance) with reference to the variables fails. It appears that the MDX statement fails as it does not expect the comma after the first variable reference:

I will raise with IBM, but was just wondering if anyone reading this has already given this a go on v12?

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------

Hello,

Sure here's a simple one, 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 build this report prior 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 use multiple selection widgets that will allow us to create selections for each columns of the report (you'll have to use sync groups between the selector widgets and the view)

Then let's create the variables

and we'll insert them in the MDX

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

This is a first initial release for MDX variables, I'll wait a bit to see what's next and I'll publish more examples

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

Original Message:
Sent: Fri February 21, 2025 11:36 AM
From: Jitesh Parmar
Subject: PAW 2.0.101 - MDX Template a practical use case

Jitesh

------------------------------
Jitesh Parmar
------------------------------