Planning Analytics

I am attempting to take a report and modify the tm2\\_0_rh to a MDX formula that changes based on what is selected in the slicers.  I have a formula, but it keeps giving me an error, and I am not sure why.  The formula should if you select a consolidated element, then the rows should show only the leaf elements under that consolidated element, but if it is a leaf element it should show that element.

IF("[CostCenter].[CostCenter].["&F9&"]"= 0,"{[CostCenter].[CostCenter].["&TRIM(MID(F9,SEARCH("(",F9)+1,5))&"]}","EXCEPT(DISTINCT(DESCENDANTS([CostCenter].[CostCenter].["&F9&"] , 99 , LEAVES)) , {[CostCenter].[CostCenter].["&F9&"]})")

Hi Glen! 

First let me start by saying super happy that you're experimenting with more advanced Universal Report customization techniques, it's exactly what I hope to see, and feedback in that area is super helpful.
Doubly so that you're checking out the new Static layout UR's, which are quite new.

At a glance, my sense is that you're targeting perhaps an unintentional named range component. RH going into MakeQuery3, should compute to an array of hierarchy names to apply the to the member range outcome, there's limited opportunity to apply expressiveness there since the values are interleaved with the RM entries to define Mun-Style names for the axis. (you should see this in the Q result outcome, if it fits below the bigString limit, otherwise can check for it in logs)

Approaches to do what I think you're shooting for with Universal Reports:

a) Consider starting from a Dynamic layout Universal Report (the normal one), it will be much more straightforward to swap in a custom whole-axis or hierarchy-set expression there. if you needed 'static'ness on the opposing axis (that led you to take a look at Static URs as the starting point), know that we do intend to offer additional MakeQuery functions that will more conveniently let you mix one axis dynamic and one axis static compositions - for the time being however, it is possible to define a similar 'name input area' outcome with Dynamic URs already.  The short of the approach is to have your name headers as before, but then leverage MakeMun referencing to compose them, and concatenate into a static list expression.

b) adjust your string processing to target the Q value outcome after a normal MakeQuery3 execution. this would let you rewrite the outcome on the fly with snippets of whatever you like.  To make this kind of thing a bit easier, you could put explicit placeholder values into the 'normal' spots (like the actual RH entries).

For me, I would recommend (a), but there are reasons why (b) can also be desirable in the present state of things - example would be if you have a desire to leverage the differences in gesture support for one layout vs another, like gutters. 

We'll continue to make sure that flexibility and alignment increase across all use cases for Universal Reports, always interested to hear how we can improve documentation/content as well (I believe we have more coming to Youtube for this).

for more check out:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=layout-universal-report-static-named-ranges