Replacing a Null field in mdx

Question

I have a situation where i have a null value coming in for a column and that value needs to be replaced with another value.

Current mdx

select
non empty { [measures.[Color count]} on columns
,non empty { [ColorColor].[Color].[Color].allmembers}
Dimention properties
member caption
,member_unique_name
on rows
from [Colors]

Current Results

Color     Color Count
null      1
Red       1
Blue      1
Purple    1
Black     1

Intended Results

Color     Color Count
Silver    1
Red       1
Blue      1
Purple    1
Black     1

Basically I need to replace the null with the color "silver". Also the null needs to be replaced in mdx and not in ssrs.

Answer 1

Using WITH MEMBER you can create a new item, giving it any name you want, and tell it to get its values from the NULL item. Then you can hide the items you don't want using the second argument of the EXCEPT function.

with member [ColorColor].[Color].[Color].[MyNewName] 
AS [ColorColor].[Color].[Color].[null]
select
non empty { [measures.[Color count]} on columns
,non empty { 
[ColorColor].[Color].[Color].[MyNewName],
EXCEPT({[ColorColor].[Color].[Color].allmembers},{[ColorColor].[Color].[Color].[null]})
}
Dimention properties
member caption
,member_unique_name
on rows
from [Colors]