Home > Articles > Data > SQL Server

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.

  1. Start FrontPage 2000 (typically Start, Programs, FrontPage) to display a new, blank page.

  2. Choose Insert, Component, Office PivotTable (see Figure 1). The result of the menu selection is an empty PivotTable list (see Figure 2).

    Figure 1Figure 1 Select Insert, Component, Office PivotTable.


    Figure 2Figure 2 The empty PivotTable list.


  3. Click the Property Toolbox button on the PivotTable list toolbar.

  4. Expand the Data Source section in the Property Toolbox (see Figure 3).

    Figure 3Figure 3 Data Source section of the Property Toolbox.


  5. Select the Connection option.

  6. Click the Connection Editor button.

    The Data Link Properties dialog box appears, displaying the Connection tab.

  7. Click the Provider tab.

  8. Select Microsoft OLE DB Provider for OLAP Services 8.0 (see Figure 4). Then click Next to display the Connection tab.

    Figure 4Figure 4 Data Link Properties dialog box, Provider tab.


  9. Enter localhost as the data source and select FoodMart 2000 as the initial catalog to use (see Figure 5).

    Figure 5Figure 5 Data Link Properties dialog box, Connection tab.


  10. 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 6Figure 6 Test connection succeeded.

  11. Click OK to close the message box and return to the Connection tab of the Data Link Properties dialog box.

  12. Click OK to return to the PivotTable Property Toolbox.

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

  14. 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 7Figure 7 The completed PivotTable Property Toolbox.


    Figure 8Figure 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.

  15. Close the PivotTable Property Toolbox.

  16. Click the Field List toolbar button to display the PivotTable field list (see Figure 9).

    Figure 9Figure 9 The Field List toolbar button initializes the PivotTable field list.

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

  18. Drag the CEO label to the left off the PivotTable list until the red X appears, to remove it from the row labels.

  19. Right-click Level 04 and select Remove Field from the context menu (an alternate means of removing a label).

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

  21. Drag the Store field to the drop area marked Filter Fields, immediately to the right of the Store Type label.

  22. Expand the Department field by clicking the plus (+) sign.

  23. 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 10Figure 10 The PivotTable list emerges.

The 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

  1. Choose File, Save As, and name the file Headcount_Design.htm.

  2. Use the Change button to modify the Page Title to Headcount - Design Model (see Figure 11).

    Figure 11Figure 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.

  3. Click the Preview tab at the lower-left corner of the new page (see Figure 12).

    Figure 12Figure 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.

  4. Click the Normal tab to return to design mode.

  5. Right-click the Senior Management label and select Expand from the context menu.

    The Level column expands to reveal its individual members.

  6. Right-click the Level label and select Expand from the context menu.

    The Level 01 column expands to reveal its individual members.

  7. 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 13Figure 13 Narrowing the scope via filtering.

  8. Right-click the Senior Management label and select Property Toolbox on the context menu.

  9. Click Captions to expand the section.

  10. Change the caption to read Sr Manager, as in the earlier exercise.

  11. Expand the Format section, and change the font to Arial Narrow, with a color of Indigo. Figure 14 shows the results.

    Figure 12Figure 14 Modifications in the Property Toolbox.


  12. Close the Property Toolbox.

    Compare the result set in the PivotTable list to that shown in Figure 15.

    Figure 15Figure 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.

  13. Right-click Jose Bernard in the Level 01 column.

  14. Select Property Toolbox from the context menu.

  15. Expand the Format section of the Property Toolbox, if necessary.

  16. Leave the font as Arial. Change the font color to Dark Teal, and the background to White.

  17. Close the Property Toolbox.

    Compare the result set to that shown in Figure 16.

    Figure 16Figure 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.

  18. Click the Field List toolbar button to display the PivotTable field list.

  19. Drag the Employee Salary measure to the Total or Detail fields drop area, dropping it immediately to the right of the Count measure label.

  20. Right-click the Employee Salary label and select Property Toolbox from the context menu.

    The Property Toolbox appears for the Employee Salary measure.

  21. Change the caption to read Salary (000), to make the salaries measure a bit more meaningful to anyone browsing the PivotTable list.

  22. Select Currency as the number format.

  23. Close the Property Toolbox.

  24. Save the PivotTable list by clicking the Save button on the top toolbar, or by choosing File, Save.

  25. Click to the left side of the PivotTable list, causing the cursor to flash on its left.

  26. Press Enter to move the PivotTable list down.

  27. Click in the space above the PivotTable list, to the left of the paragraph (¶) format symbol.

  28. Type the following text:

    Headcount: Location, Department and Employee Type
  29. Format to taste. (I used Verdana 18-pt bold, and aligned the title and PivotTable list with the Center button in the FrontPage toolbar.)

  30. Save the PivotTable list.

    NOTE

    Remember to save any time you make changes in design mode, to ensure that the changes are retained.

  31. Click the Preview tab to see a PivotTable list that should resemble (in all material respects) the one shown in Figure 17.

    Figure 17Figure 17 Final view of the second Office PivotTable list.

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

  1. Return to design mode by clicking the Normal tab.

  2. Click the Sr Manager drop-down arrow button.

  3. Select All. (Make sure it's checked, versus simply "shaded in.")

  4. Click OK to display the complete set of CEO direct reports/senior managers.

  5. Click either Employee Salary label to select the associated columns. (Both columns should be highlighted.)

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

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

  8. Right-click, and select Property Toolbar on the context menu.

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

  10. 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 18Figure 18 The modified Property Toolbox.


  11. Save the PivotTable list.

  12. Click the Preview tab.

  13. In preview mode, try to perform the actions we have restricted from the browsers of the PivotTable list.

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

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020