About PivotTable list source data

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.

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 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).

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.

The Data Connection Wizard provides access to an additional kind of data source called a data retrieval service when connecting to data from Microsoft Office Excel 2003 and the Office 2003 Web Components. A data retrieval service is a Web Service installed on Windows SharePoint Services for connecting to and retrieving data. To use a data retrieval service, a client application, such as Excel, sends a query request over HTTP to the data retrieval service on Windows SharePoint Services. The data retrieval service sends that request to the data source, and then sends back the data that is returned to the client application as XML.

Windows SharePoint Services ships with a data retrieval service for connecting to data in SharePoint lists. A Windows SharePoint Services site administrator can install the Office 2003 Web Parts and Components to install data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The Office 2003 Web Parts and Components is available from Downloads on Microsoft Office Online.

When you select Microsoft Business Solutions from the list in the Welcome to the Data Connection Wizard dialog box, you need to specify the location of a Windows SharePoint Services server that contains the Microsoft Business Solutions data you want to work with. After selecting which data object to work with, you can specify query parameters, select which fields to retrieve, and choose to limit the number of rows retrieved.

When you select the Data retrieval services data source option in the Data Connection Wizard, you can choose two data retrieval services:

After specifying the location of the Windows SharePoint Services server that hosts the data retrieval service, you specify the data object you want to work with, such as a list, table, view, or function, and can then select fields and specify additional query settings, such as query parameters, sorting, filtering, and limiting the number of rows retrieved.

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.

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.

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.

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.

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.

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.

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.