SSRS - Checking whether the data is null

Question

I've the following expression in my report.

=FormatNumber(MAX(Fields!Reading.Value, "CellReading_Reading"),3)

Now when the dataset is empty 'Fields!Reading.Value' becomes empty and finding their maximum is invalid. How can i check if the entire column is empty?

I tried the following with no luck.

=iif(IsNothing(Fields!.Reading.Value),"",FormatNumber(MAX(Fields!Reading.Value, "CellReading_Reading"),3))

But still i'm getting #Error in the report. I also checked out link and was not able to get a clue from it. I want to handle it in the report level.

Volta

Answer 1

try like this

= IIF( MAX( iif( IsNothing(Fields!.Reading.Value ), -1, Fields!.Reading.Value ) ) = -1, "",  FormatNumber(  MAX( iif( IsNothing(Fields!.Reading.Value ), -1, Fields!.Reading.Value ), "CellReading_Reading"),3)) )

Odontoid

Answer 2

Or in your SQL query wrap that field with IsNull or Coalesce (SQL Server).

Either way works, I like to put that logic in the query so the report has to do less.

Knuth