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

SAP Knowledge Base Article - Public

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

Symptom

  • Unable to change a parameter to accept multiple values.
  • When creating a report off 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?

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off 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.

Cause

  • When creating a report based on a parameterize 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.

Resolution

  • The following procedure will walk you through the general steps of this workaround :
         
    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.

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.
( To do so, we suggest to talk to your database administrator or consult your database documentation )

 

 

Keywords

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

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016