MDX query for getting the non null value records

0
votes

I am new with MDX, Can any one of you help me out for the below queries?

enter image description here

1) From the above result set how can I eliminate rows which are having the null values in the column1 & column3 using below query.

WITH
MEMBER TotalPatientCnt as '[Measures].[HealthPlanPatientCnt]'
MEMBER PrevalencePercent as '([Measures].[PatientCnt]*1.00/[Measures].    [TotalPatientCnt])*100'

SELECT 
NON EMPTY {[DimCP_Population].[Dr Key].[Dr Key]}
ON rows, 
 { 
     [Measures].[PatientCnt] ,[Measures].[TotalPatientCnt]
     ,[Measures].[PrevalencePercent]

}
ON columns
FROM [StrategyCompanionDWCube]
WHERE 
(
    [DimAnchorDate].[Date Key].&[20121231], 
    [DimCP_PCP].[PCP Key].&[124],
    [DimProduct].[Product Key].&[15],
    [DimHealthPlan].[Health Plan Key].&[1]
)

;

2) I have 3 dimensions & 3 measures respectively 

[DimCP_PCP], [DimProduct], [DimHealthPlan]

pcpCnt, productCnt, HelahtPlanPatientCnt

I would like to build same TotalPatientCnt member based on type of dimension with parameter value, Hope needs to write case statement but i could not write case statement please help us on the below examples.

WITH MEMBER TotalPatientCnt as 
case 
when [DimProduct].[Product Key].&[if getting anymember] then
'[Measures].[productCnt]'
when [DimHealthPlan].[Health Plan Key].&[if getting anymember] then
'[Measures].[HealthPlanPatientCnt]'
when [DimCP_PCP].[PCP Key].&[if getting anymember] then
'[Measures].[pcpCnt]'

If I get any parameter from [DimCP_PCP] for example [DimCP_PCP].[PCP Key].&[124] needs to show the TotalPatientCnt member like below

WITH MEMBER TotalPatientCnt as '[Measures].[pcpCnt]'

If I get any parameter from [DimHealthPlan] for example [DimHealthPlan].[Health Plan Key].&[1] needs to show the TotalPatientCnt member like below

WITH MEMBER TotalPatientCnt as '[Measures].[HealthPlanPatientCnt]'

If I get any parameter from [DimProduct] example [DimProduct].[Product Key].&[15] needs to show the TotalPatientCnt member like below

WITH MEMBER TotalPatientCnt as '[Measures].[productCnt]'
2
ssasmdx
That are two questions. I would suggest you delete the second, and post it as separate question. – FrankPl

2 Answers

4
votes

To answer your first question:

As NON EMPTY does not work for the rows here, as the second column is not empty for the records to exclude, you can use the rarely documented HAVING clause like this:

...
{[DimCP_Population].[Dr Key].[Dr Key]}
HAVING NOT IsEmpty([Measures].[PatientCnt])
   AND NOT IsEmpty([Measures].[PrevalencePercent])
ON rows
...
1
votes
SELECT 
(
[DimAnchorDate].[Date Key].&[20121231], 
[DimCP_PCP].[PCP Key].&[124],
[DimProduct].[Product Key].&[15],
[DimHealthPlan].[Health Plan Key].&[1],
[Measures].[PatientCnt],
[Measures].[TotalPatientCnt]
,[Measures].[PrevalencePercent]
) On 0,
NON EMPTY {[DimCP_Population].[Dr Key].[Dr Key]}
ON 1       
FROM [StrategyCompanionDWCube]

OR

Understand the difference between NON-EMPTY vs Nonempty()

Here, you might get your answer and to do clear concept of NULL and EMPTY