Creating a Basic PivotChart

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:

  1. In the Forms group of the Database window, click the New button, or choose Insert, Form.
  2. In the New Form dialog box, select AutoForm: PivotChart in the list.
  3. Select the prepared query to use as the PivotChart form’s data source from the drop-down list, as shown in Figure 12-24, and click OK.

    figure 12-24.select autoform: pivotchart and a query to create a new pivotchart form.

    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.

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.

Figure 12-26. You can create a query with several calculated fields for use as the data source for a PivotChart.

Choosing Fields for the 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:

  1. If the PivotChart field list isn’t visible, click the Field List button on the toolbar, or press the F8 hot key.
  2. Drag the category field you want to use (in this case, the Employee field) to the Category Fields drop zone.
  3. (Optional) Drag the field to use for filtering to the Filter Fields drop zone.
  4. (Optional) Click the arrow to the right of the OrderYear field, and select a year in the drop-down list to filter data for that year only—in this case, 1995, as shown in Figure 12-27.

    figure 12-27.select a year in the orderyear drop-down list to filter data for that year.

    Figure 12-27. Select a year in the OrderYear drop-down list to filter data for that year.

  5. Drag an appropriate field, such as the OrderMonth field, to the Series Fields drop zone.
  6. Drag the data field (the Price field, in this example) to the Data Fields drop zone.
  7. If desired, click the Employee drop-down arrow to filter for selected employees, as shown in Figure 12-28.

    figure 12-28.select individual employees in the drop-down list to filter for selected employees.

    Figure 12-28. Select individual employees in the drop-down list to filter for selected employees.

  8. Click the Show Legend button on the toolbar to give the chart a legend explaining the bar colors.
  9. To enter a vertical axis title, select the appropriate Axis Title placeholder in the PivotChart, and click the Properties button on the toolbar to open the PivotChart properties sheet. Select the Format tab, enter an appropriate caption, such as Sales by Month, in the Caption box, and modify the font, size, and color as desired.
  10. Repeat the process in step 9 for the horizontal axis title. (You’ll find this completed PivotChart form, titled Monthly Sales By Employee, saved as fpvcMonthlySalesByEmployee in the Test Access 2002 sample database on the companion CD.)

tip - Swap the series and category fields


You can easily swap the series and category fields by clicking the By Row/By Column button on the PivotChart toolbar. (You’ll need to change the series title in the PivotChart properties sheet, to reflect the data in the series area after the swap.)

Fine-Tuning PivotCharts with Advanced Tools

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’t open the PivotChart properties sheet by pressing F4, which is curious, since that technique works for most properties sheets in Access.

Changing the Appearance of the PivotChart and Its Elements

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.

Figure 12-29. The Daybreak gradient fill pattern enhances the appearance of a PivotChart.

tip - Change the border’s color and line format


When you specify a new border style on the Format tab of a PivotChart properties sheet, while the border is selected, it’s highlighted with a solid dark blue line. To see the new border with the color and line format you’ve specified, set the focus on another chart element or close the properties sheet.

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


If the properties sheet lacks a 3D View tab, change to a 3-D chart type by clicking the Chart Type button on the PivotChart toolbar.

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 PivotChart toolbar does include an Undo button, so if you make a change and don’t like the results, you can click this button to return to the previous state of the chart. It doesn’t undo all actions, but it’s very helpful all the same.

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.

Figure 12-30. You can increase the explosion factor to move the pie slices farther apart.

tip


To select an entire chart rather than a chart element, click in the chart background.

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.

Changing the Chart Type

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.

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.

Changing How Users Can Interact with a PivotChart

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.

Working with Remote Data

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.

Pivoting a PivotChart

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.

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.



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net