CODEWITHSUNDEEP
ssasmdxolap-cube
Suhandy Chow
Suhandy Chow

Reputation: 259

How to replace null with 0 in SSAS cube

I need to replace null with 0 in my SSAS cube. what is the proper way to achieve that?

This is my current query:

SELECT {([Measures].[Employee Age Count])} 
ON COLUMNS, { ([Dim Gender].[Gender Name].[Gender Name].ALLMEMBERS * 
[Dim Age Ranges].[Age Range ID].[Age Range ID].ALLMEMBERS ) }  ON ROWS 
FROM ( SELECT ( { [Dim Location].[Location].[Location Grp Name].&[BOSTON] }) ON COLUMNS
FROM [People Dashboard]) 
WHERE ( [Dim Location].[Location].[Location Grp Name].&[BOSTON] )

Result from current query:

enter image description here

Upvotes: 2

Views: 5759

Answers (3)

whytheq
whytheq

Reputation: 35605

I think that IIF(ISEMPTY... is pretty standard.

I have also simplified your script by deleting quite a few braces & also moving the logic out of the subselect into a basic WHERE clause:

WITH MEMBER [Measures].[MEASURE_NONEMPTY] AS 
  IIF(
     ISEMPTY([Measures].[Service Period Count])
     ,0
     ,[Measures].[Service Period Count]
  )
SELECT 
   {[Measures].[MEASURE_NONEMPTY]} ON 0,
       [Dim Gender].[Gender Name].[Gender Name].ALLMEMBERS 
     * [Dim Age Ranges].[Age Range ID].[Age Range ID].ALLMEMBERS  
     ON 1
FROM [People Dashboard]
WHERE [Dim Location].[Location].[Location Grp Name].&[BOSTON] 
;

Upvotes: 3

Suhandy Chow
Suhandy Chow

Reputation: 259

WITH MEMBER [Measures].[MEASURE_NONEMPTY] AS COALESCEEMPTY([Measures].[Service Period 
Count], 0)
SELECT {[Measures].[MEASURE_NONEMPTY]} ON COLUMNS, { ([Dim Gender].[Gender Name].[Gender 
Name].ALLMEMBERS * [Dim Age Ranges].[Age Range ID].[Age Range ID].ALLMEMBERS ) 
} ON ROWS FROM ( SELECT ( { [Dim Location].[Location].[Location Grp Name].&
[BOSTON] } ) ON COLUMNS FROM [People Dashboard]) WHERE ( [Dim Location].
[Location].[Location Grp Name].&[BOSTON] )

I used the code above for my solution.

Upvotes: 0

Vickyexpert
Vickyexpert

Reputation: 3171

I have not tested but you can try like below if it works,

SELECT {COALESCE (([Measures].[Employee Age Count]),"0") as AgeCount} 
ON COLUMNS, { ([Dim Gender].[Gender Name].[Gender Name].ALLMEMBERS * 
[Dim Age Ranges].[Age Range ID].[Age Range ID].ALLMEMBERS ) }  ON ROWS 
FROM ( SELECT ( { [Dim Location].[Location].[Location Grp Name].&[BOSTON] }) ON COLUMNS
FROM [People Dashboard]) 
WHERE ( [Dim Location].[Location].[Location Grp Name].&[BOSTON] )

Upvotes: -1

Related Questions