An Office PivotTable list is superficially similar to an Excel PivotTable report. Both tools can link to an Analysis Services cube. Both tools can use a member to filter, and both tools allow you to show and hide levels of a dimension on an axis. But the tools behave differently in many ways. With an Excel PivotTable report, the ability to communicate with an OLAP cube was added to an existing feature, and the user interface sometimes seems more closely adapted to the old features than to the new ones. The interface for an Office PivotTable list was created with OLAP cubes in mind, so the PivotTable list is often a more flexible tool for working with an OLAP cube.
The easiest way to add a PivotTable list to a Web page is to first create a PivotTable report in Excel and then publish the PivotTable report as an interactive Web page.
In an Excel PivotTable report, you interact frequently with dimensions and members but not with levels. Dimension levels appear and disappear as you show or hide detail for members on the report, and the button for the top level is the only one that ever displays a drop-down list. In an Office PivotTable list, you can deal much more directly with levels within a dimension.
1. | In the PivotTable list, expand the Bread member. The children of Bread appear under the Subcategory level heading. None of the subcategory labels shows a plus sign, however, even though the Product dimension contains one more level of detail below Subcategory. If you try to double-click the Bagels member, nothing happens. | |
2. | In the PivotTable Field List window, expand the Product dimension. Select the Product Name level. Then, at the bottom of the field list window, in the Add To drop-down list, select Row Area and click Add To. The Product field appears in the PivotTable list.
| |
3. | Expand the Bread member, and then expand the Bagels member. In an Excel PivotTable report, a level appears on an axis only if members from that level are visible. Showing the children for a member adds the next level if it’s available. In an Office PivotTable list, levels can be added to or removed from an axis, even if no members are visible. You can see only members from levels that are explicitly on an axis. Note The use of levels in a PivotTable list also affects what you see in the dimension tree. In an Excel PivotTable report, selecting or deselecting items in the dimension tree determines whether that item will appear on the axis, and levels are added if necessary. In an Office PivotTable list, selecting or deselecting items in the dimension tree merely filters those items. In other words, deselecting an item hides it in the dimension tree, but only if that item would otherwise be displayed. | |
4. | Drag the Subcategory level label past the edge of the PivotTable list. (A red X appears on the mouse pointer.) This removes the Subcategory level, leaving only the Category level and the Product Name level on the row axis. | |
5. | ||
6. | Drag the Category label off the PivotTable list. The report shows the complete list of products, still arranged in the order of the category and subcategory groups. This is the hierarchy order for the members, and it’s the default order for members of a level even if the parent members aren’t visible. You can override the default sort order. | |
7. | Click the Product Name level label, and activate the PivotTable Property Toolbox. Expand the Sort section of the toolbox, and select Ascending from the Sort Direction drop-down list. You can also sort the list of products in descending order of total sales. Unfortunately, you cannot use the PivotTable Property Toolbox to do this. | |
8. | ||
9. | Select the Sort Descending command a second time to remove the Sort Descending flag for the measure. This sets the sort for the Products back to the default, not to the sort direction set previously. |
In an Excel PivotTable report, you can not have a report that shows all products at only the Product level—sorted or not. An Office PivotTable list gives you more control over how to use levels within a report.
Note
You can export an Office PivotTable list back to an Excel PivotTable report. To do so, click the Export To Excel toolbar button. Interestingly, if you show only selected levels in a PivotTable list and then export that list to Excel, the resulting PivotTable report will include only the selected levels.
An Excel PivotTable report always shows the complete subtotal for a group, even if some of the items are hidden. An Office PivotTable report allows you more control over how to display subtotals.
1. | Click the Time dimension drop-down arrow, select the 1998 check box, and click OK. The total in the Sales Units column for Bagels in 1998 was 984. This is equal to the sum of sales units for the three countries. Hiding the USA column demonstrates how the PivotTable list manages totals. |
2. | |
3. | Click the PivotTable list caption bar (to select the entire PivotTable list). Activate the PivotTable Property Toolbox, and expand the Totals section. Clear the Total All Items check box. The value in the Grand Total column changes to 232, which is the sum of the two visible countries. The asterisk also disappears from the Grand Total label. Note In an Excel PivotTable report, subtotal labels include a trailing asterisk as a default. In the PivotTable Options dialog box, you can turn off the trailing asterisk. In an Excel PivotTable report, however, you can’t make the subtotals match the total of only the visible cells. |
4. | Click the Country label drop-down arrow, select the Show All check box, and click OK to redisplay USA. The total changes to match the sum of the visible cells. Your hypothetical company didn’t start selling products in Mexico until the third quarter of 1998. You can choose how Mexico totals are displayed when no data is available. |
5. | |
6. | With the entire PivotTable list selected, activate the PivotTable Property Toolbox and expand the Display Empty Items section. Select the Column check box. The Mexico member reappears, even though all the cells for the column are blank. |
7. | Clear the Column check box for Display Empty Items. Then Drag the Sales Dollars measure from the Field List window to the data area, to the right of any Sales Units column. The new measure appears in the Columns area. Unlike an Excel PivotTable report, where multiple measures create a new Data dimension, an Office PivotTable list has a property for controlling the orientation of the measures. |
8. | |
9. | Close the browser. |
A PivotTable list gives you a great deal of flexibility in how to deal with measures—or totals, as they’re called in the PivotTable list.
Note
An Excel PivotTable report offers you a greater degree of control over label and measure formatting than an Office PivotTable list. In an Excel PivotTable report, you can apply any cell formatting—including fonts, backgrounds, and custom number formats—to any relevant portion of the report. In an Office PivotTable list, you can apply different fonts or backgrounds to only three groups of items. Changing a single dimension or level label changes all dimension and level labels. Changing a single item label changes all item labels. Changing the font or background for a single measure changes the format of all measures. You can apply a unique number format to each measure, but you must select the format from a predefined list; you cannot apply custom number formats or even change the number of displayed decimal places.
You use a browser such as Microsoft Internet Explorer to display a PivotTable list. Within the browser, you can rearrange dimensions and change formatting. When you close the browser, however, all changes you made are lost. The next time you open the page containing the PivotTable list, the list returns to its original state. To create or define a PivotTable list, you must use an application capable of entering design mode. FrontPage, included in the Premium edition of Office 2000, is capable of designing a PivotTable list.
1. | Start FrontPage. With a blank, new page active, on the Insert menu, point to Component and click Office PivotTable. An empty PivotTable list, showing the text No Data Source Specified, appears. Your first task is to define a data source.
|
2. | |
3. | Under Get To Data Using, click the Connection option and click the Connection Editor button. This dialog box is similar to the Data Link Properties dialog box used in Analysis Manager, except this one defaults to use Microsoft SQL Server as a provider.
|
4. | Click the Provider tab, and select Microsoft OLE DB Provider For OLAP Services 8.0.
|
5. | Click Next, and type localhost or the name of the computer running the Analysis server in the Data Source box. In the Enter The Initial Catalog To Use drop-down list, select the Chapter 5 database. Then click OK. You still need to specify a cube from the database.
|
6. | Under Use Data From in the PivotTable Property Toolbox, click the Data Member option and then select Sales from the drop-down list. The PivotTable list immediately changes to show the drop areas. |
7. | Click the Field List toolbar button. Drag Sales Units to the Totals area, drag Product to the Filter area, drag Time to the Column area, and drag State to the row area. |
8. | Click the Preview tab to see how the control will appear in a browser. Click the Save button, type Market Test as the name of the HTML file, and click Save. Any changes you make to the document while in FrontPage—whether on the Normal tab, the HTML tab, or the Preview tab—will be retained in the HTML file. |
When you want control over the initial layout of a PivotTable list, you need to use a design program to make the changes. An HTML editor such as FrontPage can serve as a design program for the Office PivotTable list.
You might want to create a PivotTable list for others to use. Often, you want to provide some of the functionality of the PivotTable list but not all. For example, you might want to allow users of the list to drill down from a member to its children on the row axis but not move dimensions from one axis to another, change filters, or change formatting. With an Excel PivotTable report, you can write Visual Basic macros to restrict the capabilities of the report, but by using an Office PivotTable list, you can restrict the capabilities without any programming.
1. | In FrontPage, click the Normal tab to change to design mode for the PivotTable list. Customize certain settings before restricting the capabilities of the PivotTable list. |
2. | In the Product drop-down list, select the Bread check box, and click OK. In the Country drop-down list, clear the check boxes for Mexico and Canada, leaving only USA selected, and then click OK. Select the Country caption, and click the Subtotal button in the toolbar to remove the redundant Grand Total row. Drag the Quarter level label from the report, leaving only Year and Month. This is the general structure of the report you’ll allow users to see. |
3. | Click the caption bar of the list (to select the entire list), and click the Property Toolbox toolbar button. Expand the Advanced section. The PivotTable Property Toolbox contains sections in design mode that aren’t available when viewing the list in a browser. |
4. | Clear the Allow Property Toolbox check box. This prevents a user from changing the formatting of the PivotTable list. Select the Lock Filters check box and the Lock Row/Column Fields check box. This prevents a user from moving dimensions from one area to another or displaying items you have filtered. |
5. | In the Maximum Height box, type 480. In the Maximum Width box, type 640. This keeps the PivotTable list from expanding beyond the specified size. If the PivotTable list becomes larger than these dimensions, it will display scroll bars.
|
6. | |
7. | Save the HTML page, and click the Preview tab to see how the PivotTable list will appear to a user. Try expanding and collapsing members. Notice the horizontal scroll bar that appears when you expand years. Try dragging level labels off the list or to a different area. Try displaying values for Meat, Dairy, Mexico, or Canada. |
8. | Close FrontPage. |
As a PivotTable list designer, you can limit the capabilities you allow a user. By using an Office PivotTable list, you can generate a list that provides considerable flexibility—particularly for drilling down to detail levels—without writing any customization code.
Note
If you write Visual Basic code and are familiar with creating event handlers to react to the behavior of users, you might be interested to know that an Office PivotTable list supports events for several user actions. In contrast, an Excel PivotTable report doesn’t have any events.
3.145.40.189