OWC11 DataSource Misbehavior List (please explain how someone can program like this!)

[Sascha Sertel]

In an ideal world, I wouldn't have to write this posting. In an almost ideal
world, I would like to have the guy who programmed the Microsoft OWC11 (and
earlier) read this and answer me the following questions:

1. Why does the ChSeries.SetData method not accept chDataBound as the data
source? To me it seems very logic to set the Category and Value fields from
my recordset per Series, and not per Chart or per ChartSpace. The
documentation does not even explicitly say that this value cannot be used on
ChSeries. After reading it over and over again it seems to be hinted at when
the remarks say that only one data source can be set for a Chart or
ChartSpace (because the word Series doesn't even appear here). In a
knowledgebase article Microsoft says that if you want to add more data or
series, you have to populate arrays and add them as chDataLiteral to the
Chart.

2. Okay, even if I accept the fact that I cannot assign DataBound values to
Series directly, I start assigning it to the ChartSpace, only to find more
oddities. Why is a new Chart added to the ChartSpace as soon as the line
m_cspace.DataSource = m_rs (where m_cspace is the ChartSpace object and m_rs
the RecordSet) is executed? It doesn't matter if the HasMultipleCharts value
is set to false or not, the additional chart gets added in any way. Even in
Microsofts own example ChartGif (can be found in the KnowledgeBase) there is
the following line which gets rid of the additional chart again
(interestingly the only line in the whole script which is not explained with
a comment): m_cspace.Charts.Delete 1

3. Well, as long as the Delete resolves the situation I at least have a
workaround, but unfortunately it doesn't end here. Now OWC tries to be
intelligent and helpful. A noble effort, but very annoying if forced onto me
and I can't find a way to deactivate it (if anyone knows a solution for
this, please let me know!!): Why are the Category values automatically
sorted? I bind my chDimCategories dimension to a recordset field which I
presorted when selecting it, but when it is displayed suddenly the
categories are ordered lexicographically and not in the way they are in the
recordset. My categories happen to be Time labels such as "March 2004" etc.
and I wanted to have them exactly this way and not 03/2004 or 03/01/2004 or
anything like that. However, the string "March 2004" is not recognized as a
date by OWC, so it 'thinks' we have a random category list and sorts it for
me. It doesn't matter if I have the Sort string set for the Recordset or
not, since the OWC sorting comes after that. Only if I format my date in a
form such as 03/01/2004 it will be displayed and ordered correctly. But why
can't I chose if I want that to be automatically sorted or not? I looked
through all the Methods and Properties of ChartSpace, nothing even seems to
hint to something like sorting. I guess the OWC feature list I found online
says it all: "With added special handling for date/time data, the chart will
fill in missing dates, sort dates in the correct order, group data points by
time intervals, and present a visually appealing axis that is intelligent
about labels, tick-marks, and gridline intervals."


Finally I ended up setting the recordset, deleting the extra charts object,
and populating arrays manually from my recordset which I then use for the
SetData method of my Series. That way, I get MY labels in MY order, and
everything looks fine.

I'm only refering to the OWC Chart Component here, I'm sure the list goes on
for the Spreadshit and Pivot Components. In case I'm just totally stupid and
didn't use the OWC right, please feel free to explain to me how it should
work.


Sascha

 

[Alvin Bruney [MVP]]

i'm not following item 1. show me some code

 

[Sascha Sertel]

Sorry for the long delay, I had to work on some other things in the
meantime. I'll gladly elaborate with an easy example for point 1, where I
say that you can't bind data to a ChSeries object.

So here is what I was thinking:
If I want to have a chart with for example two columns in it, which means
two different series, and I'm using arrays which I define manually, it would
look like this:

---- VBScript Snipplet ------
dim Categories
Categories = array('First', 'Second', 'Third', 'Fourth')
dim ValuesForFirstSeries
ValuesForFirstSeries = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
dim ValuesForSecondSeries
ValuesForSecondSeries = array(2, 4, 6, 8, 10, 12, 14, 16, 18, 20)

dim FirstSeries
set FirstSeries = cht.SeriesCollection.Add()
FirstSeries.SetData c.chDimCategories, c.chDataLiteral, Categories
FirstSeries.SetData c.chDimValues, c.chDataLiteral, ValuesForFirstSeries

