Date Parameter with a Crystal Report

[ja01]

This is a combination crystal and SQL issue:

I created a VIEW in enterprise manager to capture a payment based on date and type. Here it is

SELECT giftkey, giftid, gifteffdat, giftamount, giftplgkey, gifttype, giftclass2, giftclass1, giftacctgp
FROM dbo.gifts
WHERE (giftclass2 = 'Foll') AND (gifttype IN ('b', 'y')) AND (gifteffdat >= '2006-07-01')

I would like to eliminate the (gifteffdat >= '2006-07-01') and replace it with a date parameter I can pass using a crystal report. For example if I want to pull a range of payments from 2006-07-01 through 2006-07-31, I would like to handle that on the crystal side.

Any thoughts here???

 

[Catadmin]

Completely remove the DATE from your View's WHERE clause. It's been a while since I've done Crystal, but you should be able to create a parameter with a date range or a single range to then filter the data after it's gotten to the report.

I seem to recall doing just that with Crystal 10, anyway.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLBill]

Create it as a stored procedure using variables for the dates. Then give permission for Crystal Report users to access stored procedures. It should then ask for the parameters to be inputted. (As I recall from setting this up in my last job).

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I knew I was forgetting something!!!

SQLBill is correct. Do what he said and then put in your WHERE clause "<MyDate> >= @StartDate and <MyDate> <= @EndDate" and you should be set (after verifying the parameters in Crystal are set up correctly).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"