- About the Series
- Continuing the Lesson
- Designing an Office PivotTable List
- Next in This Series
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Designing an Office PivotTable List
An Office PivotTable list must be designed with an application that exposes a design mode—that is, with a package that allows you not only to read the PivotTable list (like a browser), but also to design and/or edit one, and then save your changes. Microsoft FrontPage 2000, which is included in the Premium Edition of Office 2000, (and is sold separately, and is available in other ways), is well suited to the purpose of creating PivotTable lists due to its integration with Office 2000.
Designing with FrontPage 2000
Let's start FrontPage 2000, and set about the PivotTable list design process.
Start FrontPage 2000 (typically Start, Programs, FrontPage) to display a new, blank page.
Choose Insert, Component, Office PivotTable (see Figure 1). The result of the menu selection is an empty PivotTable list (see Figure 2).
Figure 1 Select Insert, Component, Office PivotTable.
Figure 2 The empty PivotTable list.Click the Property Toolbox button on the PivotTable list toolbar.
Expand the Data Source section in the Property Toolbox (see Figure 3).
Figure 3 Data Source section of the Property Toolbox.Select the Connection option.
Click the Connection Editor button.
The Data Link Properties dialog box appears, displaying the Connection tab.
Click the Provider tab.
Select Microsoft OLE DB Provider for OLAP Services 8.0 (see Figure 4). Then click Next to display the Connection tab.
Figure 4 Data Link Properties dialog box, Provider tab.Enter localhost as the data source and select FoodMart 2000 as the initial catalog to use (see Figure 5).
Figure 5 Data Link Properties dialog box, Connection tab.Click the Test Connection button to ascertain that connectivity to the database (called a catalog) is present. The Microsoft Data Link verification message box indicates that the test connection succeeded (see Figure 6).
NOTE
Failure of the verification typically indicates incorrect settings. For example, spelling errors, incorrect capitalization, and the like can cause failed connections. If the setting information is correct and the failure continues, a component may have been missed during installation, or versions of components may be inappropriately matched. Consult the online help, as well as the Microsoft Office web site (most easily reached by accessing choosing Help, Office on the Web in any Office 2000 application), where a searchable database of incidents and their resolution is maintained.
Figure 6 Test connection succeeded.Click OK to close the message box and return to the Connection tab of the Data Link Properties dialog box.
Click OK to return to the PivotTable Property Toolbox.
Select the Data Member option in the Use Data From section of the PivotTable Property Toolbox. The drop-down list opens to display the cubes available for the specified database (that's a catalog in PivotTable list terminology, remember). In this case, the database is FoodMart 2000.
Select HR from the drop-down list (see Figure 7). The "map sections" in the new PivotTable list appear, as shown in Figure 8.
Figure 7 The completed PivotTable Property Toolbox.
Figure 8 The new PivotTable list, with "map sections" in
place.Notice the differences in the appearance of the drop areas (what I often refer to as "map sections"), compared to those of the Excel PivotTable report.
Close the PivotTable Property Toolbox.
Click the Field List toolbar button to display the PivotTable field list (see Figure 9).
Figure 9 The Field List toolbar button initializes the PivotTable field
list.From the PivotTable field list:
Drag the Count field to the drop area marked Total or Detail Fields.
Drag the Pay Type field to the drop area marked Column Fields.
Expand the Time field by clicking the plus (+) sign.
Drag the Year field (beneath the Time field) to the drop area marked Column Fields, immediately to the left of the Pay Type label. The Year and Pay Type labels now appear side by side in the Column Fields section.
Drag the Employees field (and thus the entire Employee hierarchy, as demonstrated in the previous exercise) to the drop area marked Row Fields.
Drag the CEO label to the left off the PivotTable list until the red X appears, to remove it from the row labels.
Right-click Level 04 and select Remove Field from the context menu (an alternate means of removing a label).
Drag the Store Type field to the drop area marked Filter Fields, in the upper-left corner of the PivotTable list, under the title bar.
Drag the Store field to the drop area marked Filter Fields, immediately to the right of the Store Type label.
Expand the Department field by clicking the plus (+) sign.
Drag the Department Description field (beneath the Department field) to the drop area marked Filter Fields, immediately to the right of the Store label.
The Store Type, Store, and Department labels now appear side by side in the Filter Fields section.
Figure 10 shows the results.
Figure 10 The PivotTable list emerges.
)
Figure 1)
Figure 2)
Figure 3)
Figure 4)
Figure 5)
Figure 6)
Figure 7)
Figure 8)
Figure 9)
Figure 10The components are now in place for the Office PivotTable list to be tailored to resemble the PivotTable list we created from the PivotTable report published in the previous section of this tutorial. Next we'll perform the remaining tasks to finish the job, both to explore the design environment, and to review a few previously encountered concepts.
Keep focused on the fact that the lion's share of achieving success in reporting from OLAP data sources rides heavily on two elements:
Collection of the correct data components
Intersection of those components to define the precise values needed by information consumers to meet their reporting requirements
Choose File, Save As, and name the file Headcount_Design.htm.
Use the Change button to modify the Page Title to Headcount - Design Model (see Figure 11).
Figure 11 Saving the PivotTable list design.One obvious benefit of building the PivotTable list from scratch via a design environment such as FrontPage 2000 is that any enhancements or other modifications you make will be saved in the HTML file. Another benefit is the ability to preview your work as it will appear in a browser, at any time during the design process. Let's try that now.
Click the Preview tab at the lower-left corner of the new page (see Figure 12).
Figure 12 Select the Preview tab.NOTE
You can click various selectors and so forth in the preview environment. To keep your changes and retain them in the HTML file, however, design changes need to be made on the Normal (design mode) tab, and then saved.
Click the Normal tab to return to design mode.
Right-click the Senior Management label and select Expand from the context menu.
The Level column expands to reveal its individual members.
Right-click the Level label and select Expand from the context menu.
The Level 01 column expands to reveal its individual members.
Right-click the Derrick Whelply member of the Senior Management column and select Filter by Selection to narrow the scope of the PivotTable list to Whelply and his direct reports. Figure 13 shows the results.
Figure 13 Narrowing the scope via filtering.Right-click the Senior Management label and select Property Toolbox on the context menu.
Click Captions to expand the section.
Change the caption to read Sr Manager, as in the earlier exercise.
Expand the Format section, and change the font to Arial Narrow, with a color of Indigo. Figure 14 shows the results.
Figure 14 Modifications in the Property Toolbox.Close the Property Toolbox.
Compare the result set in the PivotTable list to that shown in Figure 15.
Figure 15 The modified PivotTable list.
We need only a few more changes to render our design a virtual replica of the first Office PivotTable list we created.
Right-click Jose Bernard in the Level 01 column.
Select Property Toolbox from the context menu.
Expand the Format section of the Property Toolbox, if necessary.
Leave the font as Arial. Change the font color to Dark Teal, and the background to White.
Close the Property Toolbox.
Compare the result set to that shown in Figure 16.
Figure 16 Format changes enacted for item labels.
NOTE
Remember that changing formats for any member of a group (in this case, item labels) changes all members of the group simultaneously. We chose Jose Bernard to be consistent with the item formatting steps of the last exercise, but changing any item—or, in this case, any individual in the Employee hierarchy—would have had the same impact on all item formats.
Click the Field List toolbar button to display the PivotTable field list.
Drag the Employee Salary measure to the Total or Detail fields drop area, dropping it immediately to the right of the Count measure label.
Right-click the Employee Salary label and select Property Toolbox from the context menu.
The Property Toolbox appears for the Employee Salary measure.
Change the caption to read Salary (000), to make the salaries measure a bit more meaningful to anyone browsing the PivotTable list.
Select Currency as the number format.
Close the Property Toolbox.
Save the PivotTable list by clicking the Save button on the top toolbar, or by choosing File, Save.
Click to the left side of the PivotTable list, causing the cursor to flash on its left.
Press Enter to move the PivotTable list down.
Click in the space above the PivotTable list, to the left of the paragraph (¶) format symbol.
Type the following text:
Headcount: Location, Department and Employee Type
Format to taste. (I used Verdana 18-pt bold, and aligned the title and PivotTable list with the Center button in the FrontPage toolbar.)
Save the PivotTable list.
NOTE
Remember to save any time you make changes in design mode, to ensure that the changes are retained.
Click the Preview tab to see a PivotTable list that should resemble (in all material respects) the one shown in Figure 17.
Figure 17 Final view of the second Office PivotTable list.
)
Figure 11)
Figure 12)
Figure 13)
Figure 14)
Figure 15)
Figure 16)
Figure 17The vast majority of the browser functionality for our newly designed PivotTable list will operate just as it did in the first exercise; the functionality available to information consumers is based on saved efforts from the design mode.
We could certainly undertake more innovation with the PivotTable list, as there are many more nuances to explore. I hope that the exercises above will serve as a good navigational introduction, and as a jumpstart to further exploration.
Let's conclude this lesson with a final discussion surrounding how to make functionality available to information consumers, and how to restrict what they can accomplish within the scope of the PivotTable list design.
Restricting User Actions Within the PivotTable List
While you can design highly flexible functionality into a PivotTable list, you also have options within the design environment to disable specific capabilities should you deem it in the best interests of information consumers; or within the perspective of the organization, its operating units, and so forth. For example, you might want to provide information in fixed views, or to limit switching of dimensions within the row and column axes while providing only a subset of functionality in the areas of drill-down, or modification of member properties within the PivotTable list. Unlike Excel PivotTable reports, where a restricted set of operations might only be attained with the implementation of VB macros and other programming, the PivotTable list again provides enhanced opportunities to easily build controls into the actions made available to targeted users.
Let's return to the PivotTable list designed in the last exercise to explore some of these options, and to practice the incorporation of these basic restrictions into the design environment.
Return to design mode by clicking the Normal tab.
Click the Sr Manager drop-down arrow button.
Select All. (Make sure it's checked, versus simply "shaded in.")
Click OK to display the complete set of CEO direct reports/senior managers.
Click either Employee Salary label to select the associated columns. (Both columns should be highlighted.)
Right-click the highlighted area and click Remove Total to prevent salaries from being included in the view to be made available to information consumers.
We'll assume for this exercise that the existing PivotTable list represents exactly what we want to be made available to its intended audience.
Select the entire Office PivotTable list by clicking its title bar (the blue bar with HR in white at the top of the PivotTable list).
Right-click, and select Property Toolbar on the context menu.
Expand the Advanced section of the Property Toolbox (visible only in design mode) and make the following selections:
Type 475 in the Maximum Height box and 635 in the Maximum Width box. These setpoints will physically prevent the PivotTable list from expanding beyond the specified boundaries—and from overrunning most current PC screens. If the PivotTable list grows larger than these parameters (due to AutoFit features or for other reasons), a scrollbar will appear so that users can access the entire report, while dimensions are maintained.
Select Lock Filters. With this setting, the targeted user audience cannot override or see beyond filters that are enacted in design mode.
Select Lock Row/Column Fields. With this setting, the user audience cannot add or move column fields; meaning, in effect, that they cannot add or move dimensions within the PivotTable list.
Clear the Allow Property Toolbox check box. With this selection, properties cannot be changed by the user, as the Property Toolbox cannot be accessed from within the browser.
Expand the Show/Hide section of the Property Toolbox (visible only in design mode) and make the following selections:
Click the Toolbar button to deselect it. This hides the Toolbar from the PivotTable list when it's accessed via the browser.
Click the Drop Areas button to deselect it. This hides the drop areas from the PivotTable list when it's accessed via the browser, effectively eliminating the drop option for the targeted user audience.
Figure 18 shows the completed options in the Property Toolbox.
Figure 18 The modified Property Toolbox.Save the PivotTable list.
Click the Preview tab.
In preview mode, try to perform the actions we have restricted from the browsers of the PivotTable list.
)
Figure 18We might have left the salaries columns in place, and instead prevented the user audience from drilling to the individual employee level. But that would have implied settings that delivered very high rollup, as individuals begin "showing numbers" with regard to the salary measure from the top of the Employee hierarchy (after all, it's an HR cube). This would obviously mean virtually no drills to other measures, including headcount, in the simplest sense. Of course, there are many approaches, and the business requirements of information consumers, combined with the security needs of the organization, would certainly be the first priority for any design effort.
There are additional restrictive setpoints that we might have used, including preventing detail data from being displayed, field list and toolbar restrictions, and others. Again, organizational security and information needs typically dictate the setpoints that are appropriate.