The PivotTable List control, a part of the Office XP Web Component package, enables the creation of a tabular display, known as a pivot table, on a Web page. A pivot table is a cross-tabulation report with added functions to simplify the analysis of record sources from databases by users. With the PivotTable List control, users can pivot, or drag, fields between the row, column, and detail sections, which shows individual data items or their summaries, of a report. Another report section explicitly supports filtering, but you can perform filtering from row and column sections with this tool as well. You can also pivot fields between the filter section and any of the other report sections.
This fourth Daily Drill Down in my series on Office XP Web Components illustrates processing techniques for the PivotTable List control. Using the approaches I’ll outline here, you can help your users by relieving them of the burden of making database connections and specifying valid field names for analyses. You’ll also save some of your valuable time because you won’t have to code individual programs for every report that users want.
Where is the control?
The PivotTable List control is a part of the general Help file for Office XP Web Components called owcvb10.chm. This help file provides reference materials, including code samples, for developing solutions with the PivotTable List control.
See this series' first installment for an overview of topics relating to the installation, manual design, and use of Web pages with Office XP Web Components. The second and third installments focus on the spreadsheet and chart components, respectively. These intermediate installments introduce progressively sophisticated programming techniques.
Logging a PivotTable List control into a SQL Server database
As you develop solutions with the PivotTable List control, seek ways to empower the users of the control. Just as with your custom solutions, the user of a pivot table will often be someone other than its creator. A PivotTable List control always works with a data source. Since many users find it difficult to connect to a remote database server properly with valid security settings, you can program this task for them so that it happens automatically. Then, users can proceed to analyze the data in the connection that you make available programmatically.
The following script (Listing A) for a Web page shows the Object tag for a PivotTable List control and a window_onload event procedure that connects the control to the SQL Server Northwind database with one of two connection strings. The event procedure fires whenever a browser initially opens the Web page with the script or when a user forces a refresh of the page. The script designates the Invoices view in the Northwind database as the source for the control. This script enables a user to design his or her custom pivot table based on the Invoices view. The user opens the page with the control and then drags fields from the Field List control that contains column names from the Invoices view into drop areas on the PivotTable List control.
The script opens a message box and prompts the user to confirm whether he or she wants to log in to SQL Server with his or her own Windows user ID. If the user responds affirmatively, the script constructs a connection string for an integrated security login that relies on the user ID for connecting to a Windows server. This kind of connection string can be appropriate for an intranet or an extranet where users routinely identify themselves to a Windows server. If a user answers no to the message prompt, the script constructs a connection string based on a default SQL Server login named pivottableuser. Any member of the SQL Server sysadmin or securityadmin fixed server roles can assign custom permissions to the login and matching SQL Server user account for the Northwind database. This second type of connection string is useful when you want to force all users of an application to have the same permissions. This guarantees that all users will be able to connect to your database with appropriate permissions.
After assigning one of the two connection strings to a memory variable, the script assigns the string to the ConnectionString property of the PivotTable List control. However, this by itself is insufficient to populate the Field List dialog box. This dialog box opens when a user clicks the Field List control on the toolbar for a PivotTable List control. You need to designate a specific database object that returns rows as a record source. One way to accomplish this objective is to assign the database object's name to the DataMember property of the PivotTable object representing a PivotTable List control. In the script (Listing A), PivotTable1 is the name of a pointer, or shortcut, for the PivotTable List control designated by the OBJECT tag in the body section of the Web page. The ID attribute for the OBJECT tag designates the pointer name for the PivotTable List control. The database object's name is Invoices, which is owned by the dbo user.
|The Web page defined by Listing A displayed in a browser window|
Figure A displays the construction of a pivot table on a Web page defined by the code in Listing A. Notice that the PivotTable Field List dialog box shows column names from the Invoices view as fieldset names. Within a fieldset can be one or more fields. You can reference the fields within a fieldset with numeric indexes starting at zero. For a record source from a normal relational database object, most fieldsets will contain a single field named after the fieldset.
The display in Figure A depicts the ShipCountry and ShipperName fields dragged to the row and column drop areas. You can also see the ExtendedPrice field selected while a cursor drags it from the Field List dialog box to the Totals or Detail Fields drop area. After releasing the cursor, the PivotTable List control populates the cells defined by the intersection of the ShipCountry and ShipperName field values with the extended prices of line items in the Invoices view. The table includes no summary statistics—just detail data. A user can decide which additional fields to add and which summary statistics to compute to complete an analysis.
As an IT professional, you won’t typically manipulate the Field List entries to populate the table that a PivotTable List control shows. Instead, you’ll create programs like the one in Listing A that connect a PivotTable List control to a record source. The connection empowers users to generate decision-support reports for their clients with the database assets of an organization.
Specifying custom record sources for the Field List dialog box
Notice that Listing A uses the DataMember property to assign a record source based on a database object to the PivotTable List control. This approach works properly for both tables and views. However, it isn’t appropriate for custom SQL statements.
The need for custom SQL statements can arise from at least two common scenarios. First, an organization may need to perform ad hoc analyses on record sources not represented by a database object. Basing the record source for a PivotTable List control on a SQL string can dramatically enhance the range of analyses possible without burdening IT resources. This approach is particularly appropriate when you have sophisticated users with an understanding of SQL or an organization deploys an IT professional to a business unit. Second, the PivotTable List DataMember property cannot capture a result set directly from a stored procedure. However, you can execute the stored procedure with a short SQL script and capture its result set for use as the source for a PivotTable List control.
When you base the record source for a PivotTable List control on a custom SQL script, you can use the script as the source for an ADO Recordset object. Use the CreateObject function to instantiate a Recordset object and then open it with your custom SQL script as the source for the recordset.
Listing B includes a window_onload event procedure that demonstrates the use of a Recordset object to populate the Field List dialog box for a PivotTable List control. The event procedure demonstrates the use of two custom SQL statements. The first one executes a stored procedure, Ten Most Expensive Products, from the Northwind database. The second prompts with an InputBox function for a custom SQL statement. The function statement specifies a default string in case the user elects not to designate a SQL string. In practice, you may care to use a form with a text box defined on a field type that permits a longer SQL string than the InputBox function allows. The browser's reply to a message box function determines which type of custom SQL statement the procedure uses to populate the Field List dialog box of a PivotTable List control.
Listing B begins with a pair of DIM statements declaring memory variables for the recordset and the SQL string that acts as its source. Next, it defines two constants for use in Open statements for recordsets. The Web server passes the Web page with the window_onload event procedure to a browser. Therefore, it’s appropriate to minimize the size of the file for the Web page. Using Const statements for just the intrinsic constants that the Open statement requires helps to control the file size, but it still makes the Open statement relatively easy to understand and maintain.
After the preliminary steps handled by the Dim and Const statements, the procedure opens a PivotTable List control with a connection to the Northwind database. This opens the PivotTable List control on the page. However, there’s no record source for the control. Therefore, the control indicates that it processed no query.
The procedure attempts to remedy the lack of a query by creating a recordset and assigning it to the PivotTable object's DataSource property. After instantiating a recordset with the CreateObject function, the procedure prompts the user to tell it whether to use the Ten Most Expensive Products stored procedure as a record source. If the user answers yes, the procedure creates a SQL string to execute the stored procedure. Otherwise, the window_onload event procedure issues a second prompt with an InputBox function. The second prompt requests a custom SQL string; it also designates a default one that returns all records from the Customers table. After assigning a value to strSource one way or another, the procedure invokes the Open method for the recordset with strSource as the source argument for the method. If strSource contains a valid SQL statement, the procedure assigns the recordset to the PivotTable List control's DataSource property. This outcome successfully opens the control so a user can employ it. In the case of an invalid SQL statement, the procedure aborts at the Open statement for the recordset.
|A message dialog box that a user can use to specify a record source for a PivotTable List control|
Figure B shows the Web page after the opening of the PivotTable List control but before the user completes specifying a record source. The cursor rests on the No button for the message box prompt. A click at this point opens the second prompt based on the InputBox function. The insertion of a valid custom SQL string into the InputBox dialog box leads to the preparation of a recordset that can serve as the record source for the PivotTable List control in Figure B.
Automatically populating the view of a PivotTable List control
While many users will prefer to populate their pivot tables with values they choose on their own, others will want the pivot table to have a starting set of field assignments for row, column, and detail drop areas. Users can always embellish or totally revise any starting set of values.
To populate the drop areas of a pivot table, you need to reference a PivotView object. This object represents a specific view of a PivotTable List control on a Web page. You can use the PivotView object to access both the PivotTable List control drop areas and the fieldsets in the Field List dialog box. Several axis objects, with a PivotView object as their parent, expose the drop areas of a PivotTable List control. The DataAxis property of a PivotView object returns the PivotDataAxis object. This object represents the Totals or Detail Fields drop area of a PivotTable List control (see Figure B). Likewise, the ColumnAxis and RowAxis properties of a PivotView object return a PivotGroupAxis object. The object model for the PivotTable List control uses the PivotGroupAxis object to denote both the rows and the columns of a report. Use an axis property, such as RowAxis or ColumnAxis, to designate the rows or the columns of a pivot table specifically.
Specifying a fieldset for an axis requires two inputs and a method to tie the inputs together. First, you have to denote the report axis for which you wish to specify a fieldset. The preceding paragraph generally sketches how to accomplish this. Next, apply the InsertFieldSet method to the axis object. Finally, denote the particular fieldset that you wish to assign to an axis. Your code can use the Fieldsets collection of the PivotView object to designate a specific fieldset for assignment to a report axis.
Listing C illustrates the syntax for assigning the ShipCountry, ShipperName, and ExtendedPrice fieldsets to selected axes of a PivotView object on a Web page. The listing contains the window_onload event procedure for the Web page. The code sample begins by connecting a PivotTable List control to the Invoices view of the Northwind database. Next, the sample creates a shortcut (ptView) for the default active view of the PivotTable List control. This shortcut represents a PivotView object. Therefore, the sample can apply the RowAxis, ColumnAxis, and DataAxis properties to the shortcut to denote the row, column, and data drop areas of the PivotTable List control on the Web page. The next three lines demonstrate the precise syntax for adding a fieldset to each of these areas. These lines populate the cells of the PivotTable List control with a starter set of values. Users can build from this beginning or change the fieldset assignments to suit their special requirements.
|A PivotTable List control automatically populated by the event procedure in Listing C|
Figure C shows the Web page that appears based on the event procedure in Listing C. The values in the body of the pivot table represent extended price values for order line items. The rows denote the countries to which orders ship. The columns indicate the shippers used to transport orders to a country. When the number of extended price values is more than shown in a cell, clicking in the cell opens a scroll bar. With the bar, users can scroll through all the extended price values from orders going to a particular country by a specific shipper.
Notice that there aren’t any summary statistics in the report. The code doesn’t specify the creation of any of these. The power of a PivotTable List control is that users can add these according to their own needs. For example, right-clicking ExtendedPrice in any column pops up a menu from which a user can choose AutoCalc Sum to compute aggregate extended price values for each cell, row, and column in the pivot table. Users can format the values in the table with the Command and Options dialog boxes; see the first installment in this series if you require specific instructions.
Summarizing and formatting PivotTable List control values
In addition to exposing data for analysis by end users, the PivotTable List control also supports the publishing of custom reports on Web pages. In fact, you can summarize and format data with a PivotTable List control at the same time that you format the control so that users cannot change your control settings.
The PivotTable List control has built-in features for simplifying data analysis, such as its ability to compute aggregates. In addition, it’s easy to format the data in a pivot table as well as a PivotTable List control. This section demonstrates how to perform these tasks programmatically, but you can also execute them manually. Programming your solution makes it easy to duplicate the same analysis and formatting rules on multiple servers, such as different branch offices throughout an organization. When you publish pivot tables to workstations without a license for Office XP, this is the only way to publish results because you need an Office XP license to manipulate a PivotTable List control.
The window_onload event procedure in Listing D extends the sample in Listing C. The earlier sample connects a PivotTable List control to the Invoices view and populates a pivot table with extended prices for order line items grouped by destination country for the order and shipper transporting the order. However, the report that appears in Figure C, which shows the page resulting from Listing C, includes no analysis. Notice, for example, that the Grand Total column is empty. Moreover, it’s difficult to get a sense of the dollar value of the goods going to a country based on the raw extended prices, especially when there are many of them. Computing the sum of extended prices for the pivot table cells and Grand Total column conveys a better impression of the overall value of goods going to different countries via each shipper and over all shippers.
The code in Listing D is identical to the code in Listing C up until the addition of fieldsets to the row, column, and data axes. After adding the fieldsets, the procedure applies a Currency format to the extended price field values in the body of the table. Although our final report doesn’t show these values, the aggregates that do appear in the table's cells inherit the formatting for their constituents.
After properly formatting the values, the code sample creates a shortcut (ptConstants) that points at the intrinsic constants for the PivotTable object model. This shortcut works similarly to the intrinsic constants for the Chartspace control covered in the last installment in this series. The next line shows a reference to a shortcut that denotes a sum aggregate function (ptConstants.plFunctionSum). The expression on the line specifies with the AddTotal method the sum (of extended price values). The result is a member of the PivotTotals collection. This collection corresponds to the Totals items in the Field List dialog box. The name of the Totals item in the dialog box is Total Sales. The InsertTotal method on the following line adds the new member of the PivotTotals collection to each cell as well as the Grand Total column on the right and the Grand Total row at the bottom of the pivot table. Within the event procedure, the InsertTotal method operates on the shortcut for the aggregate function, totNewTotal.
If the procedure stopped here, the table would show the detail values in each cell. Furthermore, users could manipulate the PivotTable List control and modify the table. However, you can lock in your settings for a PivotTable List control. The last four lines show just four settings from among those that help to secure a published pivot table. The first of the last four lines hides the detail items. The second makes it impossible for a user to change it through the PivotTable List control user interface. The third line suppresses the availability of the Toolbar above the user interface. Users cannot add or remove fields from the report without the toolbar. However, they can still invoke the Commands and Options dialog box by right-clicking on any cell in the table and selecting Commands And Options from the menu that pops up. Setting the AllowPropertyToolbox property to False removes this capability.
|The Web page generated by the code in Listing D|
Figure D presents the pivot table that the event procedure in Listing D generates. The table on the page concisely represents the dollar volume of goods moving to each country by each shipper, as well as the overall dollar volume of goods by shipper and by country. Right-clicking the table still opens a menu, but the menu disables the Commands And Options item. In addition, you can successively remove with other properties and methods all the functionality from the pop-up menu.
The PivotTable List control is for cross-tabulation reports. This Daily Drill Down describes the steps for creating cross-tabulation reports based on record sources in databases. As an IT professional, you can use the PivotTable List control in one of two ways. First, you can create Web pages that contain the PivotTable List control with the table partially composed. This approach relieves end users of making database connections and specifying valid field names for analyses, while it frees IT professionals from coding individual programs for every report that a user seeks. Second, you can complete the formulation of a pivot table, including data formatting and aggregate functions. With this second strategy, you can make pivot tables available to a mix of Office 2000 and Office XP workstations. An Office 2000 workstation requires the installation of the Office XP Web Components, but a workstation without an Office XP license can legitimately run the Office XP Web Components. This deployment strategy can save substantial time and money for large organizations. While the Office 2000 users will be able to see the pivot table on Web pages, they won’t be able to interact with it. You can even suppress the capability to interact with a PivotTable List control for Office XP users if it is desirable to lock in your formatting for a report and settings for a control.