Forcing Values to Stored Procedure Parameters

[Flopper]

Hi,

I'm using Crystal XIR2, BOXIR2 & MSSQL 2005. I am using a Stored Proc containing 2 parameters, Start & End Date.

I want to schedule this report to run on the first day of every month... and for the previous month. i.e. On 01-11-2007, StartDate = 01-10-2007 00:00 & EndDate = 01-11-2007 00:00.

The problem is i want this date to be populated depending on the Current Date and not by prompting the user for a parameter entry. No matter how many methods i try, i am always prompted for the SP Parameters. I know i could ask for the Stored Proc to be modified but it's used elsewhere, and it'd take some serious begging to create a new one.

Help would be appreciated.

Cheers

 

[Flopper]

I'm really struggling on this... please can somebody help?

Cheers

 

[CoSpringsGuy]

I have been waiting for someone else to throw put some expert adice on this one but since they havent I will throw out some of what I have seen to be true.

Anytime there is a parameter in the stored procedure(s), you are going to be asked for them when running the report. Im not aware of anyway around that. You mentioned that the paramter dates are used elsewhere. It seems as if they are being used elsewhere then the same hardocdedcurrentdate and currentdate-1 you need for your requirements would work in the other areas you need the paramter dates ...

I would also throw this question out in the Crystal formula section of this forum as well.

Good Luck

_____________________________________
Not the brightest
Crystal Reports XI Developer Version
user in the world........

 

[pvidya]

Hi,

I don't know how to do this through Crystal. Here are two SQL options though:

You could write or request for a new stored procedure that calls the original stored procedure passing it start and end date parameters based on the system date.

I am not very sure, but I think you can set stored procedure parameters such that they default to a value when NULL is passed in. You could request that the exising proc be changed to have default values. You could pass NULL from report version being executed. the defaults could take in the previous month start and end dates.

Hope this helps.

 

[Flopper]

Thanks pvidya,

I've actually spoken to BO regarding this and they have agreed that this can only really be achieved by modifying the Stored Proc. I thought your Nulll idea would work for me to start with, but then i realised that i'd have to retrieve all data back from the Stored Proc and do the WHERE clause locally. Alas, there's more than a few records.

I've spoken to my DBA, and so we'll create a new Stored Proc with dates based upon the System Date.

Many thanks all the same.