Crystal Report Performance Tip – Stored Procedures

 As I work with clients and their Crystal Reports, one of the items that will come up sooner than later is how to increase the performance of their reports.  The performance issue becomes more noticeable once the reports are published into Ripplestone and the users are interactively running the reports.  If the report takes 5-10 minutes to run the users will let you know that the reports are “taking forever” to run.

One of the suggestions I usually have is to look into using stored procedures to do a lot of the work and have the stored procedure return a table that is easy to report from.  What I mean by “easy to report from” is that the table returned by the stored procedure has as much processing completed within the stored procedure so the report will not need to do the processing.

One of the techniques that I have used often is to have the stored procedure create a temporary table that I will fill with the data needed for the report and return the temporary table as the output of the stored procedure.

To create the temporary table, you declare the table name and the fields that the table will contain.  The example below is for a financial report for profit and loss.

Declare @tblProfitAndLossSummary Table
    (LocationName varchar(50),
    ActualExpense smallmoney,
    ActualRevenue smallmoney,
    NonProductivityPercentage decimal(9,1),
    PaidProductivity decimal(9,1),
    WorkedProductivity decimal(9,1),
    HourlyRate smallmoney)

If the data that will be going into the table will be coming from multiple locations or tables then you might need to do some type of calculation within the stored procedure.  In the above Profit and Loss example I needed to create a SQL Server cursor and then loop through the records and insert a record into the temporary table for each record within the cursor.

        /* Initialize the variables */
        SELECT @ActualRevenue = 0
        SELECT @RegularHours = 0
        SELECT @ActualExpense = 0
        SELECT @NonProductivityPercentage = 0
        SELECT @PaidProductivity = 0
        SELECT @WorkedProductivity = 0

To hold the data for each record, I created a variable for each of the fields in the temporary table and then would insert the data into the temporary table with the following code.

INSERT INTO @tblProfitAndLossSummary
(LocationName,
ActualExpense,
ActualRevenue,
NonProductivityPercentage,
PaidProductivity,
WorkedProductivity,
HourlyRate)
VALUES
(@LocationName,
@ActualExpense,
@ActualRevenue,
@NonProductivityPercentage,
@PaidProductivity,
@WorkedProductivity,
@HourlyRate)

I was then able to run calculations within the Cursor loop such as

/* This gets the Revenue amount for a store */
SELECT @ActualRevenue = SUM(BillingCharges.ChargeAmount)
FROM BillingCharges INNER JOIN BillTo ON BillingCharges.BillNumber = BillTo.BillNumber
WHERE (BillTo.StoreNumber = @StoreNumber)
AND (BillingCharges.PeriodFrom >= CONVERT(DATETIME, @StartDate, 102)) 

AND (BillingCharges.PeriodTo <= CONVERT(DATETIME, @EndDate, 102))

/* Calculate the Regular Hours */
SELECT @RegularHours = ((@RegularTime + @Category1Time) / 60)

In the above example there were many additional queries and calculations needed to get the values for the 7 fields being inserted into the temporary table.

In the end all the data that the report needs is contained in the temporary table and the last step would be to return the data with a final SELECT statement.

SELECT LocationName,
    SUM(ActualExpense) AS Expense,
    ActualRevenue,
    AVG(NonProductivityPercentage) AS AvgNonProdPercent,
    AVG(PaidProductivity) AS AvgPaidProd,
    AVG(WorkedProductivity) AS AvgWorkedProd,
    AVG(HourlyRate) AS AvgHourlyRate
FROM @tblProfitAndLossSummary
GROUP BY LocationName, ActualRevenue
ORDER BY LocationName

RETURN

The crystal report has the added bonus of being easy to format since most of the data is ready for the report.

In this example, the report before the stored procedure was very slow with many formulas and sub-reports.  After the report was re-written with the stored procedure it runs from within Ripplestone in under 5 seconds.