SSRS Field

I'm taking this code from Access and modifing it to use in SSRS. These strings won't return anything but a 0, or a 0%. Any suggestions?

Thanks!

Eric
=Sum(IIf(DateDiff("d",IIf(IsDBNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)>0,0,1))
 
=(Sum(IIf(DateDiff("d",IIf(IsDBNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)>0,0,1)))/(Count(DateDiff("d",IIf(IsDBNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)))

Open in new window

Hoyt81Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
EmesConnect With a Mentor Commented:
Try

=Sum(IIf(DateDiff("d",IIf(IsNothing(Fields!Expr3.Value)
,Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value)
,Fields!SHIPPED_DATE.Value)>0,0,1))

Look at the inspection values  under common functions when you edit a formula
0
 
Chris LuttrellSenior Database ArchitectCommented:
I believe the problem is in translation from Access to SSRS syntax, your IsDBNull should be just IsNull in SSRS.  Try the code below.
=Sum(IIf(DateDiff("d",IIf(IsNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)>0,0,1))
 
=(Sum(IIf(DateDiff("d",IIf(IsNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)>0,0,1)))/(Count(DateDiff("d",IIf(IsNull(Fields!Expr3.Value),Fields!DESIRED_SHIP_DATE.Value,Fields!Expr3.Value),Fields!SHIPPED_DATE.Value)))

Open in new window

0
 
Hoyt81Author Commented:
OK - I took the "DB" out of there, but now there is an error when i go to preview the report saying IsNull is not declared...I thought i had given arguments?

Thanks
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Jeffrey CoachmanMIS LiasonCommented:
So you are using this in Code, not directly in a control?

<I took the "DB" out of there, but now there is an error when i go to preview the report saying IsNull is not declared>
In which formula?

Can you post a sample of this database, so we can see exactly what is happening in full context.
0
 
Hoyt81Author Commented:
Absoulely - What specifically would you like me to post?
0
 
Jeffrey CoachmanMIS LiasonCommented:
A sample of the database with this report
0
 
dotnetchickCommented:
Try replacing the IsNull with IsNothing.
0
 
Jeffrey CoachmanMIS LiasonCommented:
ooops.

Ignore my posts.

I thought this was from SSRS to Access.

JeffCoachman
0
 
Hoyt81Author Commented:
When i switched IsNothing for IsDBNull, the expression worked perfectly! Thanks!

What did youmean by inspection values under common functions?
0
All Courses

From novice to tech pro — start learning today.