SQL Server Report expression failing for NULL int values

Question

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.

Ross Bush · Accepted Answer · 2018-12-01 06:06:25Z

1

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"))

answered Dec 1, 2018 at 6:06

Ross Bush

15.2k2 gold badges39 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Report expression failing for NULL int values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related