MDX - Use formatting to change the Null values into other Characters

 

Have you ever used the If statement to convert the null into some special character mainly for Excel based reports? If then there is no need for it, you can easily achieve this via formatting

Let us first do this in a wrong way

In the below code I am checking for null values and if it is null then replace it with “–” . It works perfectly fine and it can be directly linked with excel reports and handed over to users

with member measures.[Reseller Sales Amount Formatted] as

iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])

Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0

     ,[Date].[Calendar].[Date].members 

      * 

      [Geography].[Geography].[State-Province].members on 1

from [Adventure Works]

This has clearly produces the required result  as shown below,

What is the best way then?

Though this is correct in terms of results there is a simpler way of doing this with format_String as shown below

with member measures.[Reseller Sales Amount Formatted] as

[Measures].[Reseller Sales Amount], FORMAT_STRING = "$#,##.00;($#,##.00);$#,##.00;-"

Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0

     ,[Date].[Calendar].[Date].members 

      * 

      [Geography].[Geography].[State-Province].members on 1

from [Adventure Works]

So, Are we only talking about Aesthetic reasons here?

Actually no. Using the format option gives you lot of performance advantage in complex queries especially in Crosstab report.

E.g. Test the below query it takes around 30 seconds

with member measures.[Reseller Sales Amount Formatted] as

iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])

Select [Geography].[Geography].[State-Province].members on 0

     ,[Date].[Calendar].[Date].members      on 1

from [Adventure Works]

where  measures.[Reseller Sales Amount Formatted

Just change it with format option, it will get executed under 5 seconds and that should convince you.

Happy Reading