Page 1 of 1

Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Tue Jun 26, 2018 7:56 am
by wns@rav.no
Hi!
I'm trying to create a dynamic subset using MDX where I want to use different subsets based on elements in another dimension.
I have to dimensions:
Business - with two elements; store and wholesale
Comparison - with five elements; actual, budget, factor, adjustment and adjusted budget.
And I want to show the elements in the comparison dimension different for store and wholesale (kind of like access tables in old enterprise planning). Like this :
store - actual and budget
wholesales - actual, budget, factor, adjustment and adjusted budget

So I thought I made two different static subsets and a third MDX subset where I choose subset based on the element in business dimension, but I clearly my syntax is wrong since I get a syntax error.
This is my code:
{
IIF(
[AT_Business].CurrentMember.Name = 'Wholesaler',
TM1SubsetToSet([AT_Comparison], "Wholesales"),
TM1SubsetToSet([AT_Comparison], "Store"))
}

Is this possible? And if so what is the correct syntax?

Thanks,
Willem

Re: Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Tue Jun 26, 2018 9:22 pm
by declanr

Code: Select all

{TM1SubsetToSet ( [AT_Comparison], IIF ( [AT_Business].CurrentMember.Name = "Wholesaler", "Wholesales", "Store" ) )}

Re: Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Wed Jun 27, 2018 9:02 am
by wns@rav.no
Thanks!
The MDX-query is correct now.
But it doesn't differ between the members of the Business-dimension.
It takes one subset and applies it to all members of the dimension.

If I set
[AT_Business].CurrentMember.Name = "Wholesaler"
it uses the "Wholesales"-subset for all members and if I set
[AT_Business].CurrentMember.Name = "Store"
it uses the "Store"-subset for all members.

I'm starting to think that this isn't possible do.

Re: Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Wed Jun 27, 2018 9:27 am
by declanr
To be able to use currentmember you need the dimension that it refers to to be a title element, so you can only have 1 “business” selected at a time.
I assume from your last comment you are trying to have the dimensions nested in which case it won’t work as each dimension can only apply 1 subset at a time.

Re: Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Sat Jun 30, 2018 8:08 pm
by paulsimon
Hi

It sounds as though what your are looking for are asymmetric nested dimensions. You can't have those in the Cube Viewer since as soon as you move the AT_BUSINESS dimension from the Title Area to rows or columns you have the Cube Viewer restriction that you cannot have a dimension in the row or column position where the elements vary so you cannot have eg AT_BUSINESS and AT_COMPARISON nested and showing different elements of AT_COMPARISON for different elements of AT_COMPARISON.

However, if you use PAX then you can make the rows and/or columns asymmetric and have eg different inner elements nested within each outer element, so that may be the answer for you. Using the VBA for PAX you can make your own MDX.

Another alternative that may be simpler but you may have already thought of, is that if you zero suppress and ensure that the values for the versions you don't want to display are zero then you should be able to get what you want.

Regards

Paul Simon

Re: Is it possible to choose between subsets with IIF-statement in a MDX-subset?

Posted: Thu Jul 12, 2018 11:07 am
by Mark RMBC
Hi all,

I thought I would hijack this thread rather than create a new New topic!

I have a view which returns those cost centres where there is a difference between one version and another (in the same cube). The measure it looks at is a consolidated one. To allow for this I have some mdx against the cost centre dimension, which is as follows:

{Filter(
TM1FILTERBYLEVEL({DESCENDANTS([F_CostCentre].[All_CostCentres]) }, 0),
[BudPlan_MTFS_Main].([F_Detail].Currentmember, StrToMember("[C_Year].[" + Left([adm_Assumptions].([A_Area].[General], [AB_AdminAnalysis].[CurrentYr]),4) +"]"), [B_Versions].[BudPlan_Planning], [EB_MTFSMeasures].[Latest_Budget])
-
[BudPlan_MTFS_Main].([F_Detail].Currentmember, StrToMember("[C_Year].[" + Left([adm_Assumptions].([A_Area].[General], [AB_AdminAnalysis].[CurrentYr]),4) +"]"), StrToMember("[B_Versions].[" + [adm_Assumptions].(A_Area].[MTFS], [AB_AdminAnalysis].[NYRScenVersion]) +"]"), [EB_MTFSMeasures].[Latest_Budget])<>0)}



So the above mdx will return all codes where the BudPlan_Planning version minus the NYRScenVersion is not zero. This works fine and appears to return the correct cost centres.

However I want this mdx created subset to be a little more dynamic so I can compare BudPlan_Planning version with either the NYRScenVersion or the CYRScenVersion based on whether a value in the admin cube is Yes or No. Now I could achieve this by creating 2 subsets for NYRScenVersion and the CYRScenVersion and use the following mdx,

{TM1SubsetToSet ( [B_Versions], IIF ( [adm_Assumptions].(A_Area].[General], [AB_AdminAnalysis].[StartNYRSetting]) = "Yes", "NYR_CycScenVer", "CYR_CycScenVer" ) )}

But just out of curiosity I wondered if I could somehow incorporate the iif statement into my current mdx, I tried a few alternatives but couldn’t get anything to work.

Basically I want to replace this part of the mdx with an iif alternative,

StrToMember("[B_Versions].[" + [adm_Assumptions].(A_Area].[MTFS], [AB_AdminAnalysis].[NYRScenVersion]) +"]")

So something like,

IIF ( [adm_Assumptions].(A_Area].[General], [AB_AdminAnalysis].[StartNYRSetting]) = "Yes", StrToMember("[B_Versions].[" + [adm_Assumptions].(A_Area].[MTFS], [AB_AdminAnalysis].[NYRScenVersion]) +"]"), StrToMember("[B_Versions].[" + [adm_Assumptions].(A_Area].[MTFS], [AB_AdminAnalysis].[CycleScenVersion]) +"]"))