Dynamic Pivot Chart Creation in VBA

[EddiRae]

I am trying to create a pivot chart that the user inputs what information they want for the series, filter, category before they get to the chart form.

They input the information on a form from drop-downs and then they click a button that takes them to the pivot form.

The data is based from a query which the pivot form is linked to.

On the load of the pivot form, I want to look at the selections from the prior form and load into the specific fields for the pivot chart.

I am not sure how to do this easily.

If anyone can help, thanks!!!

Eddi Rae

 

[EddiRae]

Got it to work!!

never mind ... I figured out how to do it.

I already have a form created and then in the load, I change the fields. Here is the code that does the trick!! the str[fields] are fields in a module that i pull from another form that the user selects the fields he wants to see.

Private Sub Form_Load()
Dim c As ChartSpace

'Set the Chart data.
Set c = Me.ChartSpace

c.SetData chDimCategories, chDataBound, strCategory
c.SetData chDimCategories, chDataBound, strCategory
c.SetData chDimValues, chDataBound, strData

c.Charts(0).Axes(0).HasTitle = True
c.Charts(0).Axes(0).Title.Caption = strCategoryCaption
c.Charts(0).Axes(1).HasTitle = True
c.Charts(0).Axes(1).Title.Caption = strValueCaption


If (strGPSLoss = "all" Or strGPSLoss = "") And (strLossType = "all" Or strLossType = "") And (strLoss = "all" Or strLoss = "") And (strReason = "all" Or strReason = "") Then
c.SetData chDimFilter, chDataBound, "Line"
c.SetData chDimSeriesNames, chDataBound, strSeries
Else
c.SetData chDimFilter, chDataBound, strSeries
c.SetData chDimSeriesNames, chDataBound, "line"
End If
End Sub

 

[shashidhar]

Re: Got it to work!!

never mind ... I figured out how to do it.

I already have a form created and then in the load, I change the fields. Here is the code that does the trick!! the str[fields] are fields in a module that i pull from another form that the user selects the fields he wants to see.

Private Sub Form_Load()
Dim c As ChartSpace

'Set the Chart data.
Set c = Me.ChartSpace

c.SetData chDimCategories, chDataBound, strCategory
c.SetData chDimCategories, chDataBound, strCategory
c.SetData chDimValues, chDataBound, strData

c.Charts(0).Axes(0).HasTitle = True
c.Charts(0).Axes(0).Title.Caption = strCategoryCaption
c.Charts(0).Axes(1).HasTitle = True
c.Charts(0).Axes(1).Title.Caption = strValueCaption


If (strGPSLoss = "all" Or strGPSLoss = "") And (strLossType = "all" Or strLossType = "") And (strLoss = "all" Or strLoss = "") And (strReason = "all" Or strReason = "") Then
c.SetData chDimFilter, chDataBound, "Line"
c.SetData chDimSeriesNames, chDataBound, strSeries
Else
c.SetData chDimFilter, chDataBound, strSeries
c.SetData chDimSeriesNames, chDataBound, "line"
End If
End Sub

-----------------------------------------------------------------------
Hi,
I tried with the same code in form load but i am getting run time error at 'Set c = Me.ChartSpace' .Please find attachment and help me on this.

Thanks,
Shashi

 

[vbaInet]

You may find this useful:

http://support.microsoft.com/kb/304143

 

[EddiRae]

shashidhar,
I apologize. I didn't tell you that I am using the reference "Microsoft Office XP Web Components".(OFFOWC.DLL)

Before the load form there are variables that I have defined. This is what I have before the load

Option Compare Database
Option Explicit
Public pc As OWC10.ChChart
Public c As OWC10.ChartSpace

Private Sub Form_Load()
.
.
.
End Sub