Controlling pivot chart data categories (query based)

[munkee]

All,
Basically I am trying to change my pivot chart a bit more dynamically.
I have a sub form with a query recordsource which contains all the fields I would require to create TWO pivot charts.
The sub form opens as pivot chart view and ofcourse is placed as a control on my main form.
I am trying to design my form with some code so that when I click buttons on my main form the data categories on the axis of the pivot chart change to predefined (by the code) data series I have set.
This in my mind seems very simple since it is literally just asking my pivot chart to stop using a category for the Y axis such as [NCCost] to now picking out [NCEventCount] from its record source (query).
I have been looking for ages at code and it seems to always reference excel forums. Anyway.. this is what I have at the moment:

Private Sub btnsettocosts_Click()
Dim objChartSpace As OWC10.ChartSpace
   Dim objPivotChart As OWC10.ChChart
   Dim frmChart As Access.Form
 
   Set frmChart = Me.Controls("PivotSub").Form
   Set objChartSpace = frmChart.ChartSpace
   Set objPivotChart = objChartSpace.Charts.Item(0)
 
End Sub

It took long enough to get to there as I was having issues with the OWC10 reference, however that is now fixed and I cant face struggling on anymore.
Thanks for any help,
Chris

 

[Endre]

I think it's more simpler than you realise - just changing the .RowSource property will pick up your new arrangements for you:

This is example SQL for a horizontal date range:
Me.Controls(0).RowSource = "TRANSFORM Sum(Table2.DollarValue) AS SumOfDollarValue SELECT ""DollarValue"" AS Expr1 FROM Table2 GROUP BY ""DollarValue"" ORDER BY Table2.DateValue PIVOT Table2.DateValue;"

This is same but CustID:
Me.Controls(0).RowSource = "TRANSFORM Sum(Table2.DollarValue) AS SumOfDollarValue SELECT ""DollarValue"" AS Expr1 FROM Table2 GROUP BY ""DollarValue"" ORDER BY Table2.CustID PIVOT Table2.CustID;"

You may need to also refresh after running the above code on_button_click so add the following line as well:
Me.Controls(0).Refresh

If your graph changes significantly (in this case my axis was originally date formatted and is now changed to an ID number) you may need to alter some of the settings accordingly. It may by easier to create and preset several charts and just make the correct one visible so the users believe the graph is changing.


Don't give up.....

 

[munkee]

Endre thank you for the suggestion.

It is funny I actually use vba already to change the recordsource with a SQL string in my other charts but I did not realise I can just create the table behind the pivot chart in the same way (dont ask why I think my brain needs to be kick started sometimes).

Further to your second suggestion, I actually implemented something very similar yesterday. I have simply created the seperate possible options in forms. On main main form I have controls which dependent on selection (using Select Case in vba) I can change the source of the subform to display the data. This seems to run quite smooth but I thought it was a "quick fix" however it seems as you suggested it might just be the final answer instead.

Thanks again,

Chris

 

[Endre]

Sometimes all we need is a cardboard cutout to talk to and we can solve the worlds problems (well - our own at least).