OLAP Related Features in Microsoft Excel 2002 and the Microsoft Office XP Web Components

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

 

Frank C. Rice
Microsoft Corporation

May 2002

Applies to:
    Microsoft® Excel 2002
    Microsoft FrontPage® 2002
    Microsoft Office XP Web Components

Summary: How to use the OLAP related features in Microsoft Excel 2002 and the Microsoft Office XP Web Components. (21 printed pages)

Contents

Introduction
Microsoft Excel 2002 OLAP Features
Using PivotTable Reports
Using PivotChart Reports
New OLAP Features in Excel 2002
OLAP Related Object and Collections in Excel
Excel Properties and Methods
Excel Events
Microsoft Office XP Web Components
Using the PivotTable Component in a Web Page
Conclusion

Introduction

Microsoft® Excel 2002 and the Microsoft Office XP Web Components (OWC) provide a number of features that enable powerful analysis and reporting of online analytical processing (OLAP) data. For example, Excel PivotTable® reports allow users to connect to OLAP cubes and other data sources, and interactively view and compare data in a variety of different ways. New objects in Excel 2002, such as the PivotCell object and the CalculatedMember object, provide developers with programmatic access to the data in their OLAP data sources.

The Office XP Web Components make it possible to publish Office documents to the Web while giving users the same interactivity in their documents that they have when they are viewed in their native applications. New features, such as binding to a multidimensional Connection object, creating multidimensional expression (MDX) calculations, and support for member properties can provide effective tools to emphasize issues and opportunities.

This article provides an overview of some of the OLAP related features in Excel 2002 and the Office XP Web Components. We will also demonstrate using a PivotTable component in a Web page to create calculated totals.

Microsoft Excel 2002 OLAP Features

There are a number of features in Excel 2002 that provide support for the analysis and reporting of OLAP data.

Retrieving OLAP Data

You can connect to OLAP data sources just as you do to other external data sources. You can work with databases created with Microsoft SQL Server™ 2000 Analysis Services. Excel can also work with third-party OLAP products that are compatible with OLE DB for OLAP. You can display OLAP data as a PivotTable or PivotChart® report. You can save OLAP PivotTable and PivotChart reports in report templates, and you can create Office Data Connection (.odc) files to connect to OLAP databases and query files (.oqy) for OLAP queries. When you open an .odc or .oqy file, Excel displays a blank PivotTable report, ready for you to drag and drop items onto it for analysis.

Creating Cube Files for Offline Use

You can use the Excel Offline Cube wizard to create files with a subset of the data from an OLAP server database. Offline cube files let you work with OLAP data when you are not connected to your network. You can create cube files only if you use an OLAP provider, such as Analysis Services, which supports this feature.

Creating Cubes from Relational Databases

Another wizard, the OLAP Cube wizard, allows you to organize data queried from relational databases, such as Microsoft SQL Server, into OLAP cubes. This wizard is available from Microsoft Query, which you access from Excel. The cube lets you work with larger amounts of data in a PivotTable or PivotChart report than you could otherwise, and speeds retrieval of the data.

Connecting to an OLAP Data Provider

To connect to OLAP data from Excel, you need one of the following:

An OLAP provider—To set up OLAP data sources for Microsoft Excel, you need one of the following OLAP providers:

  • Microsoft OLAP provider: Excel includes the data source driver and client software you need to access databases created with Analysis Services.
  • Third-party OLAP providers: For other OLAP products, you may need to install additional drivers and client software. To use the Excel features for working with OLAP data, the third-party product must conform to the OLE DB for OLAP standard and be Microsoft Office compatible.

Server databases and cube files—The Excel OLAP client software supports connections to two types of OLAP data sources. If a database on an OLAP server is available on your network, you can retrieve source data from it directly. If you have an offline cube file containing OLAP data or a cube definition file, you can connect to that file and retrieve source data from it.

Data sources—A data source gives you access to all data in the OLAP database or offline cube file. After you have created an OLAP data source, you can base reports on it, and return the OLAP data to Excel in the form of a PivotTable or PivotChart report. You can create a data source while you are using the PivotTable and PivotChart wizard to create a new report, or you can create a data source in Microsoft Query and use it to create reports in Excel.

