SSAS - Always show zeros - NOT blanks

Question

Hi,

I have a cube that I access via Targit (BI Solution). When I browse / use that cube I want all quantity results to be a zero if null / blank NOT a blank, but no matter what I try I get blanks. I have changed the Null Processing property for the measure in question as well as attempted to change / remove the format string. I still get blanks instead of zero's.

Thoughts?

UPDATE: I figured out that I could create a calculated measure and use iif / isempty funcitons to force zeros. This works - just wondering if there is a cleaner way to accomplish this.

Thanks

Bill

Answer 1

There may be two solutions if the source data contains NULL values for measure fields:

1. If you are using a table in the DSV, then replace it either with a view or a Named Query and ISNULL(field_name, 0) the nullable measure fields.
2. Use the calculated measure's code in the MeasureExpression property of the measure within the cube instead of in a separate calculated field. I've never tried it, but it seems like it would work.

If the problem is not caused by NULL values in the source data, then please elaborate on the problem.

Answer 2

Try using a formating expression such as FORMAT_STRING = "#.00;(#.00);#.00;-" This should give the desired results without the perf hit.

More info: http://msdn.microsoft.com/en-us/library/ms146084.aspx