techie234


Hi,

I am newbie to reporting services and I need some help. Could any one please let me know how to replace Null value with 0 in the data section of the Matrix. I don't want blanks to be displayed on the report, I want those to be replaced with 0's.

Thanks for your time.




Re: replacing Null values with 0's in Matrix

Shyam Sundar


Use this expression:

IIf(Fields!YourField.Value = Nothing Or IsNothing(Fields!YourField.Value), 0, Fields!YourField.Value)

Shyam






Re: replacing Null values with 0's in Matrix

sluggy

Or you can get rid of the nulls at data level by using a coalesce or IfNull function on the field in the select statement.




Re: replacing Null values with 0's in Matrix

techie234

Thanks a lot guys for the reply.

Sluggy, I did try to use the coaslesceempty or the isempty function to replace the null's with 0's at the data level. But somehow in reporting services when it rolls up a value containing null's it displays '#Error' in the preview tab although in the data tab it gives proper results. But it works perfectly on the analysis services browser and also in Excel. Any idea why the '#Error" occours in Reporting services. the code i am using is

Case

When IsEmpty([Measures].[Account Count])

Then Null

Else
COALESCEEMPTY(([Measures].[Debtor Count],
[Account].[Account Close Flag].&[False]), 0)
End

Thanks.





Re: replacing Null values with 0's in Matrix

sluggy

RS will typically show #Error when you try to do an aggregate function on a list (or item) of data that contains a NULL, and i see with your IsEmpty statement you are setting it to null - set to zero instead.