Friday, March 30, 2012

replacing Null values with 0's in Matrix

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.

Use this expression:

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

Shyam

|||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.|||

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.

|||

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.

No comments:

Post a Comment