Solved

Passing multiple value parameters to a stored procedure in crystal reports

Posted on 2013-01-07
5
1,723 Views
Last Modified: 2013-01-08
How can I pass multiple value parameters to a stored procedure in crystal reports?
Thanks.
0
Comment
Question by:Star79
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 38752994
The most popular way is to use a subreport and to pass the parameter from the main report to the subreport using a formula to join the values:
Join({?Parameter},',')

The subreport will use a stored procedure and the stored procedure will parse these values to a table and process them.
Details about this method ( and some others) are available here:
http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38753115
Agree.  Most stored procedures cannot accept a multiple value parameter.

You can as stated above pass a list of values but you ill have to
1.  Use a subreport and build the list in the report.
2.  Run the report from an application and build the list in the application and pass it.

mlmcc
0
 

Author Comment

by:Star79
ID: 38754962
I used the subreports and the multi value parameter is passed to the storeproc as
C-II,C-III,C-IV.How can I separate the params as
'C-II','C-III','C-IV','C-V' and pass them in my select query like
select * from drugs where class in('C-II','C-III','C-IV','C-V' )

Can it be done in sql or crystal?

Thanks,
Roopa.
0
 

Author Comment

by:Star79
ID: 38755606
Hello,
I created the sql to parse the parameter and the storproc works fine.But the sub-report is not displaying data.I created the formula field Join({?Parameter},',') to produce the comma separated params in the main report.How should I link the main report to the sub-report?
0
 
LVL 18

Accepted Solution

by:
vasto earned 500 total points
ID: 38755743
You can see an example how to write the parsing part in the provided link .
About the linking : right click on the subreport and select menu "Change Subreports Links". Find your formula inside "Available fields" tree and click the button ">" to move it to "Fields to link to". Then Select this formula and your subreport parameter and click the button "OK". Subreport parameter is in the combo box in the bottom left corner.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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