Add New Parameters to Stored Procedure Used in Crystal Reports

There is no doubt that SAP Crystal Reports is a great tool for designing and developing reports that could cover almost all business needs. Unfortunately it also has some limitations that cause trouble to developers throughout the development process. In today’s post I explain one of these limitations that I recently encountered and how I managed to overcome it.

For one of our clients who leverages SAP Crystal Reports as a reporting engine, I was required to modify more than 20 existing reports by adding a new filter to reports and respectfully to the underlying stored procedures to reflect changed business needs. Initially the task seemed quite simple but it turned out that SAP Crystal Reports is very sensitive when it comes to changes in stored procedure parameters.

What I did was to alter one of the stored procedures by adding the required parameter. Then I refreshed the report in Visual Studio using the Verify Database command available from Crystal Reports->Database->Verify Database menu (also available from the Field Explorer panel). To my surprise, the result I got was the following error message:

Database Connector Error: ADO Error Code: 0x80040e10
Source: Microsoft SQL Server Native Client 10.0
Description: Procedure or function ‘Report_CollectionAnalysis’ expects parameter ‘@year’, which was not supplied.

ScreenHunter_02 Jan. 02 14.43

Why does this happen? Well, it seems like the Verify Database command is designed to work only to refresh changes in the number and type of fields returned by the stored procedure. It does not work when a new parameter has been added to the stored procedure. Actually, what this command does is to execute the stored procedure against the server with the provided parameter values. The returned result set then presents the available fields that could be used in the report. However, the problem with the new parameter is that SAP Crystal Reports does not prompt you to enter a value for it. This way the execution of the stored procedure fails with the error above.

How to solve this issue? In fact there are two workaround solutions. The first one is to delete the stored procedure and to add it again to the report. When adding a new stored procedure to the report, SAP Crystal Reports detects all the parameters and prompts for values for all of them. However, the disadvantage of this approach is that deleting the stored procedure also causes removing all of its fields from the report which, for a complicated report like this, is not an option.

The second solution is much easier and it is, in my opinion, the recommended one. Just add the new parameter to the stored procedure with a default value (more information about default values in Microsoft SQL Server can be found here). Now, if I try to refresh the report with the Verify Database command, I get a message saying “The database file ‘Report_CollectionAnalysis;1′ has changed. Proceeding to fix up the report“.

ScreenHunter_04 Jan. 02 17.53.

When I hit the OK button to update the metadata information about the stored procedure, I get another message saying that “The database is now up to date.“.

ScreenHunter_05 Jan. 02 17.55.

That’s all. Next time when I execute the Verify Database command SAP Crystal Reports will prompt me to enter a value for the new parameter.

What about if the business logic does not allow to have a parameter with a default value? Well, that’s not a problem because once the metadata information is refreshed, I can safely remove the default value from Microsoft SQL Server. SAP Crystal Reports will continue to prompt to enter a value for this parameter whenever I try to refresh the report or to preview it.

One thought on “Add New Parameters to Stored Procedure Used in Crystal Reports

  1. Hi,Yes the procedure should work exactly the same on an Oracle. (Someone else I helped via email confirmed it), I unfortunately don’t have an Oracle DB available to me so I cannot convert the SQL commands over to Oracle. I will try to get a hold of one so I can help you out though.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>