|
Introduction
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
PDS
MPadovani@pdssoftware.com
|
