Stored Procedure: Get parameters from Crystal Report

hi experts,

I pass multiple paramter values from Crystal Report to a SP, I have a Division parameter, may have multiple values, let say i have 4 values: 1, 2, 3, and 4. I  pass those values into SP.

In SP, how could I declare a variable to accept multiple values pass in, and how to extract those values from the variable I just declare?

And with a range value (i.e. Age Range between 20 - 40), how could we handle it?

Please give me an example to do that.

thanks in advance
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Focusing on the SP part, here is an example.


@Variable1 INT,
@Variable2 INT,
@Variable3 INT,
@Variable4 INT,


RETURN @Variable1 * @Variable2 + @Variable3 / @Variable4

I don't know how Crystal retreves values from an SP though.
nguyennAuthor Commented:
Nope, if we declare like that the Crystal Report will treat them as 4 different parameters, I only need one parameter (i.e. Division), but the Crystal Report may allow users enter multiple values for Division.

Anyway, this is just a sample, the users may enter more than 4 values (dynamic), i'm looking a collection parameter in SP to solve this issue.

There isn't anything like that in a SP. You will need to store those mutliple values in a table then reference the table from the stored procedure.

Alternatively you could just concatenate them into a huge Varchar parameter but that is pretty unreliable.

Is the crystal report calling the stored procedure? I can only suggest that you try it with one parameter and see how the SP behaves.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nguyennAuthor Commented:
Yes, the Crystal Report can call SP directly. I tried it with 1 pass in parameter and it worked fine
Try it with multiple items selected in the parameter and see what it does. Knowing Crystal, a GPF!

But I can assure you there is no collection or array input parameter type in stored procedures.

nguyennAuthor Commented:
I did try with mutiple items selected in the parameter, and it worked with multiple declaration. I read in an article with SQL server limitation compare with Orable, SQL Server cannot support for an array, while Orcale can support it. This maybe the answer for this case

Anthony PerkinsCommented:
>>But I can assure you there is no collection or array input parameter type in stored procedures.<<
Not entirely true.  You can pass an XML document and use OpenXML to retrieve the value(s)
Aha I didn't think of that.

I think you can also use a table variable.

This is all academic though as there is no way Crystal will support any of these things. (Though it never hurts to try it and see)

Are you using the SP as the datasource for your Crystal report? The reason I ask is that you are better of using a view if possible as that will work with multiselect parameters.

nguyennAuthor Commented:
I'm using SP as the datasource for my CR. Actually I used View, but there are somethings need to be done in SP (i.e. create temporary table, using filter parameters, etc...) that why i come up with SP.
Anthony PerkinsCommented:
>>This is all academic though as there is no way Crystal will support any of these things.<<
Actually this has nothing to do with Crystal. As far as Crystal knows it is a long string (either varchar or text)

The other alternative is to pass a single delimited string and parse it in the Stored Procedure to a temporary table.
What I meant was, Crystal isn't going to natively support passing a Crystal Report parameter containing multiple values, to a row or XML handle type sp parameter.

I don't think there is a way to convert a Crystal multi-select parameter into a delimited string within Crystal.

ngyuenn, if you were to use a view, you can certainly support filter parameters, in fact if you use a view, this will all work as expected... your multi select or range parameter should work just fine.

But obviously as you have stated, you need to use a SP to do further calculations.

I think that you are just going to have to code a launcher for your crystal report, and code an initial call to your SP in here, then launch the report (which can use a view, as I mentioned)

Anthony PerkinsCommented:
Fair enough.
nguyennAuthor Commented:
And again, when working with view, there is no way to pass  parameters into a View.

Actually, i wish i could code before launching the CR to display the data based on the passed in parameters. Unfortunately the users can enter any values in the parameters, so we cannot know which one to hard code before launching CR. Inside CR, we can set filter based on the entered parameters. With the View I have, it present a set of data, which does not filter data from the parameters the users entered. We dont take advantage of using SP/View if we cannot pass in parameters.

Correct, there is no way to pass parameters into a view but if you use your crystal parameter in the selection formula, it filters the view as required.

I'm not sure I understand your final statement...  "which does not filter data from the parameters the users entered".... Crystal will filter the data from the parameters the users have entered if you tell it to do so in the selection formula, wether you use a table or a view.

If you need to create differing/alternative calculations depending on what is selected in the crystal parameter, then you're out of luck. But certainly from the description in your original posting, it wouldn't seem so.

As far as coding before launching, just ask the user for input and pass the parameters into the crystal object before running the report.

So in answer to your original post, AFAIK, it can't be done (unless you are using Oracle it seems!)

nguyennAuthor Commented:
Hi mncdermaid,

Even i dont have a solution for my problem here, but i think you deserve to get my points. It certainly be a challenge for MS SQL server in next versions to allow working with array in their system. Yes you're right, i'm out of luck with this situation.

"Crystal will filter the data from the parameters the users have entered if you tell it to do so in the selection formula, wether you use a table or a view.". Actually my CR have set up to filter data based on entered parameters, but it would be faster if we could filter data inside SP/Views BEFORE sending them into CR.

acperkins, you  have very good points there. Unfortunately in my case i cannot do anything with that.

Your help are very appreciated.
Thanks for all your help
Well thanks for the points, sorry I couldnt help.

I don't know that it would be all that much faster in the case of a view since Crystal just submits the SQL to the database.

However I reckon it does make sense to try to move the processing to the database as I often find crystal isn't smart enough to convert a report dataset entirely to SQL... it always wants to do some processing at the client.

Just something to keep in mind though: If for example you require a complex calculation per division, you could put a subreport in each division group which uses the SP as its datasource. Then you only need to pass the current division in for each subreports. As I'm sure you know, subreports are inefficient though.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.