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


0
Ycorth
3/9/2010 8:04:57 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
1034 Views

Similar Articles

[PageSpeed] 43

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. :)






0
nurrrthk
3/9/2010 11:27:22 AM
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
>
>
0
AdamV
3/12/2010 1:36:11 PM
Reply:

Similar Artilces:

Problem with sheet tabs
My MS Office is Office 2003. While open a new workbook in Excel, the sheet tabs are not shown, I need to set by tools -> option and check the sheet tab every time. How to set the workbook opened with the sheet tabs ? Also, there is a extra menu tab "addin" shown at the top menu of workbook, i.e. File, Edit, View, Insert, ...., addin, Help. While addin is clicked, a message of "solution.xls not found" shown. How to fix it ? The first problem: Open a blank workbook, go to Tools - Option and change the settings. Now, close the workbook. Your changes sh...

ImageFrame Problem with Empty Reports
I am getting this "type mismatch" error when I try to open a report that has no records. The report normally displays a picture of the client if they have one or more records in the report. If they don't, it should be able to still open the report but just show no records. I have traced the source of the error to the following code in the pageheader of my report. ImageCyclist.picture=[picpath] The [picpath] is a field inserted in the pageheader that contains the pathname or link where the picture of the person is retrieved from. How can I prevent this error from appearing?? ...

Deleted scheduled transfers still showing on bill calendar
I have Money 06 and use to have a scheduled transfer in the bills section. Recently, I deleted the scheduled transfer from the bill page. However, the future transactions will not delete from the bills calendar. Does anyone know how to delete these future transactions from the calendar. If it makes a difference, the account that is being transferred to was originally set up as an online account and was recently changed to an offline account. Thank you ...

Charting multiple multiples
Hopefully I can explain this in a way that will be understood. Data: Place A Place B Place C FWD Back FWD BACK FWD Back Org_1 1 2 3 2 1 3 Org_2 2 5 4 6 1 4 I would like to place the data so that FWD and Back data is stacked on top of each other in different colors, and placed in one row on a 3-D chart as Org-1 and a second row as Org_2. Any ideas? Thanks. If you arrange your data like this: Back FWD Org_1 Place A 2 1 Place B 2 3 Place C 3 1 Org_2 Place A 5 2 ...

I am having problem while installing MS Dynamics in Win XP
I am trying to install Microsoft Dynamics in Win XP, it gives an error saying MS Exchange server 2003 SP1 has to be istalled first I dont have MS exchange server installed in Win XP,what I have is outlook only Can I go ahead and install SP1 for win XP ( THIS IS AN OFFLINE INSTALLTION RIGHT NOW I am not using outlook to get my Email) Let me know how to install the same and the precaution I have to take Most of the errors expected are given for WIN 200 SERVER & 2003 SERVER IN TECHNET HENCE THIS QUESTION TO TYE COMMUNITY Regards Harish We cannot help unless you are m...

Rasing Exchange 2000 server to native mode level
In my production environment I have a primary DC which is Windows 2000 server hosting DNS, DHCP and Active Directory, and one Exchange 2000 Standard on a sperate server part of the same forest. I do have one NT 4.0 in the same forest as my Exchange 2000 server. My Development environment Primary DC is on NT 4.0 server and they just have two way trust with Windows 2000 DC. I also have other development domains which are only Windows 2000 servers. My Questions is: Does rasing Exchange 2000 standard server level to native mode raises Windows 2000 servers to native mode? Any help is mu...

blue screen reboot problems
my home prem. vista pc keeps rebooting. Blue screen message is 2 seconds long so i can't read it and several attempts to resolve this by using system restore etc is only making it worse. I've run virus scans in safe mode nothing. My deskstop comes up at startup with "windows installer" 3x then a box says that windows has to close down and then it reboots after 30 seconds or so, no time to try and update to sp1 or find the problem list because it dumps to fast. The blue screen does say TCPIP error or something. Do i have to connect Mircosoft with $$ or is this not f...

How do I create this chart?
Item Height Width Min Max Min Max A 2 4 10 20 B 4 6 9 12 C (etc.) The chart would show a rectangular area for each item with the height plotted on the X axis against width on the Y axis. Thanks in advance for any help!!! Work out a series to tabulate the co-ordinates of the 4 corners (and a 5th point to go back to the beginning). Plot that series. Same for each subsequent series. -- David Biddulph "MEL101060" <MEL101060@discussions.microsoft.com> wrote in message news:8E6446AF-56EB-4D5D-BAC8-8B7AB72D8E75@micr...

