CODEWITHSUNDEEP
ssasmdx
mohan111
mohan111

Reputation: 8865

MDX query for getting the non null value records

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]'

Upvotes: 0

Views: 12562

Answers (2)

Mitul Panchal
Mitul Panchal

Reputation: 632

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

Upvotes: 1

FrankPl
FrankPl

Reputation: 13315

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
...

Upvotes: 4

Related Questions