3 4
Like PivotTables, PivotCharts are views of forms (and also of tables and queries, although these are less useful). Creating a PivotChart is very similar to creating a PivotTable: You can open an existing form in PivotChart view, you can create a new PivotTable using the PivotChart Wizard, or you can use the AutoForm: PivotChart selection in the New Form dialog box.
As with PivotTables, you’ll probably get the best results by using the AutoForm: PivotChart selection in the New Form dialog box to create a new form for the PivotChart. First you should create a query to gather the data that will be used in the chart using the technique described in the section "Creating a Query to Gather PivotTable Data." (One query can do double duty for a PivotTable and a PivotChart.)
InsideOut
The New Form dialog box includes a Chart Wizard option. You might expect this wizard to create a PivotChart, but it doesn’t—instead, it creates an MS Graph chart, an older, noninteractive type of chart. So you might want to leave this wizard alone and create a PivotChart instead.
To create a new PivotChart form based on an existing query, follow these steps:
Figure 12-24. Select AutoForm: PivotChart and a query to create a new PivotChart form.
A form based on the selected query opens in PivotChart view, as shown in Figure 12-25, with the Chart field list visible so that you can specify the fields to be displayed in the PivotChart.
Figure 12-25. A new PivotChart form has a field list for selecting fields.
The requirements for a PivotChart data source are similar to those for a PivotTable data source: You need at least three fields—one category field, one series field, and one data field. (A few one-dimensional chart types, such as the pie and doughnut chart types, need only the category and data fields.) As with PivotTables, it’s a good idea to do some preprocessing of your data in a query, so you can use the Expression Builder to create any expressions you want to use. Figure 12-26 shows a query with several calculated fields, intended for use as a PivotChart’s data source.
Figure 12-26. You can create a query with several calculated fields for use as the data source for a PivotChart.
To choose fields for a new PivotChart—using as an example the PivotChart based on qryOrdersAndEmployees, shown in Figure 12-25 (you’ll find this query in the Test Access 2002 sample database on the companion CD)—follow these steps:
Figure 12-27. Select a year in the OrderYear drop-down list to filter data for that year.
Figure 12-28. Select individual employees in the drop-down list to filter for selected employees.
tip - Swap the series and category fields
PivotCharts are useful and attractive even if you do no more than select the category, series, and data fields; add titles and a legend; and perhaps change the chart type. However (as with PivotTables), you can set a variety of advanced options to further enhance the appearance of PivotCharts using the PivotChart properties sheet (opened by clicking the Properties button on the PivotChart toolbar). In this section, we’ll look at the PivotChart elements you can modify by configuring settings on the PivotChart properties sheet.
note
You can change the appearance of the PivotChart’s border and add a decorative background fill by setting options on the Border/Fill tab of the PivotChart properties sheet. For example, Figure 12-29 shows the Daybreak style applied as a gradient fill to the Monthly Sales By Employee PivotChart.
Figure 12-29. The Daybreak gradient fill pattern enhances the appearance of a PivotChart.
tip - Change the border’s color and line format
The Format tab of the PivotChart properties sheet (available only when a text element—for example, a title—is selected) provides more options for formatting text, such as specifying the font, size, and color for the text. You can also change the caption and the position of the text.
The 3D View tab lets you make minute adjustments to the 3-D properties of the PivotChart. This tab is available only for 3-D chart types (which are found on the Type tab), as you might expect. You can obtain quite dramatic changes in the appearance of a 3-D chart by manipulating the slider controls on this tab.
tip
The General tab includes Undo and Delete buttons, a drop-down list for selecting PivotChart elements, a button for adding titles, and several other buttons for working with multiple charts. Depending on the chart type, other options might be available. For example, if you’re working with a pie chart, you can adjust the explosion, angle of first slice, and pie thickness. (If you’re not familiar with the terminology on this tab, you can experiment by moving the sliders to change these properties, to see how they change the chart’s appearance.)
tip
The most commonly used option on the General tab is probably the Add Title button, which inserts a placeholder title in the PivotChart.
Figure 12-30 shows the sample pie chart, with an added title and a 50 percent explosion factor.
Figure 12-30. You can increase the explosion factor to move the pie slices farther apart.
tip
Troubleshooting - I see different tabs every time I open the properties sheet
As with some other properties sheets in Access (but perhaps in this case more than most), the tabs you see in the PivotChart properties sheet vary according to what element of the PivotChart is selected, and even the options available on a tab vary according to selection. It can be disconcerting to see the Format tab one time you open the properties sheet and not the next.
For example, if the chart title is selected, you’ll see only the General, Format, and Border Fill tabs. If a pie chart is selected, you’ll see only the General, Border/Fill, Data Details, Type, 3D View, and Show/Hide tabs, and on the General tab, you’ll see Explosion, Angle Of First Slice, and Pie Thickness options. If you want to modify the format of an element such as a chart title, first select the chart title, and then open the properties sheet, and you should see just the tabs you need for working with that chart element.
You can change the chart type using the Type tab, which provides a list of several chart types. Figure 12-31 shows the sample PivotChart with the SmoothLine choice selected.
Figure 12-31. You can select a new chart type on the Type tab of the PivotChart properties sheet.
Troubleshooting - My chart doesn’t show all my data
Some chart types (in particular, the pie chart and the doughnut chart) are one-dimensional, which means that they use only category data and not series data. If you select one of these chart types and you have both series and category data, one set of data will be ignored.
On the positive side, this means that you don’t have to prepare a special data set for these chart types. But if you convert a bar graph to a pie chart, for example, you might find that the pie chart displays the wrong data, perhaps using months rather than salespeople as pie slices. To fix this problem in the new chart, drag the inappropriate category field off the pie chart, press F8 to open the field list, and drag the correct field to the Category Fields drop zone.
Several tabs of the PivotChart properties sheet let you modify the interactive elements of a PivotChart. The Show/Hide tab lets you turn on or off a number of PivotChart elements. If you don’t want users to modify a PivotChart, for example, turn off the appropriate options in the Let Users View area.
It’s advisable to turn off ScreenTips and drop zones if the PivotChart will be printed or displayed on a projector screen, because these elements aren’t functional in this situation and can be distracting.
If you work with remote data (that is, data outside the current Access database), the Data Details tab lets you set data connection details, if your PivotChart is based on remote data, such as a SQL Server table. (See the online books in Microsoft Developer Network for more infomation about remote data.) The controls on this tab are all disabled for a PivotChart based on a local Access data source.
Unlike the older MS Graph charts, and as you would expect from its name, a PivotChart can be pivoted—that is, you can swap the series and category (vertical and horizontal) data simply by dragging the field buttons to opposite drop zones or by clicking the By Row/By Column button on the PivotChart toolbar. Sometimes after you select a new chart type, it becomes clear that the series and category fields need to be swapped to make the data easier to understand. For example, after I selected a smooth-line chart type for the sample PivotChart in this chapter, I realized that this chart type needed to display months on the horizontal axis and employees on the vertical axis. I clicked the By Row/By Column button to swap the Employee and OrderMonth field buttons to create a more meaningful smooth-line chart. The result is shown in Figure 12-32.
Figure 12-32. After you select a different chart type, you might need to swap the category and series fields to display the data appropriately.