Crystal Reports 10: The Complete Reference

Creating Charts with the Chart Expert

You can create a limited set of chart types within the Standard, Cross-Tab, or OLAP Report Wizard from the Report Gallery. To get full charting capabilities, you ll need to use the Chart Expert after you ve created a report, either with the Blank Report option or after you ve finished using one of the wizards. If you re using one of the report wizards, you will eventually arrive at a Chart section that contains a reduced set of charting options.

If you ve already created a report with a wizard and simply want to add a chart to it, or if your report is designed with the Blank Report option, you can display the Chart Expert in one of two ways. Either click the Insert Chart button on the Insert Tools toolbar or choose Insert Chart from the pull-down menus . The Chart Expert will appear.

The Chart Expert is a tabbed dialog box that gives you tremendous flexibility for designing your charts. You specify chart options by progressing through the Chart Expert s five tabs: Type, Data, Axes, Options, and Text (of which only three are visible if Automatically Set Chart Options is checked).

The Type Tab

When the Chart Expert first appears, the Type tab will be displayed, as shown in Figure 12-1.

Figure 12-1: The Chart Expert Type tab

First, choose the general type of chart (such as pie, bar, or area) that you want to use from the list. When you make a general choice, you typically see a more specific set of choices shown as thumbnails on the right. To use the specific type of chart, click the thumbnail that best represents what you want to use. You ll see a description of the layout and uses of the chart in the scroll box below.

Certain chart types give you a choice of horizontal or vertical direction. If you choose vertical with a bar chart, for example, the bars will grow out of the bottom of the chart. If you choose horizontal, they will spread from the left of the chart toward the right.

If you check Automatically Set Chart Options, the Axes and Options tabs (discussed later in the chapter) will disappear from the Chart Expert and default settings for items on those tabs will be chosen . If you don t like the default settings or need to customize some settings on the Axes or Options tabs, uncheck this option. The tabs will return to the Chart Expert, where you may choose your custom settings.

The Data Tab

The Data tab is where you choose the layout for the chart ”whether it will be an Advanced, Group , Cross-Tab, or OLAP chart. You also use the Data tab to select the actual summary, database, or formula fields you want Crystal Reports to use when creating your chart, to choose where you want the chart placed, and to specify when you want the chart to change graphically (when you want a new bar or pie slice to be created, when you want a new point plotted on the line, and so on).

Begin by choosing the chart layout you want to use. The following four buttons on the left side of the Data tab may or may not be enabled, depending on other elements in your report:

• Group Available only if at least one group with at least one summary field has been created on your report.

• Advanced Always available, although it is the default button only if no other chart type is available.

• Cross-Tab Available only if one or more cross-tab objects already are on the report. If you have more than one cross-tab but haven t selected the cross-tab you want to chart first, this button will be disabled.

• OLAP Available only if one or more OLAP grids already are on the report. If you have more than one OLAP grid but haven t selected the grid you want to chart first, this button will be disabled.

The only parts of the Data tab that remain constant regardless of the button you choose are the Place Chart drop-down list and the Header and Footer radio buttons. The Place Chart drop-down list lets you choose where on the report you want Crystal Reports to initially place your chart. Again, depending on the chart type you use, the choices here will be different. Group charts can be placed in a higher-level group or in the report header and footer. Advanced, Cross-Tab, and OLAP charts can be placed in a group or report header or footer. Choose Once per Report or For Each group from the drop-down list. Then click the radio button that corresponds to where in the report you want the chart placed ”the header or footer.

The rest of the Data tab will change as you click different chart layout buttons.

Group

A Group chart will graph data from fields in an existing report group. You have to have at least one group defined, with at least one subtotal or summary field, before you can use this button. Figure 12-2 shows the Data tab when the Group button is clicked.

Figure 12-2: Data tab for Group button

The On Change Of drop-down list lets you choose when you want the graph to start a new element. If, for example, you choose Customer.Country, a new bar will show up in a bar chart for every country. Or for another example, if you choose Employee.Last Name , a new slice in a pie chart will appear for every employee.

The On Change Of drop-down list s contents change in correlation with what you choose in the Place Chart drop-down list. In essence, the On Change Of drop-down list shows you one or two levels lower than where you re placing your chart. For example, if you choose to place the chart Once per Report, the On Change Of drop-down list will show the highest-level group on the report. If there is more than one group, it will also show an additional option of showing the highest-level group and the next-highest-level group. If you choose to place the report in a group, On Change Of will show the next two lower-level groups, and so on.

