SAP Knowledge Base Article - Public

2215888 - Optional parameter option greyed out in Crystal Reports

Symptom

  • Unable to set the parameter optional.
  • How to set a command object parameter to be optional, in Crystal Reports?
  • When prompted by a parameter, we always have to enter a value. How to avoid having to enter a parameter value for a stored procedure parameter or command object parameter?
  • In Crystal Reports, it is not possible to change the parameter option: "Optional Prompt", to True, as it is greyed out.
  • In Crystal Reports for Enterprise, it is not possible to change the parameter option: "Prompt Value Optional", as it is grayed out.
      
       
  • 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 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
           
  • SAP Crystal Reports for Enterprise 4.0
  • 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, connect to any data source.
  2. In the "Database Expert", create your own SQL Query by double clicking on "Add Command"
  3. In the "Add Command To Report" window, enter the SQL Query.
  4. Add a parameter, by clicking "Create"
  5. Add the parameter to a WHERE clause of the SQL Query, and click "OK"
       
     Parameter01.png   
           
           
  6. Edit the parameter, and notice in the "Edit Parameter" window, the option "Optional Prompt" is set to "False", and cannot be change to "True"
        
     Parameter02.png 

Cause

  • By design, parameters created in a Command Objects or a Stored Procedure, does not have the option to be optional.
       
  • It is only possible to set a parameter optional when reporting directly of a database table, and creating the parameter in Crystal Reports designer, because when reporting off tables directly, and adding parameter(s) to a report, Crystal Reports generates the SQL Query to be sent to the database. Since Crystal Reports generates the SQL Query, it is possible to generate a WHERE clause when there is value for the parameter, and no WHERE clause when there is no value for the parameter. This is why it is possible to have an optional parameter when reporting off tables directly in Crystal Reports.
                  
  • But when using a parameter created in a Command Object or a Stored Procedure, Crystal Reports have no control over the SQL Query generated, and this is why it is not possible to have an optional parameter when using a Command Object, or a Stored Procedure.

Resolution

  • A suggestion will be to set a default value for the parameter, and write a condition in the WHERE clause of the Command Object parameter or Stored Procedure parameter based on the default value.
        
    1. In Crystal Reports, open the report based on a Command Object or Stored Procedure.
         
    2. Edit the Command Object, or Stored Procedure Parameter, and set a default value.
        
    3. Modify the Command Object, or Stored Procedure and set a conditions when the parameter value equal the default value, then returns all the data, otherwise filters on the parameter value entered.

Keywords

Optional param, CR, mandatory , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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