inactive (hidden) expressions affect line chart formatting, what to do?

[#1]

I wrote a simple macro to hide expression on a chart (each corresponding to a cost item calc) depending on a multiselect in a list box.

The problem is that when I hide a certain expression it still affects the formatting, so that the expressions shown on the graph are not scaled. For example, I only select misc. costs (small, say, on the order of 10) and hide all totals (on the order of 1000), and I'm left with a line at the very bottom of the chart as if the totals were shown.

The only solution I found is to assign all hidden expressions to secondary axis. If only i could hide the secondary axis itself this would've been perfect. Otherwise two axis are confusing.

Does anyone know a good solution for either forcing to scale the visible expressions or to hide the secondary access? Would be much appreciated!

-Pavel

[johnw · Accepted solution]

I'd suggest disabling rather than merely hiding the expressions. I'm pretty confident that disabled expressions don't affect formatting. That's the approach I used to create dynamic charts. Every expression must then be self-contained, of course, not depending on the result of any other expression.

As for hiding the secondary axis, I think that's just a matter of setting "Hide Axis" for every expression that USES the secondary axis. So perhaps that's another approach more in line with your current one.

[#3]

Thank you much, this works!

Enabling isn't a good solution for me since cost items refer to others up the hiararchy (totals and such)

The code I used is

if (I WANT TO SHOW THIS EXPRESSION)then
cp.Expressions.Item(i).Item(0).Data.ExpressionVisual.Invisible = false
cp.Expressions.Item(i).Item(0).Data.ExpressionVisual.PrimaryAxis = true
cp.Expressions.Item(i).Item(0).Data.AxisData.Show = true
exit for
else
cp.Expressions.Item(i).Item(0).Data.ExpressionVisual.Invisible = true
cp.Expressions.Item(i).Item(0).Data.ExpressionVisual.PrimaryAxis = false
cp.Expressions.Item(i).Item(0).Data.AxisData.Show = false
end if
[/code]</body>