Office Web Component Pivot Table

To present Office data within web pages, Microsoft introduced a set of ActiveX controls called Office Web Components (OWC). The OWC Pivot tables are similar to Excel Pivot tables, but provide a slightly different range of functionality. We will use OWC Pivot tables shipped with Office 2003 for illustrations in this section along with Front , which helps you to create web pages for your end users.

The OWC Pivot table provides end users the added flexibility to analyze data as compared to the Excel Pivot table, but has limited functionality with regards to formatting and reporting. With OWC Pivot tables you can easily identify the top or bottom values it provides you. There is a tight integration between Excel Pivot tables and OWC Pivot tables; they both provide the functionality of exporting data from one to another. There are two ways to create a web page using an OWC pivot table. The easiest way is to export an existing Excel pivot table. The following steps show you how to create an OWC pivot table from an existing pivot table:

1. Create an Excel pivot table with hierarchies and data in Excel. You can use the pivot table you created in the "Analyzing Data using Pivot Tables" section. Say, arranged like Figure 15-12.

2. In the File menu, select Save as Web Page. You will see the Save As dialog as shown in Figure 15-39. Assuming you have IIS (Internet Information Service) installed on your machine navigate to the folder Inetpub\wwwroot in your system directory. Click the Publish button to select the pivot table to publish.

   
   Figure 15-39

3. In the Publish as Web Page screen, select the Pivot table as shown in Figure 15-40. Select Pivot Table under Choose option. Make sure the "Add interactivity with:" check box is enabled under Viewing options and select Pivottable functionality. This option enables you to manipulate the controls of the pivot table in the published web page. Type the file name so that the web page is published at the location shown in Figure 15-40. Click the Publish button.

   
   Figure 15-40

4. The OWC Pivot table within a web page will be opened in Internet Explorer. If your Internet Explorer settings disable ActiveX controls, the OWC Pivot table will be blocked, so you might have to enable ActiveX controls. Also, your internet browser settings might be set to block data being read from different domains. If you see an error within the OWC pivot table that data cannot be accessed across domains, enable the option in your Internet Explorer security settings as shown in Figure 15-41. If you have temporary blockers please enable them so that you can view the OWC Pivot table with the data within internet explorer.

   
   Figure 15-41

5. Once you have enabled all the options, you will see the OWC pivot table with the data as shown in Figure 15-42. You can enable the pivot table field list and command options toolbars by clicking on the last two icons on the OWC Pivot table.

   
   Figure 15-42

   Similar to the Excel Pivot table you can drag and drop measures and dimensions across various axes. In the OWC Pivot table you will see the members along with a "+" next to them. You can click the "+" to drill down to the members at the next level, unlike the Excel pivot table where you will need to double-click on the member. You can do data sorting by clicking the ascending or descending icons in the toolbar of the OWC pivot table. You have the option of including or excluding subtotals or including hidden members in the total similar to Excel Pivot tables. If you right-click the level of a hierarchy you will see the various options available to you. Almost all the functionality available in the Excel Pivot table is also available in the OWC Pivot table. One of the important differences in the OWC Pivot table as compared to Excel Pivot table is the ability to see multiple measures in the data area. The OWC Pivot table extensively uses session calculations for retrieving the data to be displayed. It sends separate MDX queries for retrieving members to be displayed on each axes and the data area. MDX queries generated by OWC are not discussed in this section in detail for the sake of brevity.

6. The OWC Pivot table has an option to display the top N members in a hierarchy. Expand the sales for fiscal year 2002 to the day level. Right-click the Date level and select "Show Only the Top→5." The OWC Pivot table will send an MDX query to retrieve the top 5 members in this level based on the data selected. Figure 15-43 shows the results of the operation.

   
   Figure 15-43

7. Additional functionality offered by the OWC Pivot table is the ability to hide certain levels of a hierarchy. If you only want to show the fiscal year and the month levels of the date.fiscal hierarchy, you can select each level and drag and drop them outside the pivot table. This will result in hiding the levels in your OWC pivot table. Figure 15-44 shows a pivot table that includes only the fiscal year and month levels.

   
   Figure 15-44

8. When you create the OWC Pivot table and publish the Web page you can specify multiple behavioral options which will impact the browsing experience for end users. The Behavior tab in the Command and Options dialog of the OWC Pivot table allows you to control expansion of items and expansion of details. Figure 15-45 shows the various options available to you.

Figure 15-45

The second way to create an OWC Pivot table is to include the component within a web page. Microsoft's Front Page allows you to graphically design web pages. We will use Front 2003 to illustrate the creation of an OWC pivot table. The following steps show how to create an OWC Pivot table within a web page and retrieve data from Analysis Services 2005:

1. Launch Front 2003 and select New from the File menu. Create a new blank page.

2. From the Insert menu select Web Component. You will see the Insert Web Component dialog as shown in Figure 15-46. Select the spreadsheets and charts component type and choose the Office PivotTable control.

   
   Figure 15-46

3. You will see the OWC pivot table that was shown in Figure 15-42 inserted into your html page, as shown in Figure 15-47.

   
   Figure 15-47

4. Click on the "Click here to connect to data" link. You will see the Commands and Options dialog shown in Figure 15-48.

   
   Figure 15-48

5. Click the Edit button to specify the connection details to Analysis Services 2005. You will now see the Select Data Source dialog. You will see the data source connection you established to the AdventureWorks database while working with Excel Pivot tables. You can select from one of the data sources available in this dialog or create a new data source connection to an Analysis Services database. Select the existing connection and click OK. In the commands and options dialog select the cube Adventure Works as shown in Figure 15-48. You can now close the commands and options dialog.

6. At this point you have established a connection to the Analysis Services database and you can now analyze data within the pivot table by dragging and dropping dimensions and measures from the pivot table field list. You can now create your OWC pivot layout with appropriate protection and publish this web page to your end users.

7. You have now learnt to create web pages that include OWC pivot tables that can be published on your IIS so that your end users can analyze data. In the next section you will look at another product from Microsoft called Data Analyzer that helps in analyzing data from Analysis Services.