Cutting a line in a line chart when data series stops
I have a series like so: Jan 10 Feb 10 Mar 11 Apr 12 May Jun Jul And so on - the rest of the year is blank. When I draw a line chart for the full 12 months the series draws a line up to April, then plummets to zero in May. I want the line to just stop at April. If I use a series as above and genuine empty cells for the end of the year this ok, but since my May cell reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a blank cell. Please help!! Change your formula to =IF($A11<=$D$17,G11,NA()) -- David Biddulph "DannyS" <Da...

Vista and Money 2005 ofx import problem
I recognise some names here from another forum but I thought it may be helpful to canvass some more opinions/expertise. I have Vista Ultimate running and have succesfully loaded Money 2005 UK and updated via the internet. Everything works fine except the bit I need ! I use Barclays Bank and have downloaded .ofx files for several years (Barclays does not support QIF). When trying to do this in Vista I get an error message saying the file is corrupt or not recognised. I have tried all manner of things including testing the ofx file etc and it appears fine and will load into Money run...

problem installing the windows vista security error code 80070020
it installs hald way then stoped with the error code 80070020 dont no wat to do help plz You receive error 0x80070020 when you use the Windows Update Web site or the Microsoft Update Web site to install updates: http://support.microsoft.com/kb/883825 dariyel wrote: > it installs hald way then stoped with the error code 80070020 dont no wat > to > do help plz dariyel wrote: > it installs hald way then stoped with the error code 80070020 dont no wat to > do help plz Is the keyboard not functioning properly ? Please see: How to ask a question h...

Pivot Table Design
Hello, I am working on learning Pivot Tables and wanted some advice. I would like to use the Pivot Tables to work with state testing data. Fields that I would be working with would include: Student Name Student Grade Level Race Special Ed (Y or N) English Language Learner (Y or N) Free and Reduced Meals (Y or N) Math Level (1, 2, or 3) Math Score (a 3-digit raw score) Reading Level (1, 2, or 3) Reading Score (a 3-digit raw score) I would like to be able to generate reports that would show the students in a race that scored at a certain level, or students in a race, who are free and re...

counter problem(you really want to read this)
i have a workbook that contains two sheets. Sheet one contains a list of names. Each name references an information sheet for that name. I wish to collect data for each name for a specified number of days "x". When that number of days is met, then I wish to click on a button on the toolbar to delete some number "x" of days of data from 1 - "x" including "x". I wish to do this to make the data more current hence meaningful ...

Problem sharing a workbook
Hi, I've created an Excel workbook (Excel 2003 (11.8120.8122) SP2), placed it on a shared network server, and then selected Tools>Protection>Protect and Share Workbook... with the "Share and track changes" option selected, and password protection set. However, if I attempt to open it when someone else has it open, I get the message stating that the workbook is opened by user so and so and am given the option to open it as Read-Only, Notify, etc. Any idea why this is happening? Thanks, Linda ...

HL-DT-ST DVD+-RW GSA-T2N1 problems
Running Microsoft Media Center Edition SP3 on Dell Inspiron 6400. Poblem - select files from explorer - right click, select Sent To - drive does not appear in window Problem - select files from explorer - drag them to drive D:\ - get error D:\ is not accessible. Incorrect function. What is wrong. Pierre C wrote: > Running Microsoft Media Center Edition SP3 on Dell Inspiron 6400. > > Poblem - select files from explorer - right click, select Sent To > - drive does not appear in window > > Problem - select files from explorer - drag them to drive D...

Unable to load and show videos from PBS
I can not get PBS.org/nbr videos to play on my computer. There is a laptop in the house that will play these videos. Both my computer and the laptop are connected to a router. I have tried to duplicate the security settings on both machines. Pop up blocked has been removed from that web site, even though they are blocked on the laptop. The pbs site will come up offering the videos, however, the videos seem to almost load, the 'circles' indicating the video downloaded, however, it will not show. If this is not the news group for this problem, please send me there. W...

How to create a line chart with two X-axes in Excel
PLEASE HELP ME!!!!! How can I create a line chart with two X-axes in Excel? select one of your data series and change axis to secondary. <chart><<chart options><Axis> Select secondary x axis. "JaVaughn" wrote: > PLEASE HELP ME!!!!! How can I create a line chart with two X-axes in Excel? Hello, thank you for replying. Which chart type would allow for 2 X-axes. The only chart type that I know that allows for 2 axes is "Line Chart with 2 Y-axes". For the above-mentioned chart, the option to designate a primary and secondary x-axis is display...

