Hi, I am writeing calculated field(Amount) with nested IIF condition, in SSRS report, and if none of the conditions are true then at last it should be NULL. So, in IIF condition at last (false part) when I write simply "" then in output it is giving #Error obviously because "" is treated as string. So, my question is what can I write at last false part such that if it finds any NULL value in database then it should not give #Error. Hope the question is clear enough. Thanks,

Can you have the last part be an empty string?

That's what...I can not put empty string (" ") at last. I mean i am already putting it and thats why its giving me that #Error. Below are my expressions:- Expression for the calculated field :- = IIF(Fields!REP_CLASS_NAME.Value = "REIMBURSABLE", 0, IIF (Fields!REP_CLASS_NAME.Value = "NON-REIMBURSABLE", Fields!ALLOTMENT_AMT.Value, IIF ((Fields!REP_CLASS_TYPE.Value = "EPF" AND Fields!REP_CLASS_NAME.Value IS NOTHING) OR (Fields!FUND_TYPE_CODE.Value = "GE" AND Fields!REP_CLASS_TYPE.Value IS NOTHING AND Fields!REP_CLASS_NAME.Value IS NOTHING), 0, ""))) -- there should be something else at the <bold> marked "" at the last... And below is textbox expression for that calculated field above , which I named as Amount_Non_Reimbursable =IIF(SUM(Fields!Amount_Non_Reimbursable.Value) is nothing, "NA" , SUM(Fields!Amount_Non_Reimbursable.Value))

I put your expression in one of my reports, and substituted my db fields, I get no errors (SSRS 2008) =IIF(Fields!CustomerName.Value = "Amy Tucker", 0, IIF(Fields!CustomerName.Value = "John Smith", Fields!OrderValue.Value, IIF((Fields!CustomerName.Value = "Philip Stone" AND Fields!PaymentAmt.Value IS NOTHING) OR (Fields!CustomerName.Value = "Jerry Mitchell" AND Fields!PaymentAmt.Value IS NOTHING), 0, ""))) Sorry, cannot help.

Hello UltraDev, You can use nothing instead of "". As "" is considered as string,it wont consider as a valid no value. And NULL in SSRS is represented with a blank. Hope this helps. SammieRS.

Hi Ultra, Use "0" in place of "" in your iif expression. Amit Mark as answer if helpful

Hi, Replace "" with Nothing. Hope its clear & helpful.... Pavan Kokkula Tata Consultancy Services.

Hello, Thanks guys, it worked. Just replaced "" with NOTHING and #Error is gone and displaying results correctly. Thanks :)- Marking as an answer