About PivotTable lists

A PivotTable list is an interactive table that you can use to analyze data dynamically from within your Web browser. You can use a PivotTable list to view and organize data from lists or databases, look for information or details, and create personalized summaries and reports. You can also add, edit, or delete the detail data that is displayed in a PivotTable list.

The following illustration shows a simple PivotTable list that displays summarized data.

PivotTable list example

When you create a PivotTable list, you select data from an external database or other source that you want to make available to viewers on your Web page. This data becomes the source data in the PivotTable list. You can then set up an initial presentation or view of the data, such as the summary shown in the preceding example, that analyzes the data in one of the many ways possible. You do this work in a design program, such as Microsoft Excel or Microsoft Access.

After you make the PivotTable list available on a Web page by publishing it, users who have the Microsoft Office Web Components — and a browser that supports PivotTable lists, such as Microsoft Internet Explorer 4.01 or later — can also analyze the data. In the browser or other run-time environment, users can display different fields and change your initial view to suit their needs. Depending on your design program and methods, you might also be able to make the PivotTable list available in programs other than browsers.

When to use a PivotTable list

The Microsoft Office Web Components provide a set of tools that you can use to include interactive PivotTable lists, spreadsheets, and charts on your Web pages. Each component has features and capabilities that suit it to particular uses.

A PivotTable list is the right choice when you want to provide information from a database — such as Microsoft Access or SQL Server records or OLAP data from a Microsoft SQL Server OLAP Services database — in a form that users can interact with and analyze from within the Web browser or in a run-time environment. You can also choose to allow the user to add, edit, or delete detail data.

Here are some examples of good applications for PivotTable lists:

  • Making a database of sales figures available to users who want to compare sales for different products and regions.
  • Publishing stock price figures from a database that's updated frequently so that users can view and compare the performance of stocks that interest them.
  • Providing a product catalog that customers can filter to find the products they want, allowing them to view the price, availability, and shipping cost information.

If users will view data without interacting with it, a noninteractive table might be a simpler way to display static data. For information about other ways to make data available on a Web page, see Help for your Web page design program.

Creating a summary report

The following series of examples show how you, working in a design program, or a user, working in a browser, could turn a set of records from a database into a summary report. In the simplest form that you can create, a PivotTable list displays the records from the source data in a grid with the field names across the top. The following example contains fields named Sport, Quarter, and Sales.

PivotTable list example with all detail fields

1 Drop areas

2 Detail fields

You can create a summary by moving fields to other areas and sorting fields to produce a logical organization. For example, the PivotTable list that follows takes the preceding example and shows how you could create a summary by moving the Sport field to the row area to organize the data by sport, and then sorting the Quarter field to put the sales in chronological order.

PivotTable list example with row field

1 Row field

2 Items

To compare the sales for each sport in each quarter, you can move the Quarter field to the column area, as shown in the following illustration.

PivotTable list example with row and column fields

1 Column field

2 Items

To view and compare total sales instead of the detailed sales data, you can add a Sum of Sales total field and then hide the detail data for the Sport and Quarter fields. A total field provides the values that are summarized in the PivotTable list. In the following example, the lower-right cell shows a total of the Tennis sales in Qtr4 — that is, the sum of the sales figures from every row in the source data that contains Tennis for the sport and Qtr4 for the quarter.

PivotTable list example with summary data

1 Total field

To create the summary report that is shown at the beginning of this topic, you could then display grand totals for the Sport and Quarter fields.

To further analyze the data, you could filter the fields to find specific data. For example, you could display only the data about tennis sales in the third quarter. You could also add other calculations to the list. For example, you could display the largest sales amount for each sport.

Designing and publishing a PivotTable list

If you need general information about Web page design, consult Help for the program you use to create your Web pages or Web site. Two programs that you can use to create Web pages with PivotTable lists are Microsoft Access (available in the Microsoft Office Professional and Premium editions) and Microsoft FrontPage (available in the Premium edition).

Before you begin

Before you begin designing a PivotTable list, you should consider whether your users will interact with the data or only view the data.

  • If you plan to publish a PivotTable list on a Web page, keep in mind that interactivity is determined by which browser the user has. If you think your users will want to interact with the PivotTable list, make sure they have one of the browsers that fully supports PivotTable lists.
  • If you're designing the PivotTable list for use in a program other than a Web browser, make sure you understand what PivotTable list capabilities are available in that program.

PivotTable lists are designed to present lists and information from databases for interactive use. Depending on the results you envision, you might want to use a different Microsoft Office Web Component or include other controls along with the PivotTable list.

The design program is the program you use to create the PivotTable list. Different design programs have different capabilities.

Step 1: Prepare the source data

The source data is the information that you want to display and analyze in the PivotTable list. Depending on the design program you're using and the type of source data you want to display, you might need to set up a data source or create a query before you can include the data in the PivotTable list. A data source is a means of recording the information necessary to connect the PivotTable list to the data in a database, and a query is a means of specifying which information to retrieve from the database. For information and instructions about accessing source data from your design program for use in a PivotTable list, see Help for that design program.

When you publish a PivotTable report from Microsoft Excel to a Web page, Excel does part of the design work for you. However, some elements of PivotTable reports are changed when you save a report as a PivotTable list for the Web. You might need to modify some elements, such as custom calculations, to display the results you want.

Step 2: Create or open a PivotTable list

The specific steps to create a PivotTable list vary for different design programs. For specific information, see Help for your design program.

If you have an existing PivotTable list that you want to modify, such as one saved from a Microsoft Excel PivotTable report or copied from another Web page, you can open the file containing the PivotTable list in a design program and then activate the control by following the instructions in that program (usually by clicking or double-clicking the PivotTable list). For details, see Help for your design program.

When you create a new PivotTable list, you'll usually designate an initial area of your Web page for the list to occupy, relative to other elements on the page. After you attach the source data (see step 3 of this process), you can allocate the amount of space it can use and then move the PivotTable list to the best position (see step 4 of this process).

After you create or select a PivotTable list in a design program, you work in the PivotTable list and continue to design it by using the toolbar and the Commands and Options dialog box in the PivotTable list.

Step 3: Attach the source data to the PivotTable list

If you're working with an existing PivotTable list, the source data might already have been associated with the control and might also have been stored with the control itself. For a new PivotTable list, you need to establish a connection with the source data you've prepared in your design program.

Step 4: Allocate space for the PivotTable list

Within the PivotTable list itself, you can set a fixed height and width or allow the PivotTable list to expand up to a maximum size that you set, displaying scroll bars as necessary. These settings determine both the size of the PivotTable list in the browser and the size of the area you have to work with while designing the presentation of the data in the design program. Later, if you want a different amount of space for the PivotTable list, or if you want different settings for the browser than you are using to design, you can change these settings.

Step 5: Design the data layout

After you create a PivotTable list and add source data to it, the next step is to determine how you want the data to be presented when users first open your Web page.

  • Fit the presentation to your users' needs. Consider how users are likely to interact with the data. For example, if all of your users are familiar with PivotTable lists and will want to create their own data layouts and summaries, you might want to merely display some fields from the source data. However, if your users need a particular type of summary or view of the data, rely on you to do the layout work, or have a mix of different skill levels, you might want to lay out, organize, and format the data yourself.

    Keep in mind that in some browsers, such as Microsoft Internet Explorer version 3.x, users cannot view or interact with the data. To interact with the data, users must have a browser that supports PivotTable lists.

  • Set up the initial layout. To display data in the PivotTable list, add fields from the source data to the drop areas for the different types of fields. Also consider how you want to summarize the data by creating and displaying totals.

  • Organize the data. After you add the fields and totals, you have several options to arrange how the results are displayed. You can sort the data to change the order of items of data in a field. To display a subset of the data, you can filter the fields to display only items of data that match the values you want to display, or you can selectively display or hide detail for a field or item of data.

  • Format the data. Formatting options include setting fonts and background colors, changing the display formats for numbers, aligning text within cells, changing column widths, displaying or hiding the drop areas for fields and formatting their captions, and displaying or hiding the expand indicators ( Plus box or Minus box) next to items. Also, you can add a title to the PivotTable list and format it.

Step 6: Restrict access in the browser

Even in a browser that fully supports PivotTable lists, such as Microsoft Internet Explorer 5, users do not have access to some of the commands and features that you can use in a design program. To prevent users from making changes or accessing parts of the source data that you don't want to make available, you can set more restrictions.

Step 7: Add other controls, automation, or help

You can do one or more of the following:

  • Include a chart. You can use another Microsoft Office Web Component, the Chart Component, to display a separate chart of the data in your PivotTable list. In the browser, when a user changes the data displayed in the PivotTable list, the chart is automatically updated. For example, if your PivotTable list provides data about stock prices over time, you can include a chart on the Web page to display the price trends graphically. When the user selects different stocks to view in the PivotTable list, the chart is automatically updated to reflect the selected stocks.

  • Write scripts. You can access and control many of the features and capabilities of PivotTable lists by using Web scripts or other programs. If you are familiar with writing scripts for Web pages in a language such as Visual Basic Scripting Edition (VBScript) or JavaScript, you might want to add scripts to the Web page to automate some tasks in the browser. For example, you might provide a script to change the PivotTable list to several of the most useful layouts and then add buttons to the Web page to select each layout. You can also enable users to run scripts from the toolbar and shortcut menus.

    Your design program might provide tools for writing scripts. For example, the Microsoft Office programs provide the Microsoft Script Editor. For information about writing scripts, see Help for your design program. For information about accessing features of PivotTable lists by using scripts, see the programming Help for PivotTable lists.

  • Provide help. In the browser, users have access to separate Help available from within a PivotTable list that explains how to use its features, unless you restrict access to this Help. You can supplement this run-time Help by providing specific instructions or information on your Web page about using your PivotTable list. For example, you could tell users which database is supplying the source data and whom to contact for the password if you require one for access.

Step 8: Test and publish

Before making the PivotTable list available to your users, it is recommended that you test it in a Web browser. Microsoft Internet Explorer 4.01 or later is recommended for users who want to interact with PivotTable lists. If your users have different browser versions, you might want to test the PivotTable list in each version to make sure all your users will be able to accomplish the tasks you intend at run time. If you provide your own help for the Web page, make sure your help looks and works the way you intended.

If you aren't satisfied with the PivotTable list and Web page, you can go back into the design program and make changes. In addition to changing the settings you made while you designed the data layout and customized the PivotTable list, you can change the layout or remove the fields that you don't want.

When you are satisfied with the results, make your Web page available to your users. For information about publishing Web pages, see Help for your design program or consult with your Webmaster.


Browsers that support PivotTable lists

To work with interactive PivotTable lists in Microsoft Internet Explorer 4.01 and later, users must have the Microsoft Office Web Components installed and an appropriate Microsoft Office XP license. Users who do not have an Office XP license can install the components and view them on a Web page, but cannot interact with them. Learn more about installation and licensing of components.

Following are the levels of browser support available when users have the Office Web Components installed and an Office XP license.

Microsoft Internet Explorer 5 and later

In this browser, users have access to all PivotTable list features that are accessible from a browser. Changes that users make, such as formatting and layout changes, are retained when users leave a PivotTable list and browse back to it later in the same browser session.

Microsoft Internet Explorer versions 4.01, 4.1x, and 4.2x

Users have access to all PivotTable list features accessible from a browser. Changes are retained when users leave a PivotTable list and browse back to it later in the same browser session. Users must have Internet Explorer 5 installed to view PivotTable lists on data access pages created in Microsoft Access.

Other browsers

In earlier versions of Microsoft Internet Explorer and in other browsers, users cannot view PivotTable lists. A PivotTable list either appears as an outlined box on the Web page or does not appear at all.


About Microsoft Office Web Components

Microsoft Office Web Components is a set of ActiveX controls installed with Microsoft Office XP. With Office Web Components installed on your computer, you can interactively use a spreadsheet, a PivotTable list, and a chart on a Web page in Microsoft Internet Explorer 4.01 or later. If you want to use the components on a data access page in Microsoft Access, you should have Internet Explorer 5 or later.

If Office Web Components is not installed on your computer but you have an Office XP site license that permits intranet distribution, you can configure the components so that users will be prompted to download Office Web Components from your corporate intranet. This allows users at your site to use a data access page without having Office XP software installed on their computers.

For information about how to configure Office Web Components, see the Microsoft Office XP Resource Kit.


About installing, licensing, and distributing Office Web Components

When you design a Web page using Microsoft Office Web Components, any user with a Microsoft Office XP license can interact with the components in the browser to the level of interactivity you provide. That is, a user with an appropriate license can make changes to data in a spreadsheet, change formatting, drag fields in a chart or PivotTable List, and so on, as long as you didn't protect these options at design time. Users with an Office XP license can also create, design, and modify components in a design environment such as Microsoft FrontPage or Microsoft Access.

Users who do not have Office XP licenses can view the components and the data in them, and can print the view of the components, but they cannot interact with the components or manipulate them in a design environment. This means that if you distribute a Web page that uses components, users who have Office XP licenses will have access to all functionality provided, but users without a license can only view the data and information you've provided.

Installing the Office Web Components

Microsoft Office Web Components are installed with Microsoft Office XP and Microsoft Office applications, or they can be installed separately from an installation point provided by the Web page designer.

When you distribute a Web page that uses components, users who do not have the components installed will be prompted to install the components, provided the Web page designer configures the components to do so. The Web page designer must also provide an installation point for the components and a pointer to that location on the component installation page. For more information, see the Microsoft Office XP Resource Kit.

Once the Office Web Components are installed, users who have access to an Office XP license will be able to interact with and make changes to the components. Users without licenses will be able to view and print the components and the data in them, but they cannot interact with or make changes to them.

Details on licensing and functionality

Office XP installed on computer Users have full functionality and interactivity with components, including run-time and design-time capabilities.

Office XP application installed on computer Users can interact with the component in design mode in that application only (not in the browser or in other applications).

Office XP site license (user doesn't have Office XP installed on computer, but user's organization has an enterprise or site license agreement) Users have full functionality and interactivity with components, including run-time and design-time capabilities. However, the Web page designer must provide a location from which components can be downloaded, and must reference the site license in a license package file (.lpk) that is associated with one or more Web pages. You use the License Package Authoring Tool to create an appropriate license file for pages. For more information, see MSDN Online.

No Office XP, Office XP applications, or site licenses Users can view and print the components in view-only mode, but they cannot interact with the components or use the design capabilities.


About View-only mode for Office Web Components

If you do not have a Microsoft Office XP software license installed on your computer or accessed via a Microsoft Office XP application, you can view a Microsoft Office Web Component — a Spreadsheet Component, Chart Component, or PivotTable Component — on a Web page, but you cannot interact with it.

To be able to interact with and use full functionality of the component you are viewing, you must install Microsoft Office XP or have access to an Office XP license. If you are in a corporate or group environment, check with your network administrator for assistance.

When you are in View-only mode, you can:

  • View data.
  • Print.
  • Select and scroll.
  • Use hyperlinks.
  • Resize a component.
  • Select sheets in the Spreadsheet Component.
  • Expand or collapse members in a PivotTable list.
  • Refresh data in a PivotTable list.
  • Use the About, Help, and Refresh commands. Help topics that deal with interactivity (such as formatting cells, dragging fields, sorting, filtering, and so on) do not apply.

All commands except About, Help, and Refresh are unavailable in View-only mode.


About getting help

Some of the content in this topic may not be applicable to some languages.

Getting help in other design programs

