Use string values in a cube to populate subset using MDX.

[Steve Rowe]

Hi,
Playing around with a bit of MDX which touches on a few posts that have come up recently and believe the forum has stated quite clearly that this is not possible.

In the below example is it possible, using MDX, to create a subset of the elements in Jnl.ID based on the string values that have been entered in a cube.

exmaple.gif (66.49 KiB) Viewed 3598 times

[gtonkin]

HI Steve,
Apologies if I am off track with the response-mind is a bit numb but sounds like something I recently did.
I have an Employee cube with Measures, one being a Manager ID. Managers are Employees and appear in the Manager dim.
I needed a dynamic subset of Managers, sorted by Surname (another measure) without wanting to run TI's, flag using attributes etc. the code below magically did this for me:

Code: Select all

ORDER(
{GENERATE(FILTER(TM1SUBSETALL( [Employee] ),
[Employee Planning].([Scenario].[RP 2016],[Employee Planning Measures].[Manager ID])<>""),
{StrToMember("[" + [Employee Planning].([Scenario].[RP 2016],[Employee].CurrentMember,[Employee Planning Measures].[Manager ID]) + "]")})},
[Employee Planning].([Scenario].[RP 2016],[Employee Planning Measures].[Surname]),BASC)

I hope you can translate into your scenario and trust that it yields fruitful results.

[Steve Rowe]

Cool thanks, "StrToMember" is what I was missing I think. Cheers

[Wim Gielis]

Interesting formula, gtonkin. I should experiment to see if I can reuse the concepts.
For example, a customer dimension with country hierarchies in an Active form.
The countries are ranked by sales value - descending, while within each country, the customers are ranked by sales - ascending. Requirement of the customer.
The drawback is that collapsing and expanding a country consolidation undoes the ranking of the children (customers) and defaults to ranking by index number in the dimension.