Solved

SSRS Field

Posted on 2009-07-15
9
807 Views
Last Modified: 2013-11-28
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
Comment
Question by:Hoyt81
9 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24865670
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
 

Author Comment

by:Hoyt81
ID: 24868685
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24869725
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Hoyt81
ID: 24869933
Absoulely - What specifically would you like me to post?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24871285
A sample of the database with this report
0
 
LVL 1

Expert Comment

by:dotnetchick
ID: 24874200
Try replacing the IsNull with IsNothing.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24874949
ooops.

Ignore my posts.

I thought this was from SSRS to Access.

JeffCoachman
0
 
LVL 14

Accepted Solution

by:
Emes earned 500 total points
ID: 24880452
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
 

Author Closing Comment

by:Hoyt81
ID: 31603891
When i switched IsNothing for IsDBNull, the expression worked perfectly! Thanks!

What did youmean by inspection values under common functions?
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question