When you are working in a design program, you can get help in the following ways:

  • Design program Help For information about the commands and features available in the design program that you can use to work with PivotTable lists, see Help in the design program. For example, in Microsoft FrontPage, click the Office Assistant, or click Microsoft FrontPage Help [image].

    Microsoft Excel provides both general Help about saving Excel data for use on Web pages, and specific information about saving PivotTable lists for use on Web pages. To access this Help in Excel, click the Office Assistant, or click Microsoft Excel Help [image].

  • PivotTable list design-time Help If you have access to the Commands and Options dialog box in another design program, you have access to the Help topics you are viewing now. In a PivotTable list that you have created or activated in a design program, click Help [image] in the Commands and Options dialog box or click Help on the PivotTable list toolbar.

Getting help with writing programs and scripts for PivotTable lists

You can use scripts and programs to automate PivotTable lists, or you can include PivotTable lists in run-time programs other than Web browsers, such as programs developed using Microsoft Visual Basic.

Writing scripts

Microsoft Office programs provide the Microsoft Script Editor to help you write scripts. For example, when working with data access pages in Microsoft Access, you can use the Script Editor in Design view to customize a PivotTable list. For information about running the Script Editor, see Help in your Microsoft Office design program. In the Script Editor, you can display additional Help about using its features and writing scripts.

Object model Help

For help developing a program or script to work with a PivotTable list, you can use the Microsoft Office Web Components Visual Basic Reference to display information about the object model, properties, and methods specific to PivotTable lists. This object model Help is installed when you install the Microsoft Office Web Components, of which the PivotTable Component is one. Depending on the design program you're using, you can get this Help in either of two ways:

  • Help in Microsoft Visual Basic In Visual Basic or Visual Basic for Applications (VBA), you can create a reference to the Microsoft Office Web Components library. You can then get Help in the object browser or by selecting a keyword and pressing F1. For information about creating references and using object model Help, see Help in Visual Basic or VBA.
  • Help in other environments If you are using another development environment, locate and double-click the Help file Owcvba10.chm.

Getting help when using a PivotTable list in a browser

In the browser, Help is available for users from within a PivotTable list. This run-time Help explains how to use the features and capabilities of PivotTable lists in the browser.

For users who have Microsoft Internet Explorer version 4.01 or later, and the Microsoft Office Web Components, Help specific to PivotTable lists is automatically available. The run-time Help is separate from the Help available from the browser's Help menu.

The run-time PivotTable list Help topics provide general information for all users about using PivotTable lists in the browser. These are not the same topics as the PivotTable list design Help topics you are reading now. Other Microsoft Office Web Components, such as the Spreadsheet Component, also have their own separate Help available in the browser.

To see what Help is provided in the browser, create a PivotTable list on a Web page, open the Web page in Internet Explorer, click the PivotTable list, and then click Help [image] on the toolbar in the PivotTable list. In the Help window, you can view a table of contents and search the topic text. The Office Assistant is not available in the browser to answer questions.

Users who do not have the Office Web Components and Internet Explorer 4.01 or later do not have access to run-time Help.


Keyboard shortcuts

For information about activating and deactivating a PivotTable list by using the keyboard, see Help for your design program.

Keys for selecting the elements of a PivotTable list

The TAB key
Move the selection from left to right, and then down
ENTER
Move the selection from top to bottom, and then to the right
SHIFT+TAB
Select the cell to the left. If the current cell is the leftmost cell, SHIFT+TAB selects the last cell in the previous row.
SHIFT+ENTER
Select the cell above the current cell. If the current cell is the topmost cell, SHIFT+ENTER selects the last cell in the previous column.
CTRL+TAB
Select the detail cells for the next item in the column area
CTRL+ENTER
Select the detail cells for the next item in the row area
SHIFT+CTRL+TAB
Select the detail cells for the previous item in the column area
SHIFT+CTRL+ENTER
Select the detail cells for the previous item in the row area
Arrow keys
Move the selection in the direction of the arrow key. If a row or column field is selected, press DOWN ARROW to move to the first item of data in the field, and then press an arrow key to move to the next or previous item or back to the field. If a detail field or an item of data is selected, press DOWN ARROW or RIGHT ARROW to move to the first cell in the detail area or data area.
SHIFT+arrow key
Extend or reduce the selection in the direction of the arrow key
CTRL+arrow key
Move the selection to the last cell in the direction of the arrow key
SHIFT+ALT+arrow key
Move the selected item in the direction of the arrow key
HOME
Select the leftmost cell of the current row
END
Select the rightmost cell of the current row
CTRL+HOME
Select the leftmost cell of the first row
CTRL+END
Select the last cell of the last row
SHIFT+CTRL+HOME
Extend selection to the leftmost cell of the first row
SHIFT+CTRL+END
Extend selection to the last cell of the last row
CTRL+SPACEBAR
Select the field for the currently selected item of data, total, or detail
SHIFT+SPACEBAR
Select the entire row containing the currently selected cell
CTRL+A
Select the entire PivotTable list
PAGE DOWN
Display the next screen. A screen is all of the data currently displayed in the PivotTable list.
PAGE UP
Display the previous screen
SHIFT+PAGE DOWN
Extend a selection down one screen. A screen is all of the data currently displayed in the PivotTable list.
SHIFT+PAGE UP
Reduce a selection by one screen

Keys for carrying out commands

SHIFT+F10
Display the shortcut menu for the selected element of the PivotTable list. Use the shortcut menus to carry out commands in the PivotTable list.
Underlined letter
Carry out a command on the shortcut menu
ESC
Close the shortcut menu without carrying out a command
CTRL+7
Display or hide the PivotTable list toolbar
ALT+ENTER
Display the Commands and Options dialog box
ALT+F4
Close the Commands and Options dialog box
F9
Refresh the PivotTable list from the source data
ESC
Cancel a refresh operation in progress
CTRL+C
Copy the selected data from the PivotTable list to the Clipboard
CTRL+V
Paste contents of the Clipboard
CTRL+X
Move selected data to the Clipboard
CTRL+E
Export the PivotTable list to Microsoft Excel

Keys for displaying, hiding, filtering, and sorting data

CTRL+8
Show or hide the expand indicators ( Plus box and Minus box boxes) beside items
CTRL+PLUS SIGN
Expand the currently selected item
CTRL+MINUS SIGN
Hide the currently selected item
ALT+DOWN ARROW
Open the list for the currently selected field
The TAB key
Alternately move to the most recently selected item, the OK button, and the Cancel button in the drop-down list for a field
Arrow keys
Move to the next item in the drop-down list for a field
SPACEBAR
Select or clear the check box for the current item in the drop-down list for a field
CTRL+>
Display detail, if available, for the current item or selection
CTRL+<
Hide detail for the current item or selection
ENTER
Close the drop-down list for a field and apply any changes you made
ESC
Close the drop-down list for a field without applying your changes
CTRL+T
Turn AutoFilter on or off
CTRL+SHIFT+A
Sort data in the selected field or total in ascending order (A – Z 0 – 9)
CTRL+SHIFT+Z
Sort data in the selected field or total in descending order (Z – A 9 – 0)
ALT+SHIFT+UP ARROW or ALT+SHIFT+LEFT ARROW
Move the selected member up or left
ALT+SHIFT+DOWN ARROW or ALT+SHIFT+RIGHT ARROW
Move the selected member down or right

Adding fields and totals and changing the layout of a PivotTable list

Keys for working with the field list

CTRL+L
Display the field list, or activate it if it's already displayed
Arrow keys
Move to the next item in the field list
SHIFT+UP ARROW
Move to the previous item and include it in the selection
SHIFT+DOWN ARROW
Move to the next item and include it in the selection
CTRL+UP ARROW
Move to the previous item, but don't include the item in the selection
CTRL+DOWN ARROW
Move to the next item, but don't include the item in the selection
CTRL+SPACEBAR
Remove the item from the selection, if the item that has focus is included in the selection, and vice versa
PLUS SIGN (numeric pad)
Expand the current item in the field list to display its contents. Or expand Totals to display the available total fields.
MINUS SIGN (numeric pad)
Collapse the current item in the field list to hide its contents. Or collapse Totals to hide the available total fields.
The TAB key
Alternately move to the most recently selected item, the Add to button, and the list next to the Add to button in the field list
ALT+DOWN ARROW
Open the drop-down list next to the Add to button in the field list. Use the arrow keys to move to the next item in the list, and then press ENTER to select an item.
ENTER
Add the highlighted field in the field list to the area in the PivotTable list that's displayed in the Add to list
ALT+F4
Close the field list

Keys for adding fields and totals

CTRL+SHIFT+S
Add a new total field for the selected field in the PivotTable list by using the Sum summary function
CTRL+SHIFT+C
Add a new total field for the selected field in the PivotTable list by using the Count summary function
CTRL+SHIFT+M
Add a new total field for the selected field in the PivotTable list by using the Min summary function
CTRL+SHIFT+X
Add a new total field for the selected field in the PivotTable list by using the Max summary function
CTRL+SHIFT+E
Add a new total field for the selected field in the PivotTable list by using the Average summary function
CTRL+SHIFT+D
Add a new total field for the selected field in the PivotTable list by using the Standard Deviation summary function
CTRL+SHIFT+T
Add a new total field for the selected field in the PivotTable list by using the Standard Deviation Population summary function
CTRL+SHIFT+V
Add a new total field for the selected field in the PivotTable list by using the Variance summary function
CTRL+SHIFT+R
Add a new total field for the selected field in the PivotTable list by using the Variance Population summary function
CTRL+SHIFT+B
Turn subtotals and grand totals on or off for the selected field in the PivotTable list
CTRL+F
Add a calculated detail field

Keys for changing the layout

CTRL+1
Move the selected field in the PivotTable list to the row area
CTRL+2
Move the selected field in the PivotTable list to the column area
CTRL+3
Move the selected field in the PivotTable list to the filter area
CTRL+4
Move the selected field in the PivotTable list to the detail area
CTRL+LEFT ARROW
Move the selected row or column field in the PivotTable list to a higher level
CTRL+RIGHT ARROW
Move the selected row or column field in the PivotTable list to a lower level

Keys for formatting a PivotTable list

To use the following shortcuts, first select a detail field or a data cell for a total field.

The first seven keyboard shortcuts change the number format of the selected element.

CTRL+SHIFT+~ (tilde)
Apply the general number format to values in the selected total or detail field
CTRL+SHIFT+$
Apply the currency format, with two decimal places and negative numbers in parentheses, to values in the selected total or detail field
CTRL+SHIFT+%
Apply the percentage format, with no decimal places, to values in the selected total or detail field
CTRL+SHIFT+^
Apply the exponential number format, with two decimal places, to values in the selected total or detail field
CTRL+SHIFT+#
Apply the date format, with the day, month, and year, to values in the selected total or detail field
CTRL+SHIFT+@
Apply the time format, with the hour, minute, and AM or PM, to values in the selected total or detail field
CTRL+SHIFT+!
Apply the numeric format, with two decimal places, thousands separator, and a minus sign for negative values, to values in the selected total or detail field
CTRL+B
Make text bold in the selected element of the PivotTable list
CTRL+U
Make text underlined in the selected element of the PivotTable list
CTRL+I
Make text italic in the selected element of the PivotTable list

Keys for following hyperlinks

To use the following shortcuts, first select the hyperlink cell or item.

ENTER
Follow the hyperlink
SHIFT+ENTER
Open the link in a new window

Keys for adding, editing, or deleting data

F2
Enter edit mode
The TAB key
Accept edit, move focus right, and then move focus down
ENTER
Accept edit, move focus down, and then move focus right
SHIFT+TAB
Accept edit, move focus left, and then move focus up
SHIFT+ENTER
Accept edit, move focus up, and then move focus left
ESC
Discard edit
DELETE
Delete selection

Keys for working with Help

Keys for working in the Help window

F6
Switch between the navigation pane and topic pane
ALT+O
Display the Options menu to access any Help toolbar command
ALT+SPACEBAR
Display the program Control menu
ALT+F4
Close the Help window

Keys for working in the navigation pane

CTRL+TAB
Switch to the other tab
ENTER
Open or close a selected book, or open a selected Help topic
DOWN ARROW
Select the next book or Help topic
UP ARROW
Select the previous book or Help topic
SHIFT+F10
Display shortcut menu

Keys for working in the topic pane

ALT+RIGHT ARROW
Go forward to a Help topic you viewed previously
ALT+LEFT ARROW
Go back to a Help topic you viewed previously
The TAB key
Select the next hidden text or hyperlink, or Show All or Hide All at the top of a topic
SHIFT+TAB
Select the previous hidden text or hyperlink
ENTER
Perform the action for the selected Show All, Hide All, hidden text, or hyperlink
UP ARROW or DOWN ARROW
Scroll toward the beginning or end of a Help topic
PAGE UP or PAGE DOWN
Scroll toward the beginning or end of a Help topic in larger increments
HOME or END
Go to the beginning or end of a Help topic
CTRL+P
Print the current Help topic
CTRL+A
Select the entire Help topic
CTRL+C
Copy selection to the Clipboard
SHIFT+F10
Display shortcut menu


About the toolbar, field list, and Commands and Options dialog box

The PivotTable list toolbar

The toolbar in a PivotTable list appears at the top of the list and provides a set of commands that you can use to work with the data in the PivotTable list while you are designing it.

PivotTable list toolbar

In the browser, users can also use the toolbar to work with the PivotTable list. Rest the pointer on each toolbar button to display the name of its command.

If you don't want users to have access to the toolbar in the browser, you can hide the toolbar. To ensure that users cannot redisplay the toolbar, you must restrict access to the Commands and Options dialog box, hide shortcut menus by writing script, and hide the title bar.

The PivotTable list field list

Use the field list to view the fieldsets and fields that are available from the source data and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. You can scroll and click the expand indicators ( Plus box or Minus box boxes) next to a fieldset to show or hide fields. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed.

Field list of a PivotTable list or view

The Commands and Options dialog box

Use this dialog box to do common tasks, such as filtering data, formatting text, and showing or hiding elements. The tabs displayed in the dialog box change as you select different parts of a PivotTable list. If you select a control on the Web page other than a PivotTable list or other Microsoft Office Web Component, the dialog box is hidden until you activate the component again.

The Commands and Options dialog box includes some commands that aren't present on the toolbar or shortcut menus. Some properties and commands are available in the Commands and Options dialog box both when you are designing the PivotTable list and when it's displayed in the browser. Others are available only when you are working in a design program. If you don't want users to have access to the commands and property settings in the Commands and Options dialog box, you can make the dialog box unavailable in the browser.


Show or hide the toolbar

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Right-click the PivotTable list, and then click Commands and Options.
  3. Click the Behavior tab.
  4. Select or clear the Toolbar check box.

Note Displaying the toolbar in the design program also makes the toolbar available in the browser. To ensure that users cannot redisplay the toolbar in the browser, you must make the Commands and Options dialog box unavailable because the dialog box includes a command to redisplay the toolbar. You must also hide the title bar to prevent access to the shortcut menu command to display the toolbar. Hiding the title bar does not prevent access to some of the commands provided on the toolbar. These commands will still be available from the shortcut menus for fields, items of data, and other selections, unless you hide them by using a script.


Show or hide the field list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click Field List [image] on the toolbar.


Show or hide the Commands and Options dialog box

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • If the toolbar is displayed, click Commands and Options [image] on the toolbar.
    • If the toolbar is hidden, right-click the PivotTable list, and then click Commands and Options.

Note If you leave the Commands and Options dialog box displayed while you select different parts of the PivotTable list or other Microsoft Office Web Components, the available sections and their contents change to reflect your current selection. If you select a part of the Web page other than an Office Web Component, the dialog box will be hidden.


Show or hide the title bar

Note You can't show the title bar if the PivotTable list is not bound to a data source.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options [image], and then click the Behavior tab.
  3. Select or clear the Title bar check box.


Show or hide drop areas

When the drop areas are hidden and the PivotTable list does not have a field in the row, column, data, or filter area, you can't drag fields to these areas.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.

  3. Select or clear the Drop areas check box.

