Bringing the Message to Life

Chapter 7 - Selectively Refining a Report Using Data Values
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

When a report first opens, the person viewing the report takes it in as a visual specimen, examining it, absorbing it, and assimilating the information. The process involves reading the information on the report and figuring out what its main message is. Have you ever wanted to have a particular part of the overall message jump out at the reader? Crystal Reports provides two features that will help you bring the data to life for the report reader and really focus their attention on its message:

• The Highlighting Expert

• Report alerts

The two differ from each other in that the Highlighting Expert uses color to focus a user’s attention on records that meet a criterion, while report alerts use text messages in a dialog box to display data that meets a coded condition.

The Highlighting Expert

If the report you’re designing will be viewed on a color monitor or printed on a color printer, it might be nice to add a splash of color to a data value to make it stand out. The Highlighting Expert can help call attention to specific information within a report by allowing you to code conditions that, when true, will force the font style, font color, background color, and data border to change.

Creating a Highlight Condition

To get started, select a field in the report whose value changes during the course of the report. This can include group names or fields in the Details section. With a valid field selected, you can choose Format > Highlighting Expert to open the dialog shown in Figure 7.22.

Figure 7.22. The Highlighting Expert

Tip 

You can also select the field and right-click to access the Highlighting Expert from the context menu.

We selected the field PurchaseDat (our badly named database field that contains the resort unit’s purchase date) prior to opening the Highlighting Expert. While there is nothing in the dialog itself that tells you which field was selected, you can determine the data type of the selected field by observing the type of data shown in the Sample box at the bottom right of the dialog.

The dialog is split into two sections. The area on the left is the Item List, which shows all the conditions being processed by the Highlighting Expert. The Item Editor, on the right of the dialog, is where you set the conditions and visual attributes that will change if the condition is true. Notice that we’re not going to write any formula code here; instead, the Highlighting Expert dialog provides drop-down boxes and buttons to automate the process for you.

When you click the New button below the Item List, a value is automatically added to the Item List, representing a new condition. When multiple conditions appear in the Item List, you can order them in the priority that you want them to be run. The up and down Priority arrows are used to move a condition up in priority so that it is considered before conditions listed below it or move a condition down in priority so that it is evaluated after conditions above it. Conditions execute in a top-to-bottom order in the Item List, and all conditions will execute. If you have conflicting settings in different conditions, the last one executed will set the format, so be careful not to override a condition with a different condition.

Clicking the New button also activates the Item Editor. This part of the screen is organized into two parts. At the top, you set the condition criteria, and at the bottom, you set the visual characteristics that will change when the condition is true. In Figure 7.23, the current field (signified by the phrase this field) has been set to change to italic maroon font with a double-lined border if the date is less than or equal to 12:00 a.m. on January 1, 1993. In business terms, this will highlight in maroon any resorts that were purchased prior to 1993. With the condition coded, it appears in the Item List on the left.

Figure 7.23. Coding a highlight

The top of the Item Editor uses three drop-down boxes to specify the three parts of a condition statement:

• Field value

• Operation

• Threshold value

You selected a field value on the report in order to open the Highlighting Expert. The selected field is the one that will be formatted (highlighted) or visually changed. You can choose to set a condition based on this same field or on a different field. All report fields, formulas, group names, and database fields are accessible through this list. This means that you can change the formatting of the selected field based on the value of a different field. For instance, if the country is Bahamas, you can set the Purchase Date field to display in italics with a maroon font and double-border box.

The operator drop-down box lets you compare a field to a value using the following comparison operations:

is equal to

is not equal to

is less than

is less than or equal to

is greater than

is greater than or equal to

is between

is not between

Note 

Table 7.1 describes the behavior of the comparison operators.

The threshold value drop-down box is automatically populated with sample data from the database for the field value chosen. You can pick a threshold value from the list or type one directly. If a field is of type date/time, the time component defaults to 12:00 a.m. and will be automatically included in the threshold value by Crystal.

Note 

You can add as many conditions to the Item List as you need, giving you the ability to set multiple fields in the same data record based on the same or differing criteria.

The Result of a Highlight Operation

When you open a report that contains a highlighted condition, the group name or variable data that meets the criteria will be formatted with the visual settings coded in the Highlighting Expert. Figure 7.24 shows the MPI and WNS resorts in the Bahamas in italic maroon font with a double-lined border; these resorts were purchased prior to 1993.