Here s an example of what shows up in the On Change Of drop-down list when the chart is placed Once per Report and there are country and region groups on the report:

If you choose a single group, your chart will just summarize the values in that group, as shown here:

If you choose the two-group option, Crystal Reports actually creates multiple sections of the chart ”the first section based on the first group, each containing individual chart elements based on the second group, like this:

The Show drop-down list lets you choose what makes up the chart element. If, for example, you create a bar chart with On Change Of set to Customer.Country, and Show set to Sum of Orders Detail.Order Amount, you ll see a new bar for every country. The bar s height or width (depending on whether you chose a horizontal or vertical bar chart) will be based on the subtotal of Order Amount for the group.

The Show drop-down list is populated according to what subtotal and summary fields you place in the group header or footer of the group chosen in On Change Of. For a group graph to work, you must have at least one summary or subtotal for the group ”if you don t, the Group layout button won t even be available.

Advanced

An Advanced chart graphs data from individual database records in your report. Although you can create an Advanced chart even if you have groups defined, it won t be affected by the groups at all. Figure 12-3 shows the Data tab when the Advanced button is clicked.

Figure 12-3: Data tab for the Advanced button

This rather busy dialog box lets you choose data from the report or database to create a chart (fields don t have to be used on the report to be used to create the chart). Because several different fields in the database can affect the chart s appearance, a little more forethought is required when using this dialog box.

The Available Fields list shows report, formula, subtotal/summary, running total, and database fields in the report. You can select any of these fields that you need for creating your chart (except summary/subtotal fields, which can be used only in the Show Value(s) list). If you re unsure what kind of data is in a field, select it and click the Browse button to see a sample of database data. Once you re ready to use a field as either an On Change Of or Show Value(s) field, select the field and click the right arrow next to the box where you want the field placed. It will be copied to the box on the right.

The drop-down list in the upper right gives you three choices: On Change Of, For Each Record, and For All Records. The choice you make here determines how often a new chart element (bar, pie slice, and so on) will appear in the chart.

If you choose For Each Record, a new element appears in the chart for every record in the report. This may be useful for very small tables that have only a few records in them. However, if your database has more than a few records, making this choice will probably render a chart that s too crowded to be of any real value. If you choose For All Records, you essentially create a grand total chart, showing just one element that displays a total of all records on the report. If you choose this option, the box below the drop-down list remains empty ”you won t be able to add any fields to it from the Available Fields list. If you choose For Each record, you may add one field from the Available Fields list that will label each chart element.

By choosing On Change Of in the drop-down list and choosing one or more fields from the Available Fields list (except group summary or subtotal fields), you can create a chart that summarizes values for the chosen field or fields. This option basically creates an invisible group within the chart, creating a new chart element every time the chosen field changes. For example, if your report contains no groups but you choose On Change Of Customer.Country, your chart will have a new element appear for each unique country that appears in the database. Whatever field you add to the Show Value(s) list will be summarized or subtotaled by country, and the result will be used as the value for the chart.

Although there isn t an actual group on the report, Crystal Reports is creating an invisible group within the chart. You have control over the way the Chart Expert uses these groups. The TopN and Order buttons control this. If you click the TopN button, you ll see the Group Sort Expert dialog box, where you can choose to include only the top N, bottom N, Top N Percentage, or Bottom N percentage groups in your chart, and choose which number to use for N .

If you click the Order button, you ll see the Chart Sort Order dialog box with a drop-down list containing Ascending, Descending, Specified Order, and Original Order options. You may choose to show the chart elements in A to Z order, Z to A order, or using specified grouping (and, if you recall from Chapter 3, although Original Order is an option, it s probably of little use). If the chosen field is a date, time, or date/time field, you can choose how often you want a new chart element to appear (monthly, weekly, every minute, hourly, and so on). Refer to Chapter 3 for information on Top N, Specified Order grouping, and grouping on date/time fields.

Once you ve chosen a field in the On Change Of box to determine when a new chart element will appear, you can add a field or fields to the Show Value(s) list to indicate which values Crystal Reports will use to size the chart element. If you add multiple fields to this list, the chart will contain multiple bars, lines, and so forth ”one for each field you add to the list.

