Passing Parameters To Stored Procedure

[danny333]

I am new to Crystal Reports. I am using CRXI with a SQL2000 database.

I am creating a report using a stored procedure as the data source. One of the parameters is a comma-delimited list of customer IDs. In the customer table, there are fields for customer ID, customer code, and customer name.

The stored procedure needs a list of the customer IDs but these mean nothing to the user. The user needs to see the customer code and the customer name. I have created a dynamic parameter using the customer code as the value and the customer name as the description and have set the "allow multiple values" to true. How can I turn the multiple customer codes into a comma-delimited string of customer Ids and send it to the stored procedure?

I have seen several comments about using a sub-report. Currently, I am trying to create a new report that uses the original report as a sub-report. I was thinking that I could have the main report get the parameters, change them to comma-delimited strings and pass them to the sub-report. The problem that I am encountering with that idea is that I am using the customer table as the data source but I cannot figure out how to write a formula to loop through the customer codes and get the corresponding customer IDs.

Any Ideas would be greatly appreciated.

 

[Tatertot45]

This is a tough one. Looks like everyone is as stumped as you are. Have you tried using an array? Maybe you could use an array to accumulate the ID's and then populate a string or something. Hope this sparks another round of ideas for you.

 

[Turkbear]

Hi,
A multiple value parameter returns an array of the selected values..Maybe you can change these to a delimited string in a formula and use the formula for the parameter sent to the SP .





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[danny333]

I appreciate the ideas. I have been doing some testing trying to incorporate these. My main problem is that I cannot figure out a way to intercept the parameters that the user enters before CR sends them to the stored procedure. It looks like the only way to do that is to go with the subreport idea. I still have the problem of locating the customer IDs for the customer codes that the user enters. I may have to have the stored procedure changed to allow the customer codes instead of the IDs. If I come up with a solution, I will pass them along. Thanks again for the ideas.