translating crystal formulas to stored procedure

[patty1]

I have a report in crystal that takes over 15 minutes to run so i need to create a stored procedure. What takes up the time is I have week to date data in the main report then in two sub reports - one is month to date and the other year to date data. The following are the formulas in Report Select Expert for each section

week to date - Date({sp_WTD_MTD_YTD_AllProducts.Post_Date})
in Date(Year({?EndDate}),Month({?
EndDate}),01) to {?EndDate}

month to date - Date{sp_WTD_MTD_YTD_AllProducts.Post_Date})
in Date(Year({?EndDate}),Month({?
EndDate}),01)to {?EndDate}

year to date - date({sp_WTD_MTD_YTD_AllProducts.Post_Date})
in date(year({?EndDate}),01,01) to {?EndDate}

to clarify: the user is prompted and puts in a month start and end date ie, 7/1/02 to 7/31/02.

the week to date takes the year of the end data and the month of the end date and always give me the first day of that month through the last date the user put in,

the month to date is in the 1st sub report and looks at the end date the same way

the year to date is the 2nd sub report and is saying always give me january 1 of what ever year the user puts in to the last date the user puts in.

I do not know how to put that in a stored procedure.

Any help would be appreciated.

 

[synapsevampire]

Your real performance problem is that you aren't passing the SQL to the database, so it slugs through every row in Crystal.

You didn't mention your database or the type of the date column, but I'll assume that it's a datetime.

Try the following, it should be fairly fast:

Create a formula for your starting date as in:
Datetime(Year({?EndDate}),Month({?EndDate}),01,0,0,0)

Now create your record selection criteria as in:

{Orders.Order Date} >= {@NewStart}
and
{Orders.Order Date} <= {?EndDate}

The key is to check the Database->Show SQL Query. Yoiu want to see a Where clause referencing the dates, your formula won't produce one, so it doesn't offload the work to the database, the above example does.

Adjust accordingly for your other reports.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[patty1]

I'm sorry - I'm not understanding (what a surprise!)- which of the date criterias do I do this for? And do you mean in Crystal (and if so, where do I put the date formula?)or in the stored procedure? Database is on Sybase.

Thanks for your help.

 

[synapsevampire]

Your record selection criteria is not properly set up.

What I'm saying is that your poor performance is because of this, though you will recognize increased performance from a Stored Procedure, you will also see a tremendous improvement by altering your record selection criteria INSTEAD of a Stored Procedure. Your record selection criteria is NOT getting passed to the database, so the reason it's slow is that Crystal is processing all of the rows, instead of getting them filtered by the database.

Your record selection formula of:

Date({sp_WTD_MTD_YTD_AllProducts.Post_Date})
in Date(Year({?EndDate}),Month({?
EndDate}),01) to {?EndDate}

Should be changed to:

Create a formula for your starting date as in:
I named it @NewStart as referenced below in the record selection criteria.
Datetime(Year({?EndDate}),Month({?EndDate}),01,0,0,0)

Now create your record selection criteria as in:

{Orders.Order Date} >= {@NewStart}
and
{Orders.Order Date} <= {?EndDate}

Hope this clarifies. You will see a drastic improvement in this report if you do this sort of thing for all of the record selection criterias.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[patty1]

I did it, but there was no change, it still goes through (each record ie, 0 of 12000, etc) and so still takes too much time.

 

[synapsevampire]

Did you check the record selection criteria to see that it's getting passed?

I tested it here and it works.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Did you check the record selection criteria to see that it's getting passed? Rather, the Database->Show SQL Query

I tested it here and it works.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[patty1]

Yes - the correct date is being passed
Transaction_History.Post_Date >= &quot;Jun 1 2002 00:00:00&quot; AND
Transaction_History.Post_Date < &quot;Jul 1 2002 00:00:00&quot; AND

but as I said, it is still going through every record. I had the same problem with a &quot;last quarter default&quot; report which I solved with a stored proc. But I was able to assign values to my variables and use getdate. This took me from 5min to 1 min download - See below sample

declare @startdate char(9), @enddate char(9), @year int
If datepart(Month,getdate()) between 1 and 3
Begin
select @year = datepart(year, getdate())-1
select @startdate = '10/1/' + convert(char(9), @year)
select @enddate = '12/31/' + convert(char(9), @year)
End
Else


I have no idea how to do the same with this report as I cannot assign the variables values. Do you know how I can do this in a stored proc?

Thanks.

 

[synapsevampire]

If it's in the Show SQL Query, then it is NOT going through every row, only the rows specified in the SQL (meaning those dates).

Again, your original formula was NOT passing the data to the database (it couldn't because of the way you'd used it).

A Stored Proc will definitely speed it up further.

I'll assume that your using SQL Server:

CREATE procedure [dbo].[usp_GetWeekToDate]
@StartDate datetime,
@EndDate datetime
as
select field1, field2, etc from YourTable
where sp_WTD_MTD_YTD_AllProducts.Post_Date >= @StartDate
and
sp_WTD_MTD_YTD_AllProducts.Post_Date <= @StartDate

if @@error > 0
begin
raiserror ('Failed on Proc: usp_GetWeekToDate' , 16, 1)
return
end

GO

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ooops, should be:

CREATE procedure [dbo].[usp_GetWeekToDate]
@StartDate datetime,
@EndDate datetime
as
select field1, field2, etc from YourTable
where sp_WTD_MTD_YTD_AllProducts.Post_Date >= @StartDate
and
sp_WTD_MTD_YTD_AllProducts.Post_Date <= @EndDate

if @@error > 0
begin
raiserror ('Failed on Proc: usp_GetWeekToDate' , 16, 1)
return
end

GO

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com