SRSS 2005, comparing dates in expresson

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

SRSS 2005, comparing dates in expresson

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2011-10-06 : 12:38:44

I have a dataset that contains two columns with delivery date information. One has the scheduled delivery date, the other has the actual delivery date. I want to write an expression that will give me the percentage of shipments that arrived on time by comparing those two columns, but expression writing in VS seems very limited and can't have the obvious solution. Can someone point me in the right direction for accomplishing this?

It seems like this should work, but it doesn't:

Count(IIF(Fields!delivered_date <> Fields!est_delivery, 1, Nothing))

The columns are both varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:46:23

=Sum(IIF(DATEDIFF(DAY,CDate(Fields!delivered_date),CDate(Fields!est_delivery))=0, 1,0))/Sum(IIF(Fields!delivered_date Is Nothing,0,1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2011-10-06 : 12:51:01

Hmm, trying that, under DAY it tells me "Wrong number of arguments"...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 12:52:00

i think you're not putting braces correctly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2011-10-06 : 14:11:57

I copied exactly what you put there when I received the error... if I preview the report, I get another message:

"Argument not specified for parameter 'DateValue' of 'PublicFunction Day(DateValue as Date) as Integer."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-06 : 14:29:05

what about this?
=Sum(IIF(DATEDIFF(DateInterval.Day,CDate(Fields!delivered_date),CDate(Fields!est_delivery))=0, 1,0))/Sum(IIF(Fields!delivered_date Is Nothing,0,1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meef
Posting Yak Master

113 Posts

Posted - 2011-10-06 : 14:36:49

Same error.

I think the problem is the CDATE function, it looks like that is only used to convert dates into varchars, my columns are already varchars so maybe I need to use the convert function?

if I just remove the CDATEs the report will run, but display #ERROR in the text boxes.

meef
Posting Yak Master

113 Posts

Posted - 2011-10-06 : 15:05:25

Looking at the stored procedure, the columns are actually already converted to Date, so that is why it's giving me a fit about the CDate I guess. I still don't know how to fix it, but there's some more info.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 01:40:14

quote:
Originally posted by meef

Looking at the stored procedure, the columns are actually already converted to Date, so that is why it's giving me a fit about the CDate I guess. I still don't know how to fix it, but there's some more info.

whatever i've posted last is working for me in reporting services and i've tested it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources