Crystal Reports & StoredProcedures

Easily Create Reports with StoredProcedures and Crystal Reports

Crystal Reports is a powerful reporting tool that allows you to easily create and export interactive and attractive reports from any type of data source. For example, you can use it to visualize your company data in a sophisticated way and make it available to your decision makers.

SAP Crystal Reports supports almost all common data sources such as databases (Microsoft SQL Server, Oracle, MySQL, Microsoft Access), files (Microsoft Excel, text files, XML) and also other data sources accessible via ODBC, JDBC or OLAP. Reports can be equipped with graphics and tables and can be directly converted into various publishable formats.

In this article, I would like to show you a way to further optimize the use of Crystal Reports and improve your workflow: Crystal Reports & StoredProcedures

How to Use Crystal Reports with StoredProcedures

With SAP Crystal Reports, you can build your reports not only by using tables, views or SQL queries, but also directly by using StoredProcedures. StoredProcedures are server-stored SQL queries. These queries can be equipped with parameters which allows for them to be used even more flexibly. The advantage is that, within the StoredProcedure, the company data can already be prepared in the way as the SAP Crystal Reports report will require it. This makes it very easy to link different data with each other.

Using Microsoft SQL Server Integration Services (MS SSIS), I first copy the company data relevant for my reports (from the various applications and database servers) to a separate central report server.
On this separate central report server, a unique StoredProcedure is created for every SAP Crystal Report. 
The advantage is that now, within my StoredProcedure, I can divide the sometimes extensive and complex SQL queries into several small, easy-to-understand, easy-to-maintain and thus expandable individual sections. 

My Conclusion:

By using StoredProcedures, I can create very complex, extensive, powerful, flexible and, above all, at a later point easy to understand & thus expandable queries, which I can then display perfectly with SAP Crystal Reports.

I hope you enjoyed these articles!