As reports grow in data size and complexity, ensuring optimal performance becomes increasingly important. This section serves as both a reminder of some performance tips already covered in the book to this point and as an introduction to some other tools and methods provided by Crystal Reports to optimize report performance in demanding environments.
Group By On Server
This Crystal Reports option enables you to push down the Grouping and Sorting activities to the database server. By performing these functions on the database instead of the Crystal server, less data is passed back to the Crystal Report and report-processing time is decreased. This option can be set locally under the Database main menu when the given report is being edited, or set globally on the Database tab of the Options menu accessed under the File main menu.
Some restrictions apply to the use of this option, including the following:
When this option is applicable and used, the involved reports will perform faster. In addition, the detail level on these reports is still accessible through the standard drill-down functionality and will make dynamic connections to the database to bring back any user-requested detailed information.
SQL Expressions in Record Selections
As referenced and discussed previously in the book, SQL expressions are SQL statements that provide access to advanced database aggregations and functions. Using SQL expressions wherever possible in record selections and formula creation (versus using Crystal or Basic syntax) optimizes the amount of work that will get processed by the database server (versus the Crystal server)and this will increase your report's performance.
Some quick examples of SQL expressions that can be used in place of Crystal formula syntax:
Crystal Formula Syntax
SQL Expression (SQL Server Syntax)
CASE [Database Field]
WHEN Condition THEN Value1
ELSE Value2 END
(x + y)
Field1], [Database Field2])
You should investigate the SQL capabilities of the report's database thoroughly when report performance and optimization becomes a critical business issue. Mature databases like Oracle, DB2, SQL Server, and so on have mature SQL capabilities that can often be leveraged in lieu of the Crystal formula language in field selection and record selection. Using SQL expressions can dramatically increase report performance in many instances.
Use Indexes on Server for Speed
This is another performance option that is set under the Database tab of the Options dialog accessed from the main File menu. This option ensures that the involved Crystal Report uses any indexes that are present for the selected database and for the given report.
On-Demand or Reduced Number of Subreports
As discussed in the chapter on subreports, these objects are reports unto themselves and maintain their own database connections and queries. As you can imagine, if too many subreports are added to a main report, this can lead to runaway report-processing times. A typical scenario where this might happen is when you want to include the data inside a subreport for every group within the main report. In a large report with hundreds or even thousands of groups, this can lead to that subreport running thousands of timesa palpable performance hit even when the subreport is small and/or optimized.
To minimize this challenge, it is a good idea to ensure that in-place subreports (as opposed to on-demand subreports) are used judiciously and that they are indeed required in performance-sensitive reports. Often times, only a very small subset of the subreports are ever viewed by a user and an acceptable user experience can be provided with On-Demand subreports instead.
After a report has been functionally designed, Crystal Reports provides the Performance Information tool to facilitate performance testing. This tool provides information that helps in optimizing the current report for fastest performance. The Performance Information dialog shown in Figure 11.5 is accessed from the main Report menu.
Figure 11.5. The Performance Information window provides detailed report performance metrics.
The left side of the Performance window provides a tree structure that facilitates navigation among the different report metrics areas maintained by this tool:
Additional tree branches and nodes are displayed if the involved report contains subreportseach of these nodes will appear under a new parent node for each subreport facilitating performance analysis at a granular level.
One final note on performance monitoring: to facilitate record-keeping on the progress of any ongoing database or report optimizations, the Performance Information window provides the capability to save the involved report's performance information to a file for future reference and time comparison.
Crystal Reports in the Real WorldWeb Report Alert Viewing
There are many creative ways to employ alerting in Crystal Reports to direct the report consumer to information that requires attention. The following scenario helps you understand the use of alerting.
As part of her daily function, a Sales Executive views the World Sales Report multiple times. Although she is familiar with the report, it is easy to overlook an important piece of information if it is hidden in the pages to follow. Simply by looking at the first page of the report, it might not be clear if there is a problem that requires attention. The Sales report that is discussed here is grouped by Country, Region, City, and Customer. The detail section shows the order date and order amount. For the purpose of the example, the problem in the business occurs when a sales order is booked for more than $5,000. An alert will be created that flags this circumstance (see Figure 11.6).
Figure 11.6. Create an alert and set the properties.
This sample report uses two techniques to draw the viewer's attention to the significant records. The first step highlights the Group Header in red if any record in the group sets the alert. To do this, the report will evaluate a built-in function IsAlertEnabled ('Order Amount Alert') and set the highlighting appropriately (see Figure 11.7).
Figure 11.7. Set properties for the group header.
Additionally, to help draw the executive to the order(s) triggering the alert you will highlight the background of the detail record(s) that have triggered the alert. To do this, conditionally set the fill color of the detail section to yellow (see Figure 11.8).
Figure 11.8. Set properties for the detail line.
Now, when the executive views the sales report and drills to the detail data, the records highlighted in yellow indicate where the problem occurred.
Conditional formatting techniques described here can be applied to other attributes of report elements such as ToolTips. ToolTips can contain alert messages based on the triggered alerts. You can also conditionally hide or display report sections to highlight (see Figure 11.9).
Figure 11.9. Report highlighting draws attention to critical records.
Part I. Crystal Reports Design
Creating and Designing Basic Reports
Selecting and Grouping Data
Filtering, Sorting, and Summarizing Data
Understanding and Implementing Formulas
Implementing Parameters for Dynamic Reporting
Part II. Formatting Crystal Reports
Fundamentals of Report Formatting
Working with Report Sections
Visualizing Your Data with Charts and Maps
Custom Formatting Techniques
Part III. Advanced Crystal Reports Design
Using Cross-Tabs for Summarized Reporting
Using Record Selections and Alerts for Interactive Reporting
Using Subreports and Multi-Pass Reporting
Using Formulas and Custom Functions
Designing Effective Report Templates
Additional Data Sources for Crystal Reports
Multidimensional Reporting Against OLAP Data with Crystal Reports
Part IV. Enterprise Report Design Analytic, Web-based, and Excel Report Design
Introduction to Crystal Repository
Crystal Reports Semantic Layer Business Views
Creating Crystal Analysis Reports
Advanced Crystal Analysis Report Design
Ad-Hoc Application and Excel Plug-in for Ad-Hoc and Analytic Reporting
Part V. Web Report Distribution Using Crystal Enterprise
Introduction to Crystal Enterprise
Using Crystal Enterprise with Web Desktop
Crystal Enterprise Architecture
Planning Considerations When Deploying Crystal Enterprise
Deploying Crystal Enterprise in a Complex Network Environment
Administering and Configuring Crystal Enterprise
Part VI. Customized Report Distribution Using Crystal Reports Components
Java Reporting Components
Crystal Reports .NET Components
COM Reporting Components
Part VII. Customized Report Distribution Using Crystal Enterprise Embedded Edition
Introduction to Crystal Enterprise Embedded Edition
Crystal Enterprise Viewing Reports
Crystal Enterprise Embedded Report Modification and Creation
Part VIII. Customized Report Distribution Using Crystal Enterprise Professional
Introduction to the Crystal Enterprise Professional Object Model
Creating Enterprise Reports Applications with Crystal Enterprise Part I
Creating Enterprise Reporting Applications with Crystal Enterprise Part II
Appendix A. Using Sql Queries In Crystal Reports
Creating Enterprise Reporting Applications with Crystal Enterprise Part II