Hi Pantelis:

It's hard to answer your question without more information about what you are trying to do. If you are just trying to set the columns of an MDX view to either Actual or Budget you should be able to accomplish it using MDX alone (without using a TI process).

If you are new to MDX views in PAW, I strongly recommend @George Tonkin's series of blog posts (there are at least 6) on MDX views. Here's a link to Part 1: https://community.ibm.com/community/user/businessanalytics/blogs/george-tonkin/2024/02/18/learning-mdx-views-in-planning-analytics

If this does not help, please provide a little more information about what you are trying to do, perhaps with a screenshot showing the shape of your view and your widget?

Regards,

A.G.

Below you may see the report (using data explorer as widget: on row are materials, on columns Actual of 2024, Budget of 2025 and the last column the % of change. The issue that I have is that the data explorer widget is not updated when I run the the process and change the year or the version of data that I want to compare.

I'm also providing the mdx that I used to create this report as well as a sample of parametrized MDX that I use in process. 

WITH MEMBER [D_Years].[D_Years].[Actual 2024] AS ([D_Years].[D_Years].[2024],[D_Versions].[D_Versions].[Actual]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base])

 

 

ViewCreatebyMDX (

    c_Cube, 

    c_SourceView, 

'WITH MEMBER [D_Years].[D_Years].[' | pSourceVersion | ' ' | pCurrent_Year | '] AS ([D_Years].[D_Years].[| pCurrent_Year |'],[D_Versions].[D_Versions].['| pSourceVersion |]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base]',0);

Hi Pantelis:

It's hard to answer your question without more information about what you are trying to do. If you are just trying to set the columns of an MDX view to either Actual or Budget you should be able to accomplish it using MDX alone (without using a TI process).

If you are new to MDX views in PAW, I strongly recommend @George Tonkin's series of blog posts (there are at least 6) on MDX views. Here's a link to Part 1: https://community.ibm.com/community/user/businessanalytics/blogs/george-tonkin/2024/02/18/learning-mdx-views-in-planning-analytics

If this does not help, please provide a little more information about what you are trying to do, perhaps with a screenshot showing the shape of your view and your widget?

Regards,

A.G.

Dear Pantelis:

Thanks for all the detail. I think I understand the problem now. I am short of time right now so I haven't had a chance to digest everything, but I just wanted to share a thought.

For this kind of problem I would probably have explored the approach of having the TI process update a report variables cube dynamically with the desired parameters, and then have the report MDX just look up the values in the cube. I would NOT use ViewCreateByMDX to rebuild the entire report MDX each time. The MDX would just live in the report (PAW Book) itself and dynamically reference whatever the report variables are.

For the report variables you can start with a simple prototype but you could get fancy and dimension it by user (use the }clients dimension perhaps), or by other things to keep different user expriences separate from each other.

I hope this helps.

Regards,

Ann-Grete  

Below you may see the report (using data explorer as widget: on row are materials, on columns Actual of 2024, Budget of 2025 and the last column the % of change. The issue that I have is that the data explorer widget is not updated when I run the the process and change the year or the version of data that I want to compare.

I'm also providing the mdx that I used to create this report as well as a sample of parametrized MDX that I use in process. 

WITH MEMBER [D_Years].[D_Years].[Actual 2024] AS ([D_Years].[D_Years].[2024],[D_Versions].[D_Versions].[Actual]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base])

 

 

ViewCreatebyMDX (

    c_Cube, 

    c_SourceView, 

'WITH MEMBER [D_Years].[D_Years].[' | pSourceVersion | ' ' | pCurrent_Year | '] AS ([D_Years].[D_Years].[| pCurrent_Year |'],[D_Versions].[D_Versions].['| pSourceVersion |]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base]',0);

Hi Pantelis:

It's hard to answer your question without more information about what you are trying to do. If you are just trying to set the columns of an MDX view to either Actual or Budget you should be able to accomplish it using MDX alone (without using a TI process).

If you are new to MDX views in PAW, I strongly recommend @George Tonkin's series of blog posts (there are at least 6) on MDX views. Here's a link to Part 1: https://community.ibm.com/community/user/businessanalytics/blogs/george-tonkin/2024/02/18/learning-mdx-views-in-planning-analytics

