The Pivot Table Web Component

The PivotTable web component brings the PivotTable report capability to your web page, Microsoft Access, or Visual Basic form. With this component, users can easily sort, filter, group, outline, and manipulate data. The PivotTable web component can get its data from a tabular data source (such as a Microsoft Excel spreadsheet, a database table, or an SQL query executed against the SQL Server, Oracle, or Microsoft Access database), an OLAP (multidimensional cube) data source, or an XML stream (see the second example in this section). You can also bind the PivotTable component to the Data Source component (DSC), as shown in the example that follows. To add a PivotTable web component to your web page, place the <OBJECT> tag and specify its class ID, as shown below:

OBJECT classid="clsid:0002E520-0000-0000-C000-000000000046" id= _ "Pivotr height="200" width="600"></OBJECT>

To use the Data Source web component for obtaining data for the PivotTable control, add a Data Source control to your web page, like this:

OBJECT classid="clsid:0002E530-0000-0000-C000-000000000046" id="dsc1"> _ </OBJECT>

Next, use the VBScript code to connect the PivotTable component to the data source:

strConnection="Provider=Microsoft.Jet.OleDB.4.0; data source=" & _

"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb" dsc1.connectionstring = strConnection

Use the AddNew method of the RecordsetDefs collection to add a new recordset definition to the Data Source control and specify the query string that will provide the data:

dsc1.RecordsetDefs.AddNew strSQL, c.dscCommandText,"Pivot1Data"

Next, set the DataSource property of the PivotTable component to an instance of the Data Source control:

Pivot1.DataSource = dsc1

Finally, set the DataMember property of the PivotTable component to the name of the created recordset definition:

Pivot1.DataMember = "Pivot1Data"

Now that the PivotTable component knows how to obtain its data, you can quickly add all the fields in the dataset to the detail area by using the ActiveView.AutoLayout method of the PivotTable object:

Pivot1.ActiveView.AutoLayout

To prepare the web page shown in Figure D-7, open Notepad and enter the HTML and VBScript code shown below. Ensure that the path points to the Northwind database on your server. Save the file as Pivot.htm. You can find this file on the companion CD-ROM.

0 0

Post a comment