Good evening everyone, 

Maybe is a dumb question but I've hit a wall in trying to achieve the following behavior:

I have a budget application with several scenarios (2024, 2025, etc.). Every scenario use a custom set of elements created in the scenario initialization, where the user define the aggregation for product element where input the budget (can be any attribute and it can choose moltiple level of aggregation).

I've tried to manage the product dimension with different hierarchies, one for each scenario, and tried to dynamically change the hierarchy in the budget input view by defining the view by MDX.

My idea was to define on rows the product as:

StrToSet("[product].["+[scenario].currentMember.name+"].members")

But unfortunately the compiler doesn't like this definition.

Is it the right approach? Is there any other method to dynamically change the hierarchy based on context without using a TI?

Thanks alot, 

Andrea

------------------------------
Andrea Bonelli
------------------------------

I had a similar issue where the MDX compiler didn't like me trying to dynamically change the hierarchy for rows based on a context element of a different dimension.

The MDX compiled ok but when changing the context element it reported a cell mismatch.

In my case I was needing to show an FY hierarchy for some versions but a Calendar Year hierarchy for others; since I only had 2 hierarchies I was able to get around it by nesting both of them in the rows - then with the MDX I set the one that I "didn't want" for a given version to return a "Total" element only. It wasn't an ideal solution but it worked well enough for the given use case.

If you have a lot of hierarchies that will continue to grow then that work-around would probably look awful for you with too many columns/rows being nested. But in the event that no one comes up with a proper solution it may give you some ideas for potential workarounds. 

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------

Hi Declan,

thank you for your Idea! I've tried and it's working, but unfortunately I want to achieve a more dynamic view since they can create a new scenario budget and it will have to appear in the mdx view automatically.

But I will consider to use this approach for time hierarchies since they are more "fixed".

Thanks again,

Andrea

------------------------------
Andrea Bonelli
------------------------------

Original Message:
Sent: Tue September 17, 2024 06:56 AM
From: Declan Rodger
Subject: MDX - Hierarchy Change by context

I had a similar issue where the MDX compiler didn't like me trying to dynamically change the hierarchy for rows based on a context element of a different dimension.

The MDX compiled ok but when changing the context element it reported a cell mismatch.

In my case I was needing to show an FY hierarchy for some versions but a Calendar Year hierarchy for others; since I only had 2 hierarchies I was able to get around it by nesting both of them in the rows - then with the MDX I set the one that I "didn't want" for a given version to return a "Total" element only. It wasn't an ideal solution but it worked well enough for the given use case.

If you have a lot of hierarchies that will continue to grow then that work-around would probably look awful for you with too many columns/rows being nested. But in the event that no one comes up with a proper solution it may give you some ideas for potential workarounds. 

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------

Hi Andrea,

I'm not 100% sure I understand the question correctly.  I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension).  I can provide a simple example of this.  Consider this view:

The MDX is this used to render the view:

------------------------------
Stuart King
Product Manager
IBM Planning Analytics
------------------------------

------------------------------
Andrea Bonelli
------------------------------

Original Message:
Sent: Tue September 17, 2024 07:19 AM
From: STUART KING
Subject: MDX - Hierarchy Change by context

Hi Andrea,

I'm not 100% sure I understand the question correctly.  I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension).  I can provide a simple example of this.  Consider this view:

The MDX is this used to render the view:

SELECT 

   {

      TM1SubsetToSet([Measures].[Measures],"Default","public")

   } ON 0, 

   {

      FILTER(

         [Store].[Store].MEMBERS , [Store Locations].(

 

            [Store].[Store].CURRENTMEMBER,

            [Locations].[Locations]) = 1)

   } ON 1 

FROM

   [Dynamic Sales Cube] 

WHERE (

   [Locations].[Locations].[East], 

   [People].[People].[Sandy], 

   [Channels].[Channels].[Retail])

As I change the Location, I will see a different set of stores on the row axis of the view.  For example (when I change Location from East to South):

To make this work I use another cube called Store Locations that flags what stores belong to which location (some stores belong to two or more locations).  This is often attribute data instead of a separate cube in production TM1 models. 

Let me know if this helps or if I didn't understand the problem you are trying to solve.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics
------------------------------

Hi Andrea,

Few years ago, I have used a quick report with a macro wich change the MDX of a view.

First with Pafe create an exploration and copy the MDX of this view.

Create a quick report based on this view

import the vba automation CognosOfficeAutomationExemple.bas and CognosOfficeMessageSuppressor.cls

(ask to IBM support where to fin files and documentation)

You are able to modify the MDX of the view with a macro like :

Public Sub Replacer()

'

' Replacer Macro

'

Dim MDX As String

Dim IDReport As Integer

MDX = "SELECT TM1SubsetToSet([month].[month], ""ss_mois_annee"") DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0, HEAD(TM1SubsetToSet([region].[region], ""ss_Mes_pays""), 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1 FROM [PNLCube] WHERE ([actvsbud].[Actual] , [account2].[Sales]) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY , [account2].[account2].[compte2]"

Worksheets("RapRapide").Activate

Cells(8, 2).Activate

IDReport = Reporting.GetCurrentReport(ActiveCell).ID

Reporting.QuickReports.Replace IDReport, MDX

End Sub

for sample change the hierarchy.

When, on quick report, a user change the point of vue, an action button can throw the macro and the quick report is ..... perhaps .... as you want.

Regards,

Philippe

------------------------------
Philippe CHAMPLEBOUX
------------------------------

Original Message:
Sent: Fri September 20, 2024 09:52 AM
From: Andrea Bonelli
Subject: MDX - Hierarchy Change by context

---

Hi Stuart,

