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")
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.
Try iif(Fields!CLOSURE_DT.Value System.DBNull.Value etc.
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'.
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'.
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")
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!
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") `
David, Brilliant! That worked. Sorry it took many iterations but I am fairly new to SSRS. Thank you!
19 People are following this question.
