kamsetty_prasad
asked on
multi select paramneter in crystal report
Can stored procuedre input parameter used as crystal report multi select parameter?
ASKER
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.
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.
Crystal doesn't support passing multiple value parameters to a SP.
Crystal uses an array for multiple value parameters.
mlmcc
Crystal uses an array for multiple value parameters.
mlmcc
ASKER
Thanks.
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
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
ASKER
I am able to follow the first 2 steps. Facing problem to link main report to subreport
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
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
ASKER
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.
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.
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
Right click the subreport
Click FORMAT SUBREPORT
I believe it is on the last tab
checkbox for ON DEMAND
mlmcc
ASKER
no it is not ondemand. https://www.box.net/shared/663ziq8unr
report also we need to click on the subreprot.
report also we need to click on the subreprot.
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
~Kurt
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://imar.spaanjaars.com/310/passing-multiple-parameters-to-a-crystal-report-programmatically