Microsoft Query—Microsoft Query is an optional Microsoft Office component that you can install and access from Excel. You can use Microsoft Query to retrieve data from an external database such as Microsoft SQL Server or Microsoft Access.

About Offline Cubes

An offline cube file is a file with a .cub extension that stores a portion of the source data from an OLAP server database. This allows you to continue to make changes to PivotTable and PivotChart reports when the server is unavailable or you're disconnected from the network. An offline cube file can speed up changes to your reports, especially if the network connection to the OLAP server is slow. However, if your OLAP database is large and you want the cube file to provide access to a large subset of the data, you'll need to provide ample disk space, and saving the file may be time consuming.

Excel provides two wizards for creating offline cube files. To copy part of the data in an OLAP server database to a cube file, you use the Offline Cube wizard, which you run from your report in Microsoft Excel. To create an OLAP cube from the records retrieved by a query, you use the OLAP Cube wizard, which you run in Microsoft Query.

Differences in Working with OLAP Data in Excel

If you work with PivotTable and PivotChart reports from both OLAP source data and other types of source data, you may notice some feature differences:

Data retrieval—An OLAP server returns new data to Excel every time you change the layout of the report. With other types of external source data, you query for all the source data at once, or you can set options to query only when you display different page field items. You can also refresh the report from the main menu or toolbar.

Note   In PivotTable reports based on OLAP source data, the page field settings are unavailable, background query is unavailable, and the optimize memory setting is not available.

Access to detail data—For OLAP source data, the server determines what levels of detail are available and calculates summary values, so the detail records that make up summary values usually aren't available. The server may, however, provide property fields that you can display. Other types of source data don't have property fields, but you can display the underlying detail for data field values and for items, and you can show items with no data. OLAP page fields may not have an All item, and the Show Pages command is unavailable.

Initial sort order—For OLAP source data, items first appear in the order in which the OLAP server returns them. You can then sort or manually rearrange the items. For other types of source data, the items in a new report first appear sorted in ascending order by item name.

Calculations—OLAP servers provide summarized values directly for a report, so you cannot change the summary functions for data fields. For other types of source data, you can change the summary function for a data field and use multiple summary functions for the same data field. Both types of source data support custom calculations. You cannot create calculated fields or calculated items in reports with OLAP source data. However, you can create calculated members.

Subtotals—In reports with OLAP source data, you cannot change the summary function for subtotals, and you cannot display subtotals for inner row or column fields. With other types of source data, you can change subtotal summary functions and show or hide subtotals for all row and column fields. For OLAP source data, you can include or exclude hidden items when you calculate subtotals and grand totals. For other types of source data, you can include hidden page field items in subtotals, but hidden items in other fields are excluded by default.

Using PivotTable Reports

A PivotTable report is very useful when you want to analyze related totals, such as when you have a long list of numbers to sum and you want to view and compare that data based on several categories. For example, in Figure 1, you can easily compare the total sales amounts of beverages and dairy products for each sales representative, as well as the total sales figures. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages.

Aa140057.odc_xlowcolap01(en-us,office.10).gif

Figure 1. PivotTable report of beverages by quarters

In a PivotTable report, each column or field in your source data becomes a PivotTable field that summarizes multiple rows of information. In the figure above, the Type column in a table contributes data to the Type column of the PivotTable. Additionally, the Salesperson column in the table is rolled up into rows in the Salesperson column of the PivotTable. A data field in the table, such as Sales, provides the values to be summarized and displayed as total sales for each employee.

To create a PivotTable report in Excel, you run the PivotTable and PivotChart wizard. In the wizard, you select the source data you want from your worksheet list or external data source. The wizard then provides you with a worksheet area for the report and a list of the available fields. As you drag the fields from the list window to the outlined areas, Excel summarizes and calculates the report for you automatically.