Note You will not see the drop areas if the PivotTable list is not bound to a data source.


Applications that you can use to design or modify a PivotTable list

A PivotTable list is a component that you create and design in one program, the design program, for use in another program, the run-time program. PivotTable lists are intended primarily for use in the Microsoft Internet Explorer Web browser, version 4.01 or later.

Depending on the content of your Web page and the tasks that users will perform with your PivotTable list, some programs might be better suited to your design task than others. You can start creating a Web page and adding components, including PivotTable lists, in one design program, and then open the Web page in another program to do further work.

For example, if you have designed a PivotTable report in Microsoft Excel, you can use Excel to save or publish this report to a PivotTable list on a Web page. If you want to customize the resulting PivotTable list to change the default settings, or if you want to add other controls to the page, you can then open and modify the Web page in Microsoft FrontPage.

See the Help for the design program you want to use to find out whether the program supports designing PivotTable lists, and to learn its procedures for creating and modifying one.

You can use the following design programs to create PivotTable lists:

Microsoft Excel

If you want to try different layouts and adjust the source data you make available for your PivotTable list, Excel is an excellent design environment. You can access external data within Excel and create an Excel PivotTable report. From Excel, you save or publish a PivotTable report to a PivotTable list on a Web page. (A PivotTable report is the Excel version of a PivotTable list.) The source data you specified in Excel is automatically attached to the PivotTable list, and most Excel PivotTable report features are included when you publish.

You might want to format the PivotTable list and make adjustments to its size and appearance in another design program. If any of the users of your Web page have Internet Explorer version 4.x, be sure to use FrontPage to modify PivotTable lists published from Excel. Excel publishes PivotTable lists to Web pages with support for Internet Explorer 4.x and later, and FrontPage maintains this support. If you modify a Web page that was published from Excel in Microsoft Access by using data access page Design view, however, your users might need to install Internet Explorer 5 in addition to Internet Explorer 4.x before they can use your Web page.

Microsoft FrontPage

FrontPage is the recommended design program for creating PivotTable lists for users who have Internet Explorer version 4.x, and for modifying PivotTable lists saved or published from Excel. In FrontPage, you can create PivotTable lists on Web pages, modify PivotTable lists that were designed in other programs, and then use the features of FrontPage to construct and manage your Web site.

Microsoft Access

Access provides specialized features for creating Web pages that operate on and display data from Microsoft Access and Microsoft SQL Server databases. For example, you can use the field list to locate data and drag it onto a PivotTable list. You can establish links between PivotTable lists and other controls that display and manipulate data from databases on a Web page. If a page includes data other than that displayed in a PivotTable list or other Microsoft Office Web Component, users of your Web page must have Internet Explorer 5 or later to view the page.

Microsoft Visual Basic

If you need a more comprehensive programming environment than the scripting capabilities of Excel, FrontPage, and Access provide, you can design PivotTable lists in the Microsoft Visual Basic. Use Visual Basic when you want to include a PivotTable list in a custom solution. Users can view and interact with PivotTable lists on the Visual Basic forms in your programs. Instead of a browser, your form becomes the run-time environment, and your program can automate many PivotTable list features.

Note Microsoft Word is not a suitable design program for PivotTable lists.


About PivotTable list source data

A PivotTable list is a means of bringing data from external sources into a Web page for display and analysis. When you design a PivotTable list, you select the source data that the control will use and make available in the browser, and you establish a connection to that source data. In the browser, users can then refresh the PivotTable list to view updated source data.

Types of source data

PivotTable lists can display data from several different sources. The most common sources of data are relational databases, Microsoft Excel worksheets, and On-Line Analytical Processing (OLAP) databases.

Relational databases

A PivotTable list can display and summarize data from a set of records in a relational database, such as Microsoft Access or Microsoft SQL Server. When your source data is from a relational database, you can create new total fields in the PivotTable list, display detail data for items in fields, and move fields to the detail area.

Microsoft Excel worksheets

When you create a Microsoft Excel PivotTable report that's based on data from an Excel worksheet and then save the report as a PivotTable list on a Web page, the data from the Excel worksheet becomes a separate HTML file in a subfolder for the Web page. When your source data is from an Excel worksheet, you can create new total fields in the PivotTable list, display detail data for items in fields, and move fields to the detail area.

For information about publishing Excel PivotTable reports to Web pages, see Excel Help.

OLAP databases

OLAP is an approach to database server processing that organizes large amounts of data in ways that make it accessible and understandable to people who want to analyze business trends and results. OLAP data is stored in structures called cubes and organized by level of detail within the cube. A set of levels, such as country, region, city, and site information, is called a dimension and can be used as a set of fields in a PivotTable list. An OLAP cube can be a server database, a cube file extracted from a server database, or a cube constructed in memory from records in a relational database, depending on the OLAP server and client software you are using.

In PivotTable lists that are based on source data from OLAP databases, dimension fields (also called fieldsets) can display different levels of detail. When you use source data from OLAP databases, you cannot create your own total fields, you cannot move fields to the detail area, and you cannot display detail data. However, OLAP databases provide their own tools for creating summary data and calculations. Because they precalculate summaries instead of returning large amounts of detail data to the client PivotTable list to be summarized, OLAP databases allow you to analyze larger amounts of data than would otherwise be possible in a PivotTable list.

For information about creating summary fields in an OLAP cube or database that can then be displayed in a PivotTable list, contact your OLAP database administrator, or see Help for the Microsoft Office OLAP tools (for example, the OLAP Cube Wizard or the Offline Cube Wizard, both of which come with Microsoft Excel).

Connecting to source data

If you are using Microsoft Access data access page Design view as your design program, you can use the methods and commands in Access to prepare data and establish connections. For information and procedures, see Access Help.

Setting up a data source

To use external source data within your design program, you set up a data source on your system. A data source is a set of named, stored information that connects your design program to an external database and retrieves data. To set up a data source, you'll need a driver program specific to the type of source data. Microsoft Office provides a set of drivers for popular database types. Your design program might provide additional drivers, and you can obtain other Office-compatible drivers from many database vendors.

If you want to work with both OLAP databases and relational databases in your design program, you need to set up data sources for both types. Your design program might provide a means of setting up data sources, or you can use Microsoft Windows tools or Office tools to perform this setup. For more information about setting up data sources, see your design program and Help for your data source driver.

Creating a query to retrieve the data

For source data from a relational database, after you set up a data source, you need to create a query, or write an SQL select statement or procedure, to define and retrieve the set of records you want. For information about how to do this, see Help for your design program and the documentation for your database. For example, Microsoft Access has tools both to create queries and to make sets of records available in the Access field list without creating a query. You can then use the field list to select data for use in PivotTable lists. You can also use Microsoft Query (a component of Microsoft Office) to create queries for data in various supported databases.

To use OLAP data, you need an OLAP server database, cube file, or cube definition. You do not need to set up a query to access the data in the cube; you need to set up only a driver and data source. For more information, see your OLAP provider documentation or the information in Microsoft Excel Help, Microsoft Query Help, or Microsoft Access Help about working with OLAP databases.

Adding the data to the PivotTable list

When you add data to a blank PivotTable list, you set up a connection between the PivotTable list and the data source. Adding the data to the PivotTable list provides the PivotTable list with all of the information necessary to reestablish that connection and retrieve the data when users open the PivotTable list in the browser.

Your design program might provide tools that help you set up this connection. If your design program does not provide tools or Help to set up a connection, you can construct a connection string for your data source by using the Commands and Options dialog box. For information about connection string syntax, consult Help for the data source driver you are using and the programming Help for PivotTable lists.

How drivers and data sources affect users of your Web page

Users who want to interact with the PivotTable list from the browser need to install an ODBC driver or data source driver specific to the source database before trying to view the PivotTable list. This applies to all types of source data except Excel worksheet data. If you use a source database for which the driver is not installed as part of Office, make sure the users of your PivotTable list have access to the driver.

In the browser, however, users do not need to set up a data source for the external database. When you add the data to the PivotTable list in your design program, the database connection information that the browser needs is incorporated into the PivotTable list and saved as part of your Web page. Whether users have to enter a login ID and password for the external database depends on how you set up the data source you use when you design the PivotTable list.

Refreshing the PivotTable list when the source data changes

After you have established a connection to a database, you can refresh the PivotTable list when you need to reflect updates and changes to the source data.

Refreshing when source data is from relational databases

For source data from relational databases, refreshing requeries the source database — that is, refreshing retrieves both changes to the data and any new data that meets the criteria you established. If you want the query, select statement, or stored procedure to select a different set of data or fields from the database, see the documentation for the database or the program you used to create the query for information about making changes.

Refreshing when source data is from Microsoft Excel worksheets

If you plan to publish a PivotTable report from Microsoft Excel, it's best to first save a copy of your workbook in Excel and then publish the PivotTable report to a PivotTable list on a Web page. When you want to refresh a PivotTable list that was published from Excel with new or changed data in the original Excel workbook, or when you want to select a different set of Excel data for the PivotTable list, you can change the original Excel PivotTable report and then republish this report to the PivotTable list. For instructions, see Excel Help.

Refreshing when source data is from OLAP databases

For OLAP source data, new data is retrieved from the server or cube file whenever you make changes to the PivotTable list that affect what data is displayed. For example, if you add a new field to the row area, summary values are retrieved for the new items of data that are now displayed. If you are working with an OLAP cube file, see the documentation for the program you used to create the cube file for information about updating the file when information in the original server database changes.

Refreshing data in the browser

In the browser, data is retrieved when the user first displays the PivotTable list. When the user browses to a different Web page and then returns to the page with the PivotTable list, the user's changes to the PivotTable list are retained, but the page is not reloaded from the Web server and the data in the PivotTable list is not refreshed. The user can see the latest data by refreshing the PivotTable list, and also can cancel a refresh in progress.


Add data to a PivotTable list

Your design program might provide a simpler way to add data to a PivotTable list. Use the following procedure if your design program does not provide a procedure to add the data.

Before you can add data to a PivotTable list, you might have to install an ODBC driver or data source driver (OLE DB or OLAP support software) and set up a data source to connect to the database. For more information, see Help for your design program.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the PivotTable list, or if data has already been added to the PivotTable list, click the title bar of the PivotTable list.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Data Source tab.
  4. Under Get data using, do one of the following:

    Use a data source control

    • If a data source control or a data source object that implements IDataSource is available on the Web page, set Data source control to a value in the list.

    Specify connection information

    1. If the Web page does not have a data source control or object, or if you want to connect to different source data, enter a connection string in the Connection box, or click Edit to browse to or enter information for a data source.

      Depending on the design program you are using, clicking Edit displays the Select Data Source dialog box or the Data Link Properties dialog box.

    2. If the Select Data Source dialog box appears, do the following:
      1. Locate the data source you want to use in the Look in box, and double-click it in the list.
      2. If the data source you want to use is not available, click New Source and follow the instructions in the Data Connection Wizard to create a new connection file for your data.
    3. If the Data Link Properties dialog box appears, enter connection information for your data source on the Connection tab. For help on options, click Help at the bottom of the dialog box.
  5. Under Use data from, do one of the following:
    • To use the data from a specific element within the database, such as an existing table or query, click Data member, table, view, or cube name, and then select the element you want from the list.
    • To use a query or command — such as an SQL statement — to select the data, click Command text or SQL, and then type the query or command in the box. For information about the query or command syntax, see the documentation for your database.
  6. Click Field List [image] on the toolbar, and drag the fields to the different areas in the PivotTable list.

Note Users cannot change a PivotTable list's connection settings in the browser.


Elements of a PivotTable list

PivotTable list showing its elements

1 Filter field

2 Row fields

3 Column fields

4 Field drop-down arrow

5 Data field

6 Expand Indicators

Fields

Each field corresponds to a column of data and can summarize multiple rows of information. Types of fields include row fields, column fields, data fields, detail fields, and filter fields.

Item

A unique element of data within a field. Items of data in row and column fields are listed down rows and across columns. The cell where a row and column intersect displays summarized data for the items listed at the top of the column and the left side of the row. When a lower level of items is available for display, an expand indicator Plus box appears beside the item. The items at the lowest level don't display expand indicators. The expand indicators ( Plus box and Minus box boxes) to the right of an item indicate that detail data is available for that item.

Row field

A field in the row area. In the above PivotTable list, Product and Sold By are row fields. Items in row fields are listed down the left side of a PivotTable list. Inner row fields, such as Sold By, are closest to the data area; outer row fields, such as Product, are to the left of the inner row fields.

Column field

A field in the column area. Items in column fields are listed across the top of a PivotTable list, above the data fields. In the above PivotTable list, Year is a column field with two items, 1996 and 1997. Inner column fields are closest to the data area; outer column fields are above the inner column fields.

Filter field

A field in the filter area that you can use to filter the entire PivotTable list to display summarized data for specific items. In the above PivotTable list, Region is a filter field that you can use to display data for all regions or only selected regions. Filter fields perform the same functions as page fields in Microsoft Excel PivotTable reports.

Data field

A field that contains summary values from the source data. In the above PivotTable list, Sales is a data field that contains summarized sales amounts — each value in the data field is the sum of the entries from the Sales field or column in the source data.

A data field usually summarizes numeric data, such as statistics or sales amounts, by using the Sum summary function. In the above PivotTable list, the cell containing 30,984 displays Davolio's total sales for dairy products in 1996. This cell combines the sales amounts from every record in the source data that has Dairy for the product, Davolio for the salesperson, and 1996 for the year.

The underlying data can also be text, summarized by default with the Count summary function, which yields the number of items — for example, a data field that contains the type of product sold might be used to count the number of dairy sales that Davolio made in 1996. A PivotTable list can also summarize data by using the Min and Max summary functions, which give the smallest and largest values, respectively.

Data area

The part of a PivotTable list that contains data fields.

Detail field

A field that displays all of the rows, or records, from the source data.

Detail area

The part of a PivotTable list that contains detail fields.

Field drop-down arrow

The arrow Field arrow at the right side of each field. Click this arrow to display a list from which you can select the items to display and hide.

In PivotTable lists that are based on OLAP source data, you can select items at different levels in a dimension field. Date and time fields and fields with custom groups provide different levels in all types of source data. For example, clicking the arrow next to a Year field might display the following:

Drop-down list for OLAP dimension field

In this example, the cleared check box next to 1998 indicates that the year 1998 is hidden. The shaded check box next to 1999 indicates that the year 1999 is currently selected for display but some of the items under 1999 are not selected for display. Within 1999, Qtr1 is hidden. The selected check box next to 2000 indicates that the year 2000 is selected for display. Selecting (All) selects all items at all levels for display.


Select an element of a PivotTable list

Select the entire PivotTable list

  • Click the title bar.

Note You can't display the title bar if the PivotTable list is not bound to a data source.

Select one or more data cells

  • To select a single cell, click it. To select a group of cells, click and drag the mouse pointer until all the cells you want are selected.

Select a row, column, or filter field

  • Click the field label.

Note Selecting a row or column field also selects all of the items of data in the field.

Select a detail field

  • Click the field label. If detail data is displayed for several items so that the field label is displayed more than once, clicking one instance of the field label selects all instances.

Select a total field

  • Click any field label for the total field.

Select one or more items

  • To select a single item, click the item label.
  • To add more items to the selection, press and hold the CTRL key when clicking the item labels.
  • To select a range of items, click the label of the first item, press SHIFT, and then click the label of the last item.

Note Selecting an item selects all of the data for that item.

Select a subtotal or grand total

  • Click the subtotal or grand total.

Note Selecting a subtotal or grand total also selects all of the data for the subtotal or grand total.

Select the title bar or a drop area

  1. Click the title bar.

  2. On the toolbar, click Commands and Options [image], and then click the Captions tab.

  3. In the Select caption list, select the item you want.

