Erik,
since you also so my other posting from 6/30/2004, where I had the same
problem, I'm glad to tell you that I found a solution that doesn't require
manually hiding additional axis lines or something like that. It's still not
as pretty as it could be, but at least it works pretty stable (with
exceptions of course).
So just to summarize what you describe, it seems to be working like this in
OWC:
- Whenever you add a Series, it is automatically using the primary X-Axis
scale.
- Now if you want a Series to use a different scale, there is that magical
Ungroup flag which can be set to true. What happens is that the Series will
then use it's own scale as if there were no other Series on the graph.
- If you want to display the Axis of the ungrouped Series, you can add
another Axis using the Scalings property of the Series. This works fine for
one Series using another scale.
- Now as you described it gets problematic once you want several Series to
not use the primary scale, but still use one scale. In Excel you can just
chose Secondary Axis for that Series, and it's fine. OWC doesn't have that
functionality, unfortunately.
- So let's assume you have one Series using the primary scale, and one
ungrouped Series using the secondary scale. Now if you add another Series
and set the Ungroup flag for that, then it will have its own (third) scale.
I tried assigning the Scalings objects from the second Series to that one in
all kinds of ways, none of them worked, either they produced an error or the
third Series completely disappeared.
So here is how I solved the problem:
- I still add the first and second Series as before, and ungroup the second
Series, and then add another Axis for that ungrouped Series.
- However, before I add the Axis for the ungrouped Series, I manually set
the Maximum value of the Scalings property for that Series. The maximum
value defines the highest value which will be reached by the Series and
directly influences the tickmarks on the Axis line as well as how the Series
is drawn.
- Now the trick is, that for every additional Series which I want to ungroup
and have it use the same Scale as the previous ungrouped Series, I also set
that Maximum Scalings property to the same value as for the previous Series.
That way, even though the Series is ungrouped, it is drawn with the same
Scaling as the previous Series.
Note that there are some things to consider when using this "technique":
- When I say "I set the Maximum value", I'm actually calling a function I
wrote which determines the Maximum value from a Series and rounds it up to
the next higher number in a similar way as OWC does it. That means, if your
highest value is 187, then the Maximum returned from my function won't be
187, but 200. That way the highest point of the Series is not drawn at the
very top of the chart, but still looks nice.
- Also, you want to make sure that this Maximum value is determined as the
highest value of all the Series which are using the same scale, otherwise
you will have a value trying to be drawn outside the Scale, and then OWC
will "assume control" and mess things up.
One more thing: The described technique worked fine for me in many cases but
one. I still don't exactly know what caused it, but it's definitely a bug. I
did everything as described above, but still one of the Series would be
drawn completely out of scale (which I guess is what I described above as
OWC assuming control of the scaling). It seemed to happen when a data point
of that Series is very close to the Maximum of the Scale (but not above it),
but did not really follow a pattern, so for example with the Maximum set to
100 the bug would show, but with 101 it wouldn't. The only way I could solve
this was to add some more to the Maximum Scale value, so now my function
which determines the Maximum adds an additional 15% to the value.
Finally, after all the talking I want to round this posting off with an
example:
<---- VBScript Example ---->
' Add Line which will have its own scale
set sc = cht.SeriesCollection.Add()
sc.Ungroup True
sc.Scalings(c.chDimValues).Maximum = GetScaleMax(ProjectedTotal)
' Add Axis for the ungrouped Series
Set ax = cht.Axes.Add(sc.Scalings(c.chDimValues))
ax.Position = c.chAxisPositionRight
ax.HasMajorGridlines = False
' Add another line using the same Scale
set sc = cht.SeriesCollection.Add()
sc.Ungroup True
sc.Scalings(c.chDimValues).Maximum = GetScaleMax(ProjectedTotal)
<---- End VBScript Example ---->
Some final comments to the example: I left out the part where you actually
assign data to the Series, of course. The GetScaleMax function is my own
function which returns the maximum value, in this case using an array called
ProjectedTotal.
Let me know if you need more help,
Sascha
