|  When you define a PivotTable view, you also automatically generate a corresponding PivotChart view of tables, queries, and forms. Access links PivotTable and PivotChart views, so there's no need for a Chart Wizard to specify the initial design. For example, open the PivotTable view of the qry1997OrdersByCountryPT sample query you created in the "Creating the Query for a Sample PivotTable View" section near the beginning of the chapter and collapse the Name category, if Country fields are visible. Choose PivotChart view to open the chart, as shown in Figure 12.26.
Figure 12.26. The PivotTable automatically creates a PivotChart view of the qry1997OrdersByCountryPT sample query. Be sure to minimize the amount of category detail in the PivotTable before opening a PivotChart.  Adding Legends, Axis Titles, and Filters Following are some of the PivotChart property values you can alter to change the format of PivotCharts and filter the data presented:  Legends The default PivotChart style is Clustered Column; each column of each category quarterly sales columns for the Name category for this example is color coded. Clicking the Show Legend button on the toolbar toggles the legend below the 1997 Quarters field button.
Axis titles Titles for the x- and y-axes of the sample chart are missing. To add axis titles, right click the axis title, choose Properties to open the Properties dialog, and click the Format page. You can change the font, size, and attributes, and type the title in the Caption text box. Category filters Filters on category fields limit the chart's display to selected values. As you change filter values, the chart automatically reformats the display and changes the scale of the y-axis to optimize the display. Adding filters to category fields also affects the PivotTable view.
Figure 12.27 shows the legend and axis titles added, and filters applied to the Name and Country categories. Axis totals and a legend have been added. X-axis category values rotate 90 degrees counterclockwise if their width fits the divisions. Figure 12.27. The sample PivotChart displays totals of North American orders for the first five employees.  Tip Remove filters applied to category fields as soon as you no longer need them. The only visible feedback that a filter is applied is a change to the color of the small arrows from black to blue, which isn't readily apparent. Accidentally leaving a filter in place when changing the chart's layout can lead to interpretation errors.
Altering Category Presentation PivotCharts have Filter Fields and Category Fields drop zones similar to those of PivotTables, and field buttons corresponding to those of the source PivotTable. An additional field button, All Orders for this example, represents the PivotTable's values displayed by the chart, called a series. You can change category presentation by dragging category fields to the Filter Fields drop zone and changing the chart's display as follows: Coalesce clusters to totals To display total sales for each employee for the year 1997, drag the 1997 Quarter button to the Filter Fields drop zone (see Figure 12.28). The y-axis scale changes to reflect the larger totals. Figure 12.28. Dragging a category field button to the Filter Fields drop zone changes the clustered columns to a single total column for each category.  Note Filter Field buttons indicate that a filter is applied by changing the All label to the filter selection. If you select more than one filter criterion, the label displays "(multiple items)."
Replace Categories Drag the Name button to the Filter Fields drop zone to display total 1997 sales for each country (see Figure 12.29). Passing the mouse pointer over the chart's bars opens a ScreenTip, which displays detail data. Figure 12.29. Dragging the leftmost (primary) category field button to the Filter Fields drop zone displays data for the remaining category button.  Tip  | If you accidentally drag a field button outside the PivotChart's window and remove it from the PivotChart (and the PivotTable), open the Field List and drag the field to the appropriate drop zone. |
 Remove excessive detail When you return the Name and 1997 Quarter fields to their original locations the Category and Series Fields drop zones, respectively the category axis of the chart becomes an unreadable jumble. To remove the Country bars from the chart, select the Name button and click the Collapse button on the toolbar or right-click the chart, and choose Collapse from the context menu.
  Drill down into a category The Drill Into toolbar button or context menu choice lets you display the second (or lower) level of detail for a category. To display sales by country for a single salesperson, right-click the name in the category axis and choose Drill Into to display sales by country and quarter for the person (see Figure 12.30). To return to the original chart format, right-click the name, and choose Drill Out. Drill Into and Drill Out menu choices and toolbar buttons are enabled only when you select a category item.
Figure 12.30. Selecting a primary category item in the x-axis, such as Peacock, and clicking the toolbar's Drill Into button displays secondary category values in this case, sales for Margaret Peacock by country and quarter. 
Changing the Chart Type  PivotCharts come in a remarkable variety of types and styles, ranging from the default Clustered Column to Radar, which displays values relative to a centerpoint (as in the radar display of an airport approach control facility). To change the chart's style, click outside the chart area, and click the Chart Type button on the toolbar to open the Properties dialog to the Type page. Click one of the styles to preview the chart's appearance. Figure 12.31 shows the sample chart type changed from Clustered to Stacked Column. Choosing a 100% Stacked Column style changes the y-axis units to percent.
Figure 12.31. Column charts are one of 12 types you can select from the Types page of the Properties dialog. Each type has between 2 and 10 styles, and most types offer 3D versions.  Tip Don't change to a 100% Stacked Column or any other 100%... style unless you specifically need this style. When you return to another style, the format of the y-axis values change to General Number. You then must select the values, click the Format tab, and reset the numeric format.
The most useful formats for conventional PivotCharts are as follows: Stacked Column charts Display contribution of series elements, such as quarters, to a total value. ScreenTips display the numeric value of each element of the column but, unfortunately, not the total value. Bar charts Rotate the axes 90 degrees counterclockwise, offer the same styles as Column charts. Stacked Area charts Better suited to time-series data, such as monthly, quarterly, or yearly categories. Figure 12.32 shows a Stacked Area chart from a slightly modified (monthly) version of the sample query. Unlike Stacked Bar or Column charts, ScreenTips of stacked area charts don't display numeric values. Figure 12.32. This Stacked Area chart displays the month-to-month trend of each employee's contribution to total sales order value. 
 | The qry1997OrdersByCountryPT sample query and the completed PivotChart and PivotTable are included in the Pivot.mdb sample database located in the \Seua11\Chaptr12 folder of the accompanying CD-ROM. |
|