Select a custom property of a row or column field

  1. Click the row or column field, or one of its items.
  2. On the toolbar, click Commands and Options [image], and then click the Item Properties tab.
  3. In the Item properties list, select the property you want.

    Note If the selected field does not have any custom properties, the Item properties list will be empty.

    You can set the caption, number format, and display location settings for the selected property. The formats you select affect only the selected property.

  4. To set the font and style settings of a property, select Property caption or Property value from the Format list.

Note Font and text style settings that you apply to a custom property affect all custom properties in the PivotTable list, but only when they are displayed in the report. The formatting is ignored when the custom property is displayed in the ScreenTip.


View information about a field or item

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • For information about a field, click the label of the field.
    • For information about an item of data in a field, click the item.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Captions tab.


Create a PivotTable list

The specific steps for creating a PivotTable list depend on the program you are using to design your Web page. In Microsoft Access, for example, you can add data to a PivotTable list automatically. For more information, see Help for your design program. If your design program Help does not provide a procedure for creating controls such as PivotTable lists, follow these steps:

  1. In your design program, open or create the Web page where you want the PivotTable list to appear.
  2. By using the design program's procedure for adding a control to a Web page, select the PivotTable Component and designate the location and initial size of the PivotTable list on the Web page.
  3. Add your source data to the PivotTable list.

    How?

    Your design program might provide a simpler way to add data to a PivotTable list. Use the following procedure if your design program does not provide a procedure to add the data.

    Before you can add data to a PivotTable list, you might have to install an ODBC driver or data source driver (OLE DB or OLAP support software) and set up a data source to connect to the database. For more information, see Help for your design program.

    1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
    2. Click the PivotTable list, or if data has already been added to the PivotTable list, click the title bar of the PivotTable list.
    3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Data Source tab.
    4. Under Get data using, do one of the following:

      Use a data source control

      • If a data source control or a data source object that implements IDataSource is available on the Web page, set Data source control to a value in the list.

      Specify connection information

      1. If the Web page does not have a data source control or object, or if you want to connect to different source data, enter a connection string in the Connection box, or click Edit to browse to or enter information for a data source.

        Depending on the design program you are using, clicking Edit displays the Select Data Source dialog box or the Data Link Properties dialog box.

      2. If the Select Data Source dialog box appears, do the following:

        1. Locate the data source you want to use in the Look in box, and double-click it in the list.

        2. If the data source you want to use is not available, click New Source and follow the instructions in the Data Connection Wizard to create a new connection file for your data.

      3. If the Data Link Properties dialog box appears, enter connection information for your data source on the Connection tab. For help on options, click Help at the bottom of the dialog box.

    5. Under Use data from, do one of the following:

      • To use the data from a specific element within the database, such as an existing table or query, click Data member, table, view, or cube name, and then select the element you want from the list.

      • To use a query or command — such as an SQL statement — to select the data, click Command text or SQL, and then type the query or command in the box. For information about the query or command syntax, see the documentation for your database.

    6. Click Field List [image] on the toolbar, and drag the fields to the different areas in the PivotTable list.

    Note Users cannot change a PivotTable list's connection settings in the browser.


Save changes to a PivotTable list

To save your changes, use the design program's command to save the file containing the PivotTable list. For information about how to save changes, see Help for your design program.

Note Users cannot save changes in the browser, but they can export or copy the PivotTable list from the browser.


Print a PivotTable list

To print a PivotTable list, use the print command in your design program. The results you get depend on the design program's print capabilities. For more information, see Help for your design program.

Notes

  • If the PivotTable list is wider or longer than the printed page, the areas that don't fit on the page are not printed. For more control over how the data is printed, including the ability to print a long list and set page breaks, you can export the PivotTable list to Microsoft Excel by clicking Export to Microsoft Excel Export to Excel on the toolbar. For information about printing from Excel, see Excel Help.

  • Microsoft FrontPage might not print a PivotTable list correctly. If you are using FrontPage as your design program, click Preview in Browser on the File menu, and then print the Web page that contains the PivotTable list from Microsoft Internet Explorer.


Remove a PivotTable list from a Web page

  1. In your design program, select the PivotTable list. For instructions, see Help for your design program.
  2. Press DELETE.

Note You cannot delete a PivotTable list from a Web page in the browser.


About creating a chart based on a PivotTable list

When you create a chart based on a PivotTable list, the data displayed in the PivotTable list is plotted in the chart. When you change the view of the data in either the PivotTable list or the chart, the other component is dynamically updated to reflect the changes. For example, if you add a field to the row or column area, the chart is updated to reflect the new field as either category or series data. When you filter data in a chart, the data in the PivotTable list is also filtered.

Preparing the PivotTable list

Before you create the chart, be sure to move all the data you want to see in the chart to the row and column areas in your PivotTable list. By default, the data in row fields is displayed as series data in the chart, and the data in column fields is displayed as category data. You must also add at least one total field to the PivotTable list to have the data displayed in the chart. If you add more than one total field, the chart uses the data from the leftmost total field.

Creating a chart

To create a chart, use your design program's procedure for adding a control to a Web page. You can select the type of chart you want and change the series data to use the data in column fields, rather than row fields.

The following example shows a chart based on a PivotTable list. The Sum of Sales field displays total sales for each type of product per salesperson. The blue arrow in the Salesperson field label indicates that the field is filtered to display totals for only four of the salespeople.

Chart based on PivotTable list

1 The PivotTable list column field Type is shown in the chart as category data (Beverages, Dairy, and so on).

2 The Salesperson row field is shown in the chart as series data.

3 The names of the series are displayed in the legend (Buchanan, Davolio, and so on).

Using the chart

When you add or move fields in the PivotTable list, the chart updates to reflect the changes.

Chart updated with changes to PivotTable list

In the preceding example, the Salesperson field was moved to the column area and the Type field was moved to the row area of the PivotTable list. The Region field was added to the column area and filtered to show only East and North. The chart was updated so that the data from the Type field is displayed in the legend as a series, and the data from the Salesperson and Region fields supplies the categories.

You can sort, filter, and move fields within the chart.


Create a chart based on a PivotTable list

The specific steps for creating a chart based on a PivotTable list depend on the design program you are using to create your Web page. For more information, see Help for that program. If Help for your design program does not provide a procedure for creating controls such as charts, follow these steps:

  1. In your design program, open or create the Web page where you want the chart to appear.
  2. If you don't have a PivotTable list on the Web page yet, create the PivotTable list.

  3. Add the necessary fields to the row and column areas of the PivotTable list.

    Note Data in row and column fields is used for the series and categories in your chart.

  4. Add a chart to the Web page by using Help for the design program.
  5. Click inside the chart.

    The chart's Commands and Options [image] dialog box will appear.

  6. Select Data from the following Web page item and click the entry corresponding to the PivotTable list you want to use.


About using a PivotTable list in a browser

You can create a PivotTable list in a design program for access in a Web browser or another run-time environment.

Note If you plan to create PivotTable lists for use in a program other than a Web browser, such as a Microsoft Visual Basic form that's part of a custom solution, see Help for your design program to find out how users of your work can access and interact with the PivotTable list.

Requirements for using a PivotTable list in a browser

Users can use a PivotTable list in a browser if they have the Microsoft Office Web Components installed. They can do this by installing Microsoft Office XP or, if their company has an Office XP site license, by downloading the Office Web Components from their corporate intranet. Users must also have a Web browser that fully supports PivotTable lists.

To take full advantage of the capabilities of PivotTable lists, users need Microsoft Internet Explorer version 4.01 or later. In other browsers, users cannot view or interact with PivotTable lists.

The browser interface for PivotTable lists

In the browser, users can activate a PivotTable list and interact with it by using the same toolbar and Commands and Options dialog box that you use while you are designing the data layout. However, users don't have access to some features in the browser that are available to you in the design program.

In the browser, users can also display the field list that you use in the design program. The field list is available from the toolbar in the PivotTable list, and lists all of the fields you made available from the source data.

Accessing Help from the browser

In the browser, the user has access to two types of Help: the Help system for the browser itself, and the additional Help content automatically provided from within a PivotTable list. The latter Help explains how to use the commands and features available in the PivotTable list in the browser. To view this Help, open a Web page that contains a PivotTable list, and click Help [image] on the toolbar in the PivotTable list. You can also provide your own custom Help for a PivotTable list in a browser.

Restricting use of a PivotTable list in a browser

If you want to simplify the options available to users at run time, you can set restrictions that apply when the PivotTable list is viewed in the browser. For example, you can prevent users from displaying detail data. You might also want to set restrictions when you want to ensure that users don't modify some aspects of the way you've arranged the data in the design program. For example, you can prevent users from moving the row and column fields.

While keeping in mind the needs and skills of your intended users, you can perform all analysis in the design program and then publish the results accordingly. For general users, you can publish the finished summaries as separate PivotTable lists on your Web page. For more skilled users, you can make the raw source data available in a PivotTable list and leave the analysis up to the users.


About restricting the use of a PivotTable list in the browser

Some PivotTable list features are available only when you're working in the design program. Other features are available both while designing the PivotTable list and while using it in a browser. However, as a designer, you can restrict the availability of these features to a user by changing property settings.

Regardless of the capabilities you have made available to the users, whether users can take advantage of those capabilities depends on what browser they are running. To take full advantage of the capabilities of PivotTable lists, users need Microsoft Internet Explorer version 4.01 or later.

Capabilities that are not available in a browser

In the browser, users cannot change information about the source database, database fields, and source data.

Only the designer of a PivotTable list can specify what source database to use and what subset of the data to make available as the source data in the PivotTable list.

In the browser, users can display only the data you've made available; if a password is required to access the database, users must enter the password to display the data. Users can refresh the PivotTable list in the browser to reflect any changes in the source data, but they cannot gain more general access to the source database or make any changes to the data it stores.

Capabilities that are usually available in the browser

If their browser fully supports PivotTable lists, users can make many of the same changes to the PivotTable list in the browser that you can make in the design program.

Subject to any restrictions you set when you design the PivotTable list, the user can add or move fields from the field list, change the layout, sort, filter, and group data. Users can analyze the data and create summaries, just as you can in the design program.

Restrictions that you can set

To prevent users from making changes to a PivotTable list or accessing portions of the source data that you don't want to make available, you can prevent them from doing the following:

  • Accessing the Commands and Options dialog box
  • Changing the layout
  • Grouping items
  • Filtering data
  • Sorting data in a custom order
  • Viewing detail data
  • Adding, editing, or deleting detail data


Restrict the use of a PivotTable list in the browser

When you change a setting to prevent a user from doing a specific task, you will not be able to do that task in the design program unless you change the setting again.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.

  2. Click Commands and Options [image] on the toolbar.

  3. Click the title bar of the PivotTable list.

  4. In the Commands and Options dialog box, click the Protection tab.

  5. Do one of the following:

    Prevent adding, changing, or removing filters

    • Clear the Filter check box.

    Prevent grouping or changing the layout

    • Clear the Group and change layout check box.

    Prevent sorting data in custom order

    • Clear the Custom order items check box.

    Prevent displaying detail data

    • Clear the Display detail data check box.

    Prevent editing detail data

    • Clear the Edit detail data check box.

    Prevent adding detail rows

    • Clear the Insert new detail rows check box.

    Prevent deleting detail rows

    • Clear the Delete detail rows check box.

    Prevent access to the Commands and Options dialog box

    • Clear the View the Commands and Options Window in run mode check box.

Notes

  • Even if you clear the View the Commands and Options Window in run mode check box, the Commands and Options button [image] is always available on the toolbar when you are designing the PivotTable list.
  • In Microsoft Excel, these restrictions have no effect. Users who export a PivotTable list to Excel from the browser can then use Excel features to view all detail data that is available in the source data. To prevent users from accessing the detail data in this way, you can restrict access to the Export to Microsoft Excel command in the browser by turning off the toolbar, restricting access to the Commands and Options dialog box, and writing code to hide the shortcut menus.


Change title, captions, and labels

Note The changes you make don't affect the source data.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click Commands and Options [image] on the toolbar.
  3. Do one or more of the following:

    Change the title bar text or the caption of a drop area

    1. Click the title bar of the PivotTable list.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Select caption list, select the element whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of a field, fieldset, or custom group field

    1. Do one of the following:
      • To change the caption of a field, click the field.
      • To change the caption of a fieldset, click a field that belongs to the fieldset.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Select caption list, select the element whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of an item property

    1. Click the field to which the item property belongs.
    2. Click the Item Properties tab in the Commands and Options dialog box.
    3. In the Item properties list, click the property whose caption you want to change.
    4. In the Caption text box, type the new title or caption.

    Change the caption of an item or custom group

    1. Click the item or custom group whose caption you want to change.
    2. Click the Captions tab in the Commands and Options dialog box.
    3. In the Caption text box, type the new title or caption.

Note If you don't want a caption for the selected item, clear the Caption text box.


About providing custom instructions for a PivotTable list

Depending on the skills of your users and the type of work they want to do, you might want to supplement the run-time Help by providing custom instructions for your PivotTable list on the Web page. However, you cannot connect your instructions directly to the online Help provided with the PivotTable list.

You can provide instructions by including text directly on the Web page that describes the PivotTable list, or by providing a hyperlink to a separate Web page of instructions.

For information about how to do this on different types of Web pages, consult Help for your Web page design program.


About adding and removing fields

When you add data to a PivotTable list, you specify the database to take the data from and the subset of that data to make available as the source data for the PivotTable list. From this source data, you can select fields for initial display in the PivotTable list.

Viewing the fields available to the PivotTable list

Use the field list to view the list of fieldsets and fields that are available from the source data, and to add fields to the row, column, filter, data, and detail areas of the PivotTable list. In the field list, the fields that are not bold are available but not yet displayed in the PivotTable list. The bold fields are already displayed. You can scroll and click the expand indicators ( Plus box or Minus box boxes) next to the fieldsets to show or hide fields in the field list.

If your PivotTable list is based on source data from an OLAP database, such as one created by using Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas. Other fields, listed under Totals, can be used only as data fields. Fields in a fieldset cannot be displayed in two different drop areas.

Adding fields to the detail area

Use detail fields to display all of the available detail data and see what data is available to be summarized. When you add fields to the detail area, you display all of the detail records from the source data for the fields. Field names become column labels, and the detail records are displayed in rows below the column labels.

Example of detail fields

1 Detail fields

2 Detail data

If your PivotTable list is not based on source data from an OLAP database, you can add a calculated detail field that uses an expression to calculate detail values.

Adding fields to the row or column area

Use row and column fields to summarize and compare data. These fields display the unique items of data within a field down rows or across columns. The cell at each row and column intersection summarizes the data for an item.

Example of row and column fields

1 Column field

2 Column field item

3 Row field

4 Row field items

Adding fields to the data area

Data fields provide the values to be summarized in the PivotTable list. When you add a field to the data area, the values from the field are summarized for the row and column field items.

Example of a data field

1 Data field

2 Summarized data

Adding fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Use filter fields to confine the view to a particular part of the available data. When you select an item in a filter field, data is displayed and calculated only for that item. For example, if you add a Region filter field, you can have the PivotTable list display and calculate data for the regions you select.

Example of a filter field

1 Filter field

2 Filter field item

3 Summary for the selected region

Adding total fields

You can create new data fields by adding total fields to the PivotTable list. Total fields provide summarized data for use as data fields. For example, if your source data includes a Sales field that shows the amount of each sale, you could add a Sum of Sales total field to calculate the total sales, or a Count of Sales total field to calculate how many units were sold.

You can also add a calculated total field that uses an expression to calculate total values.

Removing fields

