Passing single/multiple values to stored proc parameter from crystal report

[Former Member]

I tried below solution posted on this 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 this 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

[former_member357265]

Dear Mr, Vinay

I tried the option as suggested by you;

I did step 1 and 2 but the issue is that I am getting an error message that "A String array is required here". and I am not able to continue further.

I am also not able to catch up your fourth point, please do put some light on that also.

Please do advise how to go ahead.

[abhilash_kumar]

Hi Sreejith,

Please post your issue as a new 'Discussion'.

-Abhilash

[pmanildi]

My param (s) are numeric, and the number of params can vary at runtime. JOIN only works on Strings. Is there a way to turn an array of numbers into a multi-value list?

[abhilash_kumar]

Hi Paula,

This is a very old post. You should consider creating a new Discussion for your issue.

-Abhilash

[abhilash_kumar]

Hi Vinay,

First you need to make sure the stored procedure accepts multiple values in the fashion 'a','b','c'.

Then, create this formula in the Main Report:

numbervar i;
stringvar s;
for i:= 1 to ubound({?Parameter}) do
(
    s := s + "'" + {?Parameter}<i> + "'" + ",";
);
left(s,len(s)-1);

Link this formula to the sub-report's parameter.

Hope this helps!

-Abhilash

[Former Member]

I just tried your suggestion and it did not worked for multiple values.

When I say show sql quere in subreport I see the following:

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

whereas I think it should be as follows:

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

Please help.