How to replace null with 0 in SSAS cube

Question

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:

whytheq · Accepted Answer · 2016-07-28T11:31:53.190

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

Suhandy Chow · Answer 2 · 2016-07-28T09:23:35.293

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.

score -1 · Answer 3 · answered Jul 28 '16 at 05:44

-1

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

answered Jul 28 '16 at 05:44

Vickyexpert

3,147
5
21
34

it returns COALESCE function does not exist in Microsoft Sql Server Analysis Services. I tried using COALESCEEMPTY(([Measures].[Employee Age Count]),"0" and got error in: Query (1, 9) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used. – Suhandy Chow Jul 28 '16 at 07:05
Let me check it as i told you i am not able to test it currently, but let me check it again – Vickyexpert Jul 28 '16 at 07:06

How to replace null with 0 in SSAS cube

3 Answers3