To eliminate data that you don't want to see or summarize, you can remove fields from the PivotTable list layout. Removing a field does not remove it from the available source data. A field that you remove is no longer displayed in the PivotTable list, but it remains available in the field list for viewing.

Moving fields

After you add a field to a PivotTable list, you can move it to other areas. When you move a field, you change the layout of the PivotTable list, and you change the data that's displayed and the summaries that are calculated.

Impact of adding and removing fields on filtering

Filter settings are retained when you remove a field. If you remove a field and later add the field back to the layout, the same items are again hidden.

Viewing information about fields

You can view information about fields in a PivotTable list, including information about the field in the source database from which the data is taken and the type of data that the source database supplies for the field.

Restricting use of the PivotTable list in the browser

The features for adding fields and creating summaries are also available in the browser, unless you restrict the user's access. If you don't want users to change the fields you've designated as row and column fields, you can prevent row and column fields from being added and moved in the browser. You can also restrict access to detail so that users can't move fields to the detail area, and restrict access to filtering so that users can't move fields to the filter area.


Add or remove a field

Add a field

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one or more of the following:

    Add a field to the row, column, data, filter, or detail area

    1. On the toolbar in the PivotTable list, click Field List [image].
    2. Use the scroll bars and the Plus box and Minus box boxes to find the field you want to add to the PivotTable list.
    3. Click the field, and in the lower-right corner of the field list, select the area you want to add the field to.
    4. Click Add to.

    Notes

    • If Add to becomes unavailable when you select Row Area, Column Area, Data Area, or Filter Area, the field you selected might not be available for use as a row, column, data, or filter field. If your PivotTable list is based on source data from an OLAP database, only the fields that are not listed under Totals in the field list can be used as row, column, data, or filter fields.

    • If Add to becomes unavailable when you select Detail Data, detail data might not be available from the source data. For example, if your PivotTable list is based on source data from an OLAP database, often only summary data, rather than detail data, is available.

    Add a total field

    You can add total fields to the data area, but not to the row, column, detail, or filter area.

    1. Click the field that contains the values you want to summarize.
    2. On the toolbar, click AutoCalc [image], and then click the summary function you want.

    Note The AutoCalc button is unavailable when the source data for the PivotTable list does not allow you to add total fields — for example, when the source data is from an OLAP database.

    Add a calculated total or detail field

    You can add a total or detail field that uses an expression to calculate data.

    1. On the toolbar, click Calculated Totals and Fields [image].

    2. On the menu that appears, do one of the following:

      • To create a total field, click Create Calculated Total.

      • To create a detail field, click Create Calculated Detail Field.

    3. In the Calculation tab of the Commands and Options dialog box, type the expression you want, and then click Change.

      Tip

      To add field references, select a field that you want to use from the Insert Reference to box, and then click the Insert Reference To button.

    Note To edit the expression that is being used in a calculated field, select the field, edit the expression, and click Change on the Calculation tab in the Commands and Options dialog box. The field values will be automatically recalculated using the new expression.

Notes

  • You can restrict the user from adding fields in the browser. You can also restrict the user from filtering data.
  • By default, data fields that summarize numeric data use the Sum summary function. Fields that summarize other types of data use the Count summary function.

Remove a field

When you remove a field from the layout of a PivotTable list, data from the field is no longer displayed; but the field remains available in the field list for viewing.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the field label of the field that you want to remove from the PivotTable list.
  3. Drag the field outside the PivotTable list, until the pointer changes to Pointer with red X.

Notes

  • You can delete total fields and calculated detail fields from both the PivotTable list and the field list by right-clicking the field and then clicking Delete on the shortcut menu.

  • You can restrict the user from removing fields in the browser.


About customizing the layout of a PivotTable list

You can control how the data in a PivotTable list is summarized by customizing the layout. A different layout lets you calculate and compare summarized values for different elements in your data, or display summaries for a subset of the data. When you make the PivotTable list available to users, users can make further changes to your initial layout.

Using the drop areas

You change the layout by moving the fields to predefined drop areas within the PivotTable list.

Example of drop areas

1 Drop areas

2 Detail area

After you move fields to the drop areas so that the drop area captions are covered up, you can still drag additional fields to the areas. You can also use the Add to button in the field list to add fields to different drop areas.

Moving fields to the row and column areas

When you move a field to the row area, the unique items of data within the field are displayed down the rows of the PivotTable list. When you move a field to the column area, the unique items of data are displayed across the columns.

Moving fields to the detail area

To view all of the detail records from the source data for the PivotTable list, you can move fields to the detail area. If you don't want to rearrange the fields or change the summarized data, you can view details alongside the summarized data.

Moving fields to the data area

Data fields provide the values to be summarized in the PivotTable list. When you move a field to the data area, the values from the field are summarized for the row and column field items.

Moving fields to the filter area

A filter field is similar to a page field in a Microsoft Excel PivotTable report. Filter fields allow you to confine the view to a particular part of the available data. For example, when you move a Product field to the filter area, you can have the PivotTable list display a summary for one product at a time.

Moving row and column fields to a higher or lower level

When a PivotTable list has multiple row and column fields, the fields that are closest to the summarized data are referred to as inner fields. The other fields are outer fields.

Example of inner and outer row fields

1 Outer row field

2 Inner row field

3 Outer field items

4 Items for the inner field are repeated for each item in the outer field.

Adding and removing fields

The layout of a PivotTable list does not have to include all of the fields that are available from the source data. If more data is available, you can add fields to the PivotTable list layout. For example, if the PivotTable list summarizes sales revenue, and the source data also includes sales quantities, you might add the Quantity field as a data field to summarize both revenue and quantity of products sold. You can also remove fields from the PivotTable list layout that you no longer want to see.

Restrictions on moving and adding fields

If you work with a PivotTable list that is based on source data from an OLAP database, such as one created with Microsoft SQL Server OLAP Services, you'll notice that you can't move fields to the detail area. You'll also notice that some fields can be moved only to the row, column, and filter areas, and that fields in the data area cannot be moved to other areas.

Restricting use of a PivotTable list in the browser

You can restrict the user's ability to change the layout of the PivotTable list by preventing fields from being added and moved in the browser.


Move a field or column

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Display the drop areas for the PivotTable list.

    How?

    When the drop areas are hidden and the PivotTable list does not have a field in the row, column, data, or filter area, you can't drag fields to these areas.

    1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
    2. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.

    3. Select or clear the Drop areas check box.

    Note You will not see the drop areas if the PivotTable list is not bound to a data source.

  3. Do one or more of the following:

    Move a field to the row or column area

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Do one of the following:

      • To make the field a row field, click and drag the field label to the row area on the left side of the PivotTable list, until the pointer changes to Pointer with row area highlighted.

      • To make the field a column field, click and drag the field label to the column area near the top of the PivotTable list, until the pointer changes to Pointer with column area highlighted.

    Move a field to the detail area

    You cannot move fields to the detail area if the PivotTable list is based on source data from an OLAP database.

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.

    2. Click and drag the field to the detail area at the bottom right of the view, until the pointer changes to Pointer with detail area highlighted.

    Move a field to the data area

    You can move fields from the row, column, and filter areas to the data area only if the PivotTable list is based on data from a source other than an OLAP database.

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Click and drag the field to the data area at the lower-right side of the PivotTable list, until the pointer changes to Pointer with sigma.

    Note The field you drag to the data area is summarized based on the type of data in the field. Fields that contain numeric data are summarized with the Sum summary function. Fields that contain other types of data are summarized with the Count summary function.

    Move a field to the filter area

    1. Point to the field label of the field you want to move until the pointer changes to Pointer with four-headed arrow.
    2. Click and drag the field to the filter area at the top of the PivotTable list, until the pointer changes to Pointer with filter area highlighted.

    Move a row or column field to a higher or lower level (Change the column order of fields)

    1. Point to the field label of the field you want to move to a different level until the pointer changes to Pointer with four-headed arrow.
    2. Do one of the following:

      • To move the field to a higher level, click and drag the field label to the left and drop it where you want.

      • To move the field to a lower level, click and drag the field label to the right and drop it where you want.

Note You can prevent users from moving fields by changing the setting on the Protection tab in the Commands and Options dialog box. To display the Protection tab, click the PivotTable list title bar.


Set the size of a PivotTable list and its elements

Set the size of a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Commands and Options [image], and then click the Behavior tab.
  3. Do one of the following:

    Change the height or width of the control

    1. Clear the Auto fit the control check box.
    2. Place the pointer on one of the selection handles of the PivotTable list, and then drag the pointer in the direction you want.

    Automatically resize the control on a Web page to fit its contents

    1. Select the Auto fit the control check box.
    2. If you want to prevent the PivotTable list from expanding beyond a specified size, type a value in pixels in the Maximum height box and/or the Maximum width box.

Note Scroll bars appear within the PivotTable list when more data is available than will fit in the area.

Change the width of a column in the row, column, detail, or data area

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Point to the right boundary of the field label until the pointer changes to Pointer with perpendicular double-headed arrow.
  3. Do one of the following:

    • To increase or decrease the width, drag the boundary until the column is the width you want.

    • To make the column width fit the contents, double-click the column boundary.

Note You cannot adjust the height of the rows in a PivotTable list.


About calculating totals

PivotTable lists use two different types of totals to display summarized values. The first type, a total field, provides the summarized values for the PivotTable list. When your PivotTable list contains a total field, you can also display the second type — subtotals and grand totals — for the row and column fields.

Total fields

Total fields are data fields that are either provided in the source data or created by you to summarize the data from other fields in the source data. For example, if the source data has a Sales field that records the amount of each individual sale, a Sum of Sales total field could add up the sales figures for each category in the PivotTable list, or a Count of Sales total field could tally the number of sales for each category. You can also create a total field that uses an expression.

In the field list, the total fields are displayed at the top, under Totals. The Sum of Sales and Count of Units fields are available for inclusion in the PivotTable list as total fields.

Field list with total fields

Adding total fields

In PivotTable lists that are based on source data from OLAP databases, the total field values are precalculated on the database server that supplies the source data. As a result, the commands to add new total fields, move total fields to the detail area, and change the summary function used to calculate the values in a total field are unavailable.

If the total fields available in the field list don't provide the summaries you want, and the source data allows it, you can add new total fields to a PivotTable list. Most often, you will add total fields to the data area. For example, in the following PivotTable list, the Sum of Sales total field has been added to the data area to display summary data for each sport and quarter.

PivotTable list with total field

1 The Sum of Sales total field.

A total field uses a summary function, such as Sum or Count. Depending on the type of source data, you can also use more than one summary function to summarize the data in the same field by adding more total fields, each using a different summary function.

You can also add a calculated total field that uses an expression. For example, to see the year-to-date sales figures, you could create a total field that uses the following expression:

Sum (YTD(), [Measures].[Sales])

The following PivotTable list shows sales and year-to-date sales figures.

PivotTable list with calculated total field

Changing the orientation of total fields

You can change the orientation of total fields in the PivotTable list. Displaying the fields in the row orientation lists them down the first column to the right of the row fields. Displaying them in the column orientation lists them across the first row below the column fields.

Showing total values as percentages

You can also show total values as a percentage of a row, column, grand total, or as a total of the item's parent in the row or column axis.

PivotTable list showing total values as percentages

1 These values will be used if you choose to show total values as a percentage of row total.

2 These values will be used if you choose to show total values as a percentage of column total.

3 These values will be used if you choose to show total values as a percentage of the total of the item's parent in the row axis.

4 This value will be used if you choose to show total values as a percentage of the grand total.

Subtotals and grand totals

You can display subtotals for any row or column field in the PivotTable list if the list includes at least one total field and more than one row field or more than one column field. If you choose to display subtotals for a field, grand totals are also selected for display. In addition, subtotals and grand totals are displayed for every total field in the PivotTable list where it intersects the row or column field you select.

In the following example, subtotals and grand totals are displayed for both row fields by using the values in the Sum of Sales total field.

PivotTable list subtotal and grand total

1 Grand total

2 Subtotal

If you move a field for which subtotals or grand totals are displayed out of the row or column area, the subtotals or grand totals become hidden, but the setting is retained. The subtotals or grand totals are redisplayed if you move the field back to the row or column area.

When you hide subtotals and grand totals for a field, they remain hidden regardless of where you move the field, even if you remove the field from the PivotTable list and later add it back.

Depending on the source data and the type of data you're using in the PivotTable list, you might want subtotals and grand totals to include or omit the values for items of data you've hidden by filtering. You can specify that all subtotals and grand totals either include or exclude any hidden data.


Summary functions available in a PivotTable list

The following table lists the summary functions provided by the AutoCalc [image] command that you use to create total fields.

Function | Description
Sum | Calculates the sum of the values.
Count | Tallies the number of values. Empty values, such as zero-length strings (strings that contain no characters), are included in the count, but null values (values that indicate missing or unknown data) are not included.
Min | Returns the smallest value in the field being summarized.
Max | Returns the largest value in the field being summarized.
Average | Returns the average of the values in the field being summarized.
Standard Deviation | Returns the standard deviation of the values in the field being summarized, assuming the values are a sample of a larger population.
Standard Deviation Population | Returns the standard deviation of the values in the field being summarized, assuming the values are the entire population.
Variance | Returns the variance of the values in the field being summarized, assuming the values are a sample of a larger population.
Variance Population | Returns the variance of the values in the field being summarized, assuming the values are the entire population.

Note The summary functions available for a field depend on the type of data in the field. For example, if a field contains text, you cannot use Sum, Min, or Max, but you can use Count for that field.


Show or hide totals for a field

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the row or column field for which you want to show or hide subtotals and grand totals.
  3. To show the totals, on the toolbar in the PivotTable list, click the Subtotal button [image] so that it is selected. To hide totals, click the button again.

Notes

  • Subtotals and grand totals are not displayed for fields in the detail area (the area where all of the individual records can be displayed).
  • When you choose to display them, subtotal and grand total values are displayed for every total field in the PivotTable list. You can't exclude individual total fields.
  • Subtotal and grand total calculations can include or exclude items of data that are hidden by filtering.


Customize a total field

Change the orientation of total fields

This procedure affects total fields in the PivotTable list. It has no effect on subtotals or grand totals.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar, click Commands and Options [image], and then click the Report tab.
  4. Under Display totals as, select one of the following:
    • Column headings To display total fields across the first row below the column fields or in the top row if there are no column fields.
    • Row headings To display total fields down the first column to the right of the row fields or in the leftmost column if there are no row fields.

Show totals as percentages

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Right-click the total field whose values you want to view as percentages, point to Show As, and then click one of the following:
    • Percent of Row Total To display the values as a percentage of the total value of each row.
    • Percent of Column Total To display the values as a percentage of the total value of each column.
    • Percent of Parent Row Item To display the values as a percentage of the total value of the item's parent in the row axis.
    • Percent of Parent Column Item To display the values as a percentage of the total value of the item's parent in the column axis.
    • Percent of Grand Total To display the values as a percentage of the PivotTable list's grand total value.


Include or exclude hidden data when calculating totals

This procedure affects all subtotals and grand totals in the PivotTable list.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar, click Commands and Options [image], and then click the Report tab.
  4. Under Calculate totals based on, do one of the following:
    • To include hidden data, select All items (including hidden items).

    • To exclude hidden data, select Visible items only.

Notes

  • This setting affects data hidden by filtering row and column fields; data hidden by using filter fields is always excluded from totals.

  • When you choose to include hidden data, asterisks (*) appear next to the captions of subtotals and grand totals to note that the total values include hidden items as well as displayed items.


Edit a calculated field

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Select the calculated field whose expression you want to edit.
  3. On the toolbar, click Commands and Options [image], and then click the Calculation tab.
  4. Edit the expression, and then click Change.

    The data values will be automatically recalculated using the new expression.

Tip

