Technical Questions: Passing single/multiple values to stored proc para

Print Page | Close Window

Passing single/multiple values to stored proc para

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15730
Printed Date: 06 May 2024 at 11:21am

Topic: Passing single/multiple values to stored proc para

Posted By: eastwest
Subject: Passing single/multiple values to stored proc para
Date Posted: 27 Feb 2012 at 11:25am

I tried below solution posted on sap forum to pass either a single value or multi-value to a sql http://www.tek-tips.com/viewthread.cfm?qid=1676295# -
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

Replies:

Posted By: rkrowland
Date Posted: 27 Feb 2012 at 10:35pm

I think this might be possible if you pass the parameter to a subreport via a formula.

First of all your main report should still have the parameter and all of the options.

But it shouldn't contain anything which filters on the parameter.

Eg the SQL command should look as follows;

SELECT DISTINCT
product.productname
 
from table.product

Create a dynamic parameter in your main report called product and link the value to the above selected field.

Now create a formula as follows in your main report;

"'" & join({command.productname},"', '") & "'"

Now insert a subreport that contains the full SQL command you wish to use and link the parameter within it (The one that contains the products you wish to filter on) to the above formula.

The subreport should then contain all the information you want to see.

Regards,

Ryan.

Posted By: rkrowland
Date Posted: 27 Feb 2012 at 10:36pm

I just read the SAP forum solution and that's the same solution I gave, what exactly were your problems using it?

Posted By: lockwelle
Date Posted: 28 Feb 2012 at 5:19am

my users wanted multi value, selecting multiple values, well that they didn't want to do.

Instead, I had them enter the values that they wanted as comma delimited text, and then I wrote a function to split the values and create a table to join against in my proc....it would handle ranges of numbers as well.

so they could enter something like 1,5,6-10, 15 and the function would return 1,5,6,7,8,9,10, 15.

It's just another idea

Posted By: eastwest
Date Posted: 28 Feb 2012 at 5:20am

I just tried your suggested formula and it did not worked.

When I say show sql query for subreport I see the following. (I see the problem with the call statement used by crystal report to call my stored procedure.)

For Single Value:

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

For Multi-Value:

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

I think it will work if it is as follows:

For Single Value:

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

For Multi-Value:

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

Please help me out.

Posted By: lockwelle
Date Posted: 28 Feb 2012 at 8:11am

unless your stored proc is set up to take multiple parameters, the multi value should look like the single value, just repeated calls should be made to the subreport to handle all the values.

I could be wrong, but that is what I would think should happen.

Posted By: rkrowland
Date Posted: 28 Feb 2012 at 11:05pm

Your subreport sql command should look as follows;

{CALL "XYZ"."dbo"."storedprocedurename";1({?SubParameter})

In subreport links you should then link {?SubParameter} to the following mailto:%7b@MainFormula - {@MainFormula } in your main report.

mailto:%7b@MainFormula - {@MainFormula }


"'" & join({?MainParameter},"', '") & "'"

{?MainParameter} is the multiselect parameter in your main report consisting of the various product names.

Regards,

Ryan.

Print Page | Close Window