Microsoft Office Tutorials and References
In Depth Information
The Report Filter—Getting on Top of the PivotTable
You also need to keep in mind that the filters don’t filter each other. To explain: If you select both
Country and OrderID in that order (no pun intended) as filters, and you click UK for Country, all the
order IDs will still appear when you then click the OrderID drop-down arrow—including the order IDs
on USA sales. Thus if you filter for UK along with a USA order ID—which you’re able to do-your pivot
report will show…nothing.
You can also select two or more items in the same filtered field. If, for example, you move the
Salesperson field to the Report Filter area, I can filter data for several salespersons at the same time.
To try this:
Click the PivotTable Tools Options tab Clear Clear All.
Mov e the Order Amount field into the Values area.
Move Salesperson into the Report Filter area. Your report should look like this
now (Figure 8–47):
Figure 8–47. Filtering the sales order amounts by salesperson
Note the Total represents the aggregate of all sales, because we haven’t broken sales out
by any other field yet. The (All) notation in the Salesperson filter means exactly that-at
the mome n t, the sal e s of all the salespersons are being totaled.
Click the filter handle down arrow Uncheck the All checkbox. Check Select
Multiple Items che ck Cal l ahan an d Dodswor th. Click OK.
The new sum beneath Total is naturally smaller, because now the sales data
for only two salespersons has been filtered (Figure 8–48):
Figure 8–48. A Report Filter for two salespersons—but which two?
The problem here is that the entry alongside Salesperson—Multiple Items—is not very revealing.
You can’t tell exactly which salespersons have been included by the filter, and you may need to be able
to present that information on screen. What to do?