Any Crystal/Developer Experts out there?

Question

Post reply

Any Crystal/Developer Experts out there?

sswords

SSCrazy Eights

Points: 8207
More actions
May 6, 2004 at 2:40 pm

#85326

Here's my situation. We're using Crystal Reports 7 as we need to compile and distribute our custom reports. We're in the middle of converting a Sybase appointment scheduling database to SQL Server 2000 (can't wait until this is done ). I've got the data all moved but some of the legacy stored procs that are called by Crystal reports are giving us fits when we try to convert them to use the SQL Server driver. Here's an example of one (there are numerous):
create proc proc_apptstats
@start_date datetime, @end_date datetime
as
SELECT
    appt.appt_id 'appt.appt_id', appt.entered_datetime, resunit.abbr 'resunit.abbr', probooking.name 'probooking.name', facility.abbr 'facility.abbr', apptstatus.abbr 'apptstatus.abbr', resunitgrp.abbr 'resunitgrp.abbr'
FROM
    prod.dbo.appt appt,
    prod.dbo.resunit resunit,
    prod.dbo.probooking probooking,
    prod.dbo.facility facility,
    prod.dbo.apptstatus apptstatus,
    prod.dbo.patbooking patbooking,
    prod.dbo.pat pat,
    prod.dbo.resunitgrplist resunitgrplist,
    prod.dbo.resunitgrp resunitgrp
WHERE
    appt.appt_id = probooking.appt_id AND
    appt.appt_id = patbooking.appt_id AND
    appt.resunit_id = resunit.resunit_id AND
    appt.apptstatus_id = apptstatus.apptstatus_id AND
    resunit.resunit_id = resunitgrplist.resunit_id AND
    resunit.facility_id = facility.facility_id AND
    resunitgrplist.resunitgrp_id = resunitgrp.resunitgrp_id AND
    patbooking.pat_id = pat.pat_id AND
    (convert(varchar(10),appt.entered_datetime,102) between convert(varchar(10),@start_date,102)
                               and convert(varchar(10),@end_date,102)) AND
    apptstatus.abbr in ("SCHEDULE","CANCEL")
ORDER BY
    facility.abbr ASC,
    resunit.abbr ASC
The stuff in red is what's biting me. Originally, some of the reports used a substring on the datetime in the where clause and SQL Server yelled about this, so I changed them all to use convert and the procs would compile fine. The problem then was that, since we were converting the datetime to a string data type, the Crystal driver imports the @start_date datetime and @end_date parameters as string types every single time. If you remove all the convert statements language and just pass
'appt.entered_datetime between @start_date and @end_date'
then it works great and it allows the parameters which are fed by the proc to import as datetime instead of string.
Now the big problem is that the report does not return the correct data as it's looking for appointments starting from the exact date AND time entered. For example, if I enter 3/1/2004 as both the start and end date, the report does not return any values even though there is data for that date.
How can I tweak these to just pass the date without the time to the stored procedure from Crystal so that I get all the values, yet still have Crystal recognize the parameter as datetime? Thanks in advance for any help provided. I now know way more about Crystal Reports than I ever cared too! (But apparently still not enough to fix this )
My hovercraft is full of eels.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

If you have no concern with what tiem the entered_datetime field is just want to see for day then try this.

DATEADD(d,DATEDIFF(d,0,appt.entered_datetime),0) between DATEADD(d,DATEDIFF(d,0,@start_date),0)

and DATEADD(d,DATEDIFF(d,0,@end_date),0)

WDFog SSC Rookie Points: 36 More actions · Answer 2

I'm not fond of Crystal Reports. I've always given up and just had my parameters be varchar/char or integer from Crystal and then done all the conversion within the stored procedure.

A method I have used to get around the exact time issue is to extend my range by one day and use greater than and less than expressions:

WHERE appt.entered_datetime > DATEADD(d, -1, @start_date) AND appt.entered_datetime < DATEADD(d, 1, @end_date)

sswords SSCrazy Eights Points: 8207 More actions · Answer 3

Thanks gentlemen for your reply. Antares, will your method still return results if the @start_date = @end_date (in other words return the results for one day only)? Thanks again.

My hovercraft is full of eels.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Yes because of the following

If @start_date = '20040101'

and @end_date = '20040101'

Then any time value in appt.entered_datetime will be reduced to 0 for that day making it the value 'yyyymmdd' for that item.

Thus any date where 'yyyymmdd' is (>=) @start_date and (<=) @end_date) will be evaluated true the key being the fact = is involved in a between.

Hope that makes sense.

sswords SSCrazy Eights Points: 8207 More actions · Answer 5

Thanks again and sorry for the delay in responding. The analyst that I'm working with on this is out of town for a few days and I'd left this alone for a while.

Well, that makes sense and it's starting to sink in. So this also (I think) means that also if I DO want time specific data and pass the time filter from Crystal that these results will be accurate as well because SQL Server is returning the full range of values for that date, and then Crystal will filtering the results down to the hour or whatever. If this is true, we'll be in pretty good shape. If not, we gotta rethink things some. But the 2 that we tested so far seem to be working well.

Custom reports are a beast!

Happy Weekend!

My hovercraft is full of eels.