Implementing a Quick Pivot User Interface

[Previous] [Next]

This solution offers what I call the Quick Pivot user interface, which helps novice users quickly produce a report. The PivotTable Field List (described in Chapter 4) exposes all totals, fieldsets, and fields defined in the cube; however, this can be quite daunting to someone who has no idea how to begin. Because you can perform all user interface operations through the programming model, you can offer a simpler interface to your users—one that provides them with more direction. The Quick Pivot user interface in this solution enables novice users to quickly begin analyzing data by giving them a sentence-building user interface. When our team talks to customers, many of them say, "I want to see some value by some dimension by some other dimension." The Quick Pivot user interface capitalizes on that natural expression by helping the user construct that sentence. Figure 7-2 shows the Quick Pivot user interface in action.

Note that once the user configures a report using the Quick Pivot user interface, the report is still fully functional, as if the user dragged the various totals, fieldsets, and fields to it. The user can still sort, filter, and rearrange the report as he or she wants. However, the simpler user interface helps get novice users over the blank report hurdle.

Figure 7-2. The Quick Pivot user interface.

Building this user interface in HTML is actually quite easy. Let's start by looking at the code used to fill the various drop-down lists:

 Sub LoadQuickPivotLists(ptable, cbxTotal, cbxRow, cbxCol, cbxFilter)     Dim opt         ' Temporary HTML <option> element     Dim fs          ' Temporary PivotFieldSet reference     Dim ttl         ' Temporary PivotTotal reference          ' Clear the lists of any options besides the     ' blank ones     While cbxTotal.options.length > 1         cbxTotal.options.remove cbxTotal.options.length - 1     Wend     While cbxRow.options.length > 1         cbxRow.options.remove cbxRow.options.length - 1     Wend     While cbxCol.options.length > 1         cbxCol.options.remove cbxCol.options.length - 1     Wend          While cbxFilter.options.length > 1         cbxFilter.options.remove cbxFilter.options.length - 1     Wend          ' Load the Totals list     For Each ttl In ptable.ActiveView.Totals          Set opt = document.createElement("OPTION")         opt.text = ttl.Caption         opt.value = ttl.Name         cbxTotal.options.add opt     Next           ' Load the other lists with all the available fieldsets     For Each fs In ptable.ActiveView.FieldSets         Set opt = document.createElement("OPTION")         opt.text = fs.Caption         opt.value = fs.Name         cbxRow.options.add opt                  Set opt = document.createElement("OPTION")         opt.text = fs.Caption         opt.value = fs.Name         cbxCol.options.add opt         Set opt = document.createElement("OPTION")         opt.text = fs.Caption         opt.value = fs.Name         cbxFilter.options.add opt     Next End Sub 'LoadQuickPivotLists() 

This function starts by clearing the various drop-down lists of all items except the blank one at the top, which is used to indicate that the user wants nothing on that particular axis. The function then loads the Totals drop-down list with the names of the totals exposed through the Totals collection of the ActiveView property. The code ends by loading each of the other three drop-down lists (Rows, Columns, and Filters) with all the available fieldsets, which you can get through the ActiveView property's Fieldsets collection.

This function is called whenever the connection context changes. So when you elect to use an offline cube, the function reloads the available totals and fieldsets because they might be different. (We'll discuss using offline cubes in more detail later in the chapter.) In fact, the cube file on your companion CD has fewer totals and dimensions than the server-based cube that comes with SQL Server OLAP Services. That's because OLAP Services does not download calculated measures or virtual dimensions to cube files. Both calculated measures and virtual dimensions are described in detail in the OLAP Services product documentation.

The real work begins when the user chooses items from the drop-down lists that are now filled and clicks the Pivot button:

 Sub QuickPivot(ptable, ptotal, fsRows, fsCols, fsFilter)     ' Local variables     Dim pview       ' Reference to the view          ' Grab a reference to the view     Set pview = ptable.ActiveView          ' Clear the view     pview.AutoLayout()          ' Put the fsRows dimension on the Row axis     If Len(fsRows) > 0 Then         pview.RowAxis.InsertFieldSet pview.FieldSets(fsRows)     End If          ' Put the fsCols dimension on the Column axis     If Len(fsCols) > 0 Then         pview.ColumnAxis.InsertFieldSet pview.FieldSets(fsCols)     End If          ' Put the fsFilter dimension on the Filter axis     If Len(fsFilter) > 0 Then         pview.FilterAxis.InsertFieldSet pview.FieldSets(fsFilter)     End If          ' Finally, put ptotal in the data area     If Len(ptotal) > 0 Then         pview.DataAxis.InsertTotal pview.Totals(ptotal)     End If          ' Give the PivotTable control an appropriate title     AutoTitlePivot ptable      End Sub 'QuickPivot() 

The Pivot button's Click event handler calls the QuickPivot function, passing the values from the various drop-down lists. The function first clears the current view by calling the AutoLayout method and then adds the specified totals and fieldsets to the appropriate axes. Finally, it calls a method to automatically set the PivotTable control's title based on what is in the view. We'll take a look at that method (AutoTitlePivot) in the next section.