Link to home
Start Free TrialLog in
Avatar of dkim18
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("Yes") OR Fields!Country.value.equals("USA"),
iif(Isnothing(Fields!OrderDate.value.equals("Yes"),"",
Month(Fields!OrderDate.value) & "/" & 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??
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

Try this............
=IIF(Fields!Manager.value.equals("Yes") OR Fields!Country.value.equals("USA"),
IIF(IsNothing(Fields!OrderDate.value.equals("Yes"))," ",Format(Fields!OrderDate.value,"MM/dd/yy")), "N/A")
If the format does not work then you will need to add CDate to the Date.
Avatar of dkim18
dkim18

ASKER

I am sorry I wrote this incorrectly. It should be
=iif(Fields!Manager.value.equals("Yes") OR Fields!Country.value.equals("USA"),
iif(Isnothing(Fields!OrderDate.value,"",                                   <===
Month(Fields!OrderDate.value) & "/" & 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
Please paste the code into yours the same way

=IIF(Trim(Fields!Manager.value)="Yes" OR Trim(Fields!Country.value)="USA",
IIF(Trim(Fields!OrderDate.value)="","",Format(Fields!OrderDate.value,"MM/dd/yy").ToString),"N/A")
 
Make sure you are using a string OR date field for the OrderDate.IT does make a differents.
Avatar of dkim18

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))
ASKER CERTIFIED SOLUTION
Avatar of Howard Cantrell
Howard Cantrell
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dkim18

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.
Glad that I could help in someway.