Figure 7.24. A highlighted field

Note 

The Highlighting Expert can also be used to format cells in cross-tabs or OLAP grids by setting conditions on row and column heading values.

Report Alerts

A report alert is a custom message that you program to display when certain conditions are met by the data contained in the report. For instance, if there is a trend that shows a decrease in sales prices for resorts, an informational message can point this out instead of leaving it to the report reader to deduce. To create a report alert, you write a formula with a condition statement in it. To trigger the alert, the condition must be true. A true condition causes a text string message to be displayed onscreen in a prompt window with an OK button. The informational message and alert appear when the report is opened or the data in the report is refreshed.

Coding an Alert

To create an alert, choose Report > Alerts > Create Or Modify Alerts to open the Create Alerts dialog shown in Figure 7.25. In this example, an alert named SalesPriceBelowAverage has already been created. Modifying and creating an alert are done from the same dialog, and the New, Edit, and Delete buttons guide you in the task you want to do.

Figure 7.25. Creating an alert

When you create a new alert or edit an existing one, the Create Alert or Edit Alert dialog displays. The dialogs are identical with the exception of the word Create or Edit in the title bar. In Figure 7.26, an alert is being edited.

Figure 7.26. Editing an alert

You should give an alert a meaningful name in the Name box and type a meaningful text message for the alert to display into the Message box. Notice the Formula icon located to the right of the Message box. You can use this to code a formula that will dynamically create the text string message rather than hard-coding (typing it directly) it into the Message box area. Below the Message box is a check box to enable or disable the alert. The Condition button is where you actually write the logic that will trigger the alert. Clicking the Condition button opens the Formula Editor, as shown in Figure 7.27. Here, you code only the condition portion of an IF-THEN-ELSE statement, not the full code. The condition itself must be coded in a way that its value results in either TRUE or FALSE; no other value is valid for a report alert. In this example, the PurchasePrice field is being checked to see if it is less than $200,000; if it is, the alert will trigger.

Figure 7.27. Coding an alert

Running an Alert

A report alert will run automatically if its trigger condition is true. It will activate only when a report is first opened or if the data in the report is refreshed. When an alert is triggered, the dialog shown in Figure 7.28 displays, and you have the option of viewing the records that meet the trigger criteria or simply clicking the Close button and dismissing the dialog without viewing the records.

Figure 7.28. A triggered alert

If you click the View Records button, a separate tab in the report is displayed, showing only the records that meet the alert condition. The Preview tab displays the report in its entirety. The name of the alert, for instance, SalesPriceBelowAverage, becomes the tab label for the alert data, so it is a good idea to give your alerts meaningful names. Figure 7.29 shows a set of data that generated a true condition for the alert shown in Figure 7.28.

Figure 7.29. Alert results

Checking Past Alerts

Once you view a report onscreen, you may want to go back and see which alerts were triggered for this report, if any. To do this, choose Report > Alerts > Triggered Alerts. This is a handy thing to do if you simply closed the Report Alerts window earlier rather than viewing the records. The dialog shown in Figure 7.28 redisplays and gives you the chance to once again view the records that meet the alert criteria.

Formulas and Alerts

You can write formulas in the Formula Workshop that refer to report alerts to check whether an alert is enabled or was triggered or to display a text message for the alert. Any formula that references a report alert is evaluated after Crystal Reports has retrieved the data from the database and as it is being written to the screen for display. The Function tree in the Formula Workshop, shown in Figure 7.30, shows the built-in formulas that are provided to work with alerts.

Figure 7.30. Alert function tree

The three formula language functions are available in both Crystal and Basic syntax, and each take as their single parameter the name of the report alert being referenced. Table 7.3 describes the functions.

Table 7.3: Report Alert Functions

Function Name	Description
IsAlertEnabled("alertname")	Returns TRUE if an alert is in effect for the report
IsAlertTriggered("alertname")	Returns TRUE for a record that triggered the alert
AlertMessage("alertname")	Displays the text message when the alert condition is true

In addition to the built-in functions for alerts, notice in Figure 7.30 that each existing alert is also displayed in the Function tree beneath a category called Alert Names. An alert is listed in the Function tree whether it is enabled or disabled. Double-clicking the alert name simply copies its text name surrounded by double-quotes to the editing window. This lets you use the alert name in a formula without needing to type it manually, for instance, as the parameter to one of the functions described in Table 7.3.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback