Pivot Table Totals When Using Pages

Dear fellow Excel 2003 users,

I have a pivot table that lists hours of several employees.  A manager
asked if there was a way to view just the non-billable hours.  No
sweat, I added a page field and selected non-billable and the sum of
hours and the grand total showed just the non-billable hours.
However, he was hoping to see the data section of the pivot table show
just the non-billable hours with a column next to it showing the
percentage of non-billable compared to the grand total of billable and
non-billable.  

Problem:  after scouring Google and the Help file, I cannot find a way
to keep a filtered data section (just non-billable hours) of a pivot
table while maintaining the complete grand totals (all hours - both
billable and non-billable) without copying and pasting the grand
totals as values off to the side of the pivot table.

Has anyone ever come up with a solution?  Thanks in advance!

Kevin
0
nospam4107 (39)
1/29/2007 9:50:02 PM
excel 39879 articles. 2 followers. Follow

1 Replies
607 Views

Similar Articles

[PageSpeed] 6

Hi

I can only do it by "cheating"
With a simple table of Name, Hours, Type (Billable or Non Billable)  I 
produced a PT with Name as Row, Type as Column and Hours as Data, PT 
going to new sheet at A1.

On PT sheet in E4 type % Non Billable
in E5
=GETPIVOTDATA("Hours",$A$3,"Name",A5,"Type","Non 
Billable")/GETPIVOTDATA("Hours",$A$3,"Name",A5)
format cell as Percentage
Copy down as Required
Hide columns B & D
Format column E to look like PT format.

-- 
Regards

Roger Govier


"No Spam" <nospam@earthlink.net> wrote in message 
news:ikqsr25t59bfmfuaa5cse8hqe0ipkuc7de@4ax.com...
> Dear fellow Excel 2003 users,
>
> I have a pivot table that lists hours of several employees.  A manager
> asked if there was a way to view just the non-billable hours.  No
> sweat, I added a page field and selected non-billable and the sum of
> hours and the grand total showed just the non-billable hours.
> However, he was hoping to see the data section of the pivot table show
> just the non-billable hours with a column next to it showing the
> percentage of non-billable compared to the grand total of billable and
> non-billable.
>
> Problem:  after scouring Google and the Help file, I cannot find a way
> to keep a filtered data section (just non-billable hours) of a pivot
> table while maintaining the complete grand totals (all hours - both
> billable and non-billable) without copying and pasting the grand
> totals as values off to the side of the pivot table.
>
> Has anyone ever come up with a solution?  Thanks in advance!
>
> Kevin 


0
roger5293 (1125)
1/29/2007 11:17:58 PM
Reply:

Similar Artilces:

Linking to internal visio pages
Hi all, I've been trying to figure out a way to link to an specific page that is imbedded in a visio file saved as a web page. For example: I have a visio file that consists of 12 pages accessed by tabs along the bottom of the screen. When I export this file as a webpage, access to the individual pages appears as a pull down menu on the left side of the page. The file is given an address, but not the individual pages, so if I create a link to the visio file it always defaults to the first page. Is there anyway to link to specific pages within the file? Thanks in advance, Tim ...

use of xsd.exe
VS2005. I'm running a stored procedure and am stuffing the output into a dataset. I want the dataset to be strongly-typed, but can't seem to get quite what I want out. Performing a "Fill" on a generic dataset, I can get the schema of the data being returned. A snippet appears below: <xs:element name="Table2"> <xs:complexType> <xs:sequence> <xs:element name="PRODUCT_GROUP" msprop:OraDbType="126" type="xs:string" minOccurs="0" /> <xs:element ...

Using insert to paste a row--how done in Excel 2007
Hi, In my old version of Excel, I could copy a row or chunk of rows, move to a new spot and use the "insert row" icon to insert the rows and paste it automatically. Now in Office 2007 it just inserts a row instead of what I have copied. I want it the old way! How do I do it? -- Thanks, PTweety R-click, Insert Copied Cells. pickytweety wrote: > Hi, > In my old version of Excel, I could copy a row or chunk of rows, move to a > new spot and use the "insert row" icon to insert the rows and paste it > automatically. Now in Office 2007 it just inserts a r...

Saving Excel workbook in SQL server using c#
Could anyone please help me out as to how we can save the excel workbook in the database and read it back. I was able to convert the text files and image files into binary format and save them to the DB and finally able to retrive them back in the same format. But was unable to do same for the excel object. Your help will be greatly appreciated. Thanks, regards, jitender ...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field service component. Where can I find this information? I need to move RMA into a data warehouse and am having significant trouble identifying the data flows. Thanks, Will You may want to contact I.B.I.S. They wrote the application. -- Charles Allen, MVP "WS" wrote: > The 9 SDK does not reference or define the fields or tables for the Field > service component. Where can I find this information? I need to move RMA > into a data warehouse and am having significant trouble identifying th...

Looking for someone who can integrate CRM with GP using SCRIBE Integration tool
Hi, Is anyone out there in Sydney area who would like to help us in doing integration between CRM 4 and GP 10 through SCRIBE integration tool for a fee? We are looking for someone who has already done integration between these two applications using SCRIBE. You will have to do bit of customisations in the SCRIBE standard templates. If anyone available, you can contact me through my email badri1203@gmail.com Badri ...

one page in booklet format
How do I tell Publisher 2000 to print just page one and 4 and then pause while i reinsert the paper to print pages 2 and 3? duff wrote: > How do I tell Publisher 2000 to print just page one and 4 and then > pause while i reinsert the paper to print pages 2 and 3? File > Print > Pages 1 to 1 > OK > Print as separate booklet = No. Flip pages. File > Print > Pages 2 to 2 > OK > Print as separate booklet = No. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

Using A CTabView
Hi, I need to implement a tab control which contains a number of custom controls on each tab page. For example my custom control has a number of LED type static controls and a list control. Its quite small, 200x200. I need a number of them in a tab page. They need to aligned underneath each other with a static label above each giving a description of each one. I also need to be able to scroll down if I have too many to fit into one page. Has anyone done something similar or can suggest where to look?? Cheers ...

Sharing chart on two pages
Hello, I have a worksheet where I put some datas and then under this datas is a chart. Place taken by this data isn't static - once there is more datas and the other time there is more datas. I have problem with this chart, because sometimes chart is shared between two pages on printing. Is there a possibility to use something like property "keep together" in crystal reports where object is printed whole on one page (if there is no enough place it's started pritnting on next page)? Maybe somebody knows how to do it in macro if there is no appropriote property -- Bes...

