Stored Procedure Parameter List Crystal XI 1

[kvena]

I have a stored procedure that is selecting a sore number as a parameter. When I run the report in Crystal the parameter appears as an empty field instead of a list. I would like to be able to select the value for the parameter from a list as opposed to trying to memorizing every store number in my database. Can this be done in Crystal XI?

 

[synapsevampire]

You can cheat it.

Click the Add Command below your data source and place in the sql to populate the list (ignore the warning about more than one datasource it creates).

Now go Edit the parameter, and select dynamic and point to your command object as the source.

Make sure that you select the SP parameter on the right side as you do so.

-k

 

[kvena]

Thanks for the reply. I do not fully understand what I need to do. Are you saying I should copy and paste my stored procedure into the command line and run it as a command?

 

[KeertanaCR]

Suppose if you are saying {?sorenumber}={table.A} in your record selection formula to filter the records based on your parameter value( Basically to which field of table you are trying to match the parameter values)

Create a coommand prompt as
Select distinct A from table

Ignore the warning about multiple datasources.

Now go to the parameter and edit it and select dynamic for List of values.

In the value list select A under the command.

 

[synapsevampire]

Not sure why you'd need the entire SP, I doubt it.

You need SQL to populate the list.

-k

 

[kvena]

I added a command prompt to select the appropriate table and then changed my parameter (in CR) to select a dynamic list from the command. When I run the report I am prompted 2X's. The first parameter is still an empty field. The 2nd time it prompts me I get a list. ????

 

[MJRBIM]

That's because the 1st parameter doesn't come from Crystal - it comes from your SP.

I'm not 100% - but I don't think you can use Crystal's Dynamic Parameters function for parameter values that come directly from the SP.

 

[synapsevampire]

Hmmm, I thought it would work, but my test shows it prompting twice as well...

Works with tables/views.

-k

 

[MJRBIM]

Can you create a SP without parameters - and then put the parameter only on the Crystal side like you would with a Table or a View....?

 

[kvena]

The stored procedure takes to long to run without the parameter.

 

[synapsevampire]

Creating a View should work for you.

Otherwise I guess you'll have to use static values.

-k