If you add a number or currency field to this list (and you haven t chosen For Each Record in the top drop-down list), Crystal Reports automatically uses the Sum function to subtotal the field for each invisible group. If you choose another type of field, Crystal Reports automatically uses the Count function. If you wish to change the summary function (for example, to graph the average sales amount instead of the total), you can select the field you want to change in the Show Value(s) list and click the Set Summary Operation button. A dialog box will appear with a drop-down list containing the available summary functions for that type of field. Choose the summary function you want used to size the chart element. The Percentage Summary Field option for group summaries, discussed in Chapter 3, is also available here. By checking the Show As a Percentage Of check box, you can choose a higher- level group total or grand total, and chart elements will display the percentage of the higher totals that each of the invisible group totals represents.

If you choose For Each Record in the top drop-down list, Crystal Reports will display a new chart element for every record on the report ”no invisible group will be created. In this case, any fields you add to the Show Value(s) list won t be summarized ”whatever value the fields return for each record will be charted.

Cross-Tab

The Cross-Tab button is available only if you have one or more cross-tab objects on your report. If you have only one cross-tab object, this button will be enabled even if you haven t selected the cross-tab first. However, if you have more than one cross-tab, you must select the cross-tab that you want to chart before you start the Chart Expert (Chapter 11 discusses cross-tab objects). Figure 12-4 shows the Data tab when the Cross-Tab button is clicked.

Figure 12-4: Data tab for the Cross-Tab button

The On Change Of drop-down list includes the two outer fields you chose for the cross- tab row and column; if you used multiple row and column fields, only the first row or field can be used. Crystal Reports will create one chart element (bar, pie slice, and so on) for each occurrence of this field in the cross-tab.

The Subdivided By drop-down list is initially set to None. If you leave it this way, the chart will create only one series of chart elements, based on the field in the On Change Of drop-down list. If, however, you want to create two series of elements for side-by-side comparison, or if you re using a 3-D riser or 3-D area chart and want to see multiple elements three-dimensionally, choose the other row/column field in the Subdivided By drop-down list.

The Show drop-down list shows the summary field or fields you placed in your cross-tab. Choose the field that you want to use in your chart. This field determines the size of the chart elements (height of a bar, size of a pie slice, and so forth).

The following illustration shows the resulting 3-D riser chart for the Data tab shown in Figure 12-4:

OLAP

The OLAP button is available only if you have one or more OLAP grid objects on your report. If you have only one OLAP grid, this button will be enabled even if you haven t selected the OLAP grid first. However, if you have more than one OLAP grid, you must select the grid that you want to chart before you start the Chart Expert (Chapter 19 discusses OLAP reporting). Figure 12-5 shows the Data tab when the OLAP button is clicked.

Figure 12-5: Data tab for the OLAP button

Creating a chart based on an OLAP grid is very similar to creating a chart based on a cross-tab. There are just a couple of differences between the two. There is no summary field to choose (OLAP grids display only one value, so there is no choice to make). Also, the dimension hierarchy of your OLAP grid may be a little different than the multiple row/column fields you added to a cross-tab object.

Choose a dimension on which to base the chart from the On Change Of drop-down list. A new chart element will be created for every occurrence of this dimension. If you leave the Subdivided By drop-down list set to None, the chart creates only one series of chart elements, based on the dimension in the On Change Of drop-down list. If, however, you want to create two series of elements for side-by-side comparison, or if you re using a 3-D riser or 3-D area chart and want to see multiple elements three-dimensionally, choose another dimension in the Subdivided By drop-down list. You can choose a next deeper level dimension here if you ve created multiple levels of dimensions in your OLAP grid.

Depending on how many dimensions your OLAP grid contains, and how the fields that make up the dimension relate to each other (their hierarchy ), you may need to filter the chart to just certain field values in a dimension. To do this, click the Other Dimensions button in the lower right of the Data tab. This displays the Format Other Dimensions dialog box, where you can choose the particular subdimension of the OLAP grid that you want the chart filtered by.

Click one of the available Other Dimensions and click the Select a Member Value button. The Member Selector dialog box appears, with the dimension hierarchy displayed in an Explorer-like fashion.

Navigate through the dimension hierarchy and choose a level that you want to limit the chart to. For example, if you navigate down from USA and choose CO, your OLAP chart will just contain totals for the Colorado region in the OLAP grid.

