1652217 - How to prompt for a Date Parameter, when reporting of a Stored Procedure with a String Parameter in Crystal Reports?

• How to add a Date Calendar to a string prompt?
• Will like to be prompted by a Date, when reporting of a Stored Procedure with a string date parameter.
• When reporting of a Stored Procedure with a string parameter, how to set the parameter to show a Date Calendar in Crystal Reports?

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

1. In a database, create a Stored Procedure with a string parameter, meant to accept a date string.
2. In Crystal Reports, create a report of the Stored Procedure.
3. When refreshing the report, it prompt for a string parameter. 
     
   How can we provide the end user with a Date Calendar to select a value for the Stored Procedure String Parameter?

• Use a subreport to be able to be prompted by a Date Parameter. In more details, create a report on which you add a Date Parameter, then pass the date value as string to the Stored Procedure Parameter in the subreport.
     
1. In Crystal Reports, create a report based on the same data source as your stored procedure, and add a Table that returns one row of data.
        
2. Suppress all the sections of the report, except: Page Header, Page Footer, and Report Footer section.
       
3. Create a Date Parameter.
     
4. Create a formula to convert the Date Parameter from a Date, to String using a formula like:
   
      ToText(<parameter name>,'yyyy/MM/dd')
   
   Note: For additional information on converting a date to text format, see the function: ToText in Crystal Reports help.
         
5. Insert a subreport based of the Stored Procedure in the Report Footer section.
      
6. Link the main report formula to the subreport stored procedure string date parameter.
   6.1 Right click on the subreport, and select "Change Subreport Links..."
   6.2 In the upper right corner, select the Formula, and add it to the Fields to link to window.
   6.3 At the bottom right corner, select the Stored Procedure parameter.   
   6.4  Click "OK"
     
   Now, when refreshing the report, it will shows Date Calendar in the prompt window to choose a value for the parameter.  

CR, SP, StoredProc

Date type parameter, string parameter, in crystal reports, , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

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