CODEWITHSUNDEEP
reporting-servicesssrs-2008reportbuilder3.0
Jsapp
Jsapp

Reputation: 1

How can I eliminate #error in SSRS expression when subtracting by field that may or may not be null?

I have several fields in my report, but this question is related to two specific fields (Field2 and Field3).

I need to subtract Field3 from Field2 to come up with figure for new Field. My problem that I can't seem to figure out is that I get #Error in my new field when Field3 is Null. Below is the formula I have and get the error with when Field3 is Null. 

=switch(isnothing(Fields!FIELD3.Value) = 1, "" ,Fields!FIELD3.Value = 0,"" , Fields!FIELD3.Value <> 0, Fields!FIELD3.Value - Fields!FIELD2.Value)

Below is sample data of Field2 and Field3 data and the formula field (Field4) results as well as what I want the results to actually be. I tried to attach actual image, but since new to this site don't have the level allowed to post images. 

FIELD2    FIELD3    FIELD4             FIELD4_DESIRED_RESULTS
85.96     NULL      #Error             -
1428.85   476.28    -952.57            -952.57
500.00    600.00    100.00             100.00

Upvotes: 0

Views: 4484

Answers (2)

Barry
Barry

Reputation: 1

Well, I'm not sure about NULL being an empty string, but this works for me

=IIF(IsNothing(Fields!FIELD3.Value),0,Fields!FIELD3.Value) - 
 IIF(IsNothing(Fields!FIELD2.Value),0,Fields!FIELD2.Value)

I know IsNothing works and it should be applied to all fields in the calculation unless you are absolutely certain that a field can never be NULL.

Upvotes: 0

Mike
Mike

Reputation: 550

Reporting Services evaluates null as "". The expression below should work:

=IIF(Fields!FIELD3.Value = "",0,Fields!FIELD3.Value) - Fields!FIELD2.Value

Upvotes: 1

Related Questions