WebPivotTable Tutorial

Learning Objectives

The tutorial covers concepts and practice with WebPivotTable. At the completion of this

tutorial, you should have acquired the knowledge and skills to:

 Understand the relationship between pivot tables and data cubes

 Understand the process to design a pivot table and pivot chart

 Using WebPivotTable, create a basic pivot table and revise it with additional features

Pivot Tables

Data warehouse processing requires a multidimensional data representation that is

convenient for business analysts. Data cubes, a widely accepted representation of multi-

dimensional data, support operations for decision support processing by business analysts. In a

data cube, cells contain numeric data called measures while rows and columns contain

dimensions to organize the cells. The notes and video lectures provide conceptual background

about data cubes and details about online analytic processing including WebPivotTable and

Microsoft MDX. Optional reading in Chapters 2.1 and 4.1 of Data Warehouse Essentials extend

the coverage in the notes and video lectures. This tutorial supplements other material with guided

practice about WebPivotTable.

Pivot tables and pivot charts provide a convenient and flexible interface for manipulating

data cubes. Pivot tables display data in rows and columns and allow convenient rearrangement of

the row and column headings. Pivot charts display numerical data graphically to provide insights

to business analysts. Pivot tables support dynamic manipulation of the row and column headings.
 December 16, 2022 WebPivotTable Tutorial Page 2

This section provides practice with pivot tables and pivot charts. This tutorial will use a

free web tool (WebPivotTable) to build the pivot table and chart. The website tool provides

similar functionality and interface as pivot tables in Microsoft Excel.

Adventure Works Cube

WebPivotTable uses the Microsoft Adventure Works cube as a demonstration. The

Adventure Works cube contains 16 dimensions with 47 measures. To use the cube, you should

go to http://webpivottable.com and click on Open in New Window in the middle of the page

