Question about IIF() function?

I tried to replace the value on rows using the IIF() function and it did not work. The following is the mdx code.

WITH MEMBER [Measures].[ParameterCaption] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified"))) ON ROWS FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS FROM [AP Statistics by Patient Type])

What is wrong with the IIF() function....

IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

Thanks

The problem is that you are returning strings and the row axis needs a set of members. You would need to put your IIF in a calculated measure on the columns and also define a set of members on the rows.

At a guess it would probably need to look something like the following (my changes in red).

WITH MEMBER [Measures].[ParameterCaption]

AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[DIM_Patient].[In Out Patient].CURRENTMEMBER.LEVEL.ORDINAL'

MEMBER [Measures].[PatientCalc] AS IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="I",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("In")),IIF([DIM_Patient].[In Out Patient].CURRENTMEMBER.NAME="O",VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Out"),VAL([DIM_Patient].[In Out Patient].CURRENTMEMBER.PROPERTIES("Unspecified")))

SELECT {[Measures].[ParameterCaption]

, [Measures].[ParameterValue]

, [Measures].[ParameterLevel]

, [Measures].[PatientCalc]}

ON COLUMNS ,

[DIM_Patient].[In Out Patient].Members ON ROWS

FROM ( SELECT ( STRTOSET(@.DIMSourceSource, CONSTRAINED) ) ON COLUMNS

FROM [AP Statistics by Patient Type])