manipulating pivot table from code

[SunWuKung]

I just realised that I can use pivot tables in AccessXP but I don't seem to be able to find any reference on how to manipulate the pivot table from code.
Could somebody refer me to some examples\documentation\tutorial?

Many thanks.
SWK

 

[SunWuKung]

I still couldn't find a proper documentation, but I got somewhere after I found the following page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart03/html/sa03l7.asp

Could somebody tell me how to make the Grand Total row and column disappear from code?

thanks
SWK

 

[SunWuKung]

I created a sub that accepts parameters to build the pivot table. I can call it like:
PivotFormat rst, "C_SNAME", "RowAxis", "CandidateInstanceID", "RowAxis", "ScaleTag", "ColumnAxis", "Score", "DataAxis"

I would like to make sure that no subtotals are displayed when the table is created. I can manually remove them by right clicking on the column/row and unselect subtotal once they are displayed but it is cumbersome and takes a long time when the pivot table is refreshed after the removal of the subtotal.

Could somebody tell me how to do that?
Thanks
SWK


Public Sub PivotFormat(rst As ADODB.Recordset, ParamArray avar() As Variant)
On Error GoTo HandleErr
Dim lngElements As Long
Dim i As Long
Dim fst1 As Object

i = 0
lngElements = UBound(avar()) 'This is total number of array elements - 1

DoCmd.OpenForm "PivotForm", acFormPivotTable
Set Forms!PivotForm.Recordset = rst
If Not (lngElements Mod 2) Then
With Forms!PivotForm.PivotTable.ActiveView
For i = 0 To (lngElements - 1) / 2
Set fst1 = .FieldSets(avar(2 * i))
Select Case (avar(2 * i + 1))
Case "RowAxis"
.RowAxis.InsertFieldSet fst1
Case "ColumnAxis"
.ColumnAxis.InsertFieldSet fst1
Case "DataAxis"
.DataAxis.InsertFieldSet fst1
Case "FilterAxis"
.FilterAxis.InsertFieldSet fst1
End Select
Next i
End With
With Forms!PivotForm.PivotTable
.ActiveView.ExpandDetails = Forms!PivotForm.PivotTable.Constants.plExpandAlways
End With

Else
MsgBox "Incorrect number of array elements"
End If


ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Generic Pivot Format"
GoTo ExitHere
End Select

End Sub