dkim18
asked on
#error in my ssrs report?
Hi,
I don't why I am getting this #error message from my report.
Here is the expression:
=iif(Fields!Manager.value. equals("Ye s") OR Fields!Country.value.equal s("USA"),
iif(Isnothing(Fields!Order Date.value .equals("Y es"),"",
Month(Fields!OrderDate.val ue) & "/" & day(Fields!OrderDate.value ) & "/" & mid(year(Fields!OrderDate. value),3,2 )),
"N/A")
manager country, orderdate shipdate
No, null, 1/2/09 #error
Yes, null, 1/2/09 1/12/10
No, USA, 1/2/09 2/2/10
No, CHN, 1/2/09 N/A
No, JPN, 1/2/09 N/A
Ones with manager= no and country=null are giving out #errors?
Don't know why??
I don't why I am getting this #error message from my report.
Here is the expression:
=iif(Fields!Manager.value.
iif(Isnothing(Fields!Order
Month(Fields!OrderDate.val
"N/A")
manager country, orderdate shipdate
No, null, 1/2/09 #error
Yes, null, 1/2/09 1/12/10
No, USA, 1/2/09 2/2/10
No, CHN, 1/2/09 N/A
No, JPN, 1/2/09 N/A
Ones with manager= no and country=null are giving out #errors?
Don't know why??
ASKER
I am sorry I wrote this incorrectly. It should be
=iif(Fields!Manager.value. equals("Ye s") OR Fields!Country.value.equal s("USA"),
iif(Isnothing(Fields!Order Date.value ,"", <===
Month(Fields!OrderDate.val ue) & "/" & day(Fields!OrderDate.value ) & "/" & mid(year(Fields!OrderDate. value),3,2 )),
"N/A")
Also I am still getting the #error when manager= no and country=null
=iif(Fields!Manager.value.
iif(Isnothing(Fields!Order
Month(Fields!OrderDate.val
"N/A")
Also I am still getting the #error when manager= no and country=null
Please paste the code into yours the same way
=IIF(Trim(Fields!Manager.v alue)="Yes " OR Trim(Fields!Country.value) ="USA",
IIF(Trim(Fields!OrderDate. value)="", "",Format( Fields!Ord erDate.val ue,"MM/dd/ yy").ToStr ing),"N/A" )
Make sure you are using a string OR date field for the OrderDate.IT does make a differents.
=IIF(Trim(Fields!Manager.v
IIF(Trim(Fields!OrderDate.
Make sure you are using a string OR date field for the OrderDate.IT does make a differents.
ASKER
The orderDate is a datetime field in my sql table.
I tried the trim() but still the same error.
I think this is really anointing for a simple null value field.
I found this from MS sit. Is there any work around other than using the code section method.
#Error is coming form the null value of Country field.
This is from the MS knowledge base:
Suppressing Null or Zero Values at Run Time
Some values in an expression can evaluate to null or undefined at report processing time. This can create run-time errors that result in #Error displaying in the text box instead of the evaluated expression. The IIF function is particularly sensitive to this behavior because, unlike an If-Then-Else statement, each part of the IIF statement is evaluated (including function calls) before being passed to the routine that tests for true or false. The statement =IIF(Fields!Sales.Value is NOTHING, 0, Fields!Sales.Value) generates #Error in the rendered report if Fields!Sales.Value is NOTHING.
To avoid this condition, use one of the following strategies:
•Set the numerator to 0 and the denominator to 1 if the value for field B is 0 or undefined; otherwise, set the numerator to the value for field A and the denominator to the value for field B.
Copy=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))
I tried the trim() but still the same error.
I think this is really anointing for a simple null value field.
I found this from MS sit. Is there any work around other than using the code section method.
#Error is coming form the null value of Country field.
This is from the MS knowledge base:
Suppressing Null or Zero Values at Run Time
Some values in an expression can evaluate to null or undefined at report processing time. This can create run-time errors that result in #Error displaying in the text box instead of the evaluated expression. The IIF function is particularly sensitive to this behavior because, unlike an If-Then-Else statement, each part of the IIF statement is evaluated (including function calls) before being passed to the routine that tests for true or false. The statement =IIF(Fields!Sales.Value is NOTHING, 0, Fields!Sales.Value) generates #Error in the rendered report if Fields!Sales.Value is NOTHING.
To avoid this condition, use one of the following strategies:
•Set the numerator to 0 and the denominator to 1 if the value for field B is 0 or undefined; otherwise, set the numerator to the value for field A and the denominator to the value for field B.
Copy=IIF(Field!B.Value=0, 0, Field!A.Value / IIF(Field!B.Value =0, 1, Field!B.Value))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh gee.
Why didn't I think of that?
I believe that will work. Just too focused to work in SSRS report.
I appreciated sticking around to answer my questions.
Thanks much.
Why didn't I think of that?
I believe that will work. Just too focused to work in SSRS report.
I appreciated sticking around to answer my questions.
Thanks much.
Glad that I could help in someway.
=IIF(Fields!Manager.value.
IIF(IsNothing(Fields!Order
If the format does not work then you will need to add CDate to the Date.