Passing single/multiple values to stored proc parameter from crystal

[eastwestk]

I tried below solution posted on sap forum to pass either a single value or multi-value to a sql server stored procedure parameter (varchar datatype) from crystal report XI R2.

In my crystal report , I am displaying all the available parameter values to the user and the user will select either a single value or multi value.

This worked when I select single value and when I say show sql query in my subreport I see the following:

{CALL "XYZ"."dbo"."storedprocedurename";1('Product 1')}

But this did not worked when I selected multiple values and when I say show sql query in my subreport I see the following:

{CALL "XYZ"."dbo"."storedprocedurename";1('Product 1,Product 2')}

I think it might work if it is as below:

For multiple values:
{CALL "xyz"."dbo"."storedprocedurename";1('Product 1', 'Product 2')}

Please advise.


Solution Posted on sap forum is as follows:

Hi,
As you must be aware of that a crystal report created of a stored procedure will allow only a single value for inserting a multiple value as a parameter in your report and pass those values to your stored procedure please follow the below work around which will be helpful for you.

Symptom
In Crystal Reports, you want to pass a multi-value parameter to a stored procedure. The problem with doing so is that Crystal Reports considers the multi-value parameter to be an array.
How can you pass a multi-value parameter to a stored procedure?

Resolution
Here are the steps to pass a multi-value parameter to a stored procedure:
1. Create a Crystal report, and add a multi-value parameter.
2. Since the multi-value parameter is treated as an array, create a formula that uses the JOIN function. Create a formula as below:
//Formula: @JoinFormula
Join ({?Multi-value parameter array},";")
====================
NOTE:
In the formula above, a semi-colon (";") is the delimiter.
====================
3. Within the main report, create a subreport based on the stored procedure, and include the parameter to be populated with the multi-value list.
4. Link the Join formula in the main report to the stored procedure parameter in the subreport.
Doing so passes a multi-value parameter to the stored procedure.

Regards,
Vinay

 

[lbass]

Please do not start new threads on the same topic. Please add this post to your previous thread.

-LB