SAP Knowledge Base Article - Public

2225194 - Stored Procedure with Dynamic Parameter in Crystal Reports, are converted to static parameter in Crystal Reports for Enterprise

Symptom

  • Dynamic parameters are converted to static parameters.
  • Prompts with dynamic list of values does not list any values, after converting the report to Crystal Reports for Enterprise.
  • Report converted from Crystal Reports to Crystal Reports for Enterprise, based on a stored procedure or command object, that contains dynamic parameters or dynamic cascading parameters, are converted to static parameters.
      
      
  • Note: Images and data in this SAP Knowledge Base Article is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
         
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2
  • SAP Crystal Reports for Enterprise 4.3

Reproducing the Issue

  1. In Crystal Reports, create a report off a parametarized stored procedure, or a command object with parameters.
  2. Set the stored procedure parameter or command object parameter to be dynamic and select a List of Values created in the Business View Manager.
  3. When refreshing the report, the prompt display a dynamic list of values, or dynamic cascading list of values.
       
     Static Parameter - 01.png   
          
        
  4. Save the report.
  5. In Crystal Reports for Enterprise 4.1, open the report created in Crystal Reports with dynamic parameters.
  6. Under the menu "Data", select "Set Data Source Location...", and point the report to a Managed Relational Connection.
  7. Once the report is converted, notice the dynamic parameters have no list of values.
     
    Static Parameter - 02.png  
         
          
  8. When editing the parameter, notice it has been converted to static parameter, and it is not possible to convert it to a dynamic parameter.
        
     Static Parameter.png 

Cause

  • In Crystal Reports, the stored procedure parameter were assigned dynamic list of values (LOV), that were created in the Business View Manager.
  • Since Crystal Reports for Enterprise does not support Business Views, the Managed Dynamic Parameter in a report created in Crystal Reports, will be converted to a static parameter with an empty List of Values in Crystal Reports for Enterprise.
  • Also, in Crystal Reports for Enterprise, it is not possible to modify a stored procedure parameter to be dynamic.

Resolution

  • As a workaround in Crystal Reports for Enterprise, create a new report off a table, then create a dynamic parameter, and finally insert the converted report based on a stored procedure as a subreport in the Report Footer section, and link the dynamic main report parameter, to the subreport stored procedure parameter.
                 
    1. In Crystal Reports for Enterprise, create a new report off a table that contains the database fields used for the List of Values.
        
    2. Create a new parameter on the main report.
        
    3. Set the parameter to be dynamic based on the table database field.
        
    4. Insert the converted report that is based on a stored procedure with parameter, as a subreport in the Report Footer section.
         
    5. Link the main report parameter, to the subreport stored procedure parameter.
         
    6. For formatting purposes:
      • Filter the main report data to only return one record.
      • Suppress all the report sections, except the Page Header, Page Footer, and the Report footer.
               
    7. When refreshing the report, it will prompt the end user for a parameter with a dynamic list of values, and the selected value will be passed to the subreport stored procedure parameter. 

Keywords

SP, dynamic prompt, static prompt, CRFE, CR4E, CR for Enterprise. , KBA , BI-RA-CRE , Crystal Reports for Enterprise , Problem

Product

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