Binding OWC11 Chart to Pivot table

[A4e]

hi

i have an OWC11 PivotTable and Chart control on a windows forms application.
the datasource of the chart control is set to that of the PivotTable

most things work fine, ie when i make a change to filter,column or row it is
reflected in the corresponding chart, HOWEVER when i add a field to the data
area it is NOT reflected in the chart!
whats weird is if i add a field to the data area on the CHART it is
automatically reflected on the Pivot Table but not the other way round

this MUST be a bug, it doesnt make any sense

please can anyone guide me to any literature/solution on this?

 

[Alvin Bruney [MVP]]

It does seem sort of strange. How about calling refresh after the field
addition. See if that works.
--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[A4e]

hi alvin

thanks for that... however, it is happening when the user drags a new field
to the data/totals area

if i set the field in code it works fine... but if the user then drags an
additional total from the field list onto the data area of the pivottable,
the additional data does not plot onto the chart (strangely, if user drags a
field from field list to any other axis it does refresh on the chart, just
not if they drag onto data area drop zone!)

your continued support most welcome

 

[A4e]

again alvin, thank you

but i was wondering whether you could provide me with some example code for
this please?

 

[A4e]

sorry, that last reply was meant for a different post!

still having problems with binding pivottable data to chart though...?

 

[Alvin Bruney [MVP]]

Consider using one of the drag events to refresh the pivot table. That
should work for you. You'd essentially have to handle the dragenter or
dragleave event and get the pivot table object and call refresh.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[A4e]

hi

thanks... but its not the pivottable that needs refreshing, its the chart.

i have tried calling chartspace.refresh in the pivottable's datachange,
query and pivotchanged events but no luck.

everything works fine when i set it in code, but when the USER drags
additional fields to the data area it is not reflected in the chart... you
need to go into the chart and drag the field again from the field list (but
what is strange is that the field that has been added is in bold as if it is
already on the chart)

chart is bound to pivottable using chart.datasource = pivottable.getocx()

the converse works... if i drag a field to the data area of a chart it IS
added to the pivottable.

all other axes are mirrored correctly when changed/moved/added/removed from
the respective chart or pivottable

im really stuck on this, sorry to keep on!

thanls

 

[Alvin Bruney [MVP]]

This looks like another bug. So I have two support incidents remaining. To
get one, you'll need to write a small application that exactly reproduces
the problem with test data. Then, provide a short description of the
problem. I'll submit it to MS on your behalf.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[A4e]

hi alvin

i have created a small application for you to submit to microsoft, but i
don't seem to be able to attach it using this forum... how do you propose i
send it to you?

thanks

 

[Alvin Bruney [MVP]]

vapordan a t h ot m a i l dotc om subject line owc bug

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[A4e]

hi alvin

were you able to reproduce my problem with the app i sent you?

could you propose an interim workaround?

 

[jankrib]

I had the same problem. The datafield isn't directly linked to the
PivotTable because it should be possible to view only one of the
datafields.
Something like this worked for me:

Private Sub AxChartSpace1_DataSetChange(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles AxChartSpace1.DataSetChange


Dim theTotals As ArrayList
Dim oneTotal As Microsoft.Office.Interop.Owc11.PivotTotal

theTotals = New ArrayList

For Each oneTotal In AxPivotTable1.ActiveView.DataAxis.Totals
theTotals.Add(oneTotal.Name)
Next

AxChartSpace1.HasMultipleCharts = False
AxChartSpace1.PlotAllAggregates =
Microsoft.Office.Interop.Owc11.ChartPlotAggregatesEnum.chPlotAggregatesSeries

AxChartSpace1.SetData(Microsoft.Office.Interop.Owc11.ChartDimensionsEnum.chDimValues,
0, theTotals.ToArray)

End Sub


hope it helpes

 

[A4e]

absolutely perfect, it works a treat... many thanks !!!!

 

[Alvin Bruney [MVP]]

great ill consider this issue closed then

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley

 

[A4e]

thank you very much for your help here too alvin... please forward my issue
on as a bug as i still believe it is

cheers again, great book by the way (black book)

 

[Hao Jiang]

I had the same problem. The datafield isn't directly linked to the
PivotTable because it should be possible to view only one of the
datafields.
Something like this worked for me:

Private Sub AxChartSpace1_DataSetChange(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles AxChartSpace1.DataSetChange


Dim theTotals As ArrayList
Dim oneTotal As Microsoft.Office.Interop.Owc11.PivotTotal

theTotals = New ArrayList

For Each oneTotal In AxPivotTable1.ActiveView.DataAxis.Totals
theTotals.Add(oneTotal.Name)
Next

AxChartSpace1.HasMultipleCharts = False
AxChartSpace1.PlotAllAggregates =
Microsoft.Office.Interop.Owc11.ChartPlotAggregatesEnum.chPlotAggregatesSeries

AxChartSpace1.SetData(Microsoft.Office.Interop.Owc11.ChartDimensionsEnum.chDimValues,
0, theTotals.ToArray)

End Sub


hope it helpes

 

[Hao Jiang]

I had the same problem too. When I tried the following code, the app locks
up for a while, then I get a stack overflow error at the SetData function
call.

Is there any other solution for this problem?

Thanks!