Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 835
  • Last Modified:

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

0
Hoyt81
Asked:
Hoyt81
1 Solution
 
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
 
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
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
 
EmesCommented:
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
 
Hoyt81Author Commented:
When i switched IsNothing for IsDBNull, the expression worked perfectly! Thanks!

What did youmean by inspection values under common functions?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now