If you're using an .odc file to retrieve external data for your report, you can return the data directly to a PivotTable report without running the PivotTable and PivotChart wizard. Office Data Connections are the recommended method of retrieving external data for your reports when you don't need to combine data from more than one table in the external database or filter the data to select specific records before creating the report, and for retrieving data from OLAP databases. Once the PivotTable report has been created, you can customize it to fit your needs such as change the layout, change the format, or drill down to display more detailed data.

Using PivotChart Reports

A PivotChart report provides a graphical representation of the data in a PivotTable report. You can change the layout and data displayed in a PivotChart report just as you can in a PivotTable report. Figure 2 shows a PivotTable report side-by-side with a Pivot Chart based on the same data.

Aa140057.odc_xlowcolap02(en-us,office.10).gif

Figure 2. A PivotTable report and a PivotChart report based on the same data

A PivotChart report always has an associated PivotTable report. Both reports have fields that correspond to each other. When you change the position of a field in one report, the corresponding field in the other report also moves. When you create a PivotChart report from a PivotTable report, the layout of the PivotChart report is determined initially by the layout of the PivotTable report. When you create the PivotChart report first, you determine the chart layout by dragging fields from the PivotTable Field List window to the drop areas on the chart sheet. Excel automatically creates an associated PivotTable report that uses a corresponding layout.

There are three ways to create a PivotChart:

  • Starting with a PivotTable report—Make sure your PivotTable report has at least one row field to become the category field in the PivotChart report, and a column field to become the series field. If your PivotTable report is in indented format, move at least one field to the column area before you create the chart.
  • Starting from scratch—In the PivotTable and PivotChart wizard, you specify the type of source data you want to use, and set options for how the data is used. You then lay out the PivotChart report in a manner similar to a PivotTable report. If your workbook doesn't contain a PivotTable report, Excel creates one when you create the PivotChart report. When you change the PivotChart report, its associated PivotTable report changes, and vice versa.
  • Customizing the report—You can change the chart type and other options—such as the titles, the legend placement, the data labels, the chart location, and so on—by using the Chart wizard and commands on the Chart menu.

Using page fields is a convenient way to summarize and quickly focus on a subset of data without having to modify your series and category information. For instance, if you're giving a presentation, you can click (All) in the Year page field to show sales for all years, and then focus on specific years by clicking one year at a time. Each page of your chart has the same category and series layout for different years, so the data for each year can be easily compared. Also, by allowing you to retrieve one page at a time from a large set of data, page fields can conserve memory when your chart uses external source data.

New OLAP Features in Excel 2002

There are a number of new features in Excel 2002 that enhance the use and effectiveness of analyzing your OLAP data by using PivotTable reports. For example, in a PivotTable connected to an OLAP data source, you can select multiple page field items. Page fields allow you to filter the entire PivotTable report to display data for a single item or all the items. Thus you can filter your PivotTable report on one, some, or all of the items in the page field.

You can also create ad hoc groupings in a PivotTable report. For example, let's suppose that you have a PivotTable with product sales by quarter grouped by state. After reviewing the data, you decide that you'd like to see the states grouped by region such as East Coast, Midwest, Central, and West Coast. To do that, you just select the headings for the states that you want in a particular region (Washington, Oregon, and California for the West Coast region, for example), right-click, point to Group and Show Detail, and click Group. Excel creates a new heading called Group1. Then just click on the Group1 heading and enter West Coast. Now you have a new grouping that didn't exist in the original data source.

Excel also has a GETPIVOTDATA function that lets you reference an item in a PivotTable so that the reference is maintained, no matter how you pivot the table. Thus, you can create calculations on your PivotTable data that reference a particular cell without worrying that the results will return an error if the cell moves. What is new in Excel 2002 is that Excel supplies the cell references for you automatically when you create a formula outside the PivotTable.

Excel also has support for displaying member properties in a PivotTable. A member property is a value that is stored in an OLAP database that provides additional information about a particular member. For example, the OLAP database might have a table of employees with each employee listed by name. You might also wish to store additional information about each employee, such as salary, grade, or hire date, in a member property. Excel can access this information stored in the data source and display it in the PivotTable report.

You can also use color in PivotTable reports to emphasize data that meet certain criteria. For example, if sales figures fall within a certain range, use conditional formatting to show data below the range as red, figures within the range as yellow, and figures exceeding the range as green.

