question

1hensleyd avatar image
1hensleyd asked

Using Not Null in an SSRS

I am trying to join two fields as criteria before highlighting a field. I need to show the closure date field highlighted when the closure date it not null but the document status is Incomplete. I have tried many variations and nothing works yet. Ex.1 - =IIf(IS NOTHING(Fields!CLOSURE_DT.Value) and Fields!RFD_STATUS.Value ="INCOMPLETE","Transparent","Pink") Ex.2 - =IIf(Fields!CLOSURE_DT.Value = "" and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink") **EDIT** This is what I have now: =IIF((Fields!CLOSURE_DT.Value <> System.DBNull.Value and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink") this is the error I get now: The BackgroundColor expression for the text box ‘CLOSURE_DT’ contains an error: [BC30452] Operator '<>' is not defined for types 'Object' and 'System.DBNull'. I even tried putting parenthesis around the first portion and received the same error. =IIF(((Fields!CLOSURE_DT.Value <> System.DBNull.Value) and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink")
ssrsnullvalues
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please don't add a comment as answers, you should see an "add new comment" now that I up-voted your question. You should either update your question or answer if you are adding additional information.
1 Like 1 ·
David Wimbush avatar image
David Wimbush answered
Try iif(Fields!CLOSURE_DT.Value System.DBNull.Value etc.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi David, Here is what I modified it to: =IIF((Fields!CLOSURE_DT.Value System.DBNull.Value and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink") and this is the error I receive: The BackgroundColor expression for the text box ‘CLOSURE_DT’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
0 Likes 0 ·
Sorry, part of my code keeps disappearing when I submit it! There's supposed to be a less than sign and a greater than sign between Fields!CLOSURE_DT.Value and System.DBNull.Value. To express the 'not equals'.
0 Likes 0 ·
1hensleyd avatar image
1hensleyd answered
This is what I have now: =IIF((Fields!CLOSURE_DT.Value <> System.DBNull.Value and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink") this is the error I get now: The BackgroundColor expression for the text box ‘CLOSURE_DT’ contains an error: [BC30452] Operator '<>' is not defined for types 'Object' and 'System.DBNull'. I even tried putting parenthesis around the first portion and received the same error. =IIF(((Fields!CLOSURE_DT.Value <> System.DBNull.Value) and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink")
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I thought that would work. Apparently you need to use the ISNOTHING function: `ISNOTHING(Fields!CLOSURE_DT.Value)`. Looks like you were one space away!
0 Likes 0 ·
I put the ISNOTHING in and still get the same error regarding the <> operator (as stated above). =IIF((ISNOTHING(Fields!CLOSURE_DT.Value <> System.DBNull.Value) and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink")
0 Likes 0 ·
No, use ISNOTHING instead of the System.DBNull thing. Like this: `=IIF((NOT(ISNOTHING(Fields!CLOSURE_DT.Value)) and Fields!RFD_STATUS.Value ="INCOMPLETE"),"Transparent","Pink") `
0 Likes 0 ·
David, Brilliant! That worked. Sorry it took many iterations but I am fairly new to SSRS. Thank you!
0 Likes 0 ·
No problem. Glad we got there in the end.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.