chart from Pivot chart

I have a Pivot Table that is generated from an OLAP cube. I created a
Pivot Chart, but this is not as flexible as I would like. I want to
add a couple of extra trendlines so that I can see if my data series
falls within an acceptable range.

To accomplish what I want I create a standard chart, use VBA to get the
series & axes from the Pivot Chart, and then add the series and axes to
the standard chart. All of that is working well. Except for one
thing. I can't get the category axis to format the same way as it is
formatted on the Pivot Chart.

For example, if I use a date as my row values on the Pivot Table and
select the lowest drill-through value(which is month in my case), the
Pivot Chart will show the Year, Quarter and Month as category names.
My formatting problem has to do with the orientation and alignment of
each category name level. The pivot table seems to able to have a
different orientation or alignment for the Year, Quarter or month,
which I have not been able to reproduce on the standard chart.

Any idea on how I can exactly reproduce the formatting of the Pivot
Chart category asix? Or is there another way I could do what I
attempting to do with the trendlines? Again though, other than the
formatting of the category axis, everything is working well.

Is the pivot chart using a range more than one column wide for its X values?
You can apply the same shape range to the first series' .XValues.

What is the information you want to convey on the axis? My preference is to
use a single column of dates for my .XValues, making sure the chart uses a
time-scale for the axis, and playing with the category axis scale parameters
and number formats to get the spacing and labels I want. Trendlines based on
a time scale axis will be more reliable than those based on a category-type
axis, which merely uses the counting numbers {1, 2, 3,...} for the X
variable in the fitting calculations.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


<[email protected]> wrote in message
news:[email protected]...
>I have a Pivot Table that is generated from an OLAP cube. I created a
> Pivot Chart, but this is not as flexible as I would like. I want to
> add a couple of extra trendlines so that I can see if my data series
> falls within an acceptable range.
>
> To accomplish what I want I create a standard chart, use VBA to get the
> series & axes from the Pivot Chart, and then add the series and axes to
> the standard chart. All of that is working well. Except for one
> thing. I can't get the category axis to format the same way as it is
> formatted on the Pivot Chart.
>
> For example, if I use a date as my row values on the Pivot Table and
> select the lowest drill-through value(which is month in my case), the
> Pivot Chart will show the Year, Quarter and Month as category names.
> My formatting problem has to do with the orientation and alignment of
> each category name level. The pivot table seems to able to have a
> different orientation or alignment for the Year, Quarter or month,
> which I have not been able to reproduce on the standard chart.
>
> Any idea on how I can exactly reproduce the formatting of the Pivot
> Chart category asix? Or is there another way I could do what I
> attempting to do with the trendlines? Again though, other than the
> formatting of the category axis, everything is working well.
>