converting report select date formulas into 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 and mtd data in the main report then year to date data in a sub report - The following are the formulas in Report Select Expert for each section

main report - Transaction_History.Post_Date in formula:
Date({Transaction_History.Post_Date}) in
Date(Year({?EndDate}),Month({?EndDate}),01) to
{?EndDate}
Year to date sub report - Transaction_History.Post_Date in formula:
date({Transaction_History.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 main

the year to date in the sub report and is saying always give me january 1 of what ever year the user puts in
?EndDate prompt.

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

Any help would be appreciated.


k- tried to help with some modifications within Crystal but it did not help.

Thanks all.

 

[synapsevampire]

I responded in your other thread:

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 <= @EndDate

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

GO

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[patty1]

will give it a try - Thanks for taking the time, it is appreciated.

 

[patty1]

Am now getting message in Crystal saying
Stored Procedure sp_WTD_MTD_YTD_AllProducts expects parameter @EndDate, which was not supplied.

here is stored procedure

CREATE PROCEDURE dbo.sp_WTD_MTD_YTD_AllProducts
@StartDate DATETIME,
@EndDate DATETIME

AS
BEGIN
SELECT
Transaction_History.Dealer_Number,
Transaction_History.Fund,
Transaction_History.Gross_Amount,
Transaction_History.Post_Date,
Transaction_History.Trade_Type, Funds.Fund_Name,
Funds.FundGroup, Funds.FundGroup3
FROM
DGUnitracDb.dbo.Transaction_History Transaction_History,
DGUnitracDb.dbo.CONTACT CONTACT,
DGUnitracDb.dbo.UNICMPNY UNICMPNY,
DGUnitracDb.dbo.Funds Funds
WHERE
Transaction_History.Fund = Funds.Fund_Number AND
Transaction_History.Rep_Number *= CONTACT.CONTACT_ID AND
Transaction_History.Dealer_Number *=
UNICMPNY.COMPANY_ID AND
Transaction_History.Post_Date >= @StartDate AND
Transaction_History.Post_Date <= @EndDate AND
(Transaction_History.Trade_Type = 'P' or
Transaction_History.Trade_Type='R') and
Transaction_History.Trade_Class = 'R' and
Transaction_History.Territory <> '00'
END

 

[patty1]

never mind - I fixed that little problem - also have fixed original problem (in case you are curious - see below). now I just have to figure out how to get rid of the two extra StartDate and EndDate prompts that I get - I guess one set for the main report and one set is for the sub. but at least it works!

Thanks again for all of your help! Best site/techs ever!

-patty1

Top piece of SP

CREATE PROCEDURE dbo.sp_WTD_MTD_YTD_AllProducts
(@StartDate datetime, @EndDate datetime)

AS

select @StartDate = '1/1/' + convert(char(4),
datepart (year, @EndDate))

select @EndDate = convert(char(2),
datepart(month, @EndDate)) + '/' +
convert(char(2), datepart(day, @EndDate))
+ '/' +
convert(char(4), datepart (Year,
@EndDate))
BEGIN

 

[Naith]

You're getting prompted twice because your subreport is linked to your main report on the StartDate and EndDate parameters to ?Pm-?StartDate and ?Pm-?EndDate instead of linking to ?StartDate and ?EndDate.

Make sure they link to the latter fields by using the really very tiny scroll bar in the Field To Link To window until you get the field displayed which exactly matches the name of your source parameter.

Naith

 

[patty1]

Thanks-I remembered that from the last time it happened. I can't believe it but thanks to all of you, I finally finished this report!