Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  Subset Created using MDX changes when user drills down or up.

    Posted 10/01/25 09:17 AM

    Sorry this is going to be a bit detailed because I am sure that I am doing something wrong just not sure where. I have a PAW Report that we are using for data entry. The Dimension that I am having an issue with is called CapitalDetailLines. Each Project uses only a subset for the CapitalDetailLines depending on the Projects Business Unit. So the way the Report works is the user selects the Project, there is an MDX SUBSET that goes and get's the BusinessUnit for the Project and set's the business Unit, this works perfect. This business Unit is then used to define the subset using MDX for the CapitalDetailLines. This works perfect then the report opens up. So see below. This is what I want to see, the detail lines are the ROWS. the problem is that when a user Collapses and item, then expand the item it is no longer using the MDX. The 2nd Image is after that happens notice the extra items under County Bonds.

    Here is the SubSet's Original MDX

    {
    TM1SubsetToSet([CapitalDetailLines].[CapitalDetailLines] , IIF([CapitalProject].[CapitalProject].CURRENTMEMBER.PROPERTIES("BusinessUnit") = "HNPRS" , "HNPRS" , IIF([CapitalProject].[CapitalProject].CURRENTMEMBER.PROPERTIES("BusinessUnit") = "HNPWT" , "HNPWT" , "HNPWS")))
    }

    After the user Expands, or contracts the list this is what it looks like.

    {
    DRILLDOWNMEMBER(DRILLUPMEMBER(TM1SubsetToSet([CapitalDetailLines].[CapitalDetailLines] , IIF([CapitalProject].[CapitalProject].CURRENTMEMBER.PROPERTIES("BusinessUnit") = "HNPRS" , "HNPRS" , IIF([CapitalProject].[CapitalProject].CURRENTMEMBER.PROPERTIES("BusinessUnit") = "HNPWT" , "HNPWT" , "HNPWS"))) , {[CapitalDetailLines].[CapitalDetailLines].[County Bonds]}) , {[CapitalDetailLines].[CapitalDetailLines].[County Bonds]})
    }

    I am confused and not sure how to prevent this from happening.



    ------------------------------
    Jarrod McReynolds
    ------------------------------


  • 2.  RE: Subset Created using MDX changes when user drills down or up.

    Posted 10/02/25 09:48 AM

    Hi Jarrod,

    I am sorry but this is how it works and actually this is how it needs to work.

    In your case the easiest solution might be disabling the Expand and Collapses options for the rows.

    Ali Candan



    ------------------------------
    Ali Candan
    ------------------------------



  • 3.  RE: Subset Created using MDX changes when user drills down or up.

    Posted 10/02/25 07:47 PM

    An alternative suggestion could be to create an alternate hierarchy on the CapitalDetailLines dimension; build a hierarchy that looks the same but only uses the leaf elements that belong to the relevant business units.

    The users would be able to drill up and down normally, the issue (or benefit potentially) is that by default the consolidations would then show only the sum of the leaf elements in the hierarchy. At the moment your top image shows the expected behaviour of "County Bonds" summing up all of the relevant leafs even though the user can only see 1... I can imagine for a data entry point of view it could actually be beneficial to just see the sum of what they are working on at the time. BUT, if you do want it to show the totals as it currently does you could add a rule to the hierarchy that makes the consolidated element values equal to that of the same named element in the base hierarchy.



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



  • 4.  RE: Subset Created using MDX changes when user drills down or up.

    Posted 10/02/25 07:55 PM

    Thank you for the responses. I actually implemented what you suggested. Since I have 3 Different Business Units I had to create 3 New sets of consolidations with in the Dimension. The hard part was that the name of the Consolidations could not repeat so I had to add something to each one, so I added the Business Unit to the end of each one, then used an Aalias to give them a cleaner name, again still could not have duplicates so used : ; . Then I created an MDX subset for each one, then a final MDX with IIF statements to pick the one that is correct for that project. If nothing else this validates my solution. thanks all for the replies. 



    ------------------------------
    Jarrod McReynolds
    ------------------------------

    Original Message


Related Content