Multiple Charts in chartspace OWC 10

[Michael Weiss]

I am trying to add and configure multiple charts in a
single chartspace. I have the following code but am
getting an unspecified error on the line ".SetData
c.chDimCategories, 0, c.chPivotRows" when the loop comes
to the second chart in the chartspace. Does anyone have
any ideas why this would produce an error on any charts
after the first one in the chartspace? I am connecting
the chartspace to a OWC 10 pivot control using data from
an Analsyis Services cube.
Thank you,
Michael

' Get the active view
set pview = ptable.ActiveView
set pc = ptable.Constants
' Bind the Chart control to the PivotTable control
cspace.clear
set cspace.DataSource = ptable
set c = cspace.Constants
cspace.PlotAllAggregates = c.chPlotAggregatesSeries
'add in charts - one for each measure:minutes, call
count, charges, cpm
i = 1
For
i = 1 To pview.DataAxis.Totals.Count - 1 'subtract
1 - cspace adds one chart automatically
cspace.Charts.Add
Next
for i = 0 to cspace.Charts.Count - 1
set cht = cspace.Charts.item(i)
With cht
..haslegend = true
..HasTitle = True
..Title.Caption = "Call Type Analysis: " &
pview.DataAxis.Totals(i).Caption
' bind the series and categories dimensions
..SetData c.chDimCategories, 0, c.chPivotRows
..SetData c.chDimSeriesNames, 0, c.chPivotColumns

' Use the totals for values
..SetData c.chDimValues, 0, i

..Type = c.chChartTypeBarClustered3d
set ax = .Axes(c.chAxisPositionBottom)
set ttl = pview.DataAxis.Totals(i)
ax.NumberFormat = ttl.NumberFormat
ax.HasTitle = True
ax.Title.Caption = ttl.Caption
ax.title.font.size = 8
ax.title.font.color = "#336699"
set ax = cht.Axes(c.chAxisPositionLeft)
ax.Scaling.Orientation = c.chScaleOrientationMaxMin
..plotarea.interior.color = "white"
..axes(1).font.color = "#336699"
..axes(0).font.color = "#336699"
..axes(1).font.size = 7
..axes(0).font.size = 7
..legend.interior.color = "white"
..legend.font.size = 7
..legend.font.color = "#336699"
End With
next

 

[Thao Moua [ms]]

When Chartspace is bound to an external data source, you can only have one
chart. However, you can include literal charts inside the bound Chartspace.

To get multiple chart inside the Chartspace control, set
Chartspace.HasMultipleCharts to TRUE. Use SetData(chDropZoneCharts, -1,
"field name") to create multiple charts base on the field name.

You might want to consider using field name for the third parameter to
SetData() (ie SetData chSeriesName, chDataBound, "Customer") You are using
OWC9 Chart syntax and they are not longer valid in OWC10/11.

---------------------------------------
Thao Moua
OWC Chartspace Support

This posting is provided "AS IS" with no warranties, and
confers no rights. Use of included script samples are
subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

 

[Michael Weiss]

Thank you, Thao...I still must be missing something
though. Since my pivot control is bound to an OLAP data
cube, I want to create one chart in the chartspace for
each total (measure member) in the data cube. I have the
chartspace.HasMultipleCharts property set to true.
Based on the code I have pasted here, I have the
following questions:
1. The line .SetData c.chDropZoneCharts, c.chDataBound,
c.chPivotTotals does not seem to work. As I understand
it, if I want a new chart for each total in the pivot
control, wouldn't I reference the totals collection here?

2. I cannot use SetData c.chSeriesName, c.chDataBound,
c.chPivotRows? Do I have to specify for the third
argument the actual fieldset name from the OLAP cube?

3. In the OWC VB Reference files, one of the
ChartDimensionsEnums is chDimCharts and seems to be used
to set the source fields for new charts in a chartspace
where HasMultipleCharts = true. How would I use this or
do I need to? If I do, is it set at the ChartSpace level
or at the individual chart object level?

Thank you,
Michael

Code:
with cspace
..HasMultipleCharts = true
..ChartLayout = c.chChartLayoutHorizontal
..displayfieldbuttons = false
..interior.color = "white"
..HasChartSpaceTitle = true
..ChartSpaceTitle.Caption = "Call Type Analysis"
..ChartSpaceTitle.font.color = "#336699"
..ChartSpaceTitle.font.size = 9
..ChartSpaceTitle.Font.Bold = true
end with

'add in chart objects and set properties:
for i = 0 to pview.DataAxis.Totals.Count - 1
set cht = cspace.Charts.Add
With cht
..haslegend = true
..HasTitle = True
..Title.Caption = "Call Type Analysis: " &
pview.DataAxis.Totals(i).Caption
' bind the series and categories dimensions
..SetData c.chDropZoneCharts, c.chDataBound,
c.chPivotTotals
..SetData c.chDimCategories, c.chDataBound, c.chPivotRows
..SetData c.chDimSeriesNames, c.chDataBound,
c.chPivotColumns

' Use the totals for values
..SetData c.chDimValues, 0, i

..Type = c.chChartTypeBarClustered3d
End With
Next

-----Original Message-----
When Chartspace is bound to an external data source, you can only have one
chart. However, you can include literal charts inside the bound Chartspace.

To get multiple chart inside the Chartspace control, set
Chartspace.HasMultipleCharts to TRUE. Use SetData (chDropZoneCharts, -1,
"field name") to create multiple charts base on the field name.

You might want to consider using field name for the third parameter to
SetData() (ie SetData chSeriesName,

chDataBound, "Customer") You are using

 

[Thao Moua [ms]]

Answer 1-2: you are using OWC9 data reference values and they are invalid for
OWC10. In OWC10, you can now specified fields names for the SetData(x, y,
"field_name")

Answer 3: if you want to create multiple chart bases off of a record, then
set the HasMultipleChart property to TRUE and set
Chartspace.SetData(chDropZoneCharts, chDataBound, "field name") When binding
to external data (excluding Spreadsheet) you must call SetData() at the
Chartspace level.

----------------------------------------------------------------------
Thao Moua
OWC Chartspace Support

This posting is provided "AS IS" with no warranties, and
confers no rights. Use of included script samples are
subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
----------------------------------------------------------------------

 

[Michael Weiss]

Thao,
I finally got back around to this project and am again having difficulty
with getting my Column Axis fieldsets in my pivot control to be picked up as
Categories in the chart displayed in my chartspace object. I have tried your
suggestions and still no luck. Does it make a difference that my pivot
control is binding to an OLAP cube and not a spreadsheet or recordset or
literal data? Also, when calling SetData at the Chartspace level all I
receive is an error stating that SetData can only be called at the chart
level for chDimSeriesNames and chDimCategories. With OWC 9 it seemed more
straightforward to get the pivot column axis members to display in a chart as
categories.
Your thoughts and suggestions would be greatly appreciated...
Thank you,
Michael

 

[Chris Cooper]

Michael,

Not sure if this helps but I'm also using OWC10, Pivottable is bound to
Analyis Services OLAP cube, and Pivottable is my datasource for Chart. This
is the bare bones of the code I use to make MultiCharts:

Dim chConstants

chart1.HasMultipleCharts = True

Set chConstants = chart1.Constants

chart1.ChartWrapCount = 2

chart1.SetData chConstants.chDimCharts, chConstants.chDataBound, mcFieldName

Where mcFieldName is a field in my Pivottable Field List

Chris