Hiding Zero rows in Pivot Table
Posted by Dave D on March 28, 2001 7:38 AM
I use pivot tables on a daily basis and use calculated fields/items etc., but I am stumped on one thing. If i have say projects as a column item and staffing per month(sum of) as my data item. It shows the projects with zero staffing across the months and i wind up manualy deletin rows with zero before I can paste the result in a presentation. I have tried using IF statments to say if sum of twelve months is 0 than Yes
and I make this a page item to filte 0's. Is there an easier way????
Posted by Mark W. on March 28, 2001 12:05 PM
Dave, consider this...
1. Drag your "staffing" field to the ROW area.
2. Set "staffing" Subtotals to 'None' if necessary.
3. Select all the non-zero values and create a
group using the Data Group and Outline menu command.
4. Replace the default "Group1" item with a more
meaningful label such as "<>0" or "Non-Zero".
5. Drag the newly created field button to the PAGE
area.
6. Choose the newly created item category (e.g., "<>0")
and zero values will be excluded from your PivotTable.
7. If you want you can rename this new field by
double-clicking the page field button.
8. Finally, remove the "staffing" field from the
ROW area.
Posted by Mark W. on March 28, 2001 12:16 PM
Re-creating this PAGE field might be too much trouble
if your staffing values are constantly changing. If
this is the case consider adding another column to
your data list named something like "Select" and using
the formula, =IF('staffing',"<>0","0"). Then use
the "Select" field in the PAGE area instead.
Posted by Mark W. on March 28, 2001 12:38 PM
One more consideration...
If you don't like the idea of adding a new data
column to your list. There is one more option that
is a slight modification of my 1st suggestion. A
group like I suggested only "knows" the values that
were present when it was created; however, it also
"remembers" those values even if they're no longer
present in your data. If your staffing levels are
reasonably low (say, less that 100) you could
temporarily add those values to your data set, create
the grouped field as described earlier, and then
remove all those dummy records. Now the domain of
your non-zero values is set to 1-100. This trick
works well for integers, but would be worthless for
real values.
Posted by Mark W. on March 28, 2001 1:35 PM
Final consideration...
All of the proceeding assumed that you were only
interested in PivotTables that would sum your
staffing levels. With this kind of analysis you
could get away with ignoring individual records
with a "Staffing" value of 0. However, I can
now invision where you might want to calculate
average staffing levels for a given Project. In
this case you'd need to add a column to your data
set as previously suggest, but using a formula
that would evaluate a given Project's overall
staffing level. Such a formula would be:
=IF(SUMIF('Project',A2,'Staffing'),"<>0","0")
Posted by Dave Hawley on March 28, 2001 3:41 PM
Hi Dave
If this is only for presentation purposes could use the hide zeros value under Tools>Options for the Worksheet.
....Or have the cells return NA() or "" for no data and then under the Pivot Table Options set the "For error values show" and "For empty cells show" to a choice that suits.
Dave
OzGrid Business Applications
Posted by Dave D on March 29, 2001 11:11 AM
Re: Final consideration...
Thanks for the advice. I do need to use a pivot table as I slice the data different ways( group working on projects, staffing dedicated to particular customer, and our projects roll to initiatives) I think for now I will stick with the "if" statement technique. It is similar to the one you mentioned. What is puzzling is pivot tables usualy supress zero data, unless it has something to do with the way our time tracking system spits out the data as a label 0?
