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

Post Reply
wns@rav.no
Posts: 2
Joined: Mon Jun 25, 2018 12:54 pm
OLAP Product: Cognos TM1
Version: TM1 10.2.2, PA 2.0.3
Excel Version: Office 365

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

Post 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
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

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

Post by declanr »

Code: Select all

{TM1SubsetToSet ( [AT_Comparison], IIF ( [AT_Business].CurrentMember.Name = "Wholesaler", "Wholesales", "Store" ) )}
Declan Rodger
wns@rav.no
Posts: 2
Joined: Mon Jun 25, 2018 12:54 pm
OLAP Product: Cognos TM1
Version: TM1 10.2.2, PA 2.0.3
Excel Version: Office 365

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

Post 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.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

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

Post 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.
Declan Rodger
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

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

Post 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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

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

Post 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]) +"]"))
Post Reply