SAP Knowledge Base Article - Public

1665390 - How to hide the parameter prompt option: “Set to Null”, when reporting off a Stored Procedure in Crystal Reports?

Symptom

  • How to remove the "Set to Null" checkbox?
  • How to hide the option "Set to Null" in a parameter prompt window, when reporting off a stored procedure in Crystal Reports?

Environment

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

Reproducing the Issue

  1. On the database side, create a Stored Procedure with a least one parameter.
  2. In Crystal Reports, create a report off a Stored Procedure that have at least one parameter.
  3. When refreshing the report, the "Set to Null" checkbox option displays for the Stored Procedure Parameter. How to hide, or remove this option?

Resolution

  • There is no option to remove the parameter option "Set To Null" when reporting off a Stored Procedure that contain parameter in Crystal Reports.
       
  • A workaround will be to insert the report based off a Stored Procedure with parameter has a subreport, then create parameter on the main report, and link the main report parameter to the subreport parameter. Since the parameter on then main report is not coming from a Stored Procedure, it will not have the parameter prompt option "Set to Null"
         
    1. In Crystal Reports, create a report off the same data source, and insert one table on the report.
        
    2. Create a Record Selection to return only one record of data on the report. 
      ( This is because the main report will simply be used to host for the other report based off a Stored Procedure. )
        
    3. In the main report, create a new parameter off the same data type as your Stored Procedure parameter.
      ( If your Stored Procedure have more than one parameter, create a parameter for each Stored Procedure parameter )
            
    4. Insert the report based off a Stored Procedure as a subreport in the Report Footer Section.
         
    5. Link the main report parameter(s) to the subreport stored prrocedure parameter(s).
        
    6. On the main report, suppress Report Header and Details section.
             
        
      Important Note: This workaround is only possible when the original report based off a Stored Procedure does not contain any subreports, because a subreport cannot contains subreport
              
            
  • A second workaround is to call the Stored Procedure within a Command Object in Crystal Reports, and create the parameters in the Command Object to pass the values to the Stored Procedure parameter.

See Also

1291890

Keywords

Hide set to null, remove set to Null, Set to Null, crystal reports, stored procedure , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

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