iif statement that evaluates two columns in SSRS

Question

I'm new to SSRS and I'd like to create a calculated field that subtracts two columns. However, I've got a few blank fields in each column that SSRS processes like a zero. I want SSRS to create an n/a result if either field is blank, but subtracts the two fields if there is a value in BOTH columns. Maybe a "if nothing" statement"?

I came up with this but only generates n/a if both columns have a blank:

=iif(Fields!Prior_A1c.Value <> 0 OR Fields!A1c.Value <> 0, (Fields!A1c.Value-Fields!Prior_A1c.Value), "n/a")

Here is what I want it look like ([difference] is the calculated column):

 A1C       Prior A1c      Difference
   7          5              2
              6             n/a
   6                        n/a
   5          6             -1

Thank you in advance.

Answer 1

Use Isnumeric(Fields!Prior_A1c.Value) or IsNothing(Fields!Prior_A1c.Value) to test for your empty string.

Also, your IIF logic isn't how it should be I don't think. You are stating that if either of your columns aren't 0 then do the subtraction, when I think you want if either are blank / null then use NA otherwise do the subtraction. I think you want it like this:

=iif(IsNothing(Fields!Prior_A1c.Value) OR IsNothing(Fields!A1c.Value),"N/A",(Fields!A1c.Value-Fields!Prior_A1c.Value))

Answer 2

try this:

=iif(Fields!Prior_A1c.Value <> 0 OR Fields!A1c.Value <> 0, (
iif(Fields!A1c.Value=Nothing,0,Fields!A1c.Value) - iif(Fields!Prior_A1c.Value=Nothing,0,Fields!Prior_A1c.Value)
), "n/a")