(Figure 1). In the Demo page (https://webpivottable.com/releases/latest/demo.html), click on the

Open Dialog button in the top of the page. In the Connect data source window (Figure 2), select

OLAP Mode and then select Sample Microsoft Analysis Service (Figure 3). In the Connect data

source window, select “WIN-IA9HPDV1RU5” for the DataSource, then “Adventure Works DW

Standard Edition” for the Catalog, and finally “Adventure Works” for the Cube Name as shown

in Figure 4. After selecting the Cube Name, the cube opens with panes on the right (Figure 5).

You are now ready to start. When using a cube, WebPivotTable acts as a client connected

to an OLAP server. Since an external OLAP server performs calculations, you may encounter

slow performance or a server error as the pivot table refreshes for operations performed on the

OLAP server.
December 16, 2022 WebPivotTable Tutorial Page 3

Figure 1: WebPivotTable in an Open Window

Figure 2: Connect Data Source Window with Options for Data Sources
December 16, 2022 WebPivotTable Tutorial Page 4

Figure 3: Connect Data Sources Window with OLAP Mode Selected

Figure 4: Connect Data Sources Window with OLAP Mode Options

 December 16, 2022 WebPivotTable Tutorial Page 5

Figure 5: Demo Window after Adding Adventure Works Cube

Pivot Table Terminology

Figures 6 and 7 present a pivot table and pivot chart for the Adventure Works cube. Table

1 provides explanations for the selected parts of Figure 6 along with some additional terms.
December 16, 2022 WebPivotTable Tutorial Page 6

Figure 6: Sample Pivot Table1

Table 1: Terminology for Pivot Tables

Term Meaning
Filters Restricts data that appears in the pivot table. Country
is a filter field. The user can choose to display pivot
table data for selected sizes by choosing a value for
the filter field.
Rows A dimension appearing in the row area of a pivot
table. Product Line and Size Range are row fields.
Columns A dimension appearing in the column area of a pivot
table. Delivery Date.Month is a column field.
Values A measure appearing in the cell area of a pivot table.
The numeric values in the cells are the measure
1
The column labels render using without fixed headers. Fixed headers can truncate column labels.
 December 16, 2022 WebPivotTable Tutorial Page 7

values for the combination of the row and the column

values. For example, $12,568,080.44 is the reseller
sales amount for 38-40 CM size range of All
Products in All Periods.
Chart A chart appears when the chart button ( ) is
selected. The chart represents the values from the
pivot table. See Figure 7.
Fields List Specifies the details of the data cube. Numeric values
are under the Measures. Fields following measures
are dimensions.
Areas The Areas pane below the Fields List specifies the
pivot table with filters, columns, rows, and values.
Field expand/collapse The sign at the left side of each field. The user can
symbol select values to display or hide by clicking the +/-
symbol.
Item A data value within a field. Expanding a field
displays its items, while collapsing a field hides its
items.

Figure 7: Sample Pivot Chart

Creating Pivot Tables

The following steps demonstrate creation of a pivot table in WebPivotTable. After

creating the pivot table, you will modify it and manipulate its data.
 December 16, 2022 WebPivotTable Tutorial Page 8

1. Understand Field Placement: To place a field in an area of a pivot table, select it in the fields

list such as the Product Categories field ( ) in the Product dimension. You

will see a dropdown menu ( ) allowing you to select the area of the pivot

table. Alternatively, you can select the checkbox ( ) by a field (instead of

using the dropdown menu) to place the field in the rows area 2. After using the dropdown

menu, a check appears on the checkbox by the field.

2. Place Fields on the Diagram: Using the method in (1) with the drop down menu, place the

Product Categories field (from the Product dimension) in the Rows area, the Date.Month of

Year field (from the Date dimension) in the Columns area, and the Reseller Sales Amount

field (from the Measures) in the Values area. If you select the check box directly instead of

the drop down menu, you can drag the field from the Rows area to the Columns area. I

dragged it into the Columns area. The worksheet should look like Figure 8.

3. View the Pivot Table as a Pivot Chart: The chart is not shown by default on the page. To

view the pivot chart, select the Switch to Pivot Chart button ( ). Figure 9 shows the pivot

chart. To switch back to the pivot table, select the Switch to Pivot Table button ( ).

4. Configure Pivot Table: Using the current pivot table view, use the collapse symbol so that

only the four (top level) categories are shown in the rows of the pivot table (Figure 10). If

you move the horizontal slider, the row categories are not fixed so that the row labels

disappear.

2
If you receive an error message, you can ignore it.
December 16, 2022 WebPivotTable Tutorial Page 9

Figure 8: Pivot Table with Fields in Areas

December 16, 2022 WebPivotTable Tutorial P a g e 10

Figure 9: Pivot Chart after Selecting Switch to Pivot Chart Button

December 16, 2022 WebPivotTable Tutorial P a g e 11

Figure 10: Pivot Table with Row Labels Collapsed

 December 16, 2022 WebPivotTable Tutorial P a g e 12

Extending the Pivot Table

The pivot table in the previous section is somewhat limited because it has only one

dimension in the rows/columns field. In addition, the pivot table lacks a filter field to restrict the

data displayed. In this section, we add these elements to the previous pivot table and then

demonstrate some additional features of pivot tables. To extend the pivot table of the previous

section, you should follow these steps.

1. Modify Pivot Table Fields: In the Fields List, place the GeographyCountryCountry

field in the Geography dimension in the Rows area. Make sure that the Country is below

Product Categories. Figure 11 shows the revised pivot table.

2. Rollup the Pivot Table: You can drill down or rollup in the rows and columns fields using

the expand/collapse buttons for rows ( , ) or columns ( , ) in the Grid Options tab of

the control panel. Checking the No Fixed Header and Columns item improves the display of

column headings as shown in Figure 12. The remainder of this part of the tutorial does not

use fixed headers.

3. Alternatively, you may drill down/rollup single items by clicking on the sign next to the

item’s name. Configure the pivot table to collapse product categories using the – button as

shown in Figure 13. Note that the + symbol displays after rolling up product categories.

Note that the page may be slow or timeout due to server overhead for computations on the

pivot table.

4. Filter the Pivot Table: Drag the Country field from the Rows area to the Filters area. To set

the filter, select the Country item ( ). In the drop-down list, select Field
 December 16, 2022 WebPivotTable Tutorial P a g e 13

Settings … In the Field List window, select Label if required 3. Uncheck all boxes except

“Canada” and “United States” (Figure 14). Figure 15 shows the pivot table after filtering.

Note reduction in reseller sales amounts after applying the filter. For example, reseller sales

amount for all products in all periods has been reduced to $67,985,726.81 from

$80,450,596.98.

Figure 11: Pivot Table after Adding Size Range and Country

3
If you cannot select Label in the Field List window, open the Field List window using the filter edit icon (

) to the right of the dimension in the Field List.

December 16, 2022 WebPivotTable Tutorial P a g e 14

Figure 12: Column Headings without Fixed Heading

Figure 13: Pivot Table after Rollup of Product Categories without Fixed Headers
December 16, 2022 WebPivotTable Tutorial P a g e 15

Figure 14: Filter By Window with Countries Selected

 December 16, 2022 WebPivotTable Tutorial P a g e 16

Figure 15: Pivot Table after Filtering on Canada and United States

MDX

WebPivotTable supports Microsoft Multidimensional Expressions (MDX) with the

ability to display and execute MDX SELECT statements. You can display the MDX statement

for a pivot table using the MDX button. Figure 16 shows the MDX SELECT statement for the

last pivot table (Figure 15). Because the window is not resizable, the complete code does not

appear. The scroll bar can be used to see the missing part of the code. The missing parts are SET

statements.

You can modify an MDX statement to change the pivot table. Figure 17 shows a

modification of the WHERE clause to replace United States and Canada with France and
 December 16, 2022 WebPivotTable Tutorial P a g e 17

Germany as filters. After you modify the WHERE clause, you can use the Run button to modify

the pivot table. Figure 18 shows the pivot table with the MDX statement after executing the

MDX statement with the Run button.

Figure 16: Partial MDX SELECT Statement

Figure 17: MDX Statement Showing the Revised WHERE Clause

 December 16, 2022 WebPivotTable Tutorial P a g e 18

Figure 18: Pivot Table Showing Executed MDX Statement without Fixed Headers

Memory Mode

This tutorial only demonstrated OLAP Mode in which WebPivotTable acts as a client to

an OLAP server. Since calculations are performed on the OLAP server, WebPivotTable has

some limited features such as lacking support for aggregate calculations. You must setup the data

cube on the OLAP server to perform aggregate calculations. Predetermined aggregate

calculations appear in the Fields List for the Measures. For example, the Fields List of Measures
 December 16, 2022 WebPivotTable Tutorial P a g e 19

for the AdventureWorks cube contains Average Sales Amount, Average Unit Price, and other

aggregate calculations as well as basic measures.

To explore differences between Memory Mode and OLAP Mode, select a new data

source and create a pivot table with the data source. Using Figure 19 as a guide, you should

select Sales Sample Data as an Excel file. Figure 20 shows the empty pivot table.

In memory mode, WebPivotTable provides the calculation engine rather than relying on

an external OLAP server. To demonstrate calculation capabilities in memory mode, configure a

pivot table with City on the rows, Year-Id on the columns, and Sales(Sum) in the Values (Figure

21). To see available aggregate functions, you should select the edit button ( ) for

SALES(Sum) in the Values area. You can use a variety of aggregate functions using the

Summarize Values By and Show Values As lists as shown in Figure 22.

Figure 19: Connect Data Source Window with Excel File Choices
December 16, 2022 WebPivotTable Tutorial P a g e 20

Figure 20: Empty Pivot Table with Sales Sample Data Source
December 16, 2022 WebPivotTable Tutorial P a g e 21

Figure 21: Pivot Table for Sales Sample Data

December 16, 2022 WebPivotTable Tutorial P a g e 22

Figure 22: Edit Window for SALES(Sum) Field