Can you set a parameter default value with stored procedure?

[RobbOrt]

I am using CRXI SP2, MS2005. I have created a report from a stored procedure that uses two parameters.
The first parameter is {?schedule/on-demand}. Where the user chooses either "S" or "O". If an "O" is selected the user needs to fill in the second parameter {?inputdate}.
If the user selects "S" then I need the report to run from the (CurrentDate).
The problem is the the stored procedure input is the second parameter {?inputdate}. It can only be left blank if the user selects NULL, where the report then overrides my CurrentDate formula and sends NULL to the stored procedure.

The following is my report criteria formula:

Shared StringVar BeginDate;
Shared StringVar EndDate;

// Scheduled
IF {?Scheduled/On-Demand} = 'S' THEN
(
BeginDate:= ToText(DATEADD('d', -7, CurrentDate));
EndDate:= ToText(DATEADD('d', -1, CurrentDate));
)
//On-demand
ELSE IF {?Scheduled/On-Demand} = 'O' THEN
(
BeginDate:= ToText(DATEADD('d', -7, {?@inputdate}));
EndDate:= ToText(DATEADD('d', -1, {?@inputdate}));
)

Also, here is my record selection formula:

({?Scheduled/On-Demand} = 'S' and Date({aaa_BALANCE_WEEKLY_TOTALS_BYDAY_SCHEDULED;1.DATE}) >= DATEADD("d", -7, Currentdate)
and Date({aaa_BALANCE_WEEKLY_TOTALS_BYDAY_SCHEDULED;1.DATE}) <= DATEADD("d", -1, CurrentDate))

or

({?Scheduled/On-Demand} = 'O' and Date({aaa_BALANCE_WEEKLY_TOTALS_BYDAY_SCHEDULED;1.DATE}) >= DATEADD("d", -7, {?@inputdate})
and Date({aaa_BALANCE_WEEKLY_TOTALS_BYDAY_SCHEDULED;1.DATE}) <= DATEADD("d", -1, {?@inputdate}))


I have been able to create this report using a View, but the powers that be would like it done using a Stored Procedure and I am at a loss as to what I am doing wrong. Can any one please advise...

 

[briangriffin]

I think you can probably get by with just one input parameter for your stored procedure, but rather than basing the report on the SP you can base it on a command object that calls the SP.

declare @v_datevalue smalldatetime

if {?Schedule/OnDemand} = 'S'

BEGIN
set @v_datevalue = cast(Convert(varchar, getdate(), 101) as smalldatetime)
END
else
BEGIN
set @v_datevalue = convert(smalldatetime,{?DateParameter},101)
END

exec yoursp @v_datevalue

Then within the SP you can incorporate the date logic for record selection, instead of doing it within the report.

-- Your SP code 
.....
.....
where aaa._BALANCE_WEEKLY_TOTALS_BY_DAY_SCHEDULED
between dateadd(dd,-7,[@input parameter variable])
and dateadd(dd,-1,[@input parameter variable])

I'm not sure what your shared variables are doing for you, but I assume you have a subreport. Regardless, the SP should deliver the proper recordset and then you can do further manipulation within CR.

 

[RobbOrt]

Brian,
Thanks for your reply. I brought that up while I've been searching for a solution to my problem and was told that they did not want me to use a command object to call the stored procedure. Although that might be a viable solution, it is not one that I can use at this time. Any other ideas???

 

[briangriffin]

Get a more agreeable boss?

You can change your date parameter to a string, and set the default to 'Current Date', or something similar (and default the other parameter to 'S').

You would also have to add prompting text to say "If running on demand, enter date in mm-dd-yyyy format:' to ensure the user enters it correctly, since now you can't use the date picker control.

The date logic in the SP would be:

declare @v_cutoff smalldatetime

if @firstinputparamvariable = 'S' then 
BEGIN
set @v_cutoff = cast(Convert(varchar, getdate(), 101) as smalldatetime)
END
else
BEGIN
set @v_cutoff = convert(smalldatetime,@secondinputparamvariable,101)
END

And then add the date logic to the where clause as in the first example.

Not as easy for the user, and no benefit over using the command object approach - I wonder what the specific objections are.