If this does not help, please provide a little more information about what you are trying to do, perhaps with a screenshot showing the shape of your view and your widget?

Regards,

A.G.

Dear Ann,

Your suggestion "MDX would just live in the report (PAW Book) itself and dynamically reference" gave me a way to direct. This lead me to the article create-dynamic-asynchronous-views-with-mdx which provided me the solution because I already have a configuration cube.

Thanks for your answer.

Regards

Dear Pantelis:

Thanks for all the detail. I think I understand the problem now. I am short of time right now so I haven't had a chance to digest everything, but I just wanted to share a thought.

For this kind of problem I would probably have explored the approach of having the TI process update a report variables cube dynamically with the desired parameters, and then have the report MDX just look up the values in the cube. I would NOT use ViewCreateByMDX to rebuild the entire report MDX each time. The MDX would just live in the report (PAW Book) itself and dynamically reference whatever the report variables are.

For the report variables you can start with a simple prototype but you could get fancy and dimension it by user (use the }clients dimension perhaps), or by other things to keep different user expriences separate from each other.

I hope this helps.

Regards,

Ann-Grete  

Below you may see the report (using data explorer as widget: on row are materials, on columns Actual of 2024, Budget of 2025 and the last column the % of change. The issue that I have is that the data explorer widget is not updated when I run the the process and change the year or the version of data that I want to compare.

I'm also providing the mdx that I used to create this report as well as a sample of parametrized MDX that I use in process. 

WITH MEMBER [D_Years].[D_Years].[Actual 2024] AS ([D_Years].[D_Years].[2024],[D_Versions].[D_Versions].[Actual]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base])

 

 

ViewCreatebyMDX (

    c_Cube, 

    c_SourceView, 

'WITH MEMBER [D_Years].[D_Years].[' | pSourceVersion | ' ' | pCurrent_Year | '] AS ([D_Years].[D_Years].[| pCurrent_Year |'],[D_Versions].[D_Versions].['| pSourceVersion |]), SOLVE_ORDER = 1 MEMBER [D_Years].[D_Years].[Budget 2025] AS ([D_Years].[D_Years].[2025],[D_Versions].[D_Versions].[Budget]), SOLVE_ORDER = 2 MEMBER [D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)] AS ((([D_Years].[D_Years].[Budget 2025] - [D_Years].[D_Years].[Actual 2024])) /  CASE   WHEN [D_Years].[D_Years].[Actual 2024] < 0 THEN 0 - [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] > 0 THEN 1 * [D_Years].[D_Years].[Actual 2024]  WHEN [D_Years].[D_Years].[Actual 2024] = 0 THEN 0  ELSE NULL END ), SOLVE_ORDER = 3, FORMAT_STRING = '##0.00%;-##0.00%' SELECT {[D_Years].[D_Years].[Actual 2024],[D_Years].[D_Years].[Budget 2025],[D_Years].[D_Years].[% of change from(Actual 2024 , Budget 2025)]} ON 0, {DRILLDOWNMEMBER({[D_Είδη].[D_Είδη].[Total of D_Είδη]}, {[D_Material].[D_Material].[Total of D_Material]})} ON 1 FROM [F_Πωλήσεις] WHERE ([D_Customer].[D_Customer].[Total], [D_Measures].[D_Measures].[Quantity], [Sandboxes].[Sandboxes].[Base]',0);

Hi Pantelis:

It's hard to answer your question without more information about what you are trying to do. If you are just trying to set the columns of an MDX view to either Actual or Budget you should be able to accomplish it using MDX alone (without using a TI process).

If you are new to MDX views in PAW, I strongly recommend @George Tonkin's series of blog posts (there are at least 6) on MDX views. Here's a link to Part 1: https://community.ibm.com/community/user/businessanalytics/blogs/george-tonkin/2024/02/18/learning-mdx-views-in-planning-analytics

If this does not help, please provide a little more information about what you are trying to do, perhaps with a screenshot showing the shape of your view and your widget?

Regards,

A.G.