神马文学网Programming the Pivottable control with MS VB 6.0
来源：百度文库 编辑：神马文学网 时间：2024/04/27 13:57:06
The information in this article applies to:
- MS VB 6.0 SP 6
- Office Web Components (OWC)
If we intend to use an ADO-classic approach to populate the control with a Recordset then one way is to use a disconnected Recordset like the following snippet code example shows:
With cnt
.CursorLocation = adUseClient
.Open stCon
Set rst = .Execute(stSQL)
Set rst.ActiveConnection = Nothing
.Close
End With
ptTable.DataSource = rst
For demonstration purpose the example use the “OLAP-approach” which means that the control is directly connected to the source during the session.
Option Explicit
Function Setup_PivotTable()
Const stCon As String = _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;Initial Catalog=Northwind;" & _
"Data Source=IBM"
Const stSQL As String = _
"SELECT ShipCountry, Freight, ShipVia, " & _
"ShippedDate AS Year " & _
"FROM Orders;"
Dim ptTable As OWC11.PivotTable
Dim ptView As OWC11.PivotView
Dim ptFsets As OWC11.PivotFieldSets
Dim ptField As OWC11.PivotField
Dim ptTotal As OWC11.PivotTotal
Dim ptCommand As OWC11.OCCommand
Set ptTable = frmOWCPT.PivotTable1
With ptTable
'Establish the connection to the data source etc.
.ConnectionString = stCon
.CommandText = stSQL
'In case no data is returned we can catch it.
If .ActiveData Is Nothing Then
'Message...
'End the procedure...
End If
Set ptView = .ActiveView
End With
Set ptFsets = ptView.FieldSets
With ptView
'Add the Year by Week-field to the Filter area.
.FilterAxis.InsertFieldSet ptFsets("Year by Week")
'Exclude records with empty years. 'In case of multilanguage enviroment
'then the Array 'should be applied then any local word for 'Empty'.
.FieldSets("Year by Week").Fields(0).ExcludedMembers = Array("")
'Add the Year by Month-field to the Filter area.
.FilterAxis.InsertFieldSet ptFsets("Year by Month")
.FieldSets("Year by Month").Fields(0).ExcludedMembers = Array("")
'Add the ShipCountry-field to the Row area.
.RowAxis.InsertFieldSet ptFsets("ShipCountry")
.FieldSets("ShipCountry").Fields(0).Caption = "Shipping Country"
'Add the Shipvia-field to the Column area.
.ColumnAxis.InsertFieldSet ptFsets("ShipVia")
.FieldSets("ShipVia").Fields(0).Caption = "Agency"
'Add the Freight-field to the Data area.
.DataAxis.InsertFieldSet ptFsets("Freight")
'Add Total calculated field.
.DataAxis.InsertTotal ptView.AddTotal("No of Shipments", _
ptFsets("Freight").Fields(0), _
plFunctionCount)
.DataAxis.InsertTotal ptView.AddTotal("Share of Shipments", _
ptFsets("Freight").Fields(0), _
plFunctionCount)
'Show the calculated field as percentage of the row
'and hide the field from being displayed in the fieldlist.
With .Totals("Share of Shipments")
.ShowAs = plShowAsPercentOfRowTotal
.DisplayInFieldList = False
End With
.DataAxis.InsertTotal ptView.AddTotal("Total Freight", _
ptFsets("Freight").Fields(0), _
plFunctionSum)
.DataAxis.InsertTotal ptView.AddTotal("Share of Freight", _
ptFsets("Freight").Fields(0), _
plFunctionSum)
With .Totals("Share of Freight")
.ShowAs = plShowAsPercentOfRowTotal
.DisplayInFieldList = False
End With
.DataAxis.InsertTotal ptView.AddTotal("Average Freight", _
ptFsets("Freight").Fields(0), _
plFunctionAverage)
'Format fields.
.FieldSets("Freight").Fields(0).NumberFormat = "0"
.Totals("Total Freight").NumberFormat = "0"
.Totals("Average Freight").NumberFormat = "0"
'View Total captions as Rowheadings instead as Columnheadings (default).
.TotalOrientation = plTotalOrientationRow
With .TitleBar
.Caption = "A simple sample Pivottable report"
.BackColor = RGB(0, 102, 0)
.ForeColor = vbWhite
End With
'Disallow users from editing the data.
.AllowEdits = False
End With
With ptTable
'Uncheck the option to show the Drop Areas.
'No property of the Pivottable is accessible to show/hide it via code.
'At least I'm not aware of any.
Set ptCommand = .Commands(plCommandDropzones)
ptCommand.Execute
'Collapse the Pivottable so that only summary data is shown.
.ActiveData.HideDetails
.Refresh
'If necessary, show the Fieldlist.
.DisplayFieldList = True
'Fix the Pivottable's size in the form.
'The Autofit property is automatically set to False when we set the
'height and width properties of the control.
With .Object
.Height = 460
.Width = 710
End With
.BackColor = RGB(204, 255, 255)
End With
'Release objects from memory.
Set ptCommand = Nothing
Set ptFsets = Nothing
Set ptView = Nothing
Set ptTable = Nothing
End Function
When populating the control with data via code no Totals are automatically added and therefore we need to do it with code as well.
The following picture shows the initial status after that the above code has been executed:
By resetting the orientation of the Totals captions to default following view is obtained:
By drilling down the underlying data is viewed which may give a better understanding of the pattern etc:
If we use any date related field the control will per automatic generate two sets of hierarchies for each date field. One hierarchy (Year by Month) contains the grouping of Year, Quarter, Month and Day and the other (Year by Week) contains the interval Year, Week and Day. The basic explanation for the existence of two hierarchies is that weeks do not always follow the months. In the example one date field (as Filter field) is used and therefore the control generates the following hierarchies.
Year by Week and Year by Month (Days are not viewed in the picture):
In the example the Toolbar of the control is available and one of the coolest features of the control is the ability to export to Excel. By clicking on the Excel-button the active view is exported to Excel and a built-in Pivottable is created with the data.
The Pivottable control places a high demand on developers but in return we get a flexible and a very powerful control which may leverage any solutions for business analysis and reporting.
Last Reviewed: 2006-04-30 Keywords: Office Web Components, OWC