Thank you somuch for your answer. Unfortunately, the approach you described reflects the way I am currently handling the issue, but the behavior I aim to achieve aligns more closely with what Declan has described.

I am working with a product dimension that is used within a budget input cube. This dimension is recreated for each version of the budget with different aggregations:

• Budget 2024 (which I intend to transform into the hierarchy [Product].[BDG2024]):
  • Commercial Group A
    • • Product A
      • Product B
      • Product D
  • Commercial Group B
    • • Product C

• Budget 2025 (hierarchy [Product].[BDG2025].Members):
  • Technical Line A
    • •  Product A
      •  Product D
  • Technical Line B
    • • Product C
      • Product B

As such, for each scenario, there will be a hierarchy in the product dimension, as the aggregation is reshuffled each time a new budget is created.

Is there a way to automatically adjust the hierarchy in the rows of a cube viewer when the user selects a different element in the Scenario Budget dimension?

I hope this clarifies my question..

Thank you for your time and consideration.

Best regards,
Andrea

------------------------------
Andrea Bonelli
------------------------------


Original Message:
Sent: Tue September 17, 2024 07:19 AM
From: STUART KING
Subject: MDX - Hierarchy Change by context

Hi Andrea,

I'm not 100% sure I understand the question correctly.  I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension).  I can provide a simple example of this.  Consider this view:

The MDX is this used to render the view:

SELECT 

   {

      TM1SubsetToSet([Measures].[Measures],"Default","public")

   } ON 0, 

   {

      FILTER(

         [Store].[Store].MEMBERS , [Store Locations].(

 

            [Store].[Store].CURRENTMEMBER,

            [Locations].[Locations]) = 1)

   } ON 1 

FROM

   [Dynamic Sales Cube] 

WHERE (

   [Locations].[Locations].[East], 

   [People].[People].[Sandy], 

   [Channels].[Channels].[Retail])

As I change the Location, I will see a different set of stores on the row axis of the view.  For example (when I change Location from East to South):

To make this work I use another cube called Store Locations that flags what stores belong to which location (some stores belong to two or more locations).  This is often attribute data instead of a separate cube in production TM1 models. 

Let me know if this helps or if I didn't understand the problem you are trying to solve.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics

Hi Andrea,

Try using a FILTER function like this:

DESCENDANTS(FILTER(TM1SUBSETALL([Product].[Product]) , ([Product].[Product].CURRENTMEMBER.NAME = [Scenario].CURRENTMEMBER.NAME)))

The above MDX example will parse the Product dimension to find the element (rollup) matching the Scenario name. Then it will simply show the Descendants of that Product rollup. For example, if you have a rollup called 'Budget 2024' in the Product dimension and the selected Scenario is 'Budget 2024', then the above MDX will return all descendants of 'Budget 2024' Product rollup.

Alternatively, if the Product rollup is not entirely matching the Scenario name, you could potentially create an attribute on the Product dimension called 'Budget Version' that will be mapped with each Scenario. 

In this case, the MDX will be slightly different, but the idea is the same:

DESCENDANTS(FILTER(TM1SUBSETALL([Product].[Product]) , ([Product].[Product].CURRENTMEMBER.PROPERTIES("Budget Version") = [Scenario].CURRENTMEMBER.NAME)))

Regards,

Cristian

------------------------------
Cristian Serb
------------------------------

Original Message:
Sent: Fri September 20, 2024 09:52 AM
From: Andrea Bonelli
Subject: MDX - Hierarchy Change by context

---

Hi Stuart,

Thank you somuch for your answer. Unfortunately, the approach you described reflects the way I am currently handling the issue, but the behavior I aim to achieve aligns more closely with what Declan has described.

I am working with a product dimension that is used within a budget input cube. This dimension is recreated for each version of the budget with different aggregations:

• Budget 2024 (which I intend to transform into the hierarchy [Product].[BDG2024]):
  • Commercial Group A
    • • Product A
      • Product B
      • Product D
  • Commercial Group B
    • • Product C

• Budget 2025 (hierarchy [Product].[BDG2025].Members):
  • Technical Line A
    • •  Product A
      •  Product D
  • Technical Line B
    • • Product C
      • Product B

As such, for each scenario, there will be a hierarchy in the product dimension, as the aggregation is reshuffled each time a new budget is created.

Is there a way to automatically adjust the hierarchy in the rows of a cube viewer when the user selects a different element in the Scenario Budget dimension?

I hope this clarifies my question..

Thank you for your time and consideration.

Best regards,
Andrea

------------------------------
Andrea Bonelli
------------------------------


Original Message:
Sent: Tue September 17, 2024 07:19 AM
From: STUART KING
Subject: MDX - Hierarchy Change by context

Hi Andrea,

I'm not 100% sure I understand the question correctly.  I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension).  I can provide a simple example of this.  Consider this view:

The MDX is this used to render the view:

SELECT 

   {

      TM1SubsetToSet([Measures].[Measures],"Default","public")

   } ON 0, 

   {

      FILTER(

         [Store].[Store].MEMBERS , [Store Locations].(

 

            [Store].[Store].CURRENTMEMBER,

            [Locations].[Locations]) = 1)

   } ON 1 

FROM

   [Dynamic Sales Cube] 

WHERE (

   [Locations].[Locations].[East], 

   [People].[People].[Sandy], 

   [Channels].[Channels].[Retail])

As I change the Location, I will see a different set of stores on the row axis of the view.  For example (when I change Location from East to South):

To make this work I use another cube called Store Locations that flags what stores belong to which location (some stores belong to two or more locations).  This is often attribute data instead of a separate cube in production TM1 models. 

Let me know if this helps or if I didn't understand the problem you are trying to solve.

------------------------------
Stuart King
Product Manager
IBM Planning Analytics