Now let's look at some of the new objects, methods, and properties available in Excel 2002 to help you work with OLAP data.

PivotCell object

A PivotCell object represents a cell (a grouping of data) in a PivotTable report. A cell can contain aggregates, and if the underlying detail records are available, you can have the cell display a detail grid. Grouped fields on the row and column axis determine the amount of data that a given cell displays. You can use the PivotCell property of the Range collection to return a PivotCell object. For example, let's assume that you have a worksheet that contains a PivotTable and you want to notify your user when a particular cell (A5 in this example) contains data as opposed to column or row heading information. You could use the following procedure:

Sub CheckPivotCellType()
    On Error GoTo CheckPivotCellType_Err

    ' Determine if cell A5 is a data item in the PivotTable.
    If Application.Range("A5").PivotCell.PivotCellType = 
      xlPivotCellValue Then
        MsgBox "The PivotCell at A5 is a data item."
    Else
        MsgBox "The PivotCell at A5 is not a data item."
    End If

CheckPivotCellType_End:
    Exit Sub

CheckPivotCellType_Err:
    MsgBox "The chosen cell is not in a PivotTable."
    GoTo CheckPivotCellType_End
End Sub

In this procedure, we test the PivotCellType property of the PivotCell object to see if it contains a data value and displays an appropriate message to the user.

CalculatedMembers collection and CalculatedMember object

The CalculatedMember object represents the calculated fields and calculated items for PivotTables from an OLAP data source. The CalculatedMembers is a collection of CalculatedMember objects. The CalculatedMember object supports the Delete method and a number of properties. For example, you can use the following code to check the validity of a calculated field in a PivotTable. Validity is determined based on whether the specified calculated member has been successfully instantiated with the OLAP provider during the current session.

Sub CheckValidity()
    Dim pvtTable As PivotTable
    Dim pvtCache As PivotCache
    On Error GoTo CheckValidity_Err

    Set pvtTable = ActiveSheet.PivotTables(1)
    Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)

    ' Check connection setting and make connection if necessary.
    If pvtCache.IsConnected = False Then
        pvtCache.MakeConnection
    End If

    ' Check if calculated member is valid.
    If pvtTable.CalculatedMembers.Item(1).IsValid = True Then
        MsgBox "The calculated member is valid."
    Else
        MsgBox "The calculated member is not valid."
    End If

CheckValidity_End:
    Exit Sub

CheckValidity_Err:
    MsgBox "The data source is not a valid OLE DB data source."
    GoTo CheckValidity_End
End Sub

PivotItemList collection and the PivotItem object

A PivotItemList collection is a collection of all the PivotItem objects in a PivotTable. Each PivotItem object represents an item in a field in the PivotTable. You can use the RowItems or ColumnItems property of the PivotCell object to return a PivotItemList collection. Once a PivotItemList collection is returned, you can use the Item method to identify a particular PivotItem object. The following example displays the PivotItem object associated with a cell (B5) in a PivotTable to the user:

Sub CheckPivotItemList()
    ' Identify contents associated with PivotItemList.
    MsgBox "Contents associated with cell B5: " & _
        Application.Range("B5").PivotCell.RowItems.Item(1)
End Sub

Excel Properties and Methods

CalculatedMembers property

The CalculatedMembers property returns a CalculatedMembers collection representing all the calculated fields and calculated items for a PivotTable based on an OLAP data source. This property is used only for OLAP data sources; a non-OLAP source will return a run-time error. The following example adds a set to the PivotTable. It assumes a PivotTable exists on the active worksheet that is connected to an OLAP data source which contains a field titled "[Product].[All Products]".

Sub UseCalculatedMember()
    Dim pvtTable As PivotTable

    Set pvtTable = ActiveSheet.PivotTables(1)

    ' Add the calculated member.
    pvtTable.CalculatedMembers.Add Name:="[Beef]", _
        Formula:="'{[Product].[All Products].Children}'", _
        Type:=xlCalculatedSet
    pvtTable.CubeFields.Add "'My Beef Set'","[Beef]"
