Full Version: Pivot Chart Data Area Total Field VBA
UtterAccess Forums > Microsoft® Access > Access Forms
I create a Pivot Chart via VBA-code.
Ocan add a field to the Row Axis.
I can add a field to the Column Axis.
I can calculate a Total Field.
But, whatever I try, I can't add the Total Field to the Data Area.
Anybody any idea ?
I use following code :
Me.RecordSource = "<My query to retrieve all needed data> "
'Add Query-fields to the PivotTable
Set ColumnData = Me.PivotTable.ActiveView.FieldSets("date")
Set AmountData = Me.PivotTable.ActiveView.FieldSets("amount")
Me.PivotTable.ActiveView.RowAxis.InsertFieldSet ColumnData
Me.PivotTable.ActiveView.DataAxis.InsertFieldSet AmountData
'Calculate the total amount
Me.PivotTable.ActiveView.AddTotal "TotalAmount", _
Me.PivotTable.ActiveView.FieldSets("amount").Fields("amount"), plFunctionSum
--Following code doesn't work. I see the 'TotalAmount' field in the Chart Field List, but not in the Data Area of my Pivot Table--
'Add field in Data Area
Me.PivotTable.ActiveView.DataAxis.InsertTotal _
Even if I posted this question, I continue to try any possible solution.
Oalso tried the code I could find in the help-file, but I still don't see anything in the data area. I still have to drop the total field manually on the data area.
Any tip would be great.
Bump - same / similar problem in Access 2003. The InsertFieldSets into the RowAxis, ColumnAxis & DataAxis are reflected in the PivotTable view of the form, but the DataAxis InsertFieldSet & InsertTotal aren't shown in the PivotChart view.
do notice that the below code highlights RecNum in the FieldList window, but how do I get it to show on the PivotChart?
With pTable.ActiveView
Select Case optgrpReport
Case 1 'By Status
Set pFS = .FieldSets!asOf
.RowAxis.InsertFieldSet pFS

Set pFS = .FieldSets!Status
.ColumnAxis.InsertFieldSet pFS

Set pFS = .FieldSets!RecNum 'see next line
.AddTotal "Count2", .FieldSets!RecNum.Fields!RecNum, plFunctionCount 'ditto, didn't show
.DataAxis.InsertTotal .Totals("Count2") 'didn't show
.DataAxis.InsertFieldSet pFS 'didn't appear to do anything?
Case 2 'By Region
Case 3 'By Specialist
Case 4 'By SLA Left
End Select
End With
Set pFS = Nothing
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.