MDX DYNAMIC CASE STATEMENT Using Perameter

Question

I tried to build dynamic MDX case statement using Parameter, but it shows blank results.

can anyone help to resolve this issue?

WITH MEMBER ContinentRollUP AS
CASE
WHEN [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE] .CURRENTMEMBER.MEMBERVALUE='RENTAL_REV' THEN CASE WHEN @Blake Sunderman =[FACT_Sales_V].[CURRENCY].[USD] THEN [Measures].[Sum of AMOUNT_USD] ELSE [Measures].[Sum of AMOUNT] END END
SELECT (ContinentRollUP) ON COLUMNS
,NON EMPTY(FILTER([DIM_LOC_V].[REGION_NAME].[REGION_NAME],instr([DIM_LOC_V].[REGION_NAME].CURRENTMEMBER.MEMBER_CAPTION,'ALL')=0),[DIM_LOC_V].[DIVISION].MEMBERS) ON ROWS
FROM [Model]

Thanks,
Sudhakar

Answer 1

Hi,
It looks like the case clause is not complete :
CASE WHEN [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE] .CURRENTMEMBER.MEMBERVALUE='RENTAL_REV' THEN
CASE WHEN @Blake Sunderman =[FACT_Sales_V].[CURRENCY].[USD] THEN [Measures].[Sum of AMOUNT_USD]
ELSE [Measures].[Sum of AMOUNT]
END
END

Either a then aggument is missing or an else is missing depending on what you are trying to do ?

Could you add more description to clarify what are you trying now ?
Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@sudhakar yeta - So if your first condition is false you have no else statement so the expression will return blank.

And your first expression will ALWAYS return false in your example query because you are referencing [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE].CurrentMember, but you do not have the [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE] attribute on any axis in your query so it will return the default member which is usually the ALL member.

Then your second nested condition also has issues as the = operator will force a numeric comparison between the parameter and the [USD] member and comparing a string to a number will also always return false.

I'm guessing that maybe you want something like the following:

WITH MEMBER ContinentRollUP AS
SUM( [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE].Members
CASE
WHEN [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE] .CURRENTMEMBER IS [DIM_JOB_BILLING_TYPE_V].[GLJC_TYPE].[RENTAL_REV] THEN CASE WHEN @Blake Sunderman ="USD" THEN [Measures].[Sum of AMOUNT_USD] ELSE [Measures].[Sum of AMOUNT] END END )
SELECT (ContinentRollUP) ON COLUMNS
,NON EMPTY(FILTER([DIM_LOC_V].[REGION_NAME].[REGION_NAME],instr([DIM_LOC_V].[REGION_NAME].CURRENTMEMBER.MEMBER_CAPTION,'ALL')=0),[DIM_LOC_V].[DIVISION].MEMBERS) ON ROWS
FROM [Model]