My Pivot Table & The Missing Zeros

Posted by Ben Posaner on April 30, 2001 7:19 AM

When I pivot my data using the wizard and put one of my variables on the"PAGE" choice it builds the pivot table no problem. It gives me a drop down box that allows me to select the different variables and then re-draws the table. When I sellect "ALL" its fine, but if I select the individual variables and if there are blanks in the data it re-draws the pivot table & will miss out empty rows and so the number of rows changes.

I've tried the bit in the wizard which allows you to swap blanks, I put in 0's, which it does, but when I chnage the variable choice it still skips the blanks.

Basically I want to change the variable in the drop down and it redraw the table blanks & all.

Am I missing something silly? I there a box I need to tick???

Thanks for any help.

Posted by Dave Hawley on April 30, 2001 7:27 AM

Hi Ben

When you say "I've tried the bit in the wizard which allows you to swap blanks, I put in 0's" do you mean the Option in the "PivotTables Options" dialog that sais: "For empty cell, show" ? Make sure the "Preserve formatting" is checked also.

Dave
OzGrid Business Applications

Posted by Mark W. on April 30, 2001 7:37 AM

Ben, suppose you're using the following data:

{"Fruit","City","Qty"
;"Apples","Boston",10
;"Bananas","Boston",20
;"Apples","Austin",30}

Create a PivotTable with 'City' in the PAGE area,
'Fruit' in the ROW area, and Sum of 'Qty' in the
DATA area.

When 'City' is set to "(All)" there's a row for both
"Apples" and "Bananas". When 'City' is set to
"Austin" you lose the "Bananas" row because there
isn't a corresponding data row. Setting a value
for empty cells does no good in this situation;
however, if you'll double-click the 'Fruit' label
and check the "Show items with no data" check box
then your PivotTable will display a blank row
for "Bananas" when 'City' is set to "Austin".

Does this adequately address your problem?