dim SecondSeries
set SecondSeries = cht.SeriesCollection.Add()
SecondSeries.SetData c.chDimCategories, c.chDataLiteral, Categories
SecondSeries.SetData c.chDimValues, c.chDataLiteral, ValuesForSecondSeries
---- End of VBScript Snipplet ------

This works without any problems, but it doesn't happen really often that you
manually populate a chart with hardcoded data. Usually you want to bind it
to some data source such as a database, to have the data populated
automatically. In the code above there is a parameter for the SetData method
called c.chDataLiteral. The ChartSpecialDataSourcesEnum, which according to
the documentation also applied to the ChSeries object, has another constant
called chDataBound. Still according to the documentation chDataBound can be
used to bind your Series to a datacolumn in a recordset. Assuming that the
ChartSpace.DataSource property has been set to a valid ADODB Recordset
object, the following code should be expected to work:

---- VBScript Snipplet ------
dim FirstSeries
set FirstSeries = cht.SeriesCollection.Add()
FirstSeries.SetData c.chDimCategories, c.chDataBound, "ColumnWithCategories"
FirstSeries.SetData c.chDimValues, c.chDataBound,
"ColumnWithValuesForFirstSeries"

dim SecondSeries
set SecondSeries = cht.SeriesCollection.Add()
SecondSeries.SetData c.chDimCategories, c.chDataBound,
"ColumnWithCategories"
SecondSeries.SetData c.chDimValues, c.chDataBound,
"ColumnWithValuesForSecondSeries"
---- End of VBScript Snipplet ------

As stated in my original posting, this doesn't work, it results in a runtime
error from the OWC component. However, Microsoft is aware of this behavior,
as they explain in
http://support.microsoft.com/defaul...port/kb/articles/q288/9/07.asp&NoWebContent=1
how you actually have to add multiple databound series to a chart. I quote:

"In the OWC10 object model, the ChartSpace, a chart, and a series all expose
a SetData method. When the ChartSpace is bound to a data source, you can
only use the SetData method for the ChartSpace object to add series or
values from the bound data source. If you try to use SetData for either a
chart or a series that has been created as a result of the binding, you may
receive either of the following run-time errors:
a..

Run-time error '-2147467259 (80004005)':
Method 'SetData' of object 'ChChart' failed.
b..

Run-time error '-2147467259 (80004005)':
Method 'SetData' of object 'ChSeries' failed.
A general guideline for data-bound charts is that the SetData method can be
applied to an individual chart or to a series if you added the chart or the
series to the ChartSpace by using the Add method and you are loading literal
data (comma-delimited strings or arrays) to the added chart or to the
series."


This text and the KB article also apply to OWC11, although in the text only
OWC10 is mentioned, but they say it in another paragraph in the article that
it behaves the same in that matter. As you can see, I figured out the how by
now. What I don't understand is the why. I hope in an upcoming version of
the OWC it will be possible to use the SetData method on ChSeries objects
more naturally.

Oh, and why I'm on it I'll write up another posting about missing
functionality which reads in the documentation as if it was there.

One more note: I'm not trying to make OWC look bad or say it's shit or
anything. I know if I thought it was so bad I could use another office
component etc. It's just that I feel the component does not have a high
priority at Microsoft, probably because they rather want to sell their
Office package and not have people replicate Office functionality through
the OWC. Also, I spent and lost a lot of time figuring out things which
don't work as I thought they would, and it wouldn't be asked too much to
have the quote which I posted above in the documentation for OWC11. This
also applies to other limitations. It's not that those limitations exist,
it's that you just don't know about it and have to learn it the hard way or
by searching on forums and newsgroups.

Enough now, I doubt more than two people took the time to read until down
here anyway ;-)
Bye,
Sascha

 

[Sascha Sertel]

Sorry for the long delay, I had to work on some other things in the
meantime. I'll gladly elaborate with an easy example for point 1, where I
say that you can't bind data to a ChSeries object.

So here is what I was thinking:
If I want to have a chart with for example two columns in it, which means
two different series, and I'm using arrays which I define manually, it would
look like this:

---- VBScript Snipplet ------
dim Categories
Categories = array('First', 'Second', 'Third', 'Fourth')
dim ValuesForFirstSeries
ValuesForFirstSeries = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
dim ValuesForSecondSeries
ValuesForSecondSeries = array(2, 4, 6, 8, 10, 12, 14, 16, 18, 20)

dim FirstSeries
set FirstSeries = cht.SeriesCollection.Add()
FirstSeries.SetData c.chDimCategories, c.chDataLiteral, Categories
FirstSeries.SetData c.chDimValues, c.chDataLiteral, ValuesForFirstSeries

dim SecondSeries
set SecondSeries = cht.SeriesCollection.Add()
SecondSeries.SetData c.chDimCategories, c.chDataLiteral, Categories
SecondSeries.SetData c.chDimValues, c.chDataLiteral, ValuesForSecondSeries
---- End of VBScript Snipplet ------

This works without any problems, but it doesn't happen really often that you
manually populate a chart with hardcoded data. Usually you want to bind it
to some data source such as a database, to have the data populated
automatically. In the code above there is a parameter for the SetData method
called c.chDataLiteral. The ChartSpecialDataSourcesEnum, which according to
the documentation also applied to the ChSeries object, has another constant
called chDataBound. Still according to the documentation chDataBound can be
used to bind your Series to a datacolumn in a recordset. Assuming that the
ChartSpace.DataSource property has been set to a valid ADODB Recordset
object, the following code should be expected to work:

---- VBScript Snipplet ------
dim FirstSeries
set FirstSeries = cht.SeriesCollection.Add()
FirstSeries.SetData c.chDimCategories, c.chDataBound, "ColumnWithCategories"
FirstSeries.SetData c.chDimValues, c.chDataBound,
"ColumnWithValuesForFirstSeries"

dim SecondSeries
set SecondSeries = cht.SeriesCollection.Add()
SecondSeries.SetData c.chDimCategories, c.chDataBound,
"ColumnWithCategories"
SecondSeries.SetData c.chDimValues, c.chDataBound,
"ColumnWithValuesForSecondSeries"
---- End of VBScript Snipplet ------

As stated in my original posting, this doesn't work, it results in a runtime
error from the OWC component. However, Microsoft is aware of this behavior,
as they explain in
http://support.microsoft.com/defaul...port/kb/articles/q288/9/07.asp&NoWebContent=1
how you actually have to add multiple databound series to a chart. I quote:

"In the OWC10 object model, the ChartSpace, a chart, and a series all expose
a SetData method. When the ChartSpace is bound to a data source, you can
only use the SetData method for the ChartSpace object to add series or
values from the bound data source. If you try to use SetData for either a
chart or a series that has been created as a result of the binding, you may
receive either of the following run-time errors:
a..

Run-time error '-2147467259 (80004005)':
Method 'SetData' of object 'ChChart' failed.
b..

Run-time error '-2147467259 (80004005)':
Method 'SetData' of object 'ChSeries' failed.
A general guideline for data-bound charts is that the SetData method can be
applied to an individual chart or to a series if you added the chart or the
series to the ChartSpace by using the Add method and you are loading literal
data (comma-delimited strings or arrays) to the added chart or to the
series."


This text and the KB article also apply to OWC11, although in the text only
OWC10 is mentioned, but they say it in another paragraph in the article that
it behaves the same in that matter. As you can see, I figured out the how by
now. What I don't understand is the why. I hope in an upcoming version of
the OWC it will be possible to use the SetData method on ChSeries objects
more naturally.

Oh, and why I'm on it I'll write up another posting about missing
functionality which reads in the documentation as if it was there.

One more note: I'm not trying to make OWC look bad or say it's shit or
anything. I know if I thought it was so bad I could use another office
component etc. It's just that I feel the component does not have a high
priority at Microsoft, probably because they rather want to sell their
Office package and not have people replicate Office functionality through
the OWC. Also, I spent and lost a lot of time figuring out things which
don't work as I thought they would, and it wouldn't be asked too much to
have the quote which I posted above in the documentation for OWC11. This
also applies to other limitations. It's not that those limitations exist,
it's that you just don't know about it and have to learn it the hard way or
by searching on forums and newsgroups.

Enough now, I doubt more than two people took the time to read until down
here anyway ;-)
Bye,
Sascha