Pivot Diagram with Double Category Axis

[WilderLupo]

Hello,
I want to assemble a pivot chart with double category axis per VBA.
Alas, I have nowhere found how to achieve this.
Can anybody give me a hint please?

 

[RandomCoder]

Firstly I want to express that I am certainly not an authority on Microsoft Access, VBA or the Web Components library which have all caused me numerous headaches in the past (I am just self taught like many others here).

However I have been playing with Pivot Charts recently and maybe able to help? To add an additional category axis I think that you need to create a Pivot View object that is pointed at the underlying Pivot Table for the chart, then you can insert a new row field (now I'm even starting to sound like the Access help on web components ). I've modified my working example to add the Machine_Name field along with the Location category that I always load on opening the Form. Look for this function in the attached example...

Private Sub UpdateChart_Category()
    ' Set the chart category field as set by the Category combobox
    cs.SetData chDimCategories, chDataBound, cmbCategory.Value
    ' Assign a PivotView from the category axis data
    Set pv = cs.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).Axis.Data.View
    ' ### THIS LINE ADDS THE SECOND CATEGORY AXIS ###
    pv.RowAxis.InsertFieldSet pv.FieldSets("Machine_Name")
End Sub

I hope the attached example helps.

Kind regards,
RandomCoder

PS. In you're own project you will need to reference OWC10 (I've found OWC11 to be troublesome)

 

[WilderLupo]

Thank you very much for the quick reply.
I was able to adept the example to my needs.
However I encountered the same problem as when using the pivot chart assistant.
My first X-axis has for example the values K1.0, K2.0, K3.0,....K10.1, K10.2....
With the statement cs.SetData chDimCategories, chDataBound, "FlightPhase" I get a lexical ordering which I don't want.
(K1.0, K10.1, K10.2, K2.0 ...)
When using the statement cs.SetData chDimCategories, chDataLiteral, strExpression whereby strExpression is a string of the values in the desired order separated by tab I get my first X-axis alright.
The following statement
Set pv = cs.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).Axis.Data.view
return the Error message: Runtime error 9
Index outside of valid range.
So I'm at a loss again, any idea:banghead:

 

[RandomCoder]

My first X-axis has for example the values K1.0, K2.0, K3.0,....K10.1, K10.2....
With the statement cs.SetData chDimCategories, chDataBound, "FlightPhase" I get a lexical ordering which I don't want.
(K1.0, K10.1, K10.2, K2.0 ...)
When using the statement cs.SetData chDimCategories, chDataLiteral, strExpression whereby strExpression is a string of the values in the desired order separated by tab I get my first X-axis alright.

Having just had a quick play, this is because the underlying data source field is set as a String data type which is why you see the lexical ordering. Do you really need the K prefix on each value? If not then using a numeric data type will automatically resolve the problem.

The following statement
Set pv = cs.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).Axis.Data.view
return the Error message: Runtime error 9
Index outside of valid range.

I've seen this fault a lot whilst trying to work out how to use pivot charts with VBA. Basically you're trying to create a Pivot View object from a field that doesn't yet exist. Are you trying to run this command before you have added the category axis?

Pivot charts and the web components help file can be particularly frustrating, but stick with it and I'm sure you’ll get it working!

Regards,
RandomCoder

 

[WilderLupo]

The following lines do the trick I require:
I first had to fill an array "FlightPhases" with the values in the order I desire.
Then after
cs.SetData chDimCategories, chDataBound, "FlightPhase"
I inserted
cs.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).SourceField.OrderedMembers = FlightPhases
cs.Charts(0).Axes(0).CategoryLabels.PivotAxis.GroupFields(0).SourceField.SortDirection = plSortDirectionCustom
Thanks again for your support
Kind regards