Reporting Options for Analysis Services Cubes: MS FrontPage 2002

Monday May 5th 2003 by William Pearson
Share:

Create reports directly from an OLAP cube with an Office PivotTable List: In this tutorial, we will design and build a PivotTable List within Microsoft FrontPage 2002, and focus upon "natural" PivotTable List strengths, including made-for-web robustness and control over user capabilities.

About the Series ...

This is the eleventh article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.

In addition to MSSQL Server 2000 and MSSQL Server 2000 Analysis Services, of which we have made repeated use in the previous articles of the series, additional application considerations apply for this tutorial because it introduces another MS Office component, FrontPage 2002. For those joining the series at this point because of a desire to work with Analysis Services and its components from a FrontPage perspective, it is assumed that Analysis Services is accessible to / installed on the PC, with the appropriate access rights to the sample cubes (which are provided in a Typical installation of Analysis Services). In addition, we will assume that we are performing all steps on a Windows 2000 - family PC, although the steps will be quite similar with later operating systems.

For more information on the hardware/software requirements to prepare for this tutorial, see our last article, Reporting Options for Analysis Services Cubes: MS Excel 2002.

Introduction

While the majority of the articles of our series to date have focused upon the design and creation of cubes within Analysis Services (see Articles One through Nine of the Introduction to MSSQL Server 2000 Analysis Services series), we began in Article Ten to discuss reporting options for our cubes. This comes in response to the expressed need of several readers for options in this regard - options beyond the mere browse capabilities within Analysis Services.

In our last lesson, Reporting Options for Analysis Services Cubes: MS Excel 2002, we presented an introduction to using Excel PivotTable Reports to retrieve and display information from an OLAP cube. We exposed PivotTable Report features that are available with Microsoft Excel 2002 (most of which were available in Excel 2000) for creating robust and flexible reports. We explored setting up a connection to an OLAP cube, creation of the PivotTable Report, and the general navigation of member information and cube data. We practiced drilling down to (and zooming up from) details of reporting summaries, then exposed the use of intersected dimensions to make the PivotTable Report truly multidimensional. We discussed a few formatting options at relevant junctures in our exploration of the Excel 2002 PivotTable Report.

In this article we will focus our exploration on Office PivotTable Lists; we will design the reporting mechanism from the ground up, using Microsoft FrontPage as the design environment. We will expose various options available to the PivotTable List designer to control the capabilities afforded to the information consumer, specifically through placing restrictive setpoints in design mode, and enforcing those setpoints in the browser through which information consumers access the PivotTable List.

Reporting Options for Analysis Services Cubes: The Office PivotTable List and FrontPage 2002

In this lesson we will continue our exploration of reporting options for Analysis Services cubes. This has become a popular topic, which, as I mentioned in Article Nine, generates many e-mails each week in my inbox. As I have mentioned before, my focus is to offer options for obtaining reports from Analysis Services cubes within a range of similar business intelligence capabilities. My intent is therefore to review the process of establishing connectivity and enabling reporting capabilities for each of the options, and not to compare the product features themselves, to any significant extent. As I also stated in the last article, I will consider providing a similar examination of other reporting products at a later time, based upon any suggestions I receive from readers, if this turns out to be useful.

In this tutorial, we will extend our exploration into the use of MS Office applications for cube reporting to an examination of some of the options offered by the Office PivotTable List (the FrontPage "equivalent" of the Excel PivotTable Report) for report building with Analysis Services cubes. As we did with the Excel PivotTable Report in the first of our Reporting Options articles, we will present an introduction to designing PivotTable Lists in FrontPage 2002, to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. Next, we will expose the layout of the PivotTable List and its navigation, and explore its use in browsing and reporting our cube data. We will discuss the nesting of dimensions in PivotTable Report axes to achieve multidimensional reporting within the classical two-dimensional presentation of print media and PC screens, as well as general formatting considerations at relevant points in the tutorial.

Our objectives in the article include:

  • An introduction to the features that are available within Office PivotTable List, and the options and capabilities therein that provide for creating robust and flexible reports;
  • A practical walkthrough of the setup of Microsoft FrontPage 2002 connectivity and other preparatory steps;
  • Exploration of additional facets of the basic use and navigation of the various components that comprise PivotTable List functionality;
  • A focus on designing a PivotTable List, "from the ground up," to provide flexibility in information delivery to meet business needs;
  • A discussion of some of the options for the incorporation of controls over the capabilities afforded to information consumers.

