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
IIF Mdx in IBM PAW not compiling
-
- Posts: 19
- Joined: Wed Jun 28, 2017 12:27 pm
- OLAP Product: IBM Planning Analytics
- Version: 2.0.76
- Excel Version: 365
-
- MVP
- Posts: 1827
- 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
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
- gtonkin
- MVP
- Posts: 1254
- 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
Just to add to what Declan posted, you could extend to the following if needed:
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.
Code: Select all
{StrToMember ( "[Dim_Measure].[" +
CASE [Scenario].CurrentMember.Name
WHEN "Elem1" then "MeasureA"
WHEN "....." then "Measure..."
ELSE "MeasureB"
END
)
+ "]")}
-
- 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
@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.
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.
-
- 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
@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.
Thanks for the solution. I really appreciate it.
After a little search I came across STRTOSET function and it worked like a charm.
- gtonkin
- MVP
- Posts: 1254
- 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
Glad you sorted it out - have a look at this Reference Guide, may save you some time searching in the future.