Hiding parameters from users

[themikeaustin]

I have a CR 9 report that calls a SQL Server 2000 stored procedure. The SP requires 3 parameters. One of the parameters tells the SP which transaction type to select. I want this parameter to be hard-coded, that is, I will set it differently in every report that uses this SP. I want the report to use the default value that I enter at design-time and not allow the user to see, much less change, the parameter at run-time.

This report, and the others that will use the same SP, will be called from the Crystal "Workbench" product (which I have never used).

I would greatly prefer *not* to perform my filtering in the report itself. There will be too many rows returned in this scenario and network bandwidth is an issue.

My other option is to use separate SPs for each transaction type, but I'd rather not have to do this...

Any ideas on how to perform this will be appreciated.

Mike A.

 

[synapsevampire]

By definition, a parameter isn't a hardcoded object, though I understand your intent.

I don't think that you'll be able to do this, I think that I'd go with different SP's that had the value preset, or derive it from the other parms if that's possible.

-k

 

[themikeaustin]

Thanks for the response.

I tried spoofing CR by changing the datasource to a SQL statement: "EXEC dbo.pr_PendingJournal @p_StartDate, @p_EndDate, "PENDED"". When I create the 3 parameters and try to save it, I receive a "must declare the variable @p_StartDate" error.

However, I can create a SQL statement to call a SP that does not require parameters and it works fine.

Do you know of a way to get a stored proc w/parameters to work as described above?

TIA,

Mike A.

 

[synapsevampire]

No, parameters are intended to be used as a passed through variable, not hard coded.

You can set defaults for them within the SP, and you can use code in the SP to derive what should be used, but if you expose an SP with parms, the front ends are generally going to assume that you meant to use parms

-k

 

[vidru]

If you really want to do it this way, you can, using a Command object. When you're entering the Command, create your "StartDate" and "EndDate" parameters in the Command window, then execute the sp like this (this would be the text of your Command):

EXEC dbo.pr_PendingJournal {?StartDate},{?EndDate}, 'PENDED'

-dave

 

[themikeaustin]

Thanks for your help! It turns out that Dave's suggestion does the trick.

Mike