End Sub

PivotCell property

The PivotCell property returns a PivotCell object that represents a cell in a PivotTable report. The following example determines the name of the PivotTable the PivotCell object is located in and notifies the user. The example assumes that a PivotTable exists on the active worksheet and that cell A3 is located in the PivotTable.

Sub CheckPivotCell()
    'Determine the name of the PivotTable the PivotCell is located in.
    MsgBox "Cell A3 is located in PivotTable: " & _
        Application.Range("A3").PivotCell.Parent
End Sub

AddDataField method

The AddDataField method adds a data field to a PivotTable report and returns a PivotField object that represents the new data field. The following example adds a data field titled "Total Sales" to a pivot table called "PivotTable1".

Sub AddMoreFields()
    With ActiveSheet.PivotTables("PivotTable1")
        .AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Sales"), "Total Sales"
    End With
End Sub

CreateCubeFile method

The CreateCubeFile method creates a cube file from a PivotTable report connected to an OLAP data source. The following example creates a cube file titled CustomCubeFile on drive C:\ with no member properties included in the slice. With the Measures, Levels, and Members arguments omitted from the example, the cube file will match the view of the PivotTable report.

Sub UseCreateCubeFile()
    ActiveSheet.PivotTables(1).CreateCubeFile _
        File:="C:\CustomCubeFile", Properties:=False
End Sub

GetPivotData method

The GetPivotData method returns a Range object with information about a data item in a PivotTable report. In the following example, Excel displays a message to the user of the quantity of chairs in the warehouse. The example assumes that, in the report, the title of the data field is Quantity, a field titled Warehouse exists, and a data item titled Chairs exists in the Warehouse field.

Sub UseGetPivotData()
    Dim rngTableItem As Range
    ' Get PivotData for the quantity of chairs in the warehouse.
    Set rngTableItem = ActiveCell. _
        PivotTable.GetPivotData("Quantity", "Warehouse", "Chairs")
    MsgBox "The quantity of chairs in the warehouse is: " &
      rngTableItem.Value
End Sub

MakeConnection method

The MakeConnection method establishes a connection for the specified PivotTable cache. The MakeConnection method can be used after the cache drops a connection and the user wants to re-establish the connection. Various objects and methods might return a run-time error if the cache is not connected during run time so using this method assures a connection before executing other objects or methods. Excel might drop a connection temporarily in the course of a session without the developer knowing it, so this method can be useful to prevent run-time errors.

Note   This method will result in a run-time error if the MaintainConnection property of the specified PivotTable cache has been set to False or the SourceType property of the specified PivotTable cache has been set to xlExternal.

The following example determines if the cache is connected to a data source and makes a connection to the source, if necessary.

Sub UseMakeConnection()
    Dim pvtCache As PivotCache
    On Error GoTo UseMakeConnection_Err

    Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)

    ' Check connection setting and make connection if necessary.
    If pvtCache.IsConnected = True Then
        MsgBox "The MakeConnection method is not needed."
    Else
        pvtCache.MakeConnection
        MsgBox "A connection has been made."
    End If
UseMakeConnection_End:
    Exit Sub
UseMakeConnection_Err:
    MsgBox "The data source is not an OLE DB data source"
    GoTo UseMakeConnection_End
End Sub

SaveAsODC method

The SaveAsODC method saves the PivotTable cache source as an .odc file. The following example saves the cache source as an .odc file titled ODCFile.

Sub UseSaveAsODC()
    Application.ActiveWorkbook.PivotCaches.Item(1).SaveAsODC 
      ("ODCFile")
End Sub

Excel Events

PivotTableOpenConnection and PivotTableCloseConnection events

The PivotTableOpenConnection event occurs after a PivotTable report opens the connection to its data source. The PivotTableCloseConnection event occurs after a PivotTable report closes the connection to its data source. The following example displays a message stating that the PivotTable report's connection to its source has been opened.

Private Sub ConnectionApp_PivotTableOpenConnection(ByVal Target As 
  PivotTable)
    MsgBox "The PivotTable connection has been opened."
End Sub