To add field references, select a field that you want to use from the Insert Reference to drop-down box, and then click the Insert Reference To button.


About hyperlinks

A hyperlink is a pointer from one object to another. The destination is frequently another Web page, but it can also be a picture, an e-mail address, a file (such as a multimedia file or Microsoft Office document), or a program. The hyperlink itself can be displayed as text or as a picture.

You can use hyperlinks to do the following:

  • Navigate to a file or Web page on a network, intranet, or Internet

  • Send an e-mail message

  • Start a file transfer, such as a download or an FTP process

When you point to text or a picture that contains a hyperlink, the pointer becomes a hand Pointer in the shape of a hand, indicating that it is something you can click. When a site visitor clicks the hyperlink, the destination is displayed, opened, or run, depending on the type of destination. For example, a hyperlink to a sound file opens the file in a media player, and a hyperlink to a Web page displays the page in a Web browser.

Parts of a hyperlink address

A hyperlink address can have up to four parts that are separated by the number sign (#):

Part | Description
displaytext | The text that you see in the cell when you choose to display data as hyperlinks. For example, you might want to display a descriptive name for the World Wide Web site or object specified by the address and the subaddress. This is an optional field.
address | An absolute or relative path to a target document. An absolute path is a fully qualified URL or UNC path to a document. If you don't specify the address, displaytext is assumed to be the address.
subaddress | The location in the file or page. For example, you might want to point to a particular paragraph in the target document.
screentip | The text that appears when you rest the pointer on a hyperlink. This is an optional field.

Showing data as hyperlinks

You can choose to display data as hyperlinks in a row, column, or detail field by changing the field's property settings. When data is displayed as hyperlinks, users can click the hyperlinks to navigate to other Web pages or objects.

Grouping on fields that contain hyperlinks

The result of grouping on hyperlink fields depends on how the corresponding source data field is defined. If the source data field is a Character Large Object (CLOB), when you group on the hyperlinks you will see two group members — one for blank values and one for all non-blank values. If the source data field is defined as scalar, such as varchar, the values will be grouped on individual hyperlink values. The uniqueness of the member is based on the displaytext and the address parts of the hyperlink.


Show data as hyperlinks

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the row, column, or detail field that contains hyperlinks.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Format tab.
  4. Select the Display as hyperlink check box.


Show or hide ScreenTips

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Report tab.
  4. Select or clear the ScreenTips check box.

Note If you hide the ScreenTips, you won't see the item properties that are set to display as ScreenTips. Item properties set to display in the report will still be visible.


About displaying details and items

You can set up a PivotTable list to display different levels of items and detail available in the source data. You can display all of the available detail and item levels, only high-level summaries, or any level of detail in between, and you can display selected detail for items of data that you want to emphasize. Unless you restrict access, users can change the PivotTable list in the browser to display different levels and amounts of detail.

Displaying detail data

The items at the lowest level in the row area display Plus box and Minus box to their right. The items at the lowest level in the column area display Plus box and Minus box below the item captions. You can show or hide details for a specific item by clicking Plus box or Minus box.

Items at the lowest level

You can display detail data only if the details are available in the source data. For example, OLAP databases often provide only summary data rather than detail data.

Automatically showing or hiding all details

By default, detail data is not initially displayed in the browser when a user adds fields to the PivotTable list, but you can automatically display detail data for all items if you prefer.

Restricting access to detail data in the browser

You can display the amount of detail that you consider appropriate, and then prevent users from displaying additional detail in the browser.

Displaying lower-level items

When the row or column area has more than one field, higher-level items will display expand indicators ( Plus box or Minus box boxes) to their left. Clicking the expand indicators will show or hide the items in the next lower level. If the innermost field on the PivotTable list is not the lowest in the item hierarchy, clicking the expand indicator of the innermost field will automatically add the next lower level of items to the PivotTable list.

You can display lower-level data for an entire field, or you can display it on an item-by-item basis, expanding only the items you want to see. Items in the lowest level will not display expand indicators. For example, if Country and City fields are in the row area, the items in the Country field will display expand indicators like this:

Example of expand indicators

1 Expand indicators

Automatically showing or hiding all items

You can change the setting to have all item levels in the PivotTable list expanded or collapsed by default. After loading the PivotTable list, the user can expand or collapse the items as they want.

Hiding expand indicators

After you have expanded the levels to the extent you want, you can hide all the expand indicators in the PivotTable list. Users can change this setting in the browser. If you don't want users to be able to change whether expand indicators are displayed, you can restrict access to the Commands and Options dialog box in the browser.

Showing or hiding items that have no data

By default, a PivotTable list does not display items for which the source data provides no values. For example, if you're showing sales for each month and there were no sales in April, the Month field would not have an April item. If you want to include items for all 12 months, with blank cells for the months with no sales, you can change the setting in the Commands and Options dialog box to display items that have no data.


Show or hide expand indicators next to items

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Behavior tab.
  4. Select or clear the Expand indicator check box.

Notes

  • This setting does not affect the [image] and [image] boxes that appear to the right of the items at the lowest level.
  • Items at the lowest level in a row or column area don't have expand indicators on their left.
  • If you don't want users to be able to change whether expand indicators are displayed, you can restrict access to the Commands and Options dialog box in the browser.


Show or hide detail data for an item or field

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:

    • To show or hide detail data for a cell, or an item at the lowest level, select the item or cell.

    • To show or hide detail data for a row or column field, select the row or column field.
  3. Click Show Details [image] or Hide Details [image] on the toolbar.

Notes

  • To show or hide lower-level items, select the row or column field or item, and then click Expand [image] or Collapse [image] on the toolbar.
  • If you don't want users to display detail data, you can restrict access to detail in the browser.


Show or hide items

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • To show or hide the lower-level items of a specific item in the row or column axis, click the expand indicator ( Plus box or Minus box box) next to the item.
    • To show or hide the lower-level items for all items in a row or column field, click the field label, and then click Expand Plus box or Collapse Minus box on the toolbar.

Notes

  • If the Plus box or Minus box box is not visible next to the item, select the Expand indicator check box on the Behavior tab in the Commands and Options dialog box. If after selecting the check box you don't see an expand indicator next to an item, the item might be at the lowest level available for the source data.

  • To show or hide details, select the item or field, and then click Show Details Plus box or Hide Details Plus box on the toolbar.


Show or hide items that have no data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Report tab.
  4. Select or clear the Empty rows and Empty columns check boxes.


Automatically show all detail data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar, click Commands and Options [image], and then click the Behavior tab.
  4. Set Expand details by default to Always.

Notes

  • To hide detail data for all items, select Never in the Expand details by default list. If you set Expand details by default to Automatic, data from tabular sources will be expanded by default, but data from OLAP databases will be hidden.
  • To automatically show all items in a PivotTable list, on the Behavior tab, set Expand items by default to Always.
  • Detail data usually is not available for display in PivotTable lists that are based on source data from OLAP databases. If detail data is not available, changing the Expand details by default setting has no effect.


Automatically show all items

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. On the toolbar, click Commands and Options [image], and then click the Behavior tab.
  4. Set Expand items by default to Always.

Notes

  • To hide all items, select Never in the Expand items by default list. If you set Expand items by default to Automatic, fields from tabular sources will be expanded by default, but fields from OLAP databases will be hidden.

  • To automatically show all details in a PivotTable list, on the Behavior tab, set Expand details by default to Always.


Add, edit, or delete detail data

If you can't add, edit, or delete data in a PivotTable list, it means one of the following:

  • The properties on the Protection tab of the Commands and Options dialog box have been set to prevent additions, edits, or deletions.
  • The field you are trying to edit is a read-only field, and does not support edits.
  • The underlying table does not have a primary key.

Add data

  1. Click a cell in the blank row marked with an asterisk (*).
  2. Type the data you want in the first field, and then press TAB to move to the next field.
  3. Do one of the following:
    • Press ENTER to save the record and move to the next record.
    • Press ESC to leave the record without saving it.

Edit data

  1. Click the cell that you want to edit.
  2. Type the new value.
  3. Do one of the following:
    • Press ENTER to save the record and move to the next record.
    • Press ESC to leave the record without saving it.

Delete data

  • Do one of the following:
    • To delete the contents of a cell, click the cell and press DELETE.
    • To delete a record, click the leftmost cell of the record, and then press DELETE.
    • To delete multiple records, click the leftmost cell of the record, drag the mouse pointer to the last cell of the last record, and then press DELETE.

Notes

  • When entering data for a field that has a number format applied to it, you need not type formatted data. When you leave the cell, the format will be automatically applied to the cell contents.
  • Only detail cells can be edited directly in the PivotTable list. To change a caption or the title, use the Commands and Options dialog box.


About item properties

You can display additional information about items by displaying the custom properties of a row or column field. For example, if the database administrator has made available two custom properties, Manager and Phone Number, for the Store field, you can choose to display the property values for each item in the Store field, either in the PivotTable list or as ScreenTips.

The following illustration shows how the Employees report will look with the custom properties Education and Yearly Income displayed in the report.

PivotTable list with custom properties

The users of a PivotTable list cannot create custom properties. However, they can show or hide properties, and choose whether the properties are displayed in the report or as ScreenTips.

The following illustration shows the Education property displayed in the report and the Yearly Income property displayed in the ScreenTip.

PivotTable list with custom properties

The users of the PivotTable list can also format the property captions and values, and specify the order in which the properties are displayed in the report or ScreenTip.

Non-OLAP data sources do not support custom properties.


Show or hide item properties

You can show item properties of row and column fields either in the report or as ScreenTips.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the field whose custom properties you want to show or hide.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Item Properties tab.
  4. In the Item properties list, select the property for which you want to change settings.
  5. In the Display in list, select one of the following:
    • Report To show the property caption and value next to the item names.
    • ScreenTip To show the property caption and value as part of each item's ScreenTip.
    • All To show the property caption and value in the report as well as in the ScreenTips.
    • None To hide the property caption and its value.
  6. Repeat steps 4 and 5 for each custom property of the selected field.

Notes

  • If you choose to show the properties as ScreenTips, ensure that the ScreenTips check box on the Report tab in the Commands and Options dialog box is selected.
  • You can change the order in which the custom properties are displayed in the report or ScreenTip.
  • You can format the caption and value of a custom property. The format does not affect the property when displayed in a ScreenTip.
  • Non-OLAP data sources do not support custom properties.


Specify the display order of item properties

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the field whose item properties you want to order.
  3. On the toolbar in the PivotTable list, click Commands and Options [image], and then click the Item Properties tab.

    The order in which the properties are listed in the Item properties box is the order in which the properties are displayed in the report or ScreenTips.

  4. Select the property that you want to display at the top, and click Up [image] until the property appears as the first item in the list.
  5. Order the rest of the properties by clicking Up [image] or Down [image].

Note If new properties are added after you order the list, the new properties will appear at the end of the list until you reorder them.


About formatting a PivotTable list and its elements

Formatting elements

You can format row, column, total, or detail fields, the title bar and drop area captions, and custom properties of row or column fields in the following ways:

  • Setting text formats You can set the font, font size, and color for text, and make text bold, underlined, or italic. Changes that users make to the browser's settings for fonts and colors have no effect on PivotTable lists.

  • Aligning text in a cell You can display values so that they are left-aligned, right-aligned, or centered horizontally in the cells. You cannot change the values' vertical alignment.

  • Setting background colors You can change the background color for cells. Changes that users make to the browser's settings for colors have no effect on PivotTable lists.

  • Changing number formats You can change the formats in which numbers, dates, and times in a field are displayed to any of several predefined number formats. In Microsoft Visual Basic or in a script, you can assign custom number formats to the data.

    For example, you can display a number as currency or as a percentage, and you can display a date in long format, with the day and month spelled out, or in abbreviated format. Changes to the number format do not change the actual values, only how the values are displayed. In the browser, the user's regional settings in Microsoft Windows Control Panel affect how the number formats you set are displayed.

Formats that you apply to one element might affect similar elements. For example, if you change the text color for an item, all other items in the same field are formatted with the new text color.

Formatting an entire PivotTable list

You cannot set the formats for an entire PivotTable list in one operation. You need to format each element in the PivotTable list individually.

To customize the appearance of every element in a PivotTable list, you might need to change the layout temporarily to format elements that you don't plan to include in your initial presentation of the data. For example, to establish formats for a field in the detail area when the initial layout doesn't include that detail field, you'll need to move a field to the detail area, format it, and then move the field back to its previous area. When users move fields to different areas of the PivotTable list in the browser, the fields and their data take on the formatting you've established for fields and data in those areas.


Format a PivotTable list and its elements

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Select the element that you want to format.

    How?

    Select the entire PivotTable list

    • Click the title bar.

    Note You can't display the title bar if the PivotTable list is not bound to a data source.

    Select one or more data cells

    • To select a single cell, click it. To select a group of cells, click and drag the mouse pointer until all the cells you want are selected.

    Select a row, column, or filter field

    • Click the field label.

    Note Selecting a row or column field also selects all of the items of data in the field.

    Select a detail field

    • Click the field label. If detail data is displayed for several items so that the field label is displayed more than once, clicking one instance of the field label selects all instances.

    Select a total field

    • Click any field label for the total field.

    Select one or more items

    • To select a single item, click the item label.
    • To add more items to the selection, press and hold the CTRL key when clicking the item labels.
    • To select a range of items, click the label of the first item, press SHIFT, and then click the label of the last item.

    Note Selecting an item selects all of the data for that item.

    Select a subtotal or grand total

    • Click the subtotal or grand total.

    Note Selecting a subtotal or grand total also selects all of the data for the subtotal or grand total.

    Select the title bar or a drop area

    1. Click the title bar.

    2. On the toolbar, click Commands and Options [image], and then click the Captions tab.

    3. In the Select caption list, select the item you want.

    Select a custom property of a row or column field

    1. Click the row or column field, or one of its items.
    2. On the toolbar, click Commands and Options [image], and then click the Item Properties tab.
    3. In the Item properties list, select the property you want.

      Note If the selected field does not have any custom properties, the Item properties list will be empty.

      You can set the caption, number format, and display location settings for the selected property. The formats you select affect only the selected property.

    4. To set the font and style settings of a property, select Property caption or Property value from the Format list.

    Note Font and text style settings that you apply to a custom property affect all custom properties in the PivotTable list, but only when they are displayed in the report. The formatting is ignored when the custom property is displayed in the ScreenTip.

  3. Click Commands and Options [image] on the toolbar.
  4. Do one of the following:
    • To format a drop area, click the Captions tab.
    • To format an item property of a row or column field, click the Item Properties tab.
    • To format all other elements of a PivotTable list, click the Format tab.
  5. Do one or more of the following:

    Specify font and style settings

    • To make the text bold, click Bold [image].

    • To make the text italic, click Italic [image].

    • To underline the text, click Underline [image].

    • To change the text color, click the arrow next to Font Color [image], and then click the color you want.

    • To change the font, click a different font in the font list.

    • To change the font size, select a size from the list next to the font list.

    Note The fonts and font sizes available in the lists are determined by the fonts you have installed on your computer.

    Align text

    • Click the alignment you want: Align Left [image], Align Center [image], or Align Right [image].

    Set background color

    • Click the arrow next to Fill Color [image], and then click the color you want.

    Change the number format of a field, total, or item property of a row or column field

    • In the Number list, click the format you want.

    Note The number format of a field does not affect the number format of other row, column, filter, or total fields. The number format of an item property does not affect other item properties.

Notes

  • You can't format the contents of a ScreenTip. Therefore, the formats you apply to an item property apply to the property only when it is displayed in the report.
  • You cannot apply a format to an entire PivotTable list in one operation.


Format the contents of a PivotTable list to read right to left or left to right

Use right-to-left display when your PivotTable list will be viewed in languages that are read from right to left. When you select right-to-left display, the elements of the PivotTable list are displayed as a mirror image of the default left-to-right display: The drop areas are on the upper-right side of the PivotTable list, row fields are in the rightmost columns, and items in column fields are listed from right to left.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the title bar of the PivotTable list.
  3. Click Commands and Options [image] on the toolbar, and then click the Behavior tab.
  4. Select or clear the Display right to left check box.


Number formats for PivotTable lists

Some of the content in this topic may not be applicable to some languages.

The following table lists the number formats supported in a PivotTable list.

Format | Effects
General | Displays a number right-aligned, with no thousands separator, and preceded by a minus sign if negative. Displays up to 11 digits, or 10 digits with a decimal point, rounding additional digits to the right of the decimal point. Displays numbers with more than 11 digits to the left of the decimal point in scientific format. Displays text left-aligned.
General Date | Displays a number as a date and optional time. For example, 8/31/99 05:54 AM. The fractional part of the number is the time. If a number has no fractional part, only a date appears. If a number has only a fractional part and no integer part, only a time appears.
Long Date | Displays a date according to the Microsoft Windows regional setting for the long date format. For example, Thursday, January 25, 2001.
Medium Date | Displays a date using the abbreviated month names and hyphens (-) to separate month, day, and year. For example, Jan-25-2001.
Short Date | Displays a date according to the Windows regional setting for the short date format. For example, 1/25/2001.
Long Time | Displays a time according to the Windows regional setting for the long time format. For example, 8:45:36 PM.
Medium Time | Displays a time in 12-hour format with an AM or PM designator, omitting the seconds. For example, 8:45 PM.
Short Time | Displays a time in 24-hour format, omitting the seconds. For example, 20:45.
Currency | Displays a number according to the Windows regional setting for currency. For example, with the default United States English regional settings, $2,532.75.
Euro Currency | Displays a number as euro currency, with a thousands separator and two decimal places. The placement of the euro sign and the decimal places are based on the regional settings for currency in Windows Control Panel. For example, with the default United States English regional settings, [image] 12.35.
Fixed | Displays a number with two decimal places. For example, 68.30.
Standard | Displays a number according to the Windows regional settings for numbers. For example, with a comma as the thousands separator and a period as the decimal symbol, 1,800.00.
Percent | Displays a number multiplied by 100, with two decimal places and a percent sign. For example, the value 0.8914 would be displayed as 89.14%.
Scientific | Displays a number in exponential notation. For example, 1.25E+10.
Yes/No | Displays No if the number is 0; displays Yes for any other number.
True/False | Displays False if the number is 0; displays True for any other number.
On/Off | Displays Off if the number is 0; displays On for any other number.

Notes

  • The regional settings in Windows Control Panel determine how number, currency, date, and time formats are displayed, both in the design program and at run time in the browser. For example, these settings determine whether years are displayed with two digits or four digits. See Windows Help for information about these formats.

  • In Microsoft Visual Basic or in a script, you can assign custom number formats to the data. You can also type custom formats available in the PivotTable list object model in the Number box.


About sorting data

You can sort data when designing the PivotTable list or when viewing it in the browser. However, you can restrict some access to sorting in the browser.

Sort in ascending or descending order

You can sort a sales column in ascending order or an employee name column in descending order.

You can also sort the PivotTable list separately for each column. The order in which you sort the columns determines how the data will be organized.

Example of data sorted by two columns

In this example, the list is organized by salesperson. The Product column was sorted first so that the products would be in alphabetical order; the Salesperson column was sorted second.

When sorting multiple columns, first determine the order in which you want to sort the columns, and then work backward, sorting the outermost column last. For example, if you're sorting two columns, determine which is the outer column and which is the inner column. Sort the inner column first, and then sort the outer column. To list the products in alphabetical order for each salesperson, you would first sort the inner Product column and then sort the outer Salesperson column.

Define a custom sort order

You can define a custom order for a row or column field when sorting in ascending or descending order does not meet your requirements. For example, you might want to show the values in the Title column ordered on the basis of the seniority of the title. If the column had three values — Vice President, General Manager, and Manager — sorting in ascending or descending order will not show the data in the order you want. In this case, you might want to explicitly define the order of the items for the Title column.

If items are added to a field after you define a custom sort order, the new items will appear at the bottom of the field until you rearrange them.

Custom sorting is persisted but does not affect the source data.


Sort data in a PivotTable list

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:

    Sort in ascending or descending order

    You can sort data that is displayed in natural or custom order in ascending or descending order.

    1. Do one of the following:

      • To sort the data in a field based on the item labels, click the label of the field that contains the data you want to sort.

      • To sort the data in a field based on the summary values in a column, click a cell in the column that contains the values that you want to sort on.

      • To sort the data in a field based on subtotal or grand total values, click a subtotal or grand total value for the field that contains the data you want to sort.

        Note If you click a grand total value in the bottom row, the columns will be sorted instead of the rows.

    2. On the toolbar, click Sort Ascending [image] or Sort Descending [image].

    Note To sort on multiple fields, start by sorting the innermost field first and work backward, sorting the outermost field last.

    Specify a custom sort order

    1. Select the item you want to move.
    2. Drag the selected item and drop it over the item above which you want to place it.
    3. Repeat steps 1 and 2 until you have the items in the order you want.

    Note If a field with custom sorting has a parent field, each parent member will display the lower-level members in custom order.

Notes

  • To switch from ascending or descending sort to custom sort, click Sort Ascending [image] or Sort Descending [image] so that the button is not selected.
  • The order in which numbers, text, and special characters are sorted depends on the source data and your regional settings in Microsoft Windows Control Panel. If users have different regional settings in their browsers, their sort orders might be different from yours.


Remove sort order

Removing ascending or descending sort order will return the data either to its natural order, or if a custom order was specified, to the custom order.

  • Click the Sort Ascending button [image] or the Sort Descending button [image] on the toolbar so that the button is not selected.


About grouping items

You can group data when designing the PivotTable list or when viewing it in the browser. However, you can restrict some access to grouping in the browser.

You can group items in a row or column field in two ways.

Grouping on intervals

You can group items by specifying the type of interval and the size of the interval. For example, you can group a field with date values into different months, and specify the interval as 2, to create groups such as Jan-Feb, Mar-Apr, and so on.

The type of interval you can specify for a field depends on the data type of the field.

  • Numeric fields You can group in intervals of any integer, such as 1, 2, 5, 100, and so on. For example, you can group the EmployeeID field into groups of 1-100, 101-200, and so on.
  • Date and time fields You can group in intervals such as days, weeks, and quarters. For example, you can group the values in the ShippedDate field into months to show data for orders shipped in January, orders shipped in February, and so on.
  • Text fields You can group based on the first n characters of the individual items. For example, you can group the LastName field by first letter to create groups, such as A, B, and so on.

You can specify the start and end values for the grouping range. For example, if you specify the start range as 01-Jul-1999 while grouping the ShippedDate field in weekly intervals, the following groups will be created:

<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ...

Dates prior to 01-Jul-1999 are grouped into a single group titled <01-Jul-1999.

If you also specify the end value as 31-Dec-1999, the following groups will be created:

<01-Jul-1999, 01-Jul-1999, 08-Jul-1999, ... 31-Dec-1999, >31-Dec-1999

Dates that fall after 31-Dec-1999 are grouped into a single group titled >31-Dec-1999.

Custom grouping

You can randomly select items from a row or column field and group them into higher-level groups. For example, you can select from the Promotions row field all the promotions that run for a specific period and create a group. This would add a new row field named Promotions 2 above the Promotions row field with two members: Group1 and Other. You can change the caption of Group1 to Fixed and Promotions 2 to Category in the Commands and Options dialog box.

The Other group will contain all items that you did not include in the Fixed custom group. You can then select all the popular promotions from the Other group and create a new custom group that will be captioned Popular. After you do this, the Category field will have three members: Fixed, Popular, and Other.

The following illustration shows how the PivotTable list will look after the captions of the custom field and groups have been changed.

A PivotTable list or view with custom groups

1 A custom group field. Contains custom groups as its items and appears as the parent of the field whose items you grouped.

2 Custom groups. Appear as the parent of items you explicitly selected to create the group.

3 The Other group is the parent of the items you did not assign to any specific custom group.

Creating and deleting custom groups and custom group fields

  • You cannot group items that belong to different fields or items that belong to parent items into a custom group.

  • When you create the first custom group for a field, a custom group field is automatically added as the field's parent. Similarly, when you delete the last custom group for a field, the custom group field is automatically deleted.

  • When you delete a custom group that is not the last custom group in the field, the deleted group's members automatically move to the Other group.

  • The groups you create are persisted, but exist only on the client side. So any groups you create are available on the Web page you create, but are not part of the source data.

Moving a custom group field to the detail area

When you move a fieldset that has custom group fields to the detail area or filter area, you will not see the custom group fields. However, the bound field will show the custom group hierarchy in the drop-down list, so you can filter data by selecting custom groups or individual values. When you move the fieldset back to a row or column area, the custom group fields will show in the fieldset.

Adding and removing nested custom groups

You can select two or more custom groups to create a higher-level grouping. For example, you can group the members of the Category field into two groups: high priority promotions and low priority promotions.

The following illustration shows how the row area will look with nested custom groups.

PivotTable list or view with nested custom groups

1 A custom group field that appears as the parent of the Category custom group field.

You can also remove a lower-level custom group. The following illustration shows what the data will look like after the Category field has been removed.

PivotTable list or view with custom groups


Group or ungroup items

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:

    Group items at intervals

    1. Select the row or column field whose items you want to group.
    2. Click Commands and Options [image] on the toolbar, and then click the Filter and Group tab.
    3. Set Group items by to a value other than (No grouping).
    4. Set Interval to the value you want. For example, if you are grouping items by prefix characters, setting Interval to 1 would group items on A, B, C, and so on.
    5. To specify the value at which grouping begins, select the Start at check box and then enter a value in the text box.
    6. To specify the value at which grouping ends, select the End at check box and then enter a value in the text box.

      Note The end value must be higher than the start value.

    Remove grouping at intervals

    1. Select the row or column field from which you want to remove grouping.
    2. Click Commands and Options [image] on the toolbar, and then click the Filter and Group tab.
    3. Set Group items by to (No grouping).

    Create custom groups

    1. Click the label of the parent field that contains the items you want to group, and then click Expand [image] on the toolbar.
    2. Click the first item that you want to include in the group.

      To select more items, hold down the CTRL key and click each item.

    3. Right-click a selected item, and then click Group Items.
    4. Repeat steps 2 and 3 to create more groups.

    Notes

    • When you add the first custom group to a field, a custom group field is added to the field set. The custom group field will be the parent of the field whose items you grouped.
    • You can change the caption of the custom group field and the custom groups in the Commands and Options dialog box.

    Remove custom grouping

    • Do one of the following:
      • To remove a custom group, right-click the group caption, and then click Ungroup Items.
      • To remove a custom group field, right-click the field, and then click Delete.

    Notes

    • You cannot right-click the Other item and ungroup it.
    • If the group you want to delete is the last custom group in the field, the custom group field will be deleted along with the group. If the group you delete is not the last custom group, the members of the deleted group will move to the Other group.


About filtering data in a PivotTable list

You can use the filter feature to find specific data values or all data that matches a value. You can filter either when designing the PivotTable list or when viewing it in the browser. However, you can restrict access to filtering in the browser.

Filtering techniques

Filtering a field (Autofiltering) You can filter fields in the row, column, and detail areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.

Filtered PivotTable list or view

1 First, the Sport field is filtered to display only Golf sales ...

2 ... and then the Quarter field is filtered to display only Golf sales in Qtr3.

3 When you filter a field, the drop-down arrow Field arrow for the filtered field changes to blue instead of black, and the AutoFilter button [image] on the toolbar is selected.

Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.

Filtering based on the data in one cell (Filter by Selection) You can filter a field to display only data that matches the value in a selected cell. Filtering by selection is particularly useful for fields in the detail area, when you want to view all of the rows that contain a particular value.

Using a field in the Filter axis You can also filter data by using filter fields. When you select one or more items in the filter field, the data that's displayed and calculated in the entire PivotTable list changes to reflect those items. You can add new filter fields or move existing fields to the filter area.

Filter field example

1 Filter field

Conditional filtering You can apply a filter to a row or column field to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.

Combining multiple filters

Filters are additive. Each filter you apply is added to the ones you already have in effect. However, when you apply a filter by selection, the conditional filter already applied on the field is removed before the filter by selection is applied.

Conditional filters on a PivotTable list are independent of each other. The result of one conditional filter does not affect the result of another.

An autofilter is always applied before a conditional filter, regardless of the order in which you applied them. For example, if you set a conditional filter to show the top two cities based on sales, followed by an autofilter on the ShippedCity field to include only five cities, the PivotTable list will show the top two of the five cities you selected.

Removing and reapplying a filter

You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.

Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.

If you don't want to retain your filter selections, make sure the AutoFilter button [image] is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.

Identifying filters that are currently in effect

When items in a field are hidden by autofiltering or filter by selection, the drop-down arrow Field arrow in the field label is blue. Click the blue arrow [image] to see the filter that is in effect.

When items in a field are hidden by conditional filtering, a funnel Funnel icon icon appears to the left of the drop-down arrow Field arrow. You can get more information about the current filter in the Commands and Options dialog box.

Effects of filtering on calculations

Totals can include only the displayed data, or both visible and hidden data. The default is to exclude hidden data from totals — with the exception of PivotTable lists that are based on source data from OLAP databases and published from Microsoft Excel, which include hidden data in grand totals. You can change the setting that's in effect to include or exclude hidden data in totals. This setting affects only data that's hidden by filtering row and column fields; data that's hidden by using filter fields is always excluded from totals.

Search specific data

To search through the data instead of filtering, or to find data that matches a pattern, you can export the PivotTable list to Microsoft Excel. For information about using the Excel Find command, see Excel Help.

Restricting filtering in the browser

If you don't want users to change how you've filtered the data, you can make filtering unavailable in the browser.


Filter a field in a PivotTable list

Filter a field in the row, column, filter, or detail area (Autofiltering)

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the arrow Field arrow for the field that contains the items of data that you want to filter.
  3. Select the check boxes for the items you want to display, and clear the check boxes for the items you want to hide.

Notes

  • To display or hide all of the data for a field, select or clear the (All) check box. A shaded check box indicates that some of the lower-level items are hidden. To see which lower-level items are hidden, click the expand indicator Plus box beside the item with the shaded check box.
  • When placed in the row, column, or detail area, a field always supports multiple selection. A field in the filter area can support either single or multiple selection. To switch from multiple to single selection, on the Filter and Group tab of the Commands and Options dialog box, clear the Allow selecting multiple items when in filter area check box.

Display only data that matches a cell in a PivotTable list (Filter By Selection)

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Right-click the cell that contains the value you want to display, and then click Filter By Selection on the shortcut menu.

Filter a row or column field for top or bottom n values or a percentage (Conditional filtering)

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Select the field that has the values you want to filter.
  3. Click Commands and Options Field arrow on the toolbar, and then click the Filter and Group tab.
  4. Set Display the to Top or Bottom.
  5. Set Items to a number in the list, or type the value you want.
  6. To show n number of items, clear the % check box. To show n% of items, select the % check box.
  7. Set Based on to a value in the list.

Notes

  • When items in a field are hidden by an autofilter or a filter by selection, the arrow Field arrow in the field label is blue. When items are hidden by a conditional filter, the filtered field displays a funnel Funnel icon icon to the left of the arrow.
  • When you apply a filter, the displayed totals are based on visible items only. You can change the setting to include hidden data.


Remove filters to display all data in a PivotTable list

When items in a field are hidden by filtering a field or filtering by selection, the arrow Field arrow in the field label is blue. When items are hidden by filtering for top or bottom values, the filtered field displays a funnel Funnel icon icon to the left of the arrow.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:

    Remove all filters

    • Click the AutoFilter button [image] on the toolbar so that it is not selected.

    Note If you click AutoFilter [image] again to turn filtering back on, the previously set filters will be reapplied.

    Remove an autofilter or filter by selection from a field

    1. Click the blue arrow Filtered field arrow of the field from which you want to remove the filter.
    2. Select the (All) check box and click OK.

    Remove a conditional filter from a field

    1. Select the field from which you want to remove conditional filtering.
    2. Click Show Top/Bottom Items [image] on the toolbar, and then click Show All.
Notes
  • If you don't want to retain your filters, make sure the AutoFilter button [image] is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.
  • You can prevent users from removing filters in the browser by clearing the Filter check box on the Protection tab of the Commands and Options dialog box.


Allow multiple selection in the filter area

You can specify whether the field in the filter area will allow single or multiple selection. In single-selection mode, selecting an item automatically clears the previous selection. In multiple-selection mode, when you select an item, previously selected items remain selected.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Select the field in the filter area for which you want to change the setting.
  3. On the toolbar, click Commands and Options [image], and then click the Filter and Group tab.
  4. Select or clear the Allow selecting multiple items when in filter area check box.

Notes

  • By default, multiple selection is enabled.
  • This setting applies only when the field is in the filter area. If you move the field to a row, column, or detail area, the field will always support multiple selection.


Refresh a PivotTable list with updated source data

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. On the toolbar, click Refresh [image].

Notes

  • The Office logo cube Office logo cube stops spinning when the refresh operation is complete. While the PivotTable list is being refreshed, you cannot interact with it or make changes to it.

  • To cancel a refresh operation, press ESC. You can cancel the refresh operation only when the PivotTable list is being calculated — not while source data is being retrieved.


About PivotTable lists that were published from Microsoft Excel

Microsoft Excel PivotTable reports have some capabilities that are not retained when a report is saved or published as a PivotTable list. As a result, you might want to use another design program to make additional changes after publishing your PivotTable list.

The following table summarizes the Excel PivotTable report features that are affected when you publish it as a PivotTable list.

Feature | Result on a Web page
Calculated fields | Formulas are not retained. Changed to total fields that use the default summary function for the field (Sum, Count, Min, or Max).
Calculated items | Not retained.
Summary functions | Only data fields summarized with Sum, Count, Min, or Max are retained. Data fields that use other summary functions are lost.
Custom calculations | Not retained. The data field using the custom calculation is lost.
Character and cell formatting | Not retained. Changes you make to fonts, text size, text color, and cell backgrounds are lost.
Custom subtotals | Not retained. If you change the summary function in Excel, the subtotal or grand total reverts to the summary function of the total field in the PivotTable list on the Web.
Number formats | Partially retained. Number formats that you apply to PivotTable fields in Excel are retained, but number formats that you apply to individual cells are not retained. If you use a custom number format or a number format that's not supported by PivotTable lists on the Web, the numbers appear with the same number format as the Excel PivotTable report. If you subsequently change the number format for the field in the PivotTable list, you cannot return the field to the custom number format.
Asterisks that mark totals | Even if you turned off the asterisks marking totals in Excel, the asterisks are displayed in the PivotTable list on the Web.
Print settings | Not retained. The Excel settings to repeat PivotTable row and column labels on each page, repeat items labels after page breaks, and set page breaks between sections are lost.
Grouped items in fields | Partially retained. Grouped items in date fields are retained, but any selected items and numeric items that are grouped in Excel are no longer grouped in the PivotTable list on the Web, and group fields from Excel are not retained.
Custom sort order | Not retained. Instead, the data is in the order in which it is retrieved from the source database.
Indented formats | Not retained. Fields are in the same positions, but the character and cell formatting are not retained. Blank rows between item groups are lost.
Subtotals displayed at the top of item groups | Not retained. Subtotals appear at the bottom of each group.
Top/bottom items AutoShow | Not retained. All items are displayed.
Customized error values and empty cell values | Not retained. Errors and empty cells are displayed as blank cells.
Page fields in rows or columns | Not retained. Page fields become filter fields, which are always displayed across the top.
Password settings | Data in password-protected worksheets and workbooks cannot be saved or published as a Web page. To publish data that's protected, you must use a blank (or no) password. If you save your database password with the query in Excel, users must enter the password when they open the Web page in the browser.
Background refresh, retrieving data for each item in a page field individually | Not retained. The PivotTable list on the Web is always refreshed in the foreground, and all data for filter fields is retrieved in one operation.
Changes to Excel source data | Not available. To redefine the range of Excel source data or change the query to select different external data, you must make the change in Excel and republish.
Offline cube file data sources | You cannot reconnect to the original server database from the published PivotTable list, or make changes to the content of the offline cube file from the PivotTable list. If you need to perform either of these operations, you can do so in Excel and then republish to the PivotTable list.


About exporting a PivotTable list to Excel or other applications

If you are familiar with Microsoft Excel PivotTable reports and want to work with the data in Excel, there are several ways to accomplish this. However, if you open a Web page that contains a PivotTable list directly in Excel, you will not be able to view or modify the PivotTable list.

Users have the same exporting and copying capabilities in the browser as you do in the design program.

Exporting to an interactive Excel PivotTable report

To view and modify a PivotTable list on a Web page by using Excel, you can export the PivotTable list to Excel. The Excel PivotTable report will be connected to the same source data as the original PivotTable list, so all fields and data from the original PivotTable list will be available in Excel, and you can refresh the report if the source data changes.

Preparing for export to Excel Excel PivotTable reports cannot display detail fields. When you export to Excel, detail fields will be available on the Excel PivotTable toolbar, but the fields won't be displayed in the report. If you want the Excel PivotTable report to reflect the appearance of the PivotTable list on the Web, before you export to an Excel PivotTable report, either move all the fields out of the detail area, or hide detail data for items and cells so that the detail area is not displayed.

After you export You might notice some differences in your PivotTable list after you export it to Excel. This is because Excel PivotTable reports either do not support certain PivotTable list features, or they implement some features differently. For information about differences between PivotTable lists and Excel PivotTable reports, see Excel Help.

Copying data to another program for noninteractive use

Copying to Microsoft Word or Excel When you want to work with a snapshot of the data currently displayed in a PivotTable list, you can copy and paste the data into Excel or Word. When you copy the whole PivotTable list to another program, or copy selected parts of the data, the text and values from the PivotTable list become part of your Word document or Excel workbook. No connection is created between the original source data for the PivotTable list and the copy in the document or workbook, so you won't be able to refresh or update the data that was copied.

Copying to other programs You can also copy data displayed in a PivotTable list to other programs, such as Microsoft FrontPage or Microsoft Access. When you copy data to a program other than Excel or Word that does not support the HTML format used by PivotTable lists, the data is copied as unformatted text.


Export or copy a PivotTable list to Excel or other applications

Export a PivotTable list to Excel

Use this procedure to export a PivotTable list from your design program to Microsoft Excel as an interactive PivotTable report. The link between the Excel PivotTable report and the original source database is maintained.

For best results, either move all fields out of the detail area or hide detail data for items and cells before you export the PivotTable list to Excel.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Click the PivotTable list.
  3. Click Export to Microsoft Excel [image] on the toolbar in the PivotTable list to run or switch to Excel and display the data in a PivotTable report.

Note Because PivotTable reports in Excel cannot display some PivotTable list layouts and calculations, the PivotTable report might look different from the PivotTable list in the design program. For information about working with Excel PivotTable reports and adding fields to them, see Excel Help.

Copy a PivotTable list to Excel or Word

Use this procedure to copy a PivotTable list, or a part of it, as a noninteractive, formatted table in Microsoft Excel or Microsoft Word.

  1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
  2. Do one of the following:
    • To copy the entire PivotTable list, click the title bar of the PivotTable list.
    • To copy a part of a PivotTable list, select the elements that you want to copy.
  3. On the toolbar in the PivotTable list, click Copy [image].
  4. Switch to Excel or Word, and click where you want the data from the PivotTable list to appear.
  5. Click Paste [image].

Notes

  • When you copy a PivotTable list, or a part of it, you are copying only the formatting and data values. No link is maintained between the copy in Excel or Word and the original source database.

  • You can copy part of a PivotTable list to other programs, such as Microsoft Access, as unformatted text.


Troubleshoot PivotTable lists

Some of the content in this topic may not be applicable to some languages.

Interacting with the PivotTable list

Users can't interact with my PivotTable list

Users who can view but not interact with your PivotTable list might not have the Microsoft Office Web Components installed or the appropriate Microsoft Office XP license. Be sure the components are installed and that the user has a valid license.

Moving fields

I can't add or move the field I want.

The field might already be displayed You cannot display the same field in more than one area.

The source data might limit how you can use fields Source data from On-Line Analytical Processing (OLAP) databases provides sets of fields for different levels of detail. Within a set of fields, you can hide levels of detail, but you cannot separate out fields in the set for display in different areas.

When I change the layout, the data takes a long time to appear.

If the PivotTable list takes a long time to calculate, you can make several changes to the layout at one time and then retrieve the data when you are finished making changes.

  1. When the Office logo cube Office logo cube is spinning, press ESC to cancel the calculation. The PivotTable list then displays only the names of the fields.

  2. Change the layout, adding and moving as many fields as you want.

  3. To retrieve and calculate the data for the new layout, click Refresh [image] on the toolbar in the PivotTable list.

The Commands and Options dialog box or the field list disappeared.

When you click a control on the Web page other than a Microsoft Office Web Component, or when you minimize and then restore the design window, the Commands and Options dialog box and the field list might be hidden.

To redisplay the dialog box or field list, click the PivotTable list so that it is reactivated.

Refreshing data

I can't refresh the data in the PivotTable list from the source.

Some PivotTable lists can't be refreshed PivotTable lists that you save or publish from Microsoft Excel worksheet data, Excel Web queries, and Excel text queries can't be refreshed from the original source data in other design programs or in the browser. In these cases, you must refresh the original Excel workbook and republish the data.

The source database might be unavailable You might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you have in the design program. Contact the network or database administrator for assistance.

Microsoft Office might not support the source database If the source data is from a database that's not supported by Microsoft Office, you might need to install Office-compatible support software from the database vendor. Users need to install the same support software to refresh the PivotTable list in the browser.

The source database might have changed Occasionally, changes to the way a database is organized on the server might prevent you from refreshing a PivotTable list. For example, if the source data is from an On-Line Analytical Processing (OLAP) database and the database is rebuilt with changes to the available data, you might have to create a new PivotTable list. Contact the database administrator for more information.

The query failed in a PivotTable list.

The source database might be unavailable If the PivotTable list is based on source data from a database on a network, the database server might be unavailable, or the network or your connection to the network might have become unavailable. Contact the server or network administrator to check whether the server and network are available.

You might not have the permissions you need You might be able to connect to the database, but you might not have permissions to run the query for the PivotTable list. Contact the database administrator to find out about permissions.

Check the filtering in the PivotTable list Occasionally, a PivotTable list might be filtered to display specific items, and changes to the source database might eliminate one of those items, causing the query to fail. If this is the case, turn off filtering to display all items, and then click Refresh [image] on the toolbar in the PivotTable list to run the query.

Filtering data

I can't filter the data the way I want to.

You cannot filter based on part of the data in a cell, a feature which is available for forms and datasheets in Microsoft Access. If you need partial-cell filtering, work with the source data in Access instead of a PivotTable list. You might be able to view the data in a form or datasheet directly in Access, where partial-cell filtering is supported. After you have filtered the data the way you want it in Access, you can use data access page Design view to create a new PivotTable list or change the existing one to use the filtered source data. For more information, see Access Help.

Displaying data

I can't connect to the source data that's specified for my PivotTable list.

The source database might be unavailable You might not have a network connection, or you might need access privileges or a password to connect to the server. In the browser, users who want to display and work with the PivotTable list will need the same access and permissions for the source database as you do in the design program. Contact the network or database administrator for assistance.

Microsoft Office might not support the source database If the source data is from a database that's not supported by Microsoft Office, you might need to install Office-compatible support software from the database vendor. Users need to install the same support software to work with the PivotTable list in the browser. For information about the software you need, contact the database vendor.

The connection might not be set up correctly To connect to source data, you must first set up a connection, or data source, between the source database and your design program. Make sure the data source you set up in your design program is working and allows you to access the database. Check Help in your design program and in the data source driver or ODBC driver for setup information. When you add the data to the PivotTable list, make sure you include all necessary connection information.

I can't display items or detail data.

Expand indicators might be turned off If you don't see the Plus box boxes to the left of items of data in row and column fields, make sure the expand indicators are turned on.

Items might not be available When an item of data is at the lowest level of detail available from the source data, the item does not have the Plus box box beside it, even when you turn on the expand indicators.

The source data might not include detail data Some types of source data, such as source data from On-Line Analytical Processing (OLAP) databases, do not have detail records available for display.

The fields are present, but all the data is missing.

You canceled calculation of the PivotTable list, or calculation stopped before it was complete. To display the data, click Refresh [image] on the toolbar in the PivotTable list.

Tip

Before you refresh the data, you can change the layout or filter the items in the fields.

Data is missing after I copy a PivotTable list into Excel or Word.

When you copy data in a PivotTable list to the Clipboard and paste it into Microsoft Excel or Microsoft Word, only visible rows and columns are copied. Any data that you have hidden or filtered out is not copied.

Before you copy data, turn off filtering to show all data in the PivotTable list, and display the detail data that you want copied.

To avoid having to show all the data first, you can export, rather than copy, the PivotTable list. When you export a copy of a PivotTable list to Excel, all of the data is automatically included, with the same filtering in effect. You have the same access to additional fields in the source data that you do in a PivotTable list, and you can change the filtering in Excel. From Excel, you can then copy data to Word or other programs, or you can publish the data again to a PivotTable list. For information about working with PivotTable reports in Excel and publishing them to a Web page, see Help in Excel.

A cell displays ##### instead of a number.

When a number is too wide to fit in a cell, the PivotTable list displays number signs instead of the number. You can fix this in any of the following ways:

  • Make the column wider so that the entire number fits.

  • Make the font smaller if you don't want to widen the column.

  • Change the number format to one that takes less space.

I can't add, edit, or delete data on a PivotTable list.

  • The properties on the Protection tab of the Commands and Options dialog box have been set to prevent additions, edits, or deletions.

  • The field you are trying to edit is a read-only field, and does not support edits.

  • If the PivotTable list is on a data access page that supports transactions, the PivotTable list doesn't support additions, edits, or deletions.

A chart that's based on my PivotTable list is blank or has no data.

Add total fields For a chart to display data from a PivotTable list, you must add at least one total field to the PivotTable list.

Move fields to the row and column areas The row and column fields in a PivotTable list provide the category and series data in your chart. If all of the fields are in the detail area of the PivotTable list, for example, the chart has no categories and series to plot. From the detail area or field list, move at least one field to the row area or the column area.

Total fields in my PivotTable list have asterisks next to them.

Asterisks (*) appear next to the caption of the total fields in the following cases:

  • When you set the option to include all items in totals, asterisks appear next to the caption of the total fields to indicate that the totals include any hidden items in addition to the displayed items.

  • In Excel PivotTable reports that are based on source data from On-Line Analytical Processing (OLAP) databases, the calculations for subtotal and grand total values always include the values for all items in the field, both displayed and hidden. Excel provides an option to turn off display of these asterisks.

In the PivotTable list, regardless of whether it's based on source data from an OLAP database, if you want to omit data that is hidden by filtering from subtotals and grand totals, you can set an option to include only displayed data. This option also hides the asterisks.