The Axes Tab

The Axes tab will appear if you leave the Automatically Set Chart Options check box unchecked on the Data tab. The Axes tab gives you complete control over how Crystal Reports displays the X, Y, and Z (if you re using a 3-D chart) axes of the chart. Figure 12-6 (above) shows the Axes tab.

Figure 12-6: The Axes tab

By making choices in the Axes tab, you can control how Crystal Reports displays axes on your charts. The axes are the areas of the chart that describe or depict the data values in the chart. If, for example, you have a bar chart in which each bar represents sales volume for a country, the bottom of the chart where each country is listed is called the group axis (also sometimes called the X axis ). The left side of the chart where the numbers representing the volume appear is called the data axis (sometimes called the Y axis ), as shown next:

If you are using a 3-D chart, the data axis is the Z axis, and a new axis called the series axis is the Y axis, like this:

Click the Major or Minor check boxes to add gridlines to the chosen axes. Major gridlines fall directly in line with the axis labels that Crystal Reports assigns to the axis. Minor gridlines appear in between the axis labels and work only for numeric labels. Depending on the type of chart you re using, you may not notice any difference between major and minor gridlines. Also, some charts will always have a group axis gridline, regardless of what you choose on the Axes tab.

If you leave the Auto Range check box for the Data Values option on, Crystal Reports automatically formats the chart according to the number of elements it includes. If you wish to customize this, you can turn off the Auto Range option and add starting and ending values for the axes, as well as choose the number format (decimals, currency symbols, and so on) to use for the labels. If you choose a certain number format, such as a currency symbol, and then recheck Auto Range, the axes will be automatically renumbered, but the number format you chose will stay in place. The Auto Scale option affects the beginning numeric value that the data axis starts with. If you choose Auto Scale, Crystal Reports uses the values of the chart elements to choose an appropriate starting number for the data axis.

If you leave the Number of Divisions set to Automatic, Crystal Reports will create a predefined number of labels and gridlines for the data axes. Clicking the Manual radio button and specifying a number in the text box will create your specified number of divisions, along with labels and gridlines, for the data axes.

The Options Tab

The Options tab will appear if you leave the Automatically Set Chart Options check box unchecked on the Data tab. The Options tab allows you to customize general options for your chart, such as whether to display it in color or black and white, whether to show a legend and where to place it, and other options. The Options tab will change based on the type of chart you ve chosen. Figure 12-7 shows the Options tab for a bar chart.

Figure 12-7: The Options tab

The Data Points section lets you choose whether you want labels, numbers, or both to appear on your chart elements. If, for example, you choose Show Label with a pie chart, each of the slices of the pie will be labeled with the item that the slice refers to. If you choose Show Value with a bar chart and choose a number format of $1, you ll see the actual dollar amounts (with no decimal places) appear above each bar.

The Marker Size and Marker Shape drop-down lists let you choose how markers look on a line chart. Markers are the points on the line chart that are connected by the lines.

The Show Legend check box determines whether or not a legend appears on your chart. The legend is the color-coded key that indicates what the elements of your chart refer to. You can also choose where to place the legend with options in the Placement drop-down list. You might want legends for a pie chart with no labels, for example. However, if you are using a bar chart with labels already appearing along the bottom of the chart, a legend is redundant and should be turned off.

The Text Tab

The Text tab, shown in Figure 12-8, allows you to assign text to different parts of your chart, and change the appearance of these text items. You can add a chart title, subtitle , and footnote. Also, you can place titles on the group, data, and series (or data2) axes of your chart.

Figure 12-8: The Text tab

By default, the Auto-Text check boxes are all selected and the text boxes next to them are dimmed. You ll notice that Crystal Reports has added titles into certain items automatically, depending on the data that the chart is based on. If you don t wish to use Crystal Reports default titles on the chart, uncheck the Auto-Text check box for the desired title and then type the material you want to appear on the chart in the associated text boxes on the Text tab. If you leave a text box blank, that title won t appear on the chart.

To change the appearance of the different items, select the item you want to change in the list on the lower right of the Text tab. Then, click the Font button to choose the font face, size, and appearance for that item. A sample of the font you choose appears in the shaded box above the Font button.

Figure 12-9 shows a chart with all the labels set. You can see where each of the labels appears on a typical chart.

Figure 12-9: Labels from the Text tab

