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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hoyt81Author Commented:
When i switched IsNothing for IsDBNull, the expression worked perfectly! Thanks!

What did youmean by inspection values under common functions?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.