1959048 - How to pass the input of a multiple values parameter to a stored procedure parameter in Crystal Reports?

• Unable to change a parameter to accept multiple values.
• When creating a report based of a stored procedure, it is not possible to change the parameter to use multiple values.
• How to pass multiple values to a stored procedure parameter in Crystal Reports?

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

1. In Crystal Reports, create a report based on a stored procedure that have at least one string parameter.
2. Edit the parameter, and notice it is not possible to change the parameter to multiple values.

• When creating a report based on a paramaterize stored procedure, Crystal Reports will automatically generate a discrete value parameter of the same data type, but it will not be possible to change the parameter to accept multiple values, and this is because of stored procedure limitation, as it can only create discrete value parameters.

• To workaround the stored procedure parameter limitation, use the subreport technique where you convert the values entered in a multiple values parameter to a string of values separated by commas, and pass this string of values to the stored procedure parameter.
          
1. Create a report based on a dummy table.
   ( The only purpose of this report it's to create a multiple value parameter that will pass the values to the stored procedure parameter. )
           
2. Insert a multiple values parameter called "Crystal Parameter".
   ( To insert a multiple value parameter, select "Insert - Field Object", then create a new parameter. In the "Edit Parameter" windows, click on "Allow Multiple Values" )
        
3. Create a formula that will convert the values entered in the multiple values parameter into a string. The formula will look like :
     
     //@ Parameter Values
     Join({?Crystal Parameter},",")
       
4. Suppress the Report Header and Details section.
        
5. Insert your report based on the parameterized stored procedure as a subreport in the Report Footer section of the main report.
   ( To insert a Subreport, Select "Insert - Subreport" )
       
6. Link the formula "@Parameter Values" to the subreport stored procedure parameter.
       
   6.1 Right click on the subreport and select "Change Subreport Link".
      
   6.2  In the "Change Subreport Link" window, select the formula "@Parameter Values" in the upper left corner list, then click on the little arrow ">", to bring it in the "Field to link to" list.
     
   6.3 Uncheck the checked box "Select Data in subreport based on field"
    
   6.4  In the bottom left corner, select in the drop-down list your stored procedure parameter.
          ( The stored procedure parameter starts with the @ symbol. )
    
   6.5  Click "OK"

Now, when refreshing the report, you will get prompted by a multiple values parameter, which will be passed to your stored procedure parameter as one string separated by commas.

Important Note:

The final step will be to modify your stored procedure in order to select data based on one string of values separated by commas. If you need assistance on how to modify your Stored Procedure SQL Query, contact your database administrator or consult your database documentation.

• Sample report demonstrating the above technique based on the xtreme sample database: Multiple Values Parameter Woraround.rpt 
  ( Sample Report found in the Attachments section below )

CR, Stored Procedure, SP, Multiple-Values, parameter , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020