Page 1 of 1
Use string values in a cube to populate subset using MDX.
Posted: Thu Dec 03, 2015 10:11 am
by 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 3594 times
Re: Use string values in a cube to populate subset using MDX.
Posted: Thu Dec 03, 2015 3:31 pm
by 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.
Re: Use string values in a cube to populate subset using MDX.
Posted: Thu Dec 03, 2015 4:46 pm
by Steve Rowe
Cool thanks, "StrToMember" is what I was missing I think. Cheers
Re: Use string values in a cube to populate subset using MDX.
Posted: Fri Dec 04, 2015 8:54 am
by 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.