Surpressing rows and columns with zero values in a pivot table

Let's say I want a report showing the number of times that my employees, 
grouped in offices, markets and regions, makes a particular kind of error.  
At the end of the month, I'd like to create a pivot table that says, "This 
office had this number of errors of this type."  I can create a pivot table 
that does that, but it includes all the employees that have zero errors, 
making the report unwieldy.

How can I format the pivot table to hide any row or column that has no value 
in it?

0
Utf
1/14/2010 7:39:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1490 Views

Similar Articles

[PageSpeed] 9

Your issue is that it has a value. Something adding up to zero is just as 
valid as something adding up to any other number. The pivot table makes no 
distinction. I am assuming that you do not have show all members turned on. 
By default it is not turned on.

What does your source data look like and do you have the ability to suppress 
the zeros value employees prior to creating the pivot table?

FYI other cube programs such as Essbase do have the abiltiy to suppress 
missing and or zero values but that does not help you in your pivot table...
-- 
HTH...

Jim Thomlinson


"Jack" wrote:

> Let's say I want a report showing the number of times that my employees, 
> grouped in offices, markets and regions, makes a particular kind of error.  
> At the end of the month, I'd like to create a pivot table that says, "This 
> office had this number of errors of this type."  I can create a pivot table 
> that does that, but it includes all the employees that have zero errors, 
> making the report unwieldy.
> 
> How can I format the pivot table to hide any row or column that has no value 
> in it?
> 
0
Utf
1/14/2010 7:51:02 PM
Reply:

Similar Artilces:

Column BookID doesn't exist.
HI friends i am again here with my problem.I have written a code which is compiled without error,But when i want to debug it ,it shows the error Column "BookID" doesn't exist. I dont know why, Becoz in my Databank there is Column BookID. It is my code: void CBookInformationView::DoDataExchange(CDataExchange* pDX) { CRecordView::DoDataExchange(pDX); // you can insert DDX_Field* functions here to 'connect' your controls to the database fields, ex. DDX_FieldText(pDX, IDC_TITLE, m_pSet->m_BookTitle, m_pSet); DDX_FieldText(pDX, IDC_CATEGORY, m_pSet->m_Category, m_p...

Double value subtraction problem....
Hi When I tried to subtract two double values its giving unexpected results!!!! Eg:- 11.846000000000 - 11.846000000000 Its giving some values like -1.02383489238E12 !!!! instead of ZERO !!!! In an MSDN article (Q59407) it says that " subtracting double values greater than or equal to 1.0E+025 may return inaccurate results". But didn't say anything on how to rectify it!!! How to solve this prob? Please help..... rgds, RENAK RENJITH A K wrote: > Hi > When I tried to subtract two double values its giving unexpected > results!!!! > ...

Check DataGridView for existing row
Hello all, I thought this would be simple, I have a DaraGridView with 200 or so rows. All I want to do is check every row to see if a particular string value exists in column[1]. What's the best way to achieve this? I don't really want to do a foreach as there's about 100 + values to compare. Regards, Jon "Jon" <jonmyates@gmail.com> wrote in message news:b6eb3cac-c275-4c07-9360-38fb8a6f8fe2@o10g2000yqa.googlegroups.com... > I thought this would be simple, I have a DaraGridView with 200 or so > rows. All I want to do is check every row to...

File handle in global value
I open a file in one thread, save the file handle in a global value(ignoring for a moment that its not recommended), then close the thread , open a new thread and use the handle inside it to access the file and finally close the file in this thread. Is it legal from the point of view of C++/MFC(again,ignoring for a moment that its not recommended)? Regards Mandi >I open a file in one thread, save the file handle in a global value(ignoring >for a moment that its not recommended), then close the thread , open a new >thread and use the handle inside it to access the file and f...

Automatic recognition of a starting point for a column calculation
Hi Group, I'm having difficulties finding a way that will automate my calculation of Standevp as shown in image (Column AC). The variable is the starting date of the Monthly Performance as shown in image (Column V) What I'm looking for is a way that automatically finds the starting point for that calculation so I don't have to go into each tab and manually change the starting date. The image is located in this post i just created. http://excelproblems.wordpress.com/2010/03/30/hello-world/ Thanks for your help. try this =STDEVP((INDIRECT("v"&MATCH...

GAL
Hi - Currently our GAL view is: Name, Business Phone, Office, Title, Company and a few other Exchange related fields. Is there a way to set what columns are displayed here? I would like to add mobile phone, address etc... Thanks - Sean No "SD" <smd6169@hotmail.com> wrote in message news:OyMFPi8vGHA.4160@TK2MSFTNGP06.phx.gbl... > Hi - > > Currently our GAL view is: Name, Business Phone, Office, Title, Company and > a few other Exchange related fields. Is there a way to set what columns are > displayed here? I would like to add mobile phone, address et...

