SQL Stored Procedure to populate Crystal Report

I'm working on a stored procedure to populate a Crystal report. My company insists that we put the report parameters in the stored procedure instead of in Crystal...so that the SQL server (rather than the desktop)does the work of restricting the data. Is there anything I can do on the SQL side(possibly User Defined Data Type) to get Crystal to prompt me for a date WITHOUT the time? I started with this:

CREATE proc uspReportData @BeginDate datetime, @EndDate datetime

When Crstal prompts me for the parameters, I can type the date or use the calendar to pick a date, but I AM FORCED to enter a time. I know I could choose to ignore the time in the stored procedure, but the users don't want to see the time section of the parameter. Apparently SQL doesn't have a plain "date" parameter without a time.

I've also considered this:

CREATE proc uspReportData @BeginDate char(10), @EndDate char(10)

However if I do it this way, I can't seem to find a way to make sure a valid date is entered when Crystal prompts the user for the dates.

If using char(10) turns out to be the best method, is there a way I can pre-populate the Crystal prompts like this:
@BeginDate = 1st day of the current month
@EndDate = the current system date

Crystal seems to allow hard coded default values, but I can't find a way to do calculated default values.

I'm open to suggestions.
Thanks,
Mike

In Crystal just get date and pass that to stored procedure. SQL Server will default to 12:00 AM for time.

Thanks skhanal,

Can you elaborate on that for me? I don't know how to "pass" something from Crystal to the stored procedure.

Also in my original post when I mentioned:
@BeginDate = 1st day of the current month
@EndDate = the current system date

I still want the user to be prompted for the Begin and End dates. I just want the prompt boxes to default to these values.

-Mike
:confused:

When you call the stored procedure you do not have to include time portion.

exec uspReportData '2004-03-01', '2004-03-15'

is same as

exec uspReportData '2004-03-01 12:00:00', '2004-03-15 12:00:00'

USE tempdb
CREATE TABLE test_dates (Col_1 varchar(15), Col_2 datetime)
GO
INSERT INTO test_dates VALUES ('abc', 'July 13, 1998')
GO
SELECT ISDATE(Col_1) AS Col_1, ISDATE(Col_2) AS Col_2
FROM test_dates

Here is the result set:

Col_1 Col_2
----------------- --------------------
0 1

Perhaps it will help if I post my entire Stored Procedure:

CREATE proc uspReportData @BeginDate char(10), @EndDate char(10), @ToolName Varchar(50), @Office Varchar(50)
As

select Application, Server, Office, HitDate, CHitDate, Attempts, Completed, (Attempts-Completed) as UnSuccessful, ElapsedTime
from vReportData
where CHitDate >= @BeginDate and CHitDate <= @EndDate and Application like @ToolName and Office like @Office
GO

The Crystal report points to the Stored Procedure as the source (rather than pointing directly to a table or view). I included the parameters in the Stored Procedure so that it narrows down the data on the server (rather than Crystal narrowing down the data on the desktop).

So I don't think I ever really "call" or execute the Stored Procedure. I just point to it as the Crystal Report's source. Am I approaching this all wrong?

:confused:
Mike

Your stored procedure expects 4 parameters. How do you set these values?

You must be using crystal reports variable to do that. You can use calendar to get the date values ignoring time portion and assign that to crystal reports variable which in turn sends to stored procedure.

I'm not using an Crystal Reports variables. I'm pointing to the Stored Procedure as the source for the Crystal Report. When you do this, if the SP is expecting parameters, the report will automatically prompt you for the parameters before displaying the report. I used char(10) for the date fields, because the users do not want to "see" the time portion of the prompt for those parameters (even if I can choose to ignore the time). If SQL had a plain "Date" data type, I wouldn't have this problem.

Crystal lets you set a default value for a parameter, so that it's filled-in when you see the prompt (you can leave it as is or change it). However, it appears that this default value must be hard-coded. I'm trying to determine if the default value can be calculated instead of hard coded. My question is probably more suitable for a Crystal forum than for this forum. But I was hoping that something could be done on the SQL side to sort of pass a default parameter value to the Crystal prompt.

Hi,

I am having a similar problem to Mike. I pass a date in to my Oracle stored procedure and then use Crystal reports to access the procedure directly.

The Crystal format for the parameter is uneditable and is set to DateTime, even though I specify it as (parm1 IN DATE) in my procedure. Crystal sadly produces the parameter with a data type of date/time.

Using a string relies on the user knowing the correct format for the string to be passed in (e.g. 'DD-MON-YYYY')

I'd be very glad for any help!

Thanks,

Ailsa

Was this ever resolved? I am looking for exactly the same answer as the original question.

I am new to addding Stored Procedures to Crystal Reports so I am not sure I will be much help, however, I ran a Crystal Report today with a date parameter in the Stored Procedure and the parameter in Crystal displayed a calendar so I could choose a date. Try changing your parameter to a datetime datatype. The calendar will allow your users to select a date and not have to remember the datetime format.