The next example displays a message stating that the PivotTable report's connection to its source has been closed.

Private Sub ConnectionApp_PivotTableCloseConnection(ByVal Target As 
  PivotTable)
    MsgBox "The PivotTable connection has been closed."
End Sub

PivotTableUpdate event

The PivotTableUpdate event occurs after a PivotTable report is updated on a worksheet. The following example displays a message stating that the PivotTable report has been updated.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    MsgBox "The PivotTable connection has been updated."
End Sub

SheetPivotTableUpdate event

The SheetPivotTableUpdate event occurs after the sheet of the PivotTable report has been updated. The following example displays a message stating that the sheet of the PivotTable report has been updated.

Private Sub ConnectionApp_SheetPivotTableUpdate(ByVal shOne As Object, 
  Target As PivotTable)
    MsgBox "The SheetPivotTable connection has been updated."
End Sub

WorkbookPivotTableOpenConnection and WorkbookPivotTableCloseConnection events

The WorkbookPivotTableOpenConnection event occurs after a PivotTable report connection has been opened. The WorkbookPivotTableCloseConnection event occurs after a PivotTable report connection has been closed. The following example displays a message stating that the PivotTable report's connection to its source has been opened.

Private Sub ConnectionApp_WorkbookPivotTableOpenConnection(ByVal wbOne
  As Workbook, Target As PivotTable)
    MsgBox "The PivotTable connection has been opened."
End Sub

The next example displays a message stating that the PivotTable report's connection to its source has been closed.

Private Sub ConnectionApp_WorkbookPivotTableCloseConnection(ByVal wbOne
  As Workbook, Target As PivotTable)
    MsgBox "The PivotTable connection has been closed."
End Sub

The preceding collections, objects, properties, events, and methods represent just a few of those available for working with PivotTable reports and OLAP data in Excel 2002. You should explore the Help topics in Excel 2002 and articles on MSDN for more information and examples of more features that you can incorporate into your OLAP solutions.

Microsoft Office XP Web Components

The Microsoft Office XP Web Components make it possible to publish Office documents to the Web while preserving the interactivity the documents have when they are viewed in their native applications. The Office XP Web Components are a collection of Microsoft ActiveX® controls consisting of fully interactive Spreadsheet, Chart, PivotTable, and Data Source components. When users view a Web page that contains an Office XP Web Component, they can interact with the data displayed in that document to sort, filter, add to or change the data, expand and collapse detail views, work with PivotTable lists, and chart the results of their changes. In addition, the Office XP Web Components are fully programmable, which makes it possible for developers to create interactive content for Web-based applications.

The PivotTable component is the control most frequently used to analyze OLAP data. However, the Chart component can be bound to the data in the PivotTable component so that changes in one are reflected in the other. The combination of the PivotTable component and the Chart component can be used to build powerful and effective business intelligence solutions based on OLAP data. Specifically, the PivotTable component provides the following OLAP related features:

Bind to data using a multidimensional Connection object—Now you can use the PivotTable component for an existing connection. This allows developers to modify provider-specific settings on the Connection object before handing it to the component. One can also access the current session used by the control to execute specific statements on the same session.

Use custom MDX to display a PivotTable—With support for custom MDX, developers can handcraft complex MDX and make the PivotTable control use that for its initial query to the OLAP source.

New options for drilling into your data—Drilling down a hierarchy to automatically add the next level is now supported.

Create ad hoc calculated totals—Create ad hoc calculated totals by using the AddCalculatedTotals method.

Display member properties—If information has been associated with a member, the PivotTable component will display it to users. For example, a store might have additional information associated with it like a phone number, the name of the manager, and hours of operation. Cube designers use member properties for information that is related to a member, but should not be used to create additional levels of aggregation. Member properties can also contain statistics produced by the data mining features being built into Analysis Services.

Support for unbalanced and ragged hierarchies—Analysis Services and the OLE DB for OLAP specification defined support for unbalanced and ragged hierarchies. The PivotTable Component supports these new types of hierarchies.

