CODEWITHSUNDEEP
sql-serverreporting-services
Paul Gibson
Paul Gibson

Reputation: 634

SQL Server Report expression failing for NULL int values

IIF is giving me some hell. When I do this everything works:

=IIF (IsNothing(First(Fields!Temperature.Value, "ReportInfoDataSet")),"NULL","GOOD")

But when I want to actually use the value, I end up with #Error for the NULL values, and from what I've read it's because IIF evaluates everything at once, so the .ToString() argument fails for the "GOOD" condition even though it is not used.

This gives #Error when I have a null value:

=IIF(
     IsNothing(First(Fields!Temperature.Value, "ReportInfoDataSet")), 
     "N/A",
     First(Fields!Temperature.Value, "ReportInfoDataSet").ToString()
)

So how do I work around the fact that IIF wants to evaluate all terms? There is a TechNet article that shows nesting another IIF statement as the "GOOD" condition that helps with the NULL value, but I still get the error (doing this for example):

=IIF(
   IsNothing(First(Fields!Temperature.Value, "ReportInfoDataSet")),
   "N/A", 
   IIF(
        IsNothing(First(Fields!Temperature.Value, "ReportInfoDataSet")),
        "N/A",
        First(Fields!Temperature.Value,"ReportInfoDataSet").ToString() & ChrW("&H00B0") & "F"
    )
)

Here is the article that indicates this potential solution, but it seems that I am still missing something, or that something changed invalidating this solution.

One further bit of information: I put a breakpoint in just after I fill the datatable, thinking that I would just intercept the table fill and modify so that NULLs turn to 0 or something, and it seems that datatables are strongly typed and don't allow for nullable data types so there is an exception for the temperature column:

'(ReportInfoRow((new System.Linq.SystemCore_EnumerableDebugView((test.Table).Rows)).Items[0]))
.Temperature' threw an exception of type 'System.Data.StrongTypingException'

Any insights are valued . . . I'm pondering whether I just want to add an extension to the dataset and add a MyTemp parameter that fixes the value for the report.

Upvotes: 0

Views: 59

Answers (1)

Ross Bush
Ross Bush

Reputation: 15155

To not have the .ToString() forced in the lack of short-circuit Boolean evaluation above, replace it with CSTR().

CSTR(First(Fields!Temperature.Value, "ReportInfoDataSet"))

Upvotes: 1

Related Questions