SAP Knowledge Base Article - Public

1892878 - Stored Procedure requirements for creating reports in Crystal Reports

Symptom

  • List of requirements for Stored Procedure.
  • What are the requirements to be able to report of a Stored Procedure in Crystal Reports?

Environment

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

Resolution

  • Crystal Reports is a reporting tool, and in order to be able to report of Stored Procedures, the following must be observed:

    1. The stored procedure must produce only one output SELECT statement.
        
      In more details: A stored procedure that return a dynamic SQL Query where the number of database fields added to the SELECT clause is different, will not work in Crystal Reports, because the reporting tool requires a static output in order to design the report. 
       
      For example: A stored procedure that return 3 fields in a specific case, and 10 in a different case, is not supported. It always have to return the same number of database fields, with the same data type.

    2. Stored Procedure Output before the SELECT statement is not supported.
              
      For example: A stored procedures designed to return any messages before the outputted SELECT statement will not display data in Crystal Reports, or will fail with an error.

    3. Output parameters cannot be used in a stored procedure, only input parameters are supported.

    4. If any variables or functions are used in the output SELECT statement, they must be assigned proper aliases as fields.

    5. Ensure the database user have sufficient privileges to execute the stored procedure.
          
      Insufficient privileges may result in the stored procedure to be missing from the list of available stored procedures to report off. If you are uncertain of whether or not you have sufficient privileges, contact your Database Administrator (DBA) for assistance.

    6. A Stored Procedure cannot call another Stored Procedure.   

    7. Crystal Reports supports SQL Query Language in a Stored Procedure that retreive data from a data source like:
      - SELECT
      - FROM
      - WHERE
      - GROUP BY
      - ORDER BY
      - UNION

      But does not support SQL Query Language for data manipulation like:
      - CREATE TABLE
      - ALTER TABLE
      - DROP TABLE
      - INSERT
      - UPDATE
      - DELETE

    8. Most importantly, the stored procedure must be able to execute successfully on the database.

  • For Oracle, there are additional requirements. For more details, see the SAP Knowledge Base Article 1181947

Keywords

Stored Procedure Limitations, restrictions SP CR , 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