Use a Date range to sum a column
What is the formula to sum Column F (the price) if the date is between 1/1/04 and 1/31/04 Date Pric 1/1/04 1073 2-A Druid Hills John Willis Homes Roger Swims $18,000.0 2/2/04 1074 3-A Druid Hills John Willis Homes Roger Swims $18,000.0 1/1/04 1075 1/7/04 1076 29 Darthmouth John Willis Homes Kevin Aycock $21,000.0 Thank you for your help. Looks like you are totaling per month =SUMPRODUCT(--(MONTH($A$2:$A$200)=1),--($A$2:$A$200<>""),$F$2:$F$200) if you r...

inserting multiple columns from combo list
Hey everybody, I am a newbie to access. I trying to pull employee firstName, lastName, and middleInitial from a table and insert them into one employee field in a timecard table. I get the drop down list to show complete names in the 3 columns but when I choose a name I only get the first name inserted into the employee field. Evidently the query is finding everything but I can't get them to cancatenate into the field. Any help is greatly appreciated. Thanks, Andre Use the Column property of the combo box. The column reference is 0 based, so assuming your columns are First, Mi...

formula switches to value ?!?
what toggle, got switched where ? if A7 contains the value: "Christopher" and in B9 I enter: "=A7", it automatically switches the formula to the resulting value (both in the cell, and in the formula bar; the value of B9 is now not "=A7", but: "Christopher") thanks in advance, -mark mark kubicki wrote: > what toggle, got switched where ? > > if A7 contains the value: "Christopher" > and in B9 I enter: "=A7", it automatically switches the formula to the > resulting value (both in the cell, and in the formula bar; ...

Macro to color fill rows?
I know Excel doesn't read fill color but can you make a macro that finds all cells with the word "total" in it and fill color that entire row? Thanks in advance for any help! Jennifer Try: Sub ordinate() Dim r As Range For Each r In ActiveSheet.UsedRange If r.Value = "total" Then r.EntireRow.Interior.ColorIndex = 6 End If Next End Sub I picked 6 - yellow -- Gary''s Student "Jennifer" wrote: > I know Excel doesn't read fill color but can you make a macro that finds all > cells with the word "total" in it and...

Truly null value
Hi. Is there a way to enter a TRULY null value in a cell? I intend for the formula below to leave the cell blank if the criteria is met. My problem is that when I go to graph this, it graphs the point as zero. If I completely delete the formula so that the cell is empty, it will not graph the point at all (This is what I want ...) Thanks, Mike. Only workaround is to use NA() =IF(A1="",NA(),A1) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Mike D." <anonymous@discussions.mic...

euro 2004 league table
Hi, im trting to create a euro2004 league table,by entering the scores, allocating relative points to each team then sorting each league of 4 teams in order. Everything is ok if they required sorting by points only, but in the case of teams being equal on points, their position in the league is determined by the result of matches played between the two teams in question. If anyone has any thoughts that may help.....?# Thannks in anticipation Yogi. --- Message posted from http://www.ExcelForum.com/ Do you mean head to head games or just total games played? If it's total games played, t...

Formatting page header/footer using cell values.
Hello experts! Excel has a nice option to format various parts of a chart using cell values. Can the same be done for headers/footers, e.g. that the header displays value of cell A5. Thanks, Marko. Hi Marko, Try this... ActiveSheet.PageSetup.CenterFooter = Range("A5").Value Regards Ankur/Kanchan www.xlmacros.com Pygmalion wrote: > Hello experts! > > Excel has a nice option to format various parts of a chart using cell > values. Can the same be done for headers/footers, e.g. that the header > displays value of cell A5. > > Thanks, Marko. Thanks! ...

How can I reference the last data item in a column or row?
I want to display or use the last (most recent) data in either a column or row withour specifically accessing the individual cell. Is there a funcion similar to Min() or Max() that can help me or perhaps another way to solve this question? Thanks See: http://www.xldynamic.com/source/xld.LastValue.html Hope this helps Rowan snappy wrote: > I want to display or use the last (most recent) data in either a column > > or row withour specifically accessing the individual cell. Is there a > funcion similar to Min() or Max() that can help me or perhaps another > way to solve th...

column headings have changed to numbers.
column headings have changed to numbers. My cells now read (1,1) Any suggestions on how to change back.. Hi Brenda tools / options / general - untick R1C1 reference style. Cheers JulieD "Brenda" <Brenda@discussions.microsoft.com> wrote in message news:688B1077-8C36-4649-B0B5-1ED2B98BAC50@microsoft.com... > column headings have changed to numbers. > My cells now read (1,1) > Any suggestions on how to change back.. ...

How to do a lookup to show whether there is a match another table
I need to create a table/query that shows whether there is a matching record in another table in a new column). So, if there is a match, the new cell will read "Client Y", whereas if there is not a match, it will read "No" or be blank. What is the best way to accomplish this in Access? To illustrate what i am hoping to acheive in Table 2: Table 1 - Client Y NAME Joe Mike Eric Tim Table 2 - Mailing List Name Client Doug (blank) Joe Client Y Tim Client Y Seth (blank) All you need is an outer-join query: 1. Create a query using both tab...

