Reporting Options for Analysis Services Cubes: MS FrontPage 2002 - Page 4

May 5, 2003

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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers