Binding chart to pivot in C#?

[Teo Lachev]

How on earth I can bind an OWC 11 chart to pivot in C#? Whatever I tried
results in "Unknown error". It is really frustrating not to be able to find
out a single line of .NET code to use OWC 11.

My unsuccessful code so far:

//AxChartSpace chart = null;

OWC11.ChChart chart;

OWC11.PivotView pview = pivotTable.ActiveView;

// Bind the Chart control to the PivotTable control if necessary

//chartSpace.DataSourceType =
ChartDataSourceTypeEnum.chDataSourceTypePivotTable;

chartSpace.DataSource = (msdatasrc.DataSource) pivotTable.DataSourceEx;

//chartSpace.DataSourceName = pivotTable.Name;

chartSpace.DisplayFieldList = true;


//Get the chart or create one if needed

if (chartSpace.Charts.Count == 0)

chart = chartSpace.Charts.Add(0);

else

chart = chartSpace.Charts[0];


chart.HasLegend = true;




chart.HasTitle = true;

chart.Title.Caption = pview.TitleBar.Caption;

// Set the chart data

??? chart.SetData(ChartDimensionsEnum.chDimCategories, (int)
ChartSpecialDataSourcesEnum.chDataBound,
ChartPivotDataReferenceEnum.chPivotColumns);

chart.SetData (ChartDimensionsEnum.chDimCategories, 0,
ChartPivotDataReferenceEnum.chPivotRows);

 

[Teo Lachev]

After a half of day of trial and error finally the following change seems to
work:
chartSpace.DataSource = (msdatasrc.DataSource) pivotTable.GetOcx();

Now if I could only figure out why this is giving me "Unspecified Error", I
will be done:

chart.SetData(ChartDimensionsEnum.chDimSeriesNames, 0,
ChartPivotDataReferenceEnum.chPivotColumns);

Any ideas?

 

[Thao Moua [ms]]

Whenever you bind Chartspace to an external data source, all binding must be set at the Chartspace level. So you code should b

chartSpace.SetData(ChartDimensionsEnum.chDimSeriesNames, 0, ChartPivotDataReferenceEnum.chPivotColumns)

Thao Mou
OWC Webchart Suppor

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

 

[Teo Lachev]

Thao,

Thanks for your reply. Well, almost... Now it gives "Invalid Number of
arguments" error. I attach my code. Another problem that I am having is that
some of pivot table events, e.g. dbclick, beforecontextmenu, don't seem to
fire. I verified that they are wired correctly. I used the property window
to double-click on the event.

OWC11.ChChart chart;

OWC11.PivotView pview = pivotTable.ActiveView;

// Bind the Chart control to the PivotTable control if necessary

//chartSpace.DataSourceType =
ChartDataSourceTypeEnum.chDataSourceTypePivotTable;

chartSpace.DataSource = (msdatasrc.DataSource) pivotTable.GetOcx();

//chartSpace.DataSourceName = pivotTable.Name;

chartSpace.DisplayFieldList = true;

chartSpace.AllowUISelection = true;

chartSpace.AllowDrop = true;


//Get the chart or create one if needed

if (chartSpace.Charts.Count == 0)

chart = chartSpace.Charts.Add(0);

else

chart = chartSpace.Charts[0];


// Make sure the chart has a legend

chart.HasLegend = true;




//Set the title to the PivotTable title

chart.HasTitle = true;

chart.Title.Caption = pview.TitleBar.Caption;

// Set the chart data

chartSpace.SetData(ChartDimensionsEnum.chDimSeriesNames, 0,
ChartPivotDataReferenceEnum.chPivotColumns); // !!! Execption


chartSpace.SetData (ChartDimensionsEnum.chDimCategories, 0,
ChartPivotDataReferenceEnum.chPivotRows);

Whenever you bind Chartspace to an external data source, all binding must

be set at the Chartspace level. So you code should be

 

[Teo Lachev]

Never mind. Q328275 helped. Needed to install the primary interop assemblies
and re-reference the whole thing. Will re-test tomorrow but it looks like at
least the events work.

Thao,

Thanks for your reply. Well, almost... Now it gives "Invalid Number of
arguments" error. I attach my code. Another problem that I am having is that
some of pivot table events, e.g. dbclick, beforecontextmenu, don't seem to
fire. I verified that they are wired correctly. I used the property window
to double-click on the event.

OWC11.ChChart chart;

OWC11.PivotView pview = pivotTable.ActiveView;

// Bind the Chart control to the PivotTable control if necessary

//chartSpace.DataSourceType =
ChartDataSourceTypeEnum.chDataSourceTypePivotTable;

chartSpace.DataSource = (msdatasrc.DataSource) pivotTable.GetOcx();

//chartSpace.DataSourceName = pivotTable.Name;

chartSpace.DisplayFieldList = true;

chartSpace.AllowUISelection = true;

chartSpace.AllowDrop = true;


//Get the chart or create one if needed

if (chartSpace.Charts.Count == 0)

chart = chartSpace.Charts.Add(0);

else

chart = chartSpace.Charts[0];


// Make sure the chart has a legend

chart.HasLegend = true;




//Set the title to the PivotTable title

chart.HasTitle = true;

chart.Title.Caption = pview.TitleBar.Caption;

// Set the chart data

chartSpace.SetData(ChartDimensionsEnum.chDimSeriesNames, 0,
ChartPivotDataReferenceEnum.chPivotColumns); // !!! Execption


chartSpace.SetData (ChartDimensionsEnum.chDimCategories, 0,
ChartPivotDataReferenceEnum.chPivotRows);

Whenever you bind Chartspace to an external data source, all binding

must
be set at the Chartspace level. So you code should be

chartSpace.SetData(ChartDimensionsEnum.chDimSeriesNames, 0, ChartPivotDataReferenceEnum.chPivotColumns);

Thao Moua
OWC Webchart 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

 

[Thao Moua [ms]]

ChartPivotDataReferenceEnum.chPivotColumns needs to be casted into an object. Any type-less paramter is treated as object in C#

Thao Mou
OWC Webchart Suppor

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

 

[Teo Lachev]

Thao,

It didn't help if you meant:
chart.SetData(ChartDimensionsEnum.chDimSeriesNames, 0, (object)
ChartPivotDataReferenceEnum.chPivotColumns);

The above gives me "Unspecified error". Replacing chart with chartSpace
results in "Invalid Number of Arguments" exception.

Please don't give up In addition, could you let me know how:

1.I can get programmatically in OWC11 PivotTable to the member key
column defined in the Analysis Services for a dimension. For
example, an Employee dimension may have a member key column set to the
Employee ID, while its member name column may be the Employee Name. I would
like to get to the Employee ID column in my pivot table.

2. Is it possible to programatically get to custom-defined member
properties, e.g
Employee Title, Birth Date, etc? Ishould be able to because thouse can be
displayed as tooltips or inside the member cell.

Thanks in advance.

ChartPivotDataReferenceEnum.chPivotColumns needs to be casted into an

object. Any type-less paramter is treated as object in C#.

 

[Thao Moua [ms]]

It seems you're using OWC9 data binding code for OWC10/11 and this no longer works. In OWC9, you can set the Chartspace series name and category to the PivotTable row/column/value field. In OWC10/11, we removed this behavior because it doesn't make sense

Now by default the Chartspace series name is bound to the Pivottable column field and the Chartspace category is bound to to the Pivottable row (or vice-versa.) If you like to toggle this behavior (ie series bounds to the row and category bounds to the column) then you should use Chartspace's HasSeriesByRows property

When Chartspace is bound to a PivotTable control, a chart is created for you so there is no need to create/add a chart

Hope this helps

Thao Mou
OWC Webchart Suppor

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.ht


----- Teo Lachev wrote: ----

Thao

It didn't help if you meant
chart.SetData(ChartDimensionsEnum.chDimSeriesNames, 0, (object
ChartPivotDataReferenceEnum.chPivotColumns)

The above gives me "Unspecified error". Replacing chart with chartSpac
results in "Invalid Number of Arguments" exception

Please don't give up In addition, could you let me know how

1.I can get programmatically in OWC11 PivotTable to the member ke
column defined in the Analysis Services for a dimension. Fo
example, an Employee dimension may have a member key column set to th
Employee ID, while its member name column may be the Employee Name. I woul
like to get to the Employee ID column in my pivot table

2. Is it possible to programatically get to custom-defined membe
properties, e.
Employee Title, Birth Date, etc? Ishould be able to because thouse can b
displayed as tooltips or inside the member cell

Thanks in advance

ChartPivotDataReferenceEnum.chPivotColumns needs to be casted into a

object. Any type-less paramter is treated as object in C#

 

[Teo Lachev]

Thao,

Thanks. That was my problem. I was trying to migrate an old code with OWC 9.

I would really appreciate it you could address my other questions as well
which I enclosed again.

1. With OWC11 PivotTable, is it possible to get programmatically to the
member key column defined in the Analysis Services for a dimension. For
example, an Employee dimension may have a member key column set to the
Employee ID, while its member name column may be the Employee Name. I would
ike to retriive the Employee ID column in my pivot table.

2. Is it possible to get programatically to custom-defined member
properties, e.g Employee Title, Birth Date, etc?

It seems you're using OWC9 data binding code for OWC10/11 and this no

longer works. In OWC9, you can set the Chartspace series name and category
to the PivotTable row/column/value field. In OWC10/11, we removed this
behavior because it doesn't make sense.

Now by default the Chartspace series name is bound to the Pivottable

column field and the Chartspace category is bound to to the Pivottable row
(or vice-versa.) If you like to toggle this behavior (ie series bounds to
the row and category bounds to the column) then you should use Chartspace's
HasSeriesByRows property.

When Chartspace is bound to a PivotTable control, a chart is created for

you so there is no need to create/add a chart.