Solved

multi select paramneter in crystal report

Posted on 2011-02-15
13
489 Views
Last Modified: 2012-05-11
Can stored procuedre input parameter used as crystal report multi select parameter?
0
Comment
Question by:kamsetty_prasad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 16

Expert Comment

by:Kalpesh Chhatrala
ID: 34903932
0
 

Author Comment

by:kamsetty_prasad
ID: 34903947
Hi ,

I have created a stored procuedure with the paramter social code.  And created the  Crystal report .
I want to allow user to select mulitple social code. I modified the stored procedure input paramter to multiselect. but when I preview the report it is passing null value to stored procedure.

One more thing what is delimter the multiselect is using.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34904069
Crystal doesn't support passing multiple value parameters to a SP.

Crystal uses an array for multiple value parameters.

mlmcc
0
Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

 

Author Comment

by:kamsetty_prasad
ID: 34904078
Thanks.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34904426
Actually, this is a SQL limitation, not a Crystal Reports limitation.  SQL doesn't allow multiple value input parameters.  That being said, I have a technique for getting around the limitation.  The basic steps are:

1)  Create an empty crystal report with a multiple-value string parameter
2)  Create a formula that joins all of the selected values into a single string array
3)  Build a subreport that uses either a parameterized  SQL Command or Stored Procedure as its data source
4)  Link the main report to the subreport on the formula in the main report to the parameter in the Command or Proc

The benefit to this approach is that you get the Crystal Reports multi-parameter functionality and can still pass it into the SQL for efficient processing.  Here's a sample report:

https://www.box.net/shared/663ziq8unr

~Kurt
0
 

Author Comment

by:kamsetty_prasad
ID: 34905101
I am able to follow the first 2 steps. Facing problem to link main report to subreport
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34907641
What kind of problem, specifically?

1)  You need to make sure the datatype for your formula are the same as the data type for your parameter field in your proc.
2)  Since your formula is creating an array of values that's formatted like this 'A','B','C' you'll need to make sure your proc accounts for an array like this table.field in (@parameter)
3)  When you link the main report to the subreport, crystal will automatically create a parameter starting with  ?pm- . You do NOT want to link based on this field.  Instead, drill down into the possible parameter values and select the actual parameter that exists inside the proc.

~Kurt
0
 

Author Comment

by:kamsetty_prasad
ID: 34913480
Hi,

I have modified the report as per the suggestion. But what happening while preveiwing I need to click the subreport to view teh actual result.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34913587
You apparently made the subreport an On Demand subreport or it is in a section that requires drill down

Right click the subreport
Click FORMAT SUBREPORT
I believe it is on the last tab
  checkbox for ON DEMAND

mlmcc
0
 

Author Comment

by:kamsetty_prasad
ID: 34913623
no it is not ondemand.   https://www.box.net/shared/663ziq8unr
report also we need to click on the subreprot.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34916942
My sample report does not have an on demand subreport.  The question is whether or not your report has an on demand subreport.  If you have to click it to see the results then yes, it's an on demand subreport.

~Kurt
0
 

Author Comment

by:kamsetty_prasad
ID: 34940573
Sorry for delayed response.  I was on vacation last few days.

My report also on not a on demand report. But I just reviewed the sample report you have provided.

In that report also I have click to open the sub report. Eventhough it is not on demand.

0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 34940654
What do you mean by "open the sub report"?  In order to edit the subreport or drill down into it you do need to click it.  But the data is visible in the main report without clicking.

~Kurt
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

726 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