Solved

Stored Procedure: Get parameters from Crystal Report

Posted on 2003-12-12
16
1,165 Views
Last Modified: 2012-05-04
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
nguyenn
0
Comment
Question by:nguyenn
  • 7
  • 6
  • 3
16 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9929237
Focusing on the SP part, here is an example.


CREATE PROCEDURE p_YourProcedure

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

AS

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


I don't know how Crystal retreves values from an SP though.
0
 
LVL 1

Author Comment

by:nguyenn
ID: 9929282
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.

0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 50 total points
ID: 9929341
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.
0
 
LVL 1

Author Comment

by:nguyenn
ID: 9929594
Yes, the Crystal Report can call SP directly. I tried it with 1 pass in parameter and it worked fine
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9929632
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.

0
 
LVL 1

Author Comment

by:nguyenn
ID: 9929838
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

thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9929901
>>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)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9940650
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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:nguyenn
ID: 9941627
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9941734
>>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.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9942164
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)

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9942576
Fair enough.
0
 
LVL 1

Author Comment

by:nguyenn
ID: 9942663
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.

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9942734
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!)

0
 
LVL 1

Author Comment

by:nguyenn
ID: 9942802
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9943001
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.

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now