Relationship problems
I have a DB with 6 tables tbl 1 (Person's Info) ClientNum (PK) FName LName + all person info tbl 2 (Employment info) EmpID (PK) ClientNum EmpCodeNum EmpStatus TrainingRelated StartDate EndDate tbl 3 (Employer Codes) EmpCodeNum (PK) EmployerName tbl 4 (Trades) TradeNum (PK) TradeName tbl 5 (Pri-Training) PriTraID (PK) ClientNum TradeNum tbl 6 (Sec-Training) SecTraID (PK) ClientNum TradeNum The problem i'm have is that when i go to create a form to enter all the clients data the de...

Having problems opening pst file
When I try to open a pst file - I get the following message "Properties for this informaton service must be defined prior to use" then I get another pop-up "Errors |\ve been detected in the file C:\WINDOWS\APPLICATION DATA\MICROSOFT\OUTLOOK\outlook.pst. Quit all mail-enabled applications, then use the Inbox Repair Tool" Please help!!! I have important contact information I can't get to. GHR 770-451-8565 x11 "GHR" <anonymous@discussions.microsoft.com> wrote in message news:012d01c3ddf8$5ef50650$a301280a@phx.gbl... > When I try to open a...

Chart with two types of lines
I am plotting data from 2006 and now starting to plot data from 2007 in a line chart. I would like the line from 2006 to be a solid line, and the line from 2007 to be a dotted line. This is measuring the same data, just in different years. When i try to add a different series, the new line for 2007 appears, and the 2006 line disappears. I'm not sure how I get both of them to appear at the same time. Any idea what I'm doing wrong? B Have you checked Chart/ Source Data/ Series tab to see what you've got for your two series? It may also be worth thinking about whether it might be...

Send Mail Problem
Hi all One of our user got the below two message from postmaster, i try to disable the rule(list.dsbl.org) from the connection filtering, but the problems still exists. I search my mailserver ip on list.dsbl.org, it listed. To removal need one week. Have any faster way to fix this problems? abc@abc.com on Thu, 7 Dec 2006 08:28:05 +0800 There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <"Mailserver Host" #5.5.0 smtp;553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1)> 1...

Help: pivot chart data in secured Access database wont show fields
Hello Is there a way for me to crate a pivot chart in Excel where the data is in a secured access database. By default (ie using system.mdw) the Access security allows a user view permissions on the tables. When connecting to the database the tables are visible, but the next step where the fields get shown doesnt happen, no fields can be selected. Is this a security issue? Is there a way around this? Thanks A ...

Filtering data to different worksheets
Hi all, I'm sue this should be really simple to do but I'm struggling to get i to work. I have a list of account numbers and who has keyed each one on. I want to filter down the list so that I have a seperate worksheet fo each person with all of the account numbers (plus other columns of inf ie. address, phone number) that they have done. I've tried vlookup but that, obviously, only looks up the 1st accoun number. Can anyone point out what I'm missing? Thank -- jappe ----------------------------------------------------------------------- japper's Profile: http://www...

Bar Chart
I would like to create a bar chart with the information below. I would like the lost and sold to be shown on one bar stacked for each year. So I would have 3 bars for Jan - 2003, 2004 and 2005 each bar showing two colors. Any help would be appreciated. Jan 2003 sold = 4 lost = 2 Jan 2004 sold = 6 lost = 2 Jan 2005 sold = 10 lost = 3 Set up your data like this: A B C Month Sold Lost Jan03 Jan04 Jan05 HIghlight the data (including Month, etc). Select the Chart WIzard tool. CHoose Stacked Colum chart with series in columns. That should get you there. Barb Reinhardt ...

How can I data sort to more than 3 levels
Is there a technique or add-in utility that will let me do a data sort in Excel to 4 or more levels at once. Currently you can only do 3 levels, and then you have to separate the data to do more. Thanks for any help -- Pam LaDue ------------------------------------------------------------------------ Pam LaDue's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36773 View this thread: http://www.excelforum.com/showthread.php?threadid=564880 You have replies at your other post. Pam LaDue wrote: > > Is there a technique or add-in utility that will let me d...