10 Essential Best Practices:
Developing Efficient Crystal Reports

There is no doubt that Crystal Reports is an outstanding report development tool.  But at times, with all its features and functionality, it can become hard for developers to see the forest from the trees when it comes to determining if the report template is truly efficient in performance.  Some customers complain of reports taking 20 to 30 minutes to generate data, only to find that modifying the template with a few simple function updates can generate the same report in 2 to 3 minutes.  The trick is in understanding the report process model of Crystal Reports and applying best practice development rules.  This whitepaper has been developed to help unleash the full power of Crystal Reports efficiencies so that you can be confident your reports are running at maximum efficiency and take advantage of Crystal Reports development best practices:

1. Perform Grouping on Server
2. Create "Pushed-Down" Selection Expressions
3. Avoid "Page N of M" in Complex Reports
4. Pay Attention to Performance Information
5. Understand Database Basics
6. Use Custom SQL Commands & Stored Procedures
7. Avoid Sub-Reports if Possible
8. Evaluate Manual Running Totals
9. Use Formulated Concatenation

Perform Grouping on Server

One of the biggest bangs for your buck in terms of gaining reporting efficiencies is performing SQL grouping on the database server (known as server-side processing).  Server-side processing allows you to set up a report that performs the majority of its processing on the server and pushes only relevant details to your computer.  This pays back enormous dividends given the fact that the database is built for speed and that much smaller amounts of data need to be transferred from the server to your workstation.  
Server-side processing provides a number of benefits including:
    - lower transfer time from the server to the client;
    - less time connected to the server; and
    - less memory needed to process the report on your computer.
So how does server-side processing work?  By using SQL pass-through technology to send an SQL statement to the database server and retrieve an initial set of data, Crystal Reports off-loads much of the data retrieval and sorting work onto the server system.  This frees up local memory and resources. That is why server-side processing works only for reports that have been sorted and grouped; if a report has not been sorted and grouped (for example, if it is a simple detail list report), then there is no processing to push to the server. You should also note that server-side processing works only for reports based on SQL data sources.  Large amounts of server resources can be used for the temporary databases required when pushing grouping to the server.
Keep in mind that in order to perform the grouping on the server, your report must conform to the following conditions.
    - The Perform Grouping on Server option (in the Options dialog box) is enabled.
    - The report uses some form of grouping.
    - The report is at least partially hidden (at the very least, the Details section must be hidden). Since the server will process
       those sections that are hidden, the greater the portion of the report that is visible, the greater the amount of processing
       that must take place on the client side. If the Details section is shown, server-side processing will not be possible.
To enable Server-Side processing
On the File menu, click Report Options.
Select Perform Grouping on Server on the Report Options dialog box.
This check box is inactive if Use Indexes or Server for Speed is not selected.
Tip: When Use Indexes or Server for Speed is selected, you can quickly enable Perform Grouping On Server from the Database menu.
Click OK.

To receive the rest of this whitepaper, please fill out the form below.  The whitepaper will be emailed to you momentarily after submitting the form.  Thank you!