Introduction to the Office PivotTable List

While multiple options exist for creating a PivotTable List, one of which involves starting by creating a PivotTable Report in Excel (complete with data source setup), and then transforming it into a PivotTable List, we will focus upon the design and creation of a PivotTable List "from scratch" in this lesson. We will use FrontPage 2002 as the application from which we can not only to read the PivotTable List (just as a browser would), but from which we can also design and/or edit the PivotTable List and apply our changes for immediate effect. Microsoft FrontPage 2002, which is included in the Premium Edition of Office XP, (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 XP.

Designing in FrontPage 2002

Let's start FrontPage 2002, and set about the PivotTable List design process.

1.      Click the Start button.

2.      Select Programs -` FrontPage 2002.

FrontPage 2002 opens, and a new, blank page appears. (If not, simply click the "New" button to create a new page).

3.      Choose Insert from the top menu.

4.      Select Web Component, as shown in Illustration 1.


Illustration 1: Select Web Component

The Insert Web Component selection dialog appears.

5.      Under Component Type (left pane of the dialog), click Spreadsheets and Charts.

6.      From the Choose a Control options that appear in the right pane, select Office PivotTable as shown in Illustration 2 below.


Illustration 2: Selecting the Office Pivot Table Control

7.      Click Finish to enact our selections.

The empty Office PivotTable List appears in the new page, as shown in Illustration 3.


Illustration 3: The Empty PivotTable List

For the new PivotTable List, we need to establish a connection with our source data, an Analysis Services cube.

8.      Click the link, labeled Click here to connect to data that is centered on the lower portion of the new PivotTable List.

The Commands and Options dialog appears, displaying the Data Source tab.

9.      Select the Connection option by clicking the radio button alongside Connection in the Get Data Using section of the dialog.

The Commands and Options dialog- Data Source tab appears as shown in Illustration 4.


Illustration 4: The Commands and Options Dialog

10.  Click the Edit button to the right of the Connection box.

The Select Data Source dialog box appears.

11.  Click-select the +Connect to a New Data Source option, as shown in Illustration 5.


Illustration 5: The Select Data Source Dialog

12.  Click the Open button.

The Data Connection Wizard is initialized.

13.  Select Microsoft SQL Server OLAP Services, as shown in Illustration 6.


Illustration 6: Select Microsoft SQL Server OLAP Services

14.  Click Next.

The Data Connection Wizard - Connect to Database Server dialog appears.

15.  Type in the Server name (mine is MOTHER in this illustration).

16.  Choose the appropriate log on credentials for the local environment.

The Data Connection Wizard - Connect to Database Server dialog appears as shown in illustration 7.


Illustration 7: The Completed Data Connection Wizard - Connect to Database Server Dialog

17.  Select Next.

The Data Connection Wizard - Choose Data dialog appears.

18.  Select FoodMart 2000 in the "...database that you want" selector.

19.  Ensure that the Connect to a Specific Cube or Table checkbox is checked.

20.  Select the HR cube in the selection list below.

The completed Data Connection Wizard - Choose Data dialog appears as shown in Illustration 8.


Illustration 8: The Completed Data Connection Wizard - Choose Data Dialog

21.  Click Next.

The Data Connection Wizard - Finish dialog appears.

22.  Leave the File Name at default.

23.  Type FoodMart 2000 - HR Salary Cube in the Description box, if necessary.

The Data Connection Wizard - Finish dialog should now resemble that shown in Illustration 9.


Illustration 9: The Completed Data Connection Wizard - Finish Dialog

24.  Click the Finish button.

We are returned to the Commands and Options dialog- Data Source tab, which appears as depicted in Illustration 10.


Illustration 10: The Commands and Options Dialog

Our PivotTable List is now connected to the HR Analysis Services cube. We can begin at this point to further the design of the PivotTable List in various ways, and to build reports. The "map sections" in the new PivotTable List appear, as shown in Figure 11, indicating drop points for filter, column, row, and totals / details fields in the PivotTable List. (Several differences exist in the appearance of the drop areas we see and those we saw in the Excel PivotTable Report in Lesson Ten.)

25.  Click the PivotTable Field List button in the PivotTable List Toolbar, shown in Illustration 11.


Illustration 11: The PivotTable List Toolbar

The PivotTable Field List appears, as shown in Illustration 12. It is from this field list that we obtain the items that we drag and drop into our PivotTable List.


Illustration 12: The PivotTable Field List

26.  From the PivotTable Field List:

  1. Drag the Count field to the drop area marked Total or Detail Fields (also known as the Data Area).
  2. Drag the Pay Type field to the drop area marked Column Fields.
  3. Expand the Time field by clicking the "+" sign to its immediate left.
  4. 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, as shown in Illustration 13.


    Illustration 13: The Initial PivotTable List

  5. Drag the Employees field (and thus the entire Employee hierarchy) to the drop area marked Row Fields.

27.  Right-click the CEO label.

28.  Select Expand from the context menu that appears.

The Senior Management level appears to the right of the CEO level.

29.  Right-click the Senior Management label.

30.  Select Expand Items from the context menu that appears.

The Level level (the naming convention in the cube is a bit confusing here) appears to the right of the Senior Management level.

31.  Right-click each label in the Employee hierarchy in turn, and click Expand Items, until we reach the Level 4 level.

32.  Click, highlight, and drag the CEO level off the Pivot Table List, dragging it left until a red "X" -like (the Delete) icon appears.

33.  Drop the CEO level to delete it, while leaving the remaining columns in place.

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

The PivotTable List appears as shown in Illustration 14.


Illustration 14: The PivotTable Field List (Partial View)

35.  From the PivotTable field List:

  1. 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.
  2. Drag the Store field to the drop area marked Filter Fields, immediately to the right of the Store Type label.
  3. Expand the Department field by clicking the "+" sign to its left.
  4. 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, as shown in Illustration 15.


Illustration 15: The PivotTable Field List with Further Modifications (Partial View)

36.  Choose File --> Save As from the top menu.

37.  Name the file Headcount_Report.htm.

38.  Use the Change button to modify the Page Title to Headcount - Report Design 1, as depicted in Illustration 16.


Illustration 16: Save As, and Changing the Page Title

39.  Save the file, leaving it open for the next section.

Next we'll perform a few remaining tasks to finish the PivotTable List example, both to explore the design environment, and to review a few concepts we encountered with the Excel PivotTable Report.

Polishing Our Design in FrontPage 2002

As report developers, regardless of the medium in which we develop any given report, we should keep focused on the fact that the lion's share of achieving success in reporting from OLAP data sources rides heavily on two elements:

  • The collection of the correct data components; and
  • The intersection of those components to define the precise values needed by information consumers to meet their reporting requirements.

One obvious benefit of building the PivotTable List from scratch via the FrontPage 2002 design environment is that any enhancements or other modifications we make will be saved in the HTML file. Another benefit is the capability that FrontPage 2002 provides us to preview your work as it will appear in a browser, at any time during the design process. Let's try that now.

1.      Click the Preview tab at the lower-left corner of the PivotTable List we just saved (see Illustration 17 below).


Illustration 17: Select the Preview Tab in FrontPage 2002

Note that we may receive the warning message shown in Illustration 18, depending upon the environment in which we are developing.


Illustration 18: Warning Message, with Instructions re: Avoiding Same in the Future

2.      Click Yes to bypass the message for now (and if it appears again at any point in this lesson), and to close the message box.

We arrive at the Preview screen, seeing the PivotTable List just as we would from a browser, in most respects. We can click various selectors and make various on-the-fly modifications, and so forth, to the PivotTable List in the Preview environment. We need to keep in mind, however, that, to make our modifications permanent, and to retain them in the HTML file we created earlier, design changes need to be made on the Normal (design mode) tab, and then saved.

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

4.      Right-click the Level 03 label.

5.      Click Collapse Items in the context menu that appears.

6.      Right-click the Level 02 label.

7.      Click Collapse Items in the context menu that appears.

8.      Right-click the Level 01 label.

9.      Click Collapse Items in the context menu that appears.

10.  Right-click the Derrick Whelply member of the Senior Management column, and select Filter by Selection.

As we see in Illustration 19 below, the Filter by Selection action narrows the scope of the PivotTable List to Whelply and his direct reports.


Illustration 19: Filtering by Selection to Narrow Scope Easily

11.  Right-click the Senior Management label.

12.  Select Commands and Options on the context menu.

The Commands and Options dialog appears.

13.  Click the Format tab, if necessary.

14.  Change the Font to Arial Narrow, with a Color of Indigo.

NOTE: Indigo is the color selection to the furthest right, in the top row of the color palate - the tool tip that appears when we touch the color swatch shows the name of the color.

The Commands and Options dialog - Format tab appears, with our changes, as shown in Illustration 20.


Illustration 20: The Commands and Options Dialog - Format Tab

15.  Click the Captions tab.

16.  Change the caption to read Sr Manager.

17.  Make the Font Arial, Bold and Indigo.

The Commands and Options dialog - Captions tab appears, with our changes, as shown in Illustration 21.


Illustration 21: The Commands and Options Dialog - Captions Tab

18.  Close the Commands and Options dialog, leaving all other setpoints as before.

The result set in the PivotTable List should resemble that shown in Illustration 22.


Illustration 22: The Pivot Table List - After Formatting Modifications

Formatting virtually any PivotTable List component is just this simple. We simply need to remember that changing formats for any member of a group (in our case, labels) changes all members of the group simultaneously.

19.  Click the Pivot Table Field List toolbar button to display the PivotTable Field List.

20.  Drag the Employee Salary measure (under Totals in the tree) to the Total or Detail fields drop area, dropping it to the immediate right of the Count measure label.

21.  Right-click the Employee Salary label and select Commands and Options from the context menu.

The Commands and Options dialog appears for the Employee Salary measure.

22.  Change the caption (Captions tab) to read Salary (000), to make the Employee Salary measure perhaps a bit more meaningful to anyone reviewing the PivotTable List.

23.  Select Currency as the Number format (Format tab).

24.  Close the Commands and Options dialog.

25.  Save the PivotTable List by clicking the Save button on the top toolbar, or by choosing File -` Save.

26.  Click to the left side of the PivotTable List, causing the cursor to flash on its left.

27.  Press Enter to move the PivotTable List down.

28.  Click in the space above the PivotTable List.

29.  Type the following text:

Headcount and Salary: Location, Department and Employees

30.  Format to taste.

(I used Verdana 18-pt Bold, and aligned the title and PivotTable List with the Center button in the FrontPage toolbar.)

31.  Save the PivotTable List, leaving it open for the remainder of the lesson.

NOTE: Always remember to save any time changes are made in design mode, to ensure that the changes are retained.

32.  Click the Preview tab to see a PivotTable List that should resemble (in all material respects) the one shown in Illustration 23.


Illustration 23: The Pivot Table List - Final View

The majority of the browser functionality that is available to information consumers from our newly designed PivotTable List is based upon our saved efforts from the design mode. We will see even more how this is true in the following section.

Far more innovation can be undertaken with the PivotTable List, and 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 we can design highly flexible functionality into a PivotTable List, we also have options within the design environment to disable specific capabilities, should we deem it in the best interests of information consumers, or from within the perspective of the organization, its operating units, and so forth. For example, we 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 generally be attained with the implementation of VB macros, other programming, and other "workarounds," 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"), as shown in Illustration 24 below.


Illustration 24: Select All at the Sr Manager Dropdown Arrow

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

5.      Click either Salary (000) (previously Employee Salary) label to select the associated columns.

Both Salary (000) columns should be highlighted.

6.      Right-click the highlighted area and click Remove Total (as shown in Illustration 25) to prevent salaries from being included in the view to be made available to information consumers.


Illustration 25: Select Remove Total on the Context Menu

We'll now assume for this exercise that the currently displayed PivotTable List represents exactly what we want to be made available to its intended audience.

7.      Select the entire PivotTable List by clicking the Report Title Bar (the blue bar with Microsoft Office PivotTable 10.0 in white, atop the PivotTable List).

8.      Right-click, and select Commands and Options on the context menu.

The Commands and Options dialog appears, defaulting at the Captions tab.

9.  Ensure the Select Caption selector remains at Report Title Bar.

10.  In the Caption field, type in Headcount and Salary Data.

11.  Change the Font to Arial Narrow, with a Color of SkyBlue (the first color swatch in the fourth line down in the color selection palette.)

The Commands and Options dialog - Captions tab appears, with our changes, as shown in Illustration 26.


Illustration 26: The Commands and Options Dialog - Captions Tab

12.      Click the Behavior tab in the Commands and Options dialog and make the following selections:

  1. Type "22000" in the Maximum Height box.
  2. Type "22000" in the Maximum Width box.

These setpoints (the ones above are simply examples) can be used to physically prevent the PivotTable List from expanding beyond the specified boundaries and from overrunning PC screens, and other such, well, behavior. 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 visual dimensions are maintained. We can also take advantage of various Show / Hide capabilities here, as we can see with an examination of the Behavior tab. The Commands and Options dialog - Behavior tab appears in Illustration 27 below.


Illustration 27: The Commands and Options Dialog - Behavior Tab

13.  Click the Protection tab (visible only in design mode) in the Commands and Options dialog and make the following selections:

  1. Clear the Filter check box.

    With this setting, the targeted user audience cannot add, change, override or see beyond filters that are enacted in design mode.

  2. Clear the Group and Change Layout check box.

    Clearing this checkbox means the targeted users cannot group or change layouts beyond those enacted in design mode.

  3. Clear the Edit Detail Data check box.

    This setting prevents users from modifying detail data in the PivotTable List.

  4. Clear the Delete Detail Rows check box.

Clearing this checkbox prevents users from deleting detail rows in the PivotTable List.

Our modifications to the Protection tab settings appear in Illustration 28.


Illustration 28: The Commands and Options Dialog - Protection Tab

14.  Close the Commands and Options dialog to save changes.

Other Protection features exist, each of which is somewhat self-evident in the control that it affords. It is useful to realize that if we clear the last of the list of checkboxes, the Commands and Options Window in run mode check box, the Commands and Options button remains available on the toolbar at any time we are in design mode for the PivotTable List.

It is important the fact that, in Microsoft Excel, the restrictions we have explored above 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 - a potentially compromising scenario. To prevent users from accessing the detail data in this way, we can restrict access to the Export to Microsoft Excel command in the browser by turning off the toolbar, by restricting access to the Commands and Options dialog box (unchecking the appropriate checkbox on the Protection tab), and taking action to hide the shortcut menus.

For more information on any of these features, consult the online documentation for the Office PivotTable List.

15.  Save the PivotTable List.

16.  Click the Preview tab.

17.  In Preview mode, attempt 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. However, 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 drill downs 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 a high priority within any design effort.

There are additional restrictive setpoints that we might have used, some of which we have touched upon earlier. Again, organizational security and information needs typically dictate the setpoints that are appropriate.

Next in Our Series ...

In this lesson, Reporting Options for Analysis Services Cubes: MS FrontPage 2002, we explored some of the options offered by the Office PivotTable List for report building with Analysis Services cubes. As we did with the Excel PivotTable Report in the first of our Reporting Options articles (Article Ten), we presented an introduction to using FrontPage to retrieve and display information from our cubes, first discussing the steps needed to define our data source, and to establish a connection to the cube. We exposed the functionality involved in using the PivotTable List, its physical layout, and aspects of its general navigation. We then explored the use of the PivotTable List in practice browsing / reporting scenarios with Analysis Services cube data, discussing advantages in architecture where appropriate. Finally we discussed various presentation and formatting considerations for PivotTable List reporting, as well as features that allow us to control the actions of information consumers with the PivotTable List.

Our next lesson will explore yet another option for reporting from Analysis Services cubes. In Lesson Twelve, Reporting Options for Analysis Services Cubes: Cognos PowerPlay, we will examine some of the options offered by Cognos PowerPlay for report building. As we did with the MS Office reporting options in this and the previous Reporting Options article, we will present an introduction to using Cognos PowerPlay to retrieve and display information from our cubes, discussing in order the steps needed to set the application up specifically for accessing a non-Cognos (e.g., Analysis Services) cube, then exposing the steps required for the definition of our data source, and for the establishment of a connection to the cube from two different perspectives. Next, we will briefly examine the layout navigation of the two reporting approaches that PowerPlay offers, and practice examples of browsing and reporting our cube data from each.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved