Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • 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 CoachmanCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Hoyt81Author Commented:
Absoulely - What specifically would you like me to post?
0
 
Jeffrey CoachmanCommented:
A sample of the database with this report
0
 
dotnetchickCommented:
Try replacing the IsNull with IsNothing.
0
 
Jeffrey CoachmanCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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