Pass parameters to sql store proc?

[Former Member]

Is it possible to pass parameters from Crystal Report to sql store proc? I know it will prompt for paramters if the report is built based on a parameterized store proc. I am NOT talking about these parameters. I still want user to be able to select parameter values from dropdowns and use them as the procedure parameters.

Reason for my question is I don't want the store proc to create a table having all records then crystal makes report by filtering. The all-record table could grow huge very quickly as more data is put in.

Thank you very much.

[Former Member · Accepted solution]

Hi Peter

Yes you can do this.

1. Use the stored procedure in a subreport.

2. Create parameters in the main report as you have in the sub-report.

2. Link this subreport with the main report using these parameters {main report's parameters nad subreport's parameters}.

This will directly pass the user's selection in the main report to the subreport's parameter and will the eventually passed on to the procedure.

Regards

Nikhil Sabnis

[Former Member · Accepted solution]

Thank you for you help, Nikhil.

As following your suggestion, the subreport still prompts for parameters to the stored proc. I linked each parameter in the subreport with the ones created in main, under subreport links, but still not working. I only see Database field and Report field under "Select data in subreport based on field", no parameter field. I just selected the corresponding database field.

Pls let me know what I got wrong. Many thanks.

BTW, I am using XI R1.

[Former Member · Accepted solution]

Hi Peter,

You can download sample reports from the following link which will help you to create a report as per your requirement.:

[]

Hope this helps!!

Regards,

Shweta

[Former Member]

Hi Peter

Follow these steps:

A. Following steps applies to the main procedure:

1. Create a main report which accepts the same fields as you want to pass to the stored procedure. (Note that this main report is not based off the procedure that you want to execute. Just get the data that you want to pass to the procedure. We will call the procedure in the subreport.)

2. Now create the no. of dynamic parameters that you want your user to choose the data from in the main report. (Lets assume that the user will select a single value.)

B. Following section applies to the subreport:

1. Add a subreport to the main report. This subreport will be based off your procedure. This will automatically add up the stored procedure parameters to the subreport.

2. Take care that in the procedure you define the datatype of the parameters same as the field's data type that you want to pass to the procedure. This will show up your stored procedure parameters while subreport linking.

3. Now link the main report to the subreport with each parameter. Uncheck the "Select data based on" dialog box while linking each parameter.

Hope this post solves your problem. i am sorry for sounding descriptive.

Regards

Nikhil Sabnis

[Former Member]

Hi Peter

In the report creation process,after connecting to the required database,you will be able to see the 'Add Command' option.

In this dialogue box you can make a call to the required stored procedure and assign the respective parameter values to the desired fields.If you observe,user has the option to create parameter on a desired field in the(right pane of the) "Add Command to Report" dialogue box.

Please keep the thread updated, so that all the contributors of this thread will know the status.

Thanks

[Former Member]

Thanks to all. Problem is solved now.

[Former Member]

Hi,

What version of Crystal Reports are you using?

Sincerely,

Amit