IIF Mdx in IBM PAW not compiling

Post Reply
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

IIF Mdx in IBM PAW not compiling

Post by vishalpoddar90 »

Hello Everyone,

I am writing an IIF statement in mdx but it is getting failed to compile.

Scenario is based on the selection of version, different measure members should be shown.

Currently tried mdxs

1. IIF( [Scenario].CurrentMember = "Elem1", [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )
2. IIF( [Scenario].CurrentMember.Name = "Elem1", [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )
3. IIF( [Scenario].CurrentMember.Name = [Scenario].[Elem1], [Dim_Measure].[MeasureA], [Dim_Measure].[MeasureB] )

I used Filter too but still its not working. I tried mentioning the hierarchy with dimension name and that is also not working. I also tried writing this in Architect but I had no luck there too.

It would be great if anyone can help me here.

Thanks in advance
declanr
MVP
Posts: 1816
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: IIF Mdx in IBM PAW not compiling

Post by declanr »

Code: Select all

{StrToMember ( "[Dim_Measure].[" +
IIF ( [Scenario].CurrentMember.Name = "Elem1",
   "MeasureA",
   "MeasureB"
)
+ "]")}

Generally speaking, IIF should be inside of other functions that will return either a SET or MEMBER.

Think of it as the results of the IIF are TEXT/String and the engine doesn’t know what to do with it - so even if it has the same name as a set or member - you need to explicitly tell it to treat it as such. Here we do that with StrToMember.

Also worth noting that currentmember will return a Member Unique Name - so if you do a lookup of it you would need to check for [Scenario].[MyMember] but if you use the .Name suffix you can just look for the name itself.
Declan Rodger
User avatar
gtonkin
MVP
Posts: 1209
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: IIF Mdx in IBM PAW not compiling

Post by gtonkin »

Just to add to what Declan posted, you could extend to the following if needed:

Code: Select all

{StrToMember ( "[Dim_Measure].[" +
CASE [Scenario].CurrentMember.Name
	WHEN "Elem1" then "MeasureA"
	WHEN "....." then "Measure..."
	ELSE "MeasureB"
END
)
+ "]")}
Also, to reiterate what Declan mentioned too as it is critical to both IIF and Case. These return either a Numeric or a String - members or sets cannot be returned. You need to build these using the StrToMember.
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: IIF Mdx in IBM PAW not compiling

Post by vishalpoddar90 »

@devclanr @gtonkin
Thanks for the quick reply.

These solutions worked. Now in actual usecase, I need to return multiple measures. As you mentioned it cannot return members and sets, does it mean I have to write multiple StrToMember statements or is there a better way to do it. I have around 50 measures and based on selection of scenario, I need set of members to be displayed.
vishalpoddar90
Posts: 19
Joined: Wed Jun 28, 2017 12:27 pm
OLAP Product: IBM Planning Analytics
Version: 2.0.76
Excel Version: 365

Re: IIF Mdx in IBM PAW not compiling

Post by vishalpoddar90 »

@declanr @gtonkin

Thanks for the solution. I really appreciate it.
After a little search I came across STRTOSET function and it worked like a charm.
User avatar
gtonkin
MVP
Posts: 1209
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: IIF Mdx in IBM PAW not compiling

Post by gtonkin »

Glad you sorted it out - have a look at this Reference Guide, may save you some time searching in the future.
Post Reply