Placing and Sizing Charts

Once you complete all the information on the Chart Expert and click OK, Crystal Reports places the chart in the upper-left corner of the header or footer section you chose. Unlike other objects you add, it won t initially be attached to your mouse cursor allowing you to drop it where you wish. If other objects are already in the section, the chart is placed on top of them (the Transparent Background option on the Chart Expert s Options tab allows objects underneath the chart to show through).

When the chart is placed, it remains selected, however. You ll notice the shaded outline and sizing handles around the chart. You can now drag it to another location in the same section, or move it to another section on the report. You can also resize the chart by using the sizing handles, or move and resize the chart by choosing Format Size and Position from the pull-down menus, or by right-clicking the chart and choosing Size and Position from the pop-up menu.

Remember that where you place a chart determines the data that it displays. If you place an Advanced or Group chart in the report header or footer, it will display data for the whole report. If you place the chart in a group header or footer, the chart will appear for every group, showing data for only that particular group. Cross-Tab and OLAP charts display the data from the particular objects they re based on. If you place a cross-tab object and matching chart in a group header or footer, the cross-tab and chart will display data only for the group they re in. Since OLAP grids don t change with their location on the report, a matching OLAP chart won t change if you move it.

Cross-Tab and OLAP charts are always in the same section as their matching cross-tab object or OLAP grid object. You may have an OLAP grid in the report footer and its matching chart in the report header, but if you try to move the chart into a group header or footer, it won t work. And if you then move the OLAP grid from the report footer to a group footer, the chart will automatically move to the matching group header.

Placing Charts Alongside Text

When you first create a chart in a section, it s placed in the upper-left corner of the section by default. If the chart is in a report or group header, the chart will print before the rest of the report or the group, because the section containing the chart prints first. Sometimes, you may want a chart to appear alongside the data it s referring to. Typically, this might be an Advanced chart that you ve placed in a group header. Instead of having the chart print by itself, followed by the details that belong to the group, you may want the chart to print alongside the details sections.

By using the Underlay option in the Section Expert, you can format the group header section to underlay the following details sections, thereby printing the chart alongside the other items. For this to work effectively, you need to size and move the details section objects so that they won t be overprinted by the chart. Then, move and size the chart so that it will appear to the side of the details section objects. Using the Section Expert, choose the Underlay Following Sections option for the section containing the chart. If there is a group name, column headings, or other information in the group header that you don t want to be underlain, you need to create a second group header section for the chart that you underlay. Format it to use the Underlay feature and format the first group header containing the textual information with Underlay turned off. See Chapter 10 for more information on multiple sections and the Underlay feature.

Figure 12-10 shows a chart placed in a Group Header with Underlay Following Sections turned on.

Figure 12-10: Chart placed alongside text with the Underlay feature

Modifying Existing Charts

Once you ve created a chart, you may wish to change it. Perhaps you prefer to see a pie chart instead of a bar chart. Or, you may want to change the titles that appear on the chart. You may even want to change the chart from an Advanced chart to a Group chart, or vice versa.

First, select the chart you want to change in either the Design or Preview tab. Then, choose Format Chart Expert from the pull-down menus, or right-click the selected chart and choose Chart Expert from the pop-up menu. The Chart Expert will reappear, and you can make any desired changes before clicking OK.

Zooming In and Out on Charts

You may zoom in and out on a limited number of chart types. If you have created a Bar or Line chart, you ll notice additional options, such as Select Mode and Zoom In, available on the pop-up menu when you right-click. You may also select a chart and use the Zoom options from the Chart pull-down menu.

If you choose the Zoom In option, your mouse cursor will change to a magnifying glass with a plus sign. While you may be tempted to just click somewhere inside the chart expecting to zoom in, you must actually hold down your mouse button and draw an elastic box with the mouse. When you release the mouse button, the chart will zoom in to the area you surrounded . You may continue to highlight additional areas to zoom in further on the chart.

To zoom back out, right-click the chart and choose Zoom Out from the pop-up menu, or choose the associated option from the Chart pull-down menu. The mouse cursor will change to a magnifying glass with a minus sign. Just click anywhere on the chart to zoom back out.

When you re finished zooming in or out on a chart, choose Select Mode from the chart pop-up or pull-down menus. Your mouse cursor will return to its default four-arrow state so that you can select the chart to move or resize it on the report.