chart from pivot data, and sheet data?

[mark]

Hi.

The latest request is to do a chart which is partially from data in a pivot
table, and partially from static data in another sheet.

The pivot table itself is based upon too many rows to handle in Excel
2003... there's a pivot cache built from an ADO recordset, returned from
Oracle tables.

Is it possible to present a chart with the data mentioned which would be
dynamically updated when the selections in the pivot table are changed?

If not, here's what I've thought up as of now:

1) Fire code when the sheets' PivotTableUpdate event is fired
2) Have that code Copy PasteSpeckial values the data in the pivot table
3) Create the chart off of the static version of what was selected in the
pivot table, and the static data from the other source sheet.

That should work...

but, I'm open to suggestions.

 

[Jon Peltier]

You can use pivot table data in a regular chart, but you have to specify it
carefully. Create a chart based on the non-pivot data, or starting with a
blank cell (no data). In step 2 of the wizard, or after creating the chart,
from Chart menu > Source Data, click on the Series tab, add your series on
at a time, and select the X and Y value ranges. If you start with any data
in the pivot table selected, the chart becomes a pivot chart. If you use the
Data Range tab, the chart becomes a pivot chart. The pivot table can still
be live, you don't have to paste values. However, the chart will not
automatically update its data range with the change in the pivot table,
unless the pivot table only changes one dimension, which is the number of
points in a series, and you have a dynamic name defined to track that.

- Jon

 

[mark]

okay, thanks, Jon.

I may give that a try.

Another thing I thought of since the post would be to use =GETPIVOTDATA()
formulas to summarize the pivot's current selected data...

And then do the chart off of the range with the =GETPIVOTDATA() formulas,
and the static data.

Thanks for you suggestion!
Mark

 

[Jon Peltier]

You could, but it's an extra layer.

- Jon

 

[mark]

Another thing I thought of since the post would be to use =GETPIVOTDATA()

formulas to summarize the pivot's current selected data...

You could, but it's an extra layer.

Yeah, I see that now. I had thought of it before I saw your info that you
can just add Pivot data directly to a chart.

I went ahead and did the first part of your suggestion last night... put the
pivoted data into the chart, piece by piece.

It's not my data, and I'm a little unclear as to the source of the capacity
data to be charted against it... have a question into the data owner on that,
now.

I think it will just be one dimension of the Pivot that is changing...
there's one column field in it, and they would be selecting different ones of
them.

The row fields are year and month in one, and week in another... those might
be different.

But I think I'm ready to tackle creating the dynamic formula that tracks how
many columns are currently visible in the pivot table, to be used in the
definition of the SERIES in the chart.

Thanks for your help, Jon. I've looked through your website before, and
know the type of thing you can do. I've put your idea on clustered stacked
bar charts (by creatively charting blank space, essentially), to use a couple
of times.

thanks.

 

[mark]

Jon,

I've looked at your downloaed the zip of the dynamic chart...

I see what you're doing there.

I think it's complicated a little bit by the fact that one source is from
the pivot, and one source from the static capacity grid (I don't think I said
that before... the pivot data is extracted shop floor hours requirements, and
the static data is a capacity definition).

But, first, I'm not sure they need the chart to be dynamic... but it would
be nice.

I think it's doable, in a couple of ways. One would be to lump the capacity
data into the pivot, with a defining field... might run inot some issue with
that.

And another way woud be to do the series definition of the capacity
similarly to the dynamic series definition of the pivot data. Probably run
into a bump here and there getting that in, but it should work.

Thanks again for your suggestions.
Mark