Note   Unbalanced hierarchies occur when some of the branches in a hierarchy descend to different levels. For example, an Organization dimension contains a member for each employee in a company. The CEO is the top member in the hierarchy, and the division managers and executive secretary are immediately beneath the CEO. The division managers have subordinate members but the executive secretary does not. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. For example, a Region dimension contains a level with a USA member and a France member. Directly beneath the USA member is a State member, and directly beneath that is a City member. Looking at the France member, the level directly beneath it is empty because France has no State member. The next level beneath France would be the City member, resulting in a ragged hierarchy.

The Office XP Web Component Toolpack provides a walkthrough of features and is an excellent resource packed with many code samples to get you started. The Office XP Web Components are also included in the Office XP Service Pack 1.

Using the PivotTable Component in a Web Page

To illustrate, let's add a PivotTable Component to a Web page in Microsoft FrontPage® 2002. In this sample, we will first connect to the Sales cube in the FoodMart 2000 multidimensional database which is available with Analysis Services. Then we will create a calculated total. This sample assumes that you have the Office XP Web Components on your computer. It also assumes that you have access to a Web server that contains the Sales cube of the FoodMart 2000 database that comes with SQL Server 2000 Analysis Services.

  1. Start FrontPage 2002.

  2. On the File menu, point to New, and click Page or Web.

  3. Click the HTML tab at the bottom of the new page.

  4. Between the<body>and</body>tags, insert the following code:

    <h1>Calculated Totals Example</h1>
    
    <SELECT id=cbxExprs>
        <OPTION selected value="">(Choose a sample calculation 
          expression)</OPTION>
            <option value="Sum(YTD(),[Measures].[Store Sales])">Year to
              Date Sales</option>
            <option value="([Measures].[Store Sales], 
              Time.PrevMember)">Sales Last Period</option>
            <option value="(ParallelPeriod(), [Measures].[Store 
              Sales])">Sales in Parallel Period</option>
    </SELECT>
    
    <br>
    
    <TEXTAREA rows=4 id=txtExpr wrap="off" style="font-family:Courier 
      New;font-size:10pt;width=100%"></TEXTAREA>
    Number Format: <INPUT type="text" id=txtNumFmt width=20 
      value="$#,##0.00">
    <br>
    <INPUT type="button" value="Create Calculated Total" id=btnCreate>
    </p>
    
    <hr>
    
    <OBJECT CLASSID="clsid:0002E552-0000-0000-C000-000000000046"
      id=ptable>
    </OBJECT>
    
    <script language=vbscript>
    Option Explicit
    
    Sub Window_onLoad()
        Dim sServer
    
        sServer = InputBox("Enter the name of your OLAP Services
          server:", 
          "Connect", "localhost")
        if len(sServer) = 0 then exit sub
    
        ptable.ConnectionString = "provider=msolap;data source=" &
          sServer 
          & ";initial catalog=FoodMart 2000"
        ptable.DataMember = "Sales"
        ptable.ActiveView.DisplayCalculatedMembers = True
    
        Dim view
        set view = ptable.ActiveView 
        view.RowAxis.InsertFieldset view.Fieldsets("Time")
        view.DataAxis.InsertTotal view.Totals("Store Sales")
        view.Totals("Store Sales").NumberFormat = "$#,##0.00"
    End Sub 'Window_onLoad()
    
    Sub btnCreate_onClick()
        Dim sName
        Dim ttl
    
        sName = InputBox("Enter the new calculated total's name:",
          "Create Calculated Total",
            cbxExprs.options(cbxExprs.selectedIndex).text)
        if len(sName) > 0 then
            set ttl = ptable.ActiveView.AddCalculatedTotal(sName, sName,
              txtExpr.value)
            ttl.NumberFormat = txtNumFmt.value
            ptable.ActiveView.DataAxis.InsertTotal ttl
        end if
    End Sub 'btnCreate_onClick()
    
    Sub cbxExprs_onChange()
        txtExpr.value = cbxExprs.value
    End Sub 'cbxExprs_onChange()
    </script>
    
  5. On the File menu, click Save. Type in a file name and save it as a HTML page (with an .htm extension).

  6. Now click the Preview tab at the bottom of the page. Figure 3 shows the PivotTable showing Store Sales and Year to Date Sales.

