Displaying rows with blank values

[butlerrbrian]

I have a table with market cities and contractors by industry, similar to this:

Trouble is, I need to display even blank markets like city 4 in a pivot, since blank values are where work needs to be done.

Any assistance is appreciated.

Market	Industry 1	Industry 2	Industry 3	Industry 4
City 1	5	1	2	4
City 2		1	3	12
City 3	1	22
City 4
City 5				14

<TBODY>
</TBODY>

 

[MD610]

Under PivotTable Options, in the Display tab, there is a checkbox for "Show items with no data on rows".

 

[butlerrbrian]

Normally, I would agree. But this is Powerpivot.

 

[Jacob Barnett]

In 2010 I'm pretty sure this functionality ONLY works on PowerPivot Pivots.

 

[MD610]

What Jacob said.

 

[miguel.escobar]

what MD610 said

 

[wivory]

Hi Jacob,

I have a similar issue to Brian (OP), but I don't understand your response.

In 2010 I'm pretty sure this functionality ONLY works on PowerPivot Pivots.

At our sawmill we store logs in 35 Rows. (For the purpose of this example I've scrolled down so we can see starting from Row 30.) If I have only the log row in the (PowerPivot) PivotTable Row label I get the following:

However if I then select the sum of the log volume I get the following:

But like Brian I need to be able to see which log rows are empty so we know where we can put more logs!

If I click on the log RowID in the PowerPivot Field List the Field Settings menu item is greyed out:

And if I select Field Settings from the standard PivotTable Options tab the "Show items with no data" option is greyed out:

So back to your response...what functionality are you saying only works on PowerPivot Pivots? I'm using a PowerPivot Pivot but I just can't get it to display those empty rows. This has been frustrating me for 5 hours and I'd welcome in solution or even explanation.

Thanks

Wayne

 

[miguel.escobar]

=right click anywhere on the pivot table
>click where it says PivotTables Options
>>Go to the 'Display' Tab
>>>check the 'Show Items with no data on rows'

Final step: let me know if it works

 

[wivory]

Final step: let me know if it works

Oh..my..God. It worked!! I'd even been to that dialog box but got stuck focussing on the Layout & Format tab because the same option at the field level is shown on the Layout & Print tab. I don't recall if I even looked at the Display tab.

Thank you so much Miguel.

 

[miguel.escobar]

Watch out! if you start buying drinks to people this might happen to you

the one on the left its you and the ones chasing you are Jacob, MD and the others!

I'm glad it helped