| Microsoft defines a pivot chart as a "practical interface that allows users to view comparisons, patterns, and trends in data in a visually appealing manner." That might or might not be more useful than saying that a pivot chart is a lot like a pivot table, except that it's a chart. Pivot charts and tables are closely entwined in some ways, yet separate in others. If you create a table in PivotTable view, you will see a graphical representation of it in PivotChart view. In fact, the data source for the chart is the pivot table. Any changes you make in PivotTable view will be reflected in PivotChart view, and vice versa. On the other hand, for the most part, there's no need to bother with an underlying pivot table if your only objective is to create a pivot chart. You can create the chart directly in PivotChart view. Moreover, when you create a pivot chart, you can choose among numerous chart types, each of which presents data graphically in its own way. So when your objective is a pivot chart, I advise doing all your work in PivotChart view and ignoring PivotTable view entirely. NOTE This general rule has some exceptions. If you want to display aggregate functions besides Sum and Count, you'll need to create them first in PivotTable view. They will then be available in the pivot chart's field list, and you can add them to the chart.
Figure 14.15 shows a pivot chart that contains no fields. As with pivot tables, you drag and drop fields from the field list onto the chart. Where exactly you drop the fields is of utmost importance. Figure 14.15. A blank pivot chart. The scale at the left is purely a template view and will change to reflect the values in your chart. 
Pivot Chart Elements Describing the various data components of a pivot chart might not be particularly useful. A little experimenting will tell you how each element affects the chart and how the various components interact with each other. Still, in the interest of completeness, here is a description of each element: Category fields represent one point of each data series. In most charts, the categories are represented along the x-axis (horizontal axis) of the chart. Category fields correspond to the Row Fields area of the pivot table. The series fields are represented in the chart by data markersa bar in a bar chart, a slice in a pie chart, and so on. Each data item in the series is defined in the chart legend. The data points are usually plotted on the y-axis (vertical axis) of the chart. The data fields contain the values that will be summarized in the chart. You will almost always use other drop areas as well, but the data area is the only area that must have at least one field. The Data Fields area corresponds to the Totals or Details area of a pivot table. The filter fields can be used to filter data. As with a pivot table, however, the filtering method is not robust. To filter the records, it's best to filter first in Datasheet or Form view.
Create a Pivot Chart You can use PivotChart view in any table or form. But as with pivot tables, often you'll want to create a query first so you can use fields from different tables. First you'll create a new query; then you'll design the pivot chart. Suppose you want to get a picture of your inventory situation. Specifically, you want to know how many units you have on hand of each item. Additionally, you want to see the supplier for that item. You also want to be able to tell quickly what kind of product it isnovelty, jewelry, and so on. 1. | In the Database window, click Queries and click New. In the New Query dialog box, click Design View and click OK.
| 2. | In the Show Table dialog box, click tblCategories, press Ctrl, and click tblMerchandise and tblSuppliers. Click Add to add the three field lists to the design grid. Close the Show Table dialog box.
| 3. | In the Categories field list, double-click CategoryName. In the Merchandise field list, double-click MerchName, UnitsInStock, and UnitsOnOrder. In the Suppliers field list, double-click SuppCompanyName.
| 4. | Save the query as qryMerchandiseUnits.
| 5. | Click View to see and review your records in Datasheet view.
| 6. | Click the View drop-down arrow and choose PivotChart view.
| 7. | Click Category Name in the field list and drop it onto Drop Series Fields Here (if you can't find Drop Series Field Here, refer to Figure 14.15 for its location).
Because you have only five types of product, this will work nicely as a series. You'll be able to use the legend to quickly distinguish one category from the next.
| 8. | Click Company Name and drop it onto Drop Category Fields Here.
Let's think about the relationship between suppliers and products for a moment. Each supplier sells several products, and each product has only one supplier. If you want to see which products are supplied by which supplier, it's best to put suppliers first (on the outside) and products second (on the inside).
| 9. | Click Product Name and, in the Drop Category Fields Here area, drop it to the right of Company Name.
| 10. | Click Units in Stock and drop it onto Drop Data Fields Here.
Units in stock is the data you want to measure. The scale at the left tells you how many units are in stock for each product.
Take a look at the chart. There are some problems. First, where's the legend that tells the product type (jewelry, novelty, and so on)? You can easily display it.
| 11. | On the PivotChart toolbar, click Show Legend (see Figure 14.16).
Figure 14.16. A pivot chart built from a query. It has the data you need, but its presentation could be improved. 
The next big problem is that listing suppliers and products along the x-axis makes them difficult to read.
Perhaps if you used another chart type, the chart could be read more easily. Instead of a column chart, use a bar chart that will measure the units along the x-axis instead of the y-axis.
| 12. | Click any empty space in the chart, away from any element. Choose PivotChart, Chart Type. Click the Type tab. Click Bar. In the first row, select the first bar (see Figure 14.17). The axes are switched and the chart is easier to read. Close the Properties dialog box. Figure 14.18 shows your work (I've closed the field list chart for a better view).
Figure 14.17. In the Properties dialog box, you can choose another chart type. Click the type of chart in the left column, and choose a chart on the right. 
Figure 14.18. The bar chart shows the number of units in stock for each product by supplier, as well as the product category. It still needs some work, however: You can add axis titles, eliminate the decimal places on the scale, and more by using the Properties dialog boxes described in the next section. 
|
Suppose you also wanted to show the Units On Order for each product. Let's add it to the chart: 1. | Click Units On Order in the field list. Drag and drop it to the right of Sum of Units in Stock.
| 2. | This chart gives you the data. But on smaller screens, it will be difficult to read because the parallel Units In Stock and Units On Order bars are difficult to distinguish. Let's choose another chart type.
| 3. | Right-click any empty area on the chart and choose Chart Type. On the Type tab, click Bar. Click the second sample in the first row (see Figure 14.19). Close the Properties dialog box.
Figure 14.19. This bar chart includes both data fields on the same bar. 
| 4. | You can now see both units in stock and units on order. The total length of each bar shows the sum of units in stock and units on order, an additional and useful piece of information. The legend is now a bit complicated, with 10 categories, but it can still be deciphered.
| 5. | Save your changes and close the query.
|
Multiple Plot Charts Sometimes you might prefer to break the data into two or more charts for comparison purposes. The PivotChartFreight query shows the total freight of orders, broken down by shipper and state. One of the fields is Overnight, a Yes/No field that tells you whether the order was shipped by regular freight or overnight delivery. You can have separate charts for overnight and regular delivery of orders. 1. | Open the PivotChartFreight query. Open the View drop-down list and choose PivotChart view.
| 2. | Choose PivotCharts, Multiple Plots.
In the middle of the chart, Multi-Chart appears.
| 3. | Click Overnight? (either in the field list or on the chart) and drag and drop it onto Drop MultiChart Fields Here.
The chart on the left is for regular delivery (NO, not overnight); the chart on the right is for overnight delivery (YES, overnight).
| 4. | Right-click directly on No and choose Properties. Click the Format tab.
| 5. | Edit the Caption to Regular Delivery. Close the property dialog box.
| 6. | Right-click directly on Yes and choose Properties. Click the Format tab.
| 7. | Edit the Caption to Overnight Delivery. Close the property dialog box (see Figure 14.20).
Figure 14.20. You might want multiple charts to segregate presentations of different data elements. 
| 8. | Close the query and save your changes.
|
Chart Properties As with pivot tables, you can set a host of properties for each of the many elements in the chart. Editing chart properties can get confusing because there are so many choices and so many parts. Choosing the correct element is crucialthe properties you edit depend completely on the element chosen. But finding the right place to click is a task unto itself. Look at the element name in the Select box in the General tab of the Properties dialog box to make certain you've chosen the one you need. One possible (but by no means foolproof) solution is to select the element you want from the General tab in the Properties dialog box. Even a simple chart has a host of elements, however, and figuring out which element is which from their names can be difficult (see Figure 14.21). Figure 14.21. You can select chart elements directly on the chart or on the General tab of the Properties dialog box. With so many chart elements, neither method is certain, so carefully check your changes after editing properties. 
Table 14.2 shows you how to edit several key items in the various properties dialog boxes. Table 14.2. Editing Chart PropertiesIf You Want To… | Select Properties for the… | Click This Tab… | And Do This… |
|---|
Show the field list by default | Chart Workspace | Show/Hide | Select the Field List | Show the scale in a different format (e.g., percent or currency) | Value Axis | Format | Open the Number drop-down list and select a format | Change the units on the scale | Value Axis | Scale | Edit Custom Major Unit | Include vertical/horizontal gridlines | Value or Category Axis | Axis | Select Major Gridlines and Minor Gridlines | Dress up the chart with a pattern | Plot Area | Border/Fill | Select Picture/Texture in Fill Type, open the Preset drop-down list, and choose a pattern | Flip the chart | Plot Area | General | Click one of the flip icons | Change the color of one bar or column | Single bar, column, etc. | Border/Fill | Select a color from the palette | Give the chart a title | Chart Workspace | General | Click the Add Title icon | Group items | A category field | Filter and Group | Edit Group Items By and Interval settings | Display top or bottom values | A category or series field | Filter and Group | Edit Filtering settings |
|
