I have found myself working on projects where reports have been last minute additions that demanded a great deal of time and a certain touch of detail. Many times, reports are cobbled together, with good design and performance falling to the wayside.  Reports are the primary representation of the data on your system; after putting so much hard work into the application that is collecting data it is important to put a little extra effort forth to design truly "killer" reports for your application.

The development tips in this article are primarily intended for developers without much experience with Crystal Reports.  This article assumes the reader has a basic knowledge of Crystal Reports data access.

We will cover six quick tips that will enable you to quickly create reports that are richly featured. We will start with a basic report that a manager of a project may ask for; order fulfillment. The following examples will be created from the SQL Server 2000 Northwinds Trading Company sample database. The sample report referenced throughout this article pulls data from the Orders, Customers, and Products tables. The report is intended to be a representation of a typical order fulfillment report. It groups orders together, and displays information about each product under the order. The examples below are using Crystal Reports 2008, but have also been tested with Crystal Reports XI.

Using templates helps with one of the most important aspects of delivering quality products, being consistent. Crystal Reports 2008 ships with twelve built in templates, but the power comes from the ability to create your own templates. 

Report templates are existing Crystal Report (.rpt) files which will allow you to "inherit" many design related formatting settings including:

·         Page headers and footers

·         Color formatting

·         Charting settings

·         Lines, Borders and Boxes

·         Logos and images

·         Addition of Standard Functions

·         Addition of Special Fields

·         Field highlighting

Figure 1: A report that does not have a template applied

To apply a template, select the “Template Expert” found on the report menu.  

Figure 2: Crystal Reports Template Expert

To apply a custom template, select "Browse" on the Template Expert and navigate to an existing report you would like to use as a template for your new report. In our example we will use the "NorthwindTemplate.rpt" file.

Figure 3: A report with a custom template applied

The Northwind Template report file contained header information with company detail and a logo that our new report has now inherited.    

This tip may be one that many readers are already aware of, but it is still worth mentioning. Formatting objects on a report can be a tedious task, when there may be multiple formatting options applied to each object on your report. Including labels, the formatting may need to be applied to over a hundred objects on a report, which increases the possibility of errors. To help minimize errors, use the “Format Painter” tool.

Figure 4: Format Painter Tool

To use the "Format Painter" tool, follow the following steps:

1. Select an object from which to copy formatting.

2. Select the "Format Painter" tool from the menu.

3. The cursor will change to a paint brush; use it to select the object from which you would like to copy the formatting.

4. Double click the "Format Painter" tool from the menu.

5. Click on any other object to apply the formatting to that object.

Now the formatting from the original object you selected will be applied to each of the objects you select. To turn off the "Format Painter" tool, click on the tool again in the menu.

It is important to monitor performance from the start of development when you are creating Crystal reports. This will save time later in the development cycle trying to figure out why a report is slow. Code profiling is the amount of time that various behaviors of your application take to run. You should be running a profiler on your SQL statements before the data even gets to the report, to be aware of how long your queries take to run. 

Crystal Reports provides a tool to estimate how long the report will take to load (render time). In my opinion if you are working with data and you’re not profiling your code then you’re not doing your job.

The Crystal Reports Performance Information tool can be found on the report menu.

Figure 5: Performance times for the order fulfillment report

So, what is an acceptable load time? It depends. Load times of not more than six seconds are a good goal, but there are always exceptions. Keep reports as simple as possible, and query only the data needed for the report. Use SQL to perform as many functions as possible before the data is passed to the report, such as ordering, or in some cases grouping data.  If you see a section of data that can be broken out into a new report, doing so may help with load times.

Often times the style of certain types of items needs to be changed to make them stand out on the report. This is commonly used to indicate negative numbers, or numbers over or under a certain threshold. Crystal Reports has built in support for this that makes this easy to accomplish.

In this example, the formatting of the "Units in Stock" objects needs to be changed when the value is less than 15. To accomplish this, first select the field by clicking on it once. Select "Highlighting Expert" from the Format menu; then select "New".

Figure 6: The Highlighting Expert

You will then be able to set options for when and how the highlighting will occur. For this example, I set the font color to red and the background to yellow, when the “Units in stock” object is below 15.

Figure 7: Highlighting when Units in Stock is below 15

One of the most important things I have learned with Crystal Reports is that many times you need to “think outside of the box”. Crystal has support for Boolean operators to be displayed as the following:

True or False

T or F

Yes Or No

Y or N

1 or 0

Crystal Reports does not have built in support to display a check box for Boolean operators, but with a little creativity and the Wing Ding font, this task can be easily accomplished. The following example will illustrate how to use check boxes to replace true/false statements in the “Discontinued” field. The simplest way to accomplish this task is to change the display string of the value, and then set the font of the object to Wing Ding.  First a formula will need to be added to change the display text of the field. Right click on the Boolean field you would like to change, and select “Format Expert”. On the Common tab, click the X+Y button for the “Display String” option.

Figure 8: Format editor display string

Ensure that “Crystal Syntax” is selected and copy the following formula:

Figure 9: Changing the display string

The character code 0xFE in the Wing Ding font represents a box with check mark. In contrast, the character code 0xA8 represents an empty box. The Character Map tool, which can be found in the Accessories folder in the Windows Start menu, will assist you in discovering the hex values for character codes of the items you would like to display. After you have found the character code for the value you would like to display, add it to your formula. It is much simpler to use the decimal representation of the ASCII value instead of the hex value. This is a great site http://www.asciitable.com/ to find all of the ASCII values, including their hex and decimal representations. After you have set your formula, make sure you set the font for the object to Wing Ding, on the “Format Editor “screen.

Figure 10: The Windows Character Map tool

Figure 11: Fulfillment report with discontinued check boxes

Objects on a Crystal Report allow for the addition of a Tool Tip. What’s great about features like this in Crystal Reports is the ability to apply formulas.  This example will illustrate how to add a Tool Tip to the “Units in Stock” field that will indicate how many units are on order when the user hovers over the object. The Tool Tip will generate this data from a formula.

To add a Tool Tip to a report object, right click your object and select “Format Editor”. Select the Common tab and click on the X+Z button next to the Tool Tip text box.

Ensure Crystal Syntax is selected, and enter the following for the formula:

Figure 12: Tool Tips for units on order

Figure 13: The Tool Tip over the Units in Stock field

A common report request is the ability to add a watermark to a report indicating that it is a draft. This example will show you how to accomplish this. In order to create the watermark, create a new header section, add a text object for the watermark and select the “Underlay Following Sections” option. This allows for new header to be above the rest of the sections of the report.

Figure 14: The Section Expert

Figure 15: The watermark in the designer

The text object that was added has a silver 72 point font set, font spacing of 80 and the text turned 90 degrees. The text rotation option is found off the common tab in the Section Expert.

Figure 16: Fulfillment report with draft watermark

Don't forget that you can suppress sections in Crystal Reports via code if you would like to provide an option to print the report without the watermark. Suppressing sections through code is out of scope of this document.

The six tips in this article are just the tip of the iceberg when it comes to developing Crystal Reports. The tools available within Crystal Reports make it easy to design professional, scalable reports for those without a fine art degree or advanced-level knowledge.