Geoffseymour.Com

When designing dataset in Report Designer, null value is ignored by default because the corresponding MDX query uses “NON EMPTY” keyword. This raises a problem in reporting when Date is a dimension because we want to show every date in the range even there is no item or value for the date.

This can be easily done by removing “NON EMPTY” keyword in MDX queries. However, the value for these dates will be (null) which may not be the desirable output. For example, we have a field “Order Quantity”, and we want to report daily order quantities. If we have quantity of 10,0,20 in three consecutive days, because it is null instead of 0, when you chart the quantity there will be a straight line from 10 to 20 directly, instead of two segments (10 to 0 and then 0 to 20). Therefore, we need to assign some default value (e.g. “0″) to . This can be done by using “IIf” and “IsNothing” functions:

By removing “NON EMPTY” keyword in MDX, we may have some days with (null) in “Fields!OrderQuantity.Value”, now we need to assign default value to 0. This is the expression:

=IIf(IsNothing(Fields!OrderQuantity.Value),0,Fields!OrderQuantity.Value)

Now the field will have value 0 if there is nothing for the date.

Source: chenty