Crystal Reports 10: The Complete Reference
So far, this chapter has concentrated on the basic steps required to create cross-tabs, on grouping options, and on some of the finer points of multiple row, column, and summarized fields. In all of these examples, the resulting cross-tab object looks fairly plain. In keeping with the ability of Crystal Reports to create publication-quality reports, you have numerous options available to help you improve the appearance of your cross-tab reports .
The most basic type of formatting options for cross-tabs lies in the individual cross-tab objects themselves . A cross-tab actually consists of a series of individual objects. The best way to see this is to look at a cross-tab in the Design tab.
Notice the row and column name fields, which are similar to group name fields in a regular report ”they display the database fields that make up the row and column headings. The Total text objects indicate the subtotal and total rows and columns . These are standard text objects ”simply double-click them to change their contents, if you wish. And, in the actual cells , notice the subtotal or summary functions that calculate the cross-tab totals.
Each of these individual objects can be resized or formatted to change the appearance of the cross-tab. For example, if a column in the cross-tab isn t wide enough to show its contents, the contents will just be cut off, or truncated. Examine the following cross-tab:
Notice that the row labels are being truncated. Also, note that many of the cells contain pound signs, indicating that the cells aren t wide enough to show all the data in them. (Remember that if Allow Field Clipping is turned on, you won t see pound signs when numbers are truncated.)
Although you may be tempted to look on one of the tabs of the Cross-Tab Expert dialog box for some sort of column width setting, you simply need to select the individual object that makes up the column and resize it. This can be done in either the Design or Preview tab. Simply select the object, noting that all other similar cells are selected as well. Then, point to the desired sizing handle (one of the small blue blocks on all sides of a selected object) until the mouse cursor changes to the two-way sizing cursor. Then, simply resize the object to its desired width.
| Tip | If the summarized values in your cross-tab are just too large to fit into a tidy grid, there is also a function you can use to abbreviate thousands or millions with the standard K or M notation. See Conditionally Formatting Cross-Tabs later in this chapter. |
You can format the individual pieces of the cross-tab just like any other text object or number field, using either the Formatting toolbar or the Format Editor. You can change the object s color , font face and size , horizontal alignment, or any other standard formatting option. If you choose one of the summary or subtotal objects in the middle, you can choose one of the default formatting styles, or choose a custom style to specify the number of decimal places, whether to include a thousands separator or currency symbol, or any other formatting option available to numeric or currency fields. If you base a row or column on a date or time field, you can choose how the field is displayed ”month/year, month/day/year, hour:minute, hour :minute:second, or any other variation provided by the Format Editor.
| Tip | You can select multiple objects in a cross-tab by using CTRL -click. You can then format them all at the same time with the Formatting toolbar or the Format Editor. Note, however, that the elastic box selection method doesn t select multiple objects within a cross-tab. You ll need to CTRL -click to do this. |
The Style Tab
When you select a cross-tab object and choose Format Cross-Tab Expert or right-click and choose Cross-Tab Expert from the pop-up menu, the Cross-Tab Expert dialog box will appear. Two tabs in this dialog box control formatting: Style and Customize Style. The Style tab lets you choose from several predefined formatting styles for the cross-tab object.
The Original option simply displays the cross- tab object with no special formatting ”this is the original formatting option when a cross-tab is first created. You may choose from any of the predefined styles in the list. When you click a style, a sample of the style appears in the right side of the Style tab. Then, just click OK. The cross-tab will be formatted according to the built-in style that you chose.
| Caution | Crystal Reports behaves somewhat oddly when using the Style tab on the Cross-Tab Expert dialog box. Even if you haven t chosen any customized settings, you ll always receive a warning that you ll lose customized settings when you choose one of the built-in styles. And if you return to the Style tab later after choosing one of the built-in styles, the Custom setting will be highlighted, not the built-in style you chose before. Just remember that if you don t like a style setting, you may simply press CTRL - Z or use Edit Undo to revert to your previous formatting. |
The Customize Style Tab
For very specific formatting of a cross-tab object, you need to use the Customize Style tab on the Cross-Tab Expert dialog box. This tab contains advanced cross-tab options that more precisely control cross-tab behavior. The Customize Style tab is shown in Figure 11-6.
If you ve already chosen one of the built-in styles on the Style tab, you ll see the settings for that built-in style when you choose the Customize Style tab. For example, you ll see background colors for each row or column item that the built-in style selected. If the built-in style shows totals before rows and columns instead of after, you ll see those options chosen. If you haven t chosen one of the built-in styles, or you want to change some of the settings that the built-in style selected, you may choose the various options in the Custom Style tab. The various options are explained in Table 11-1.
| Option Customize Grid's Style | Description |
|---|---|
| Rows list | Select the row that you want to format. If you've added multiple row fields, you'll see each row field listed. You can also choose separate formatting options for the row grand total. |
| Columns list | Select the column that you want to format. If you've added multiple column fields, you'll see each column field listed. You can also choose separate formatting options for the column grand total. |
| Summarized fields | When you have multiple fields summarized, you can choose whether to display them in a vertical stack (this is the default) or horizontally side-by-side. The Show Labels option prints the summarized field name(s) in the column header area (you can't customize the labels here, but can edit them in the Design or Preview tabs just like text objects). Figure 11-7 shows the horizontal summaries with labels. |
| Group Options | |
| Suppress Subtotal | If you select this option, the subtotal row or column (depending on what's selected in the Rows or Columns list) won't appear. In this case, the cross-tab still shows the hierarchical grouping relationship among the multiple row or column fields, but the subtotals for the selected field won't appear. This option is available only for higher- level fields when you've chosen multiple row or column fields ”the option is disabled if you select the lowest -level (or if you added only one) row or column field. |
| Suppress Label | If you choose the Suppress Subtotal option, this option becomes enabled. Checking this option will completely eliminate the field you chose from the row or column. The grouping hierarchy will remain , but the higher-level group won't appear at all in the cross-tab. If you have also checked Indent Row Labels (described later in this table), this option will be checked, and you can't change it. |
| Alias for Formulas | Used to refer to an entire row or column when performing conditional formatting on the cross-tab. See Conditionally Formatting Cross-Tabs later in the chapter. |
| Background Color | Sets the background color for the entire row or column that's chosen in the Rows or Columns list. This color is independent of any individual cell colors you may choose by selecting an object in the cross-tab and using the Format Editor. |
| Grid Options | |
| Show Cell Margins | Pads cells with white space on all sides. Turning this option off will place cells right next to each other. |
| Indent Row Labels | Checking this option will indent the label for the chosen row from the left of the cross-tab. You may specify how much to indent the row in the text box after the Indent Row Labels check box. This is typically used to highlight a hierarchical grouping relationship when you've added multiple row fields to the cross-tab. |
| Format Grid Lines button | Displays the Format Grid Lines dialog box (described later in the chapter) to customize where and how grid lines appear in the cross-tab. |
| Repeat Row Labels | If Keep Columns Together is checked, this option will repeat the row labels when a cross-tab exceeding the width of the page is printed on two or more pages. |
| Keep Columns Together | Prevents columns from being cut in half when a cross-tab exceeding the width of the page is printed. |
| Column Totals on Top | Displays column totals on top of the actual columns containing the data being totaled, rather than at the bottom of the columns. |
| Row Totals on Left | Displays row totals to the left of the actual rows containing the data being totaled, rather than on the right of the rows. |
| Suppress Empty Rows | Rows with no data will not appear in the cross-tab. |
| Suppress Empty Columns | Columns with no data will not appear in the cross-tab. |
| Suppress Row Grand Totals | Prevents row grand totals from appearing in the cross-tab. |
| Suppress Column Grand Totals | Prevents column grand totals from appearing in the cross-tab. |
Several of the options on the Customize Style tab, particularly the Repeat Row Labels and Keep Columns Together options, dictate how a cross-tab appears when printed on paper. This is significant, because Crystal Reports displays cross-tabs differently in the Preview tab than it will print them on a printer. Even if the cross-tab width exceeds the width of the page, the Preview tab will show the entire cross-tab across the screen. You can continue to scroll farther right to see the rest of the cross-tab.
When Crystal Reports prints the cross-tab on paper, however, it must add page breaks if the cross-tab exceeds the width of the printed page. You can control how Crystal Reports formats the cross-tab across multiple pages with the options in the Customize Style tab. When you display the cross-tab in the Preview tab, you can see where Crystal Reports will insert page breaks when the cross-tab is printed on paper. In Figure 11-7, notice that the page break occurs between columns (it s not running right through the numbers of a column) and that the row labels are repeating after the page break. This is the result of turning on both Keep Columns Together and Repeat Row Labels.
Formatting Grid Lines
Crystal Reports provides the ability to customize grid line appearance, including which grid lines appear and how they look. Customize the grid lines by clicking the Format Grid Lines button in the Grid Options area of the Customize Style tab. The Format Grid Lines dialog box will appear, as shown in Figure 11-8.
You may simply choose to not show any grid lines at all by unchecking the Show Grid Lines option. Or to control individual grid lines, you may either select the grid line you want to customize in the grid line list or actually click a particular line in the grid lines diagram at the top of the dialog box. Then, choose individual options for the grid line in the Line Options portion of the Format Grid Lines dialog box.
Labels or Legends with Cross-Tabs
If you add multiple summarized fields to a cross-tab, it may not be clear to your viewer what the numbers mean ”they may be totals, averages, or counts. The Show Labels option, demonstrated in Figure 11-7, is effective with one or two summaries. With more than one or two summaries, they may become unwieldy and hard to fit in the column or row headers.
One way of reducing the size of labels is to change their content. Initially, they display the field name of the summarized field, including any special characters (such as the @ sign that precedes formula names ). These labels are actually special text objects added to the cross-tab that you can manually change. As with other text objects, you may edit cross-tab row and column labels by simply double-clicking on them.
You may be using a version of Crystal Reports prior to 9 (earlier versions don t allow display of cross-tab labels), or you may prefer to display a cross-tab legend with any Crystal Reports version. Although Crystal Reports does not have a built-in legend capability for cross-tabs, you can create your own legends using text objects and, optionally , with filled-box drawing. Here is a multiple-summary cross-tab with a legend.
Text objects have simply been placed in the same report section as the cross-tab so that they appear in the upper-left corner of the cross-tab. The small white area in the cross-tab above the first row and to the left of the first column can be placed right over the top of the text objects. The summary fields have been formatted to show in a different color, and small filled boxes of the same color have been drawn with the box-drawing tool (discussed in Chapter 9).
Conditionally Formatting Cross-Tabs
You may wish to conditionally format cross-tab cells, depending on their contents. Conditional formatting (discussed in more detail in Chapter 9) is the process of changing the appearance of a cross-tab cell according to its contents. You may wish to highlight certain cells that exceed a certain sales goal or shipping level; you can change the color, shading, or border of just those cells.
Select the summary or subtotal object that you want to conditionally format. Then, choose Format Highlighting Expert from the pull-down menus , or right-click the cell and choose Highlighting Expert from the pop-up menu. You can choose Highlighting Expert conditions and formats to highlight certain cells.
You can also use conditional formulas. After selecting a summary or subtotal object in the cross-tab, display the Format Editor by choosing options from the Format pull-down menu or by right-clicking and choosing options from the pop-up menu. You can click a Conditional Formula button anywhere on the Format Editor to set that formatting property conditionally.
What Is CurrentFieldValue?
When you choose to use conditional formulas instead of the Highlighting Expert, you must be careful about the tests you use to conditionally format cross-tab summaries. Since the summaries are calculations based on database fields, but are not actually the database fields themselves, you can t just test a database field to conditionally format the cross-tab. And contrary to what you see if you ve placed subtotals or summaries in a group footer, you won t see the summaries or subtotals that make up the cells in the field list of the Formula Editor.
You must test the built-in CurrentFieldValue function when conditionally formatting cross-tabs. CurrentFieldValue, as its name suggests, returns whatever the cell or field being tested contains. You can, therefore, use a conditional formula similar to the following to apply a silver background color to cross-tab subtotals that exceed $10,000:
If CurrentFieldValue > 10000 Then Silver Else NoColor
Figure 11-9 shows a cross-tab with this formatting.
What Are GridRowColumnValue and the Alias for Formulas?
Using the CurrentFieldValue function described in the preceding section, you can set conditional formatting in the cross-tab only based on the value of the current cell. However, you may also want to set conditional formatting based on the row or column that the cell is in, not just the value in the cell. Crystal Reports provides this capability using two functions, GridRowColumnValue and Alias for Formulas.
When you conditionally format a cell, notice the GridRowColumnValue function in the Functions box of the Format Formula Editor. By using this function with an If-Then-Else formula (when setting a multiple-choice formatting property) or a Boolean formula (when setting an on/off formatting property), you can determine which row or column the cell is in and format accordingly . Consider the following Boolean formula that conditionally sets the Drop Shadow property on the Format Editor Border tab:
GridRowColumnValue("Product.Product Name") = "Nicros"
The result is shown in Figure 11-10. Notice that only cells in the Nicros row have drop shadows applied to them. By supplying an alias name as the parameter for the GridRowColumnValue function, you can determine which row or column the formula will refer to. In this scenario, Product.Product Name is supplied as the alias name. So the GridRowColumnValue for the Product Name row is tested. If the value of the row is Nicros, the drop shadow is applied.
By default, each row or column s alias is the field or formula name of the row or column (without the curly braces). If you want to change the alias name to something more meaningful (perhaps just the field name instead of the entire table/field name combination), you may change it on the Customize Style tab of the Cross-Tab Expert dialog box (refer to Figure 11-6).
Select a row or column field in the Rows or Columns list. Then, type a new value in the Alias for Formulas text box. You may then use the new text you typed as the parameter for the GridRowColumnValue function to refer to the row or column. If you re unsure of the row or column name aliases that are available, you may expand the Row or Column names subcategory of the Formatting Functions function tree category to see them.
| Caution | As much as you might like to, you cannot drill down on a cross-tab object. If you include a cross-tab in a summary report, you can drill down on the summary report groups, but not on the cross-tab. |
New Version 10 Formatting Options
Crystal Reports deals with cross-tabs that span multiple pages in an inconsistent manner between the Preview tab and what actually is printed or exported from the finished report. If a cross-tab contains so many columns that it exceeds the chosen Crystal Reports page width, it simply expands the Preview tab horizontally and shows the cross-tab across a single page. However, when you print or export the report, the cross-tab will actually be broken into multiple pages that print one after the other.
While this behavior hasn t changed in version 10, some new formatting options have been added that make cross-tabs that span pages easier to view. A new formatting option in the Format Editor, as well as a new page number special field, have been added to version 10 to enhance cross-tab appearance.
The Repeat on Horizontal Pages formatting option now appears on the Format Editor Common tab. You may select this formatting property for virtually all Crystal Reports object types ”database fields, bitmap graphics, text objects, and so forth. Any object that you apply this formatting option to will repeat on each subsequent page that a cross-tab object appears on, whether in the Preview tab or on a printed or exported report.
The other new version 10 feature that s helpful with multipage cross-tabs is the Horizontal Page Number special field. Available along with other special fields in the Field Explorer, Horizontal Page Number will increment as cross-tabs span multiple pages, whereas the standard Page Number special field won t. Simply drag and drop the Horizontal Page Number special field onto the report as you would any other field. The page numbers will increment as cross-tabs span pages.
Figure 11-11 illustrates these new features. Notice that the XTREME logo and a text object indicating custom formatting in the cross-tab appear on multiple horizontal pages displayed in the Preview tab. Also, notice that the Horizontal Page Number special field automatically increments when the cross-tab spans multiple pages.