Report Builder and NULL values

Hi,

I am trying to create a report with Report Builder, using a simple data model residing on SQL2005 reporting server.

The problem that literally drives me crazy is that I can't find any way to handle NULL values in my report. Say, a table has 3 numeric columns, and I need to create a formula to make a sum of those 3 columns. This would be an ordinary sum for the same entity, not an aggregate. So I create a new formula with following body:

Field1 + Field2 + Field3

The problem is, each one of the 3 columns can have NULL values, and if any column actually is NULL, I end up with empty sum value, instead of having sum of those columns that do have values.

What I need is one of the following:

- a way to check for NULL values before summarizing, like ISNOTHING() in Report Designer

- a way to replace NULL values with empty values, like ISNULL() or COALESCE() in TSQL

- a way to tell the report to just convert NULLs to empty values, like in Crystal Reports.

I haven't been able to find any of the above in Report Builder. Please help me!

Denis

This is a nice usability feature request.

Right now you can do it by building the following expression:
if(Field1 = Empty, 0, Field1) + if(Field2 = Empty, 0, Field2) + ...

|||

Thank you Alexander, this worked like a charm.

I think that this Empty keyword should be mentioned in Report Builder help system, or in a KB article.

Best regards,

Denis

P.S. Please also read my another usability request that I will submit very shortly.