Using Stored Procedures to Create Crystal Reports®


And why it's worth you learning more about this powerful tool!



Sometimes the nature of data in the Vista database and Crystal’s selection and manipulation capabilities combine to make it difficult to generate certain types of reports. For instance, examine the below report. At first glance, it looks relatively simple—a small table of statistics highlighted with a corresponding bar chart. Both the tabular and graphic displays are straightforward Crystal capabilities.

However, if you look at the underlying tables that feed this report, you will start to see the difficulties.

The problem is with the structure of the emprate table, which keeps each rate increase as a new row. As per our report requirements, before we summarize data into departments (distributions), we must get detailed annual rate data by person for a given company, rate type, and date range. Company and rate code are simple, but the date range is more difficult because we’re not trying to get annual rates; rather, we’re trying to get increases in annual rates. To get this, for each person/company/rate, we need to first get the record with the most recent effective date just prior to the start date of the input range. This is the person’s base rate prior to any increase during that period. Then, for each base rate, we must get the record with the latest effective date up to but not past the end date of the input range. This gives us each person’s final increase (assuming there are increases). Finally, we can compare the base rates with the final rates to come up with our statistics.

Let’s look at some sample data in emprate to illustrate this. Assume that the below data is for one person in one pay company for one rate code.

For the data in the above table, if we wanted to analyze the time period spanning the year 2006, we’d first need to get the base rate just prior to that time period—in this case, the 3/5/2005 annual rate of 40,000. Then we need to get the last rate increase during that time period—in this case the 9/9/2006 rate of 42,000.

As you can imagine, our query is no longer that simple, which makes the job in Crystal even harder. So, when the nature of data makes processing in Crystal difficult, one of the first things you can do is to make the data simpler by using or creating a database view that encapsulates and simplifies the data as well as incorporates security.

In our example, the first thing we do is replace the above tables with their corresponding secured views. However, there is no need to combine multiple complex views in our report since we don’t really need any information in empcomp other than the distribution_code and we really don’t need any information in tbldist other than the distribution name.

On the other hand, there is a secured view that encapsulates emprate information—emp_pay_rates. So, we’ll try to create a single view for this report, combining information from emp_pay_rates (instead of emprate) with the two fields from empcomp and tbldist. This way, we retain the underlying security built into emp_pay_rates.

(Note that in the below query, we use emprate instead of emp_pay_rates when we do self-joins. We could have just used emp_pay_rates each time; however, as long as we use emp_pay_rates once, we incorporate its underlying security. On the other hand, if we keep joining back to emp_pay_rates instead of emprate, we build in extra overhead because the database will unnecessarily try to reapply the security for each join.)

However, we still have not dealt with the complexity of emp_pay_rates (whose structure, for our purposes, is not materially different from emprate). We’ll try to handle this by writing the SQL query that retrieves the information we want and then see how we can convert that into a view that we can use in Crystal. (If you’re not too familiar with SQL, don’t let the below scare you. The whole point of this article is to show options when the SQL gets too complicated.) Here it is.

Although the above is slightly more than a simple “SELECT * FROM emprate” (ha, ha), it not only gets us the data we want, but also does all of the summarizing and ordering.

So, if we can feed this query into Crystal, the report will be very simple. To do this, our general procedure is to turn complex queries into database views. However, there is no easy way to do this with this view because of the way the input start and end dates are used in different sub-SELECTs.

Using a Stored Procedure in Crystal

And with that little introduction, we finally get to the point of this article—using stored procedures to simplify data queries for Crystal. Although we can’t make the above query into a database view, we can incorporate it into a stored procedure that returns a result set and then feed that stored procedure into Crystal no differently than we would feed a query based on views or tables.

Since stored procedures are so different between Oracle and SQLServer, we’ll first show you how to use a stored procedure in Crystal, given that it is already created. Then, we’ll separately show you how to create the procedure in SQLServer and Oracle.

When creating a Crystal Report, make it like any other report, except for the following.
1. When specifying a data source, instead of selecting Tables or Views, select Procedures, and then select the newly created procedure.

2. Crystal will immediately prompt you to enter values for the procedure’s input parameters. Do NOT do so; rather, check “Set as Null” and click OK so Crystal will know to prompt for these as input parameters each time the report is run.
3. If you are using SQLServer, click on the Field Explorer and rename the fields to remove the “@” prefix that Crystal automatically adds to conform to SQLServer parameter/variable naming conventions. (This is not necessary if you are using Oracle.)
Then finish the report as you normally would. In our example, since all of the hard work was done in the query embedded inside of the stored procedure, the work in Crystal is trivial.

Creating a Result Set Stored Procedure in SQLServer

Once you have your SQL statement, creating a SQLServer stored procedure that returns a result set is trivial. The basic format is as follows:

So here is the code to create a procedure named RATE_IncreaseSummary for our example.

Once the procedure is created, just like you must GRANT SELECT on new views TO PUBLIC, you must give execution rights on this new procedure to your users. So, the last step is to execute the below GRANT statement.

You can easily test this procedure in QueryAnalyzer with a statement such as the below.

Creating a Result Set Stored Procedure in Oracle

Once you have your SQL statement, creating an Oracle stored procedure that returns a result set is not quite trivial, but it is relatively easy if you follow these steps.

Although it is not necessary, since Oracle wants to know up front a template for the output of the stored procedure, an easy way to accomplish this is to create a temporary table definition. This table won’t receive any data, but its existence describes the result set from our procedure.

1. Next, Oracle stored procedures require REFERENCE cursors, which must be defined within the context of Oracle packages. So, we need to create a package.

2. The stored procedure itself must use syntax that incorporates the above reference cursor, returning a result set defined by the above temp table’s type. Here is the code.

Once the procedure is created, just like you must GRANT SELECT on new views TO PUBLIC, you must give execution rights on this new procedure to your users. So, the last step is to execute the below GRANT statement.

Testing stored procedures returning result sets is also a little more complicated in Oracle, but this script will work in SQL*Plus.

Integrating this Report into the Vista Menu

Now that we’ve taken a complex set of data and converted it into a useful Crystal Report, the only thing remaining is to incorporate the report into the Vista menus. When doing so, we’ll want to take into account the Vista security that will enforce who can see what data, but we’ll also want to make the prompts user-friendly (e.g., by using list boxes for parameters such as company and rate code). Alternately, we might want to hide and default some parameters (e.g., default the company to the current person’s pay company). Finally, we also might want to schedule a report like this to run on a recurring basis, reporting on the previous time period (without continually reentering the previous time period’s dates).

If you’re coming to the 2007 UGA, be sure to sign up for the Sunday workshop on Integrating Crystal Reporting with Vista. We’ll use this exact report as our example to do this integration.

See you in Salt Lake City!

contributed by:

Marco Padovani
Senior Software Specialist

  Back to Top
  © Copyright 2007
PDS and PDS User Group Association