Aa140057.odc_xlowcolap03(en-us,office.10).gif

Figure 3. PivotTable Component showing Store Sales and Year to Date Sales

Notice the drop-down list where you can select the calculated total you want displayed, the text box which contains the MDX statement that created the calculated total, and the text box that lets you view and change the format of the output.

Now let's examine the code:

...
<h1>Calculated Totals Example</h1>

<SELECT id=cbxExprs>
    <OPTION selected value="">(Choose a sample calculation 
      expression)</OPTION>
        <option value="Sum(YTD(),[Measures].[Store Sales])">Year to
          Date Sales</option>
        <option value="([Measures].[Store Sales],
          Time.PrevMember)">Sales Last Period</option>
        <option value="(ParallelPeriod(), [Measures].[Store 
          Sales])">Sales in Parallel Period</option>
</SELECT>
<br>
<TEXTAREA rows=4 id=txtExpr wrap="off" style="font-family:Courier 
  New;font-size:10pt;width=100%"></TEXTAREA>
Number Format: <INPUT type="text" id=txtNumFmt width=20 
  value="$#,##0.00">
<br>
<INPUT type="button" value="Create Calculated Total" id=btnCreate>
</p>
<hr>
...

The first section of HTML markup code sets up the drop-down list of calculated totals, the MDX statements that are displayed in the text box, and the text box that displays the number format.

...
<OBJECT CLASSID="clsid:0002E552-0000-0000-C000-000000000046" id=ptable>
</OBJECT>
...

The next section contains an<OBJECT>tag, which inserts the PivotTable Component into the page. The ID for the object is ptable and the classid attribute contains the control's CLSID, which is used to uniquely identify the control.

The next section of code is theWindow_onLoadsubroutine which displays an input box that prompts for the OLAP server to connect to.

Sub Window_onLoad()
    Dim sServer

    sServer = InputBox("Enter the name of your OLAP Services server:",
      "Connect", "localhost")
    if len(sServer) = 0 then exit sub

    ptable.ConnectionString = "provider=msolap;data source=" & sServer
      & ";initial catalog=FoodMart 2000"
    ptable.DataMember = "Sales"
    ptable.ActiveView.DisplayCalculatedMembers = True

    Dim view
    set view = ptable.ActiveView
    view.RowAxis.InsertFieldset view.Fieldsets("Time")
    view.DataAxis.InsertTotal view.Totals("Store Sales")
    view.Totals("Store Sales").NumberFormat = "$#,##0.00"
End Sub 'Window_onLoad()

It then connects to the Sales cube in the FoodMart 2000 database. The DisplayCalculatedMembers property is set to True so that the calculated totals can be displayed in the PivotTable. Then we define the axis and totals information, and set the format of the displayed totals.

Sub btnCreate_onClick()
    Dim sName
    Dim ttl

    sName = InputBox("Enter the new calculated total's name:", "Create 
      Calculated Total", cbxExprs.options(cbxExprs.selectedIndex).text)
    if len(sName) > 0 then
        set ttl = ptable.ActiveView.AddCalculatedTotal(sName, sName, 
          txtExpr.value)
        ttl.NumberFormat = txtNumFmt.value 
        ptable.ActiveView.DataAxis.InsertTotal ttl
    end if
End Sub 'btnCreate_onClick()

The btnCreate_onClick procedure prompts for which calculated total to use, adds the calculated total column heading to the PivotTable, displays the number format in the number format text box, and inserts that result of the calculation in the data section of the PivotTable.

Sub cbxExprs_onChange()
    txtExpr.value = cbxExprs.value 
End Sub 'cbxExprs_onChange()

And finally, the cbxExprs_onChange event is triggered when a selection is made in the drop-down list. This event displays the MDX statement representing the drop-down list selection in the txtExpr text box.

Conclusion

In this article, we examined some of the OLAP related features in Excel 2002 and the Office XP Web Components. While this overview is by no means comprehensive, it should give you some ideas as to ways you can use these features for your own data analysis and reporting.