A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pivot table chart problem, How to show different subtotal levels



 
 
Thread Tools Display Modes
  #1  
Old March 9th, 2010, 09:04 AM posted to microsoft.public.excel.charting
Ycorth
external usenet poster
 
Posts: 1
Default Pivot table chart problem, How to show different subtotal levels

Hi,
Using Excel 2003,
I have a pivot table using external data via ODBC.
I have a chart based on my pivot table.
The table and chart are working correctly.

The PTcontains two levels of sales subtotals
1st level is by county
2nd level is by state.

My client is telling me that the data in the chart is too detailed.

He wants to have the both levels of subtotals in the PT,
but only the county subtotals in the chart.
If I delete the county sales subtotals from the chart, they are also deleted
from the PT.

Is it possible for the chart to be based on the PT and not show the same
subtotal levels ?

---
I used to have a workaround for this where I hid a second PT underneath the
chart.
This second PT was based on the same external ODBC data but did not have the
county subtotal details.
One satisfied client.
And that was great while it lasted ...

The problem now is that the client needs to use the data filters in the
visible PT
and his filtering is not being reflected in the second PT and therefore not
being updated on the chart.

So I have tried basing the second table on the first table hoping to catch
any filtering but that is not working.
Maybe I'm doing it wrong but the filtering in the first PT is not having any
effect on the chart.

Any help appreciated.
TIA,
Ycorth


  #2  
Old March 9th, 2010, 12:27 PM posted to microsoft.public.excel.charting
nurrrthk
external usenet poster
 
Posts: 5
Default Pivot table & chart problem, How to show different subtotal levels

I think you meant to write :



He wants to have the both levels of subtotals in the PT,

BUT ONLY THE STATE SUBTOTALS IN THE CHART



That way your question makes more sense, even if I don't have the answer.






  #3  
Old March 12th, 2010, 02:36 PM posted to microsoft.public.excel.charting
AdamV[_2_]
external usenet poster
 
Posts: 38
Default Pivot table chart problem, How to show different subtotal levels

You can use VBA to twiddle the filters, usually based on other controls
such as combo boxes (choose something in a combo box or data validation
list, this triggers the macro, which sets filters on multiple PTs)

Or you can upgrade to Excel 2010 (due in May / June) and use slicers,
which are designed to do exactly what you need here - provide easy to
use filtering that you can link to multiple pivot tables if you wish.

Hope this helps

Adam


On 09/03/2010 08:04, Ycorth wrote:
Hi,
Using Excel 2003,
I have a pivot table using external data via ODBC.
I have a chart based on my pivot table.
The table and chart are working correctly.

The PTcontains two levels of sales subtotals
1st level is by county
2nd level is by state.

My client is telling me that the data in the chart is too detailed.

He wants to have the both levels of subtotals in the PT,
but only the county subtotals in the chart.
If I delete the county sales subtotals from the chart, they are also deleted
from the PT.

Is it possible for the chart to be based on the PT and not show the same
subtotal levels ?

---
I used to have a workaround for this where I hid a second PT underneath the
chart.
This second PT was based on the same external ODBC data but did not have the
county subtotal details.
One satisfied client.
And that was great while it lasted ...

The problem now is that the client needs to use the data filters in the
visible PT
and his filtering is not being reflected in the second PT and therefore not
being updated on the chart.

So I have tried basing the second table on the first table hoping to catch
any filtering but that is not working.
Maybe I'm doing it wrong but the filtering in the first PT is not having any
effect on the chart.

Any help appreciated.
TIA,
Ycorth


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.