HOW USE 2 LANGUAGES IN AX. ???????????
Hello I have problem and I need Help: 1. Axapta using English Language in all system 2. I need write something in Russian Language. When I use Russian Keyboard and I wriet somethings in Axapta fields is ok, but after this Russian words was change and I see only symbol likely this ??????? (query) 3. In MS SQL database in the fields I see words likely this symbol ??????? (query) Please send my all information how I can write something in different Languages in this same Axapta - I need write simultaneously in 2 Languages. Please send answer on ma email: search1234@wp.pl Thank...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

Using a Space in Webbrowser Control
Hello: As usual, I've run into a problem with something that is probably so simple... any help is appreciated. I'm using the webbrowser control with the navigate method. The url I'm using needs a space (which I know is not normally allowed, but it works directly in firefox and IE). Here's some sample code: ------------------------------------ strWebPage = "http://www.imdb.com" &" ?" & Text2.Text txtURL.Text = strWebPage Web1.Navigate txtURL.Text Do While Web1.ReadyState <> READYSTATE_COMPLETE DoEvents Loop DoEven...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

Cash-Basis Reporting using GP Analytical Accounting
We are a new GP 8 customer, still in the process of implementation, migrating from QuickBooks Pro. We are a non-profit and have unique reporting needs for our board and donors. We switched from cash-basis to accrual accounting about 2 years ago. However, a lot of our reports are still run on a cash-basis level, we need to be able to report on a cash basis as well as accrual basis. I know ther eis a cash-basis tool add-on for GP from AIM technologies. But I was wondering if the GP Analytical Accouning or multidimensional module would give us the same functionality and flexibility in g...

Word 2003-Page Orientation
I have a long Word 2003 document with all pages in portrait orientation except one. That page contains a table the size of which requires landscape orientation. I have always done this by inserting section breaks before and after this page. But now for some reason I can't get the orientation to change either on-screen or in my printer. When I check the page orientation in that page (section) it is set to landscape. Suspecting typical Word corruption (this document was made from previous versons of the same stuff) I did the usual copy-and-paste all but the last few empty ...

2 Axis Pivot Chart
Is it possible to create a 2 axis (a combination of line & bar) chart from a pivot table? Sure is, did you try? Right click a series that you want on the secondary axis, choose Format Series, and on the Axis tab (Excel 2003 and earlier) or the Series Options tab (2007) select the Secondary Axis option. To change the chart type, right click on the series, choose (Change) Chart Type, and select the type you want. Of course, the pivot chart is likely to lose this formatting when it is refreshed, Which even Microsoft admits is a problem. They suggest recording a macro next time you f...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

How 2 sort an autosum total list after adding items 2 autosum item
Am using detail dollar amount list to insert new transactions in the middle of a the detail list, and they in turn get included in the 'autosum' total calc which is located in another item total summary list in the same workbook. If we try to sort the autosum total summary list, the autosum totals seem to loose their connection with the detail autosum calc list. Is there anyway to keep this connection, ie be able to add items into the detail autosum calc lists, and then sort the autosum summary totals ? -- Thanks again for your help. akm (XPpro,SP-2,IE-7,Office2003...

Please! The product release page filters are not working!!
After changing selection criteria, the result list does not honor those criteria. I select Great Plains New Releases for v8.0 and end up with all kinds of irrelevant items in the list. This is very frustrating! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://ww...

How do I determine whether my exchange server is being used to relay SPAM
This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C41E7A.F70E2800 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We have recently changed from Exchange 5.5 to 2003. We have also = instituted an signature program for our outgoing email. This week I have = been getting NDR's from sites and some say that our site is sending them = SPAM. I beleive that either these sites are registering the signatures = as SPAM or our server is relaying SPAM messages. How do I determine = this, I imagine that some logs would show t...

Create hyperlinks using VLOOKUP?
I would like to enter a model number(CM3636a) in A1. When entered, is there a way to lookup in a table and create a hyperlink automatically? I have hundreds of model numbers which I want to associate a diagram(jpg). I only want to do this once, then everytime the same model number is entered, a link is created to that diagram. Can anyone think of any ingenious ideas on how to achieve something like this? Joe Hi if you have the name of a file in column b try something like =HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) & ".jpg") -- Regards...