locked
How to set null value to 0 for measures? RRS feed

  • Question

  • Hi ,
    I have a query with regards to SSAS cube. I have a cube with 4 fact tables and around 20 measures. The question is I need to set null value of the measures to 0 i.e., to have a check for isEmpty for the existing measures instead of creating new members in the calculation of the cube. Is there a way to do this?
    Appreciate your help in this.

     

    Best Regards

     

    Tuesday, July 22, 2008 9:30 AM

Answers

  • I think the answer is to set a format string that will do what you want. For instance, if you have no fact row for Jan 1 sales for the North region, and if you want that to display as zero, not null, you will need a format string with a 4th segment:

     

    #,##0;(#,##0);0;\0 

     

    The answer Thomas was giving you would have been helpful if you had null values in your fact table.

     

    Also, what client tool are you using? This might make a difference if you're not seeing that combination at all.

    Tuesday, July 22, 2008 3:49 PM
  • I think this still may be a bug, but I did notice a way around it. Right click on the PivotTable and choose PivotTable Options. Then you see a "For empty cells show" option. Set it to 0. That should do the trick.

     

    Wednesday, July 30, 2008 9:52 PM

All replies

  • Hi,

     

    I have written a blog post about this topic here:

     

    http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!558.entry

     

    HTH

    Thomas Ivarsson

     

    Tuesday, July 22, 2008 9:41 AM
  • Hi Thomas,

    Thanks for your reply. But when I tried to set the NullProcessing = ZeroOrBlank or Automatic and browse my cube i still get empty cell. I actually need to set to 0 instead of empty. Appreciate your help.

     

    Regards

    Vijay

    Tuesday, July 22, 2008 9:55 AM
  • Hi again,

     

    I am not sure why this does not work. If you can tell what client you are using I might be able to check that.

     

    To set zero for null values in the fact table is not a good practice.

     

    Best regards

    Thomas Ivarsson

    Tuesday, July 22, 2008 2:40 PM
  • I think the answer is to set a format string that will do what you want. For instance, if you have no fact row for Jan 1 sales for the North region, and if you want that to display as zero, not null, you will need a format string with a 4th segment:

     

    #,##0;(#,##0);0;\0 

     

    The answer Thomas was giving you would have been helpful if you had null values in your fact table.

     

    Also, what client tool are you using? This might make a difference if you're not seeing that combination at all.

    Tuesday, July 22, 2008 3:49 PM
  • Good point Greg!

     

    Formatting is an option.

     

    Regards

    Thomas Ivarsson

     

    Tuesday, July 22, 2008 4:20 PM
  • Hi,

    I tried this option of formatting, it works well in the BI studio when i browse the cube. But when i try from the Excel client using pivot table it still shows empty. Apprecia your advise and help in this..

     

    Regards

    VJJ

    Wednesday, July 23, 2008 9:12 AM
  • Looks like an Excel bug. If you look at the MDX it generates, it ends with:

     

    CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

     

     

    Note it doesn't ask for the Formatted_Value property? That means Excel is using the Format_String and formatting it in Excel. Apparently it's ignoring that 4th segment and not formatting null values.

     

    If you know where to report Excel bugs, please report.

     

    Wednesday, July 30, 2008 9:06 PM
  • I think this still may be a bug, but I did notice a way around it. Right click on the PivotTable and choose PivotTable Options. Then you see a "For empty cells show" option. Set it to 0. That should do the trick.

     

    Wednesday, July 30, 2008 9:52 PM