Temp Table Problem
I am getting this following error message randomly (with different table names each time). "An open operation on table 'taxRebate_TRX' failed because the path does not exist." Each table is a temp table, so I can't view it in SQL. It is only happening on one computer. How can I resolve this? Did you try restarting the computer w/ the error? Are all the modules installed on this computer? "Ryan" <Ryan@discussions.microsoft.com> wrote in message news:4B46A24C-2A30-4EE4-8109-C5F94E19F5E0@microsoft.com... >I am getting this following error mes...

Printing Table headings at the top of every page
This is frustrating me to no end. I have a simple table with column headings and I wish to re-print the column headings at the top of page 2 and page 3. I even see a place in the set-up dialogue box to enable me to do this, but it is greyed out. Why? And, more importantly, how do I tell Excel about the two rows I have devoted to column titles? Do it in File=>PageSetup, not in print preview. then entry would be $2:$3 which you can enter by selecting in the sheet if you do it through page setup under the file menu. -- Regards, Tom Ogilvy "jayceejay" <jayceejay@dis...

How do I combine 2 tables?
I have 2 tables in a database. Each table has 1 data field, "Slot", in common with the other. How do I create a new table or query that combines data in one table with data in the other table based on the slot number? In other words I have the following information: Table 1 Table 2 A 1 2 3 4 A 4 7 2 9 B 3 5 1 3 B 4 9 1 2 C 0 7 8 3 C 5 1 4 2 Where the capital letter is the data for the slot field, the desired result is a new table or query that looks like this: Table 3 A 1 2 3 4 4 7 2 9 B 3 5 1 3 4 9 1 2 C 0 7 8 3 5 1 4 2 I am new to this so ...

pivot table form 01-08-08
i have access 2007 and have created a pivot table form. it works fine for me. the users are using access runtime and when they go to open the form, it displays in data view mode. there doesnt seem to be an option to change to pivot table view. is there a way around this so the users can view the pivot table. thanks ...

pivot table with external data
I am querying my database and saving it to a recordset...see below...know how do i put that into a pivot? I only know how to write the code for an XL based data source.. Thank you Sub GetAccessData() Dim sh As Worksheet Set sh = Sheets("Sheet2") Dim MyConnect As String Dim MyRecordset As ADODB.Recordset MyConnect = "Provider=Microsoft.ACE.OlEDB.12.0;" & _ "Data Source = C:\Dans_DB.accdb" Set MyRecordset = New ADODB.Recordset MyRecordset.Open "DMA", MyConnect, adOpenStatic, adLockReadOnly For...

Numbering Unique Values in a list....
I have a list of names. Is there any way to count the unique names in a corresponding row, and if a name repeats, list the same number it had before? Names # Larry 1 Larry 1 Larry 1 Jim 2 Jim 2 Jane 3 Jane 3 Larry 1 Larry 1 Jane 3 Using your posted example... Try this: B2: =IF(A2="","",SUMPRODUCT(--(A2<A$2:A$11),1/COUNTIF(A$2:A$11,A$2:A$11&""))+1) Copy that formula down through B11 Is that something you can work with? Post back if you have more questions. -----------...

Change A Cell Value Directly from a Chart
Folks, this is a follow-up to my earlier post. I've done some experimenting and I can quickly accomplish what I'm after if I could figure out how to change a cell value from a worksheet directly from a chartsheet. Here's my plan. Worksheet1 = PopulationModel Worksheet2 = PrehuntEstimates Worksheet3 = PivotTable Chartsheet4 = Chart A closeup look at each component: Worksheet1 Year PHFD PHYD PHAD WS2 WS2 WS2 (CELLS ARE ALL LINKED TO CELLS IN WORKSHEET2 and updated of course when they change) Worksheet2 (88R*7C) PHFD PHYD PHAD ADJUST UPHFD UPHYD UPHA...

Rebuilding Purchase Receipts Table IV10200
The change valuation uses the data within the IV10200 table. Somehow, items with current quantities do not have any records in the IV10200 table (ie there is a quantity on hand value in the IV00102, there are transaction history, but nothing in the IV10200 table). When the change valuation is run for these items, the historical stock status report gets messed up. With so many reports relying on this one table, is there anyway of reconciling this table to ensure that it is accurate? Running an invenory reconcile does not fix this table, rather it uses this table to update the IV00102...

How does one change number zero to the zero with diagonal line.
I want to be able to type the numbe zero, but with the line through the character as often used in computer generated text. Jackson wrote: > I want to be able to type the numbe zero, but with the line through > the character as often used in computer generated text. The preferred way is to use a font that has the line through the zero character. One such font is Consolas, which comes with Office 2007 (or, if you have an earlier version of Office, download the Compatibility Pack which includes the new fonts). You may find other fonts with this character on the Web. A l...