Hi,
I was wondering if any knew a way to capture the name of a subset for reference in an IIF MDX expression on an unrelated dimension.
For example:
Say I have the “FIN OCoA” account dimension on the rows of my view and I set “Enable Subset Selection” to ON for this dimension. My users will now have the ability to select between “Profit and Loss” and “Balance Sheet” subsets, and accounts will update accordingly on rows without issue.
My users have however asked for selected “GL Measure” to change when they change the account subset. They want the view to read from “Closing Balance” if they select “Balance Sheet” subset, and the measure to instead point to “Movement” when they select the “Profit and Loss” subset.
Is there any way to get a handle to the selected Account Subset value so that I can reference it in an IIF MDX statement on the “GL Measure” dimension?
Also… I’m very new to ApliqoUX so was wondering if there’s any documentation I can refer to which details the settings service variables available to me and the syntax for using them?
Hi,
We typically cover those things in an initial training and provide examples with the UX demo application - it might be worthwhile reserve some time with your consultant to do a crash course to get you up to speed. There is also the documentation of the advanced options with examples embedded in the application (I suppose you have seen this already). We should add to this documentation a cheat sheet on the usage of MDX placeholders - thanks for the suggestion!
Anyways, what you want to do is possible - the syntax for the placeholder would be:
$<<ApliqoFPM.FIN OCoA.FIN OCoA Subset>>
The general syntax is: <<instance.dimension.hierarchy>>, e.g. <<ApliqoFPM.FIN OCoA.FIN OCoA>> (which returns the principal name of a selection) and further options are shown as examples for the FIN OCoA dimension:
$<<ApliqoFPM.FIN OCoA.FIN OCoA-alias>> (returns the assigned alias/attribute)
$<<ApliqoFPM.FIN OCoA.FIN OCoA::Description>> (returns any attribute of the selected element, “Description” in this example)
We had actually tried exactly that syntax before submitting this topic. We entered it in the view’s title, to see if we could return a result. Unfortunately it always resolves to blank.
I am able to retrieve the name of elements from the View Global Settings using that syntax in the title. eg: $<<apliqo.FIN Version.FIN Version>> will return “ACT - Actual” from the global settings.
However, using $<<apliqo.FIN OCoA.FIN OCoA Subset>> sadly fails to return the subset name that the rows are referencing.
As additional information on this… the fact the MDX placeholders even exist at all were only pointed out by the Cubewise consultant who was doing the hand over to me.
However, neither I nor the Cubewise consultant were able to get the Subset placeholder to return any value. Which is why I raised this topic on his (and my) behalf.
are all documented, but unfortunately $<<instance.dimension.hierarchy Subset>> isn’t. We will see what we can do about closing that gap.
The important thing to note is that the subset name is only stored in the setting service (and therefore only retrievable) under the following conditions:
the dimension must be “global” (that is either visible in the filterBar on on rows/columns in a View. If the dimension is a widget level filter in a Dashboard or on rows/columns in a widget then the subset won’t be stored in the setting service)
the list type must be defined as “subset” (not MDX, not Min/Max, not anything else)
Thanks for you help. I was unable to get $<<instance.dimension.hierarchy Subset>> to work even after your help.
For clarity, the subset who’s value I was trying to capture was the “FIN OCoA” dimension on the rows of the main view. List type was subset. It had 3 predefined subset options flagged for user to select from. “Allow subset selection” was true so user could change subset value at top of page and rows updated accordingly.
With that setup, I was unable to get $<<instance.dimension.hierarchy Subset>> to return a value.
In the end, I came up with a workaround.
I created a new dimension called “FIN OCoA Subsets”, with leaf elements exactly matching the 3 subset options in the “FIN OCoA” dimension.
I added the new dimension to the View Global Settings
I changed list type of the rows on the view from Subset to MDX, turned off subset selection, and updated the MDX of rows to be “{TM1SubsetToSet( [FIN OCoA].[FIN OCoA], “$<<apliqo.FIN OCoA Subsets.FIN OCoA Subsets>>” )}”
To this point I hadn’t effectively changed anything as report still functioned exactly the same and looked the same to the user. However, by adding that extra “subset” dimension, I could get a handle to its value (my initial issue) and use that handle to dynamically select the measure via “{tm1SubsetToSet( [FIN General Ledger Measure].[FIN General Ledger Measure], “$<<apliqo.FIN OCoA Subsets.FIN OCoA Subsets>>”)}”.
So got there in the end via a work around!
One final question @cw-ch, you mention the placeholders are documented? Where do I go to view this documentation? I’m very keen to read up on it! Thanks again.
@monder , I believe that UX by default does not load up the entire elementAttribute cube for the filter dimensions for performance reasons. I found that for $<instance.dim.hier::attr> to work consistently, we need to force the elementAttribute cube load which can be achieve through the “getAllAttributeValues” parameter:
