Charting 2 pivot data items as one result

I have a pivot table that has a Count of Tools and a
count of plants. What I want to chart is the result of
the Count of Tools/Count of Plants. I tried using
formulas from the PivotTable tools but I can't seem to
make it work.

I hate to think that I have to write the code to read the
pivot table and place the valuse in cells below the pivot
table then chart that range.

Thanks for the help.


Scott

Scott -

You wouldn't need VBA code to do this. If you know the structure of
your pivot table, you can put a formula in a cell or cells outside the
pivot table to give you this value.

Make a regular chart, not a pivot chart. You can't add data outside of
the pivot table to the pivot chart, and you can't do a lot of formatting
to a pivot chart, either. But you can add pivot table data to a non
pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Scott Stewart wrote:

I have a pivot table that has a Count of Tools and a
count of plants. What I want to chart is the result of
the Count of Tools/Count of Plants. I tried using
formulas from the PivotTable tools but I can't seem to
make it work.

I hate to think that I have to write the code to read the
pivot table and place the valuse in cells below the pivot
table then chart that range.

Thanks for the help.


Scott

I have to use a pivot table because the data comes from a
sheet with tons of data. In my code I create a chart,
but it defaults to a pivot chart. I have code that reads
the pivot table and calculates the values I want and when
I try setting the range into the chart source I get an
error saying I cannot change the Pivot Data. I don't
want to. Either show me what you are saying with an
example or can you tell me how to set the chart
sourcedata. Either way I would greatly appreciate your
help.


Here is my code:

arange is a type. In a previous routine I create a range
of data from the pivot table and save the range info.

Sub BuildEAToolChart(ByRef aRange As ThisRange)
Dim wksht As Worksheet
Set wksht = Worksheets("EA Tools")
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=wksht.Range
(wksht.Cells(aRange.StartRow, aRange.StartCol),
wksht.Cells(aRange.LastRow, aRange.LastCol)), _
PlotBy:=xlRows
ActiveChart.Location Whe=xlLocationAsObject,
Name:="EA Tools"
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "% Of Events with E&AT Report"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue,
xlPrimary).AxisTitle.Characters.Text = "% Events"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.NumberFormat = "0%"
End Sub





-----Original Message-----
Scott -

You wouldn't need VBA code to do this. If you know the
structure of
your pivot table, you can put a formula in a cell or
cells outside the
pivot table to give you this value.

Make a regular chart, not a pivot chart. You can't add
data outside of
the pivot table to the pivot chart, and you can't do a
lot of formatting
to a pivot chart, either. But you can add pivot table
data to a non
pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Scott Stewart wrote:

I have a pivot table that has a Count of Tools and a
count of plants. What I want to chart is the result
of
the Count of Tools/Count of Plants. I tried using
formulas from the PivotTable tools but I can't seem to
make it work.

I hate to think that I have to write the code to read
the
pivot table and place the valuse in cells below the
pivot
table then chart that range.

Thanks for the help.


Scott

.

I fixed it by setting a range to the "aRange" parameters,
then I called the Charts.Add. This allowed the chart to
be referenced to the selected area of the page. I was
able to set the sourceData and it worked fine.

Thanks for the help

-----Original Message-----
I have to use a pivot table because the data comes from
a
sheet with tons of data. In my code I create a chart,
but it defaults to a pivot chart. I have code that
reads
the pivot table and calculates the values I want and
when
I try setting the range into the chart source I get an
error saying I cannot change the Pivot Data. I don't
want to. Either show me what you are saying with an
example or can you tell me how to set the chart
sourcedata. Either way I would greatly appreciate your
help.


Here is my code:

arange is a type. In a previous routine I create a
range
of data from the pivot table and save the range info.

Sub BuildEAToolChart(ByRef aRange As ThisRange)
Dim wksht As Worksheet
Set wksht = Worksheets("EA Tools")
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=wksht.Range
(wksht.Cells(aRange.StartRow, aRange.StartCol),
wksht.Cells(aRange.LastRow, aRange.LastCol)), _
PlotBy:=xlRows
ActiveChart.Location Whe=xlLocationAsObject,
Name:="EA Tools"
With ActiveChart
.HasTitle = True
.ChartTitle.Text = "% Of Events with E&AT Report"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue,
xlPrimary).AxisTitle.Characters.Text = "% Events"
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Selection.TickLabels.NumberFormat = "0%"
End Sub





-----Original Message-----
Scott -

You wouldn't need VBA code to do this. If you know the
structure of
your pivot table, you can put a formula in a cell or
cells outside the
pivot table to give you this value.

Make a regular chart, not a pivot chart. You can't add
data outside of
the pivot table to the pivot chart, and you can't do a
lot of formatting
to a pivot chart, either. But you can add pivot table
data to a non
pivot chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Scott Stewart wrote:

I have a pivot table that has a Count of Tools and a
count of plants. What I want to chart is the result
of
the Count of Tools/Count of Plants. I tried using
formulas from the PivotTable tools but I can't seem
to
make it work.

I hate to think that I have to write the code to read
the
pivot table and place the valuse in cells below the
pivot
table then chart that range.

Thanks for the help.


Scott

.

.

Hi All,

I also have the same issue..

I have a query on Pivot Table and pivot chart. In our application, We draw a pivot table for which a chart also is displayed .I use the same process as in MS BI Portal.

I need to show some extra fields in the pivot chart which will not be displayed in pivot Table like I have Product sales in pivot table but sales, profit will come in Pivot chart.Is it possible.

I supply XML for Chart separately and Pivot Table separately.. And when user changes the Chart I have to check the present XML of Pivot chart and change the Pivot Chart format also accordingly. I hope to capture one event (Query..?)

I think there is another approach also.I just draw two Pivot tables.One with the Table's requirement.Other with The chart requirement. and make the dummy Pivot table (created just for chart ) as source of Pivot table..and make this dummy one as invisible..(Is it possible..Once I assign the data source THe visibility property does not work..???)

Thanx
Jegan



Is it possible..Plz throw some light