Report Filters

Quick reference

• 00:04 In this video we're going to focus on how we can slice and
• 00:07 dice a pivot table to drill into just the relevant items.
• 00:11 The reason this is important is because we've got a lot of information showing on
• 00:14 this worksheet right now.
• 00:16 The report is configured to have item names on the left and
• 00:18 our weeks across the top on columns.
• 00:21 And then we've got our sales dollars as values.
• 00:22 And it's great, because it does show us our total sale by product, by week.
• 00:26 But the problem is, if I press End+Down Arrow, there is over 200 rows of data.
• 00:31 That's an awful lot to read.
• 00:33 So let's go back up to the top here, and just scroll my top rows back into view.
• 00:37 This is what the Filters area is for, it gives us the ability to start filtering
• 00:41 down into our pivot table to look at a smaller subset of data.
• 00:45 So I'm going to go here, I'm going to drag Class onto my Filters area.
• 00:49 And you'll notice it gives me this new field at the top of the pivot table,
• 00:52 classes, and this is all.
• 00:54 And if I check the box here, you'll notice that I can select Alcohol.
• 00:58 And it will drill into just the alcohol items.
• 01:00 What kind of effect does that have?
• 01:01 If I press End+Down Arrow, now there's only 117 as our last row.
• 01:06 So we've actually cut half of the data out of this dataset.
• 01:09 If I go back and take a look at food, and say,
• 01:12 okay, I'm going to go down to the end again.
• 01:15 Now we're at 106 rows, different rows, because this is just the food items.
• 01:19 So this allows us to drill in to look at a lot
• 01:22 smaller subset of data that may be relevant to us.
• 01:25 I can also click All to bring everything back.
• 01:29 And I also have the ability to choose to select multiple items that will allow me
• 01:32 to actually check the individual boxes for what I need or uncheck them as well.
• 01:36 Now, I'm going to add another component in here as well, and that's Category.
• 01:40 We'll slide that under Class on the Filters area.
• 01:43 And now what I could do with this, I could say, hey, you know what,
• 01:46 let's filter into Alcohol, so we'll get rid of Food.
• 01:48 So okay.
• 01:50 And now maybe I want to see just Wine.
• 01:52 And I'm going to make my list a little bit bigger, and holy smokes,
• 01:55 there's a lot of stuff here.
• 01:55 So you know what, let's just type in wine and see what happens.
• 01:58 Hey, look at that, there we go, there's Wine.
• 02:00 So it actually picks it up for me.
• 02:01 So I can now say, OK, and it will drill into just my wine items.
• 02:06 And there's still quite a lot of them, only about 40, I guess.
• 02:09 But still more than I might want to be digging into right now.
• 02:12 What I'm interested in at the moment though, is beer.
• 02:15 Let's go and take a look at that.
• 02:16 So I'm going to go and say, let's bring the all back and
• 02:20 what I'll do is I'll type in beer.
• 02:23 And what you'll see here, if I expand this list just a little bit,
• 02:26 is it's actually picked up three different categories of beer.
• 02:29 So it's not like it's actually has the beers in the beginning.
• 02:32 It can have it anywhere in the actual text name.
• 02:34 So this picks up all three of my categories which is fantastic.
• 02:37 When I say OK now, I can see all of my different beers,
• 02:41 all 20 of them or so, all listed in one report, nice and easy.
• 02:45 And of course, if I want to, I can go back and clear this back to all and
• 02:50 show everything I need here and clear my alcohol and
• 02:53 food to get it back to all as well.
• 02:56 What if I wanted to go even deeper with this?
• 02:58 What if I wanted to add one more level?
• 03:00 I'm going to add the Hour.
• 03:02 So maybe I want to see exactly what's happening in the 10 o'clock hour on any
• 03:05 one of these given days.
• 03:07 There we go, easy to do.
• 03:09 And now of course, I can filter into just Alcohol or just Food or
• 03:12 whatever I want to do.
• 03:13 The next thing I want to talk about is just the layout of this because as we add
• 03:17 new fields, it's stacking something vertically for us and
• 03:20 that obviously takes a little bit of space.
• 03:22 Is it possible to actually have these arranged horizontally across the top of
• 03:26 the pivot table?
• 03:27 So let's see how we can do that.
• 03:29 We'll go right-click > Pivot Table Options.
• 03:32 And what you'll notice is that it's got a section here, right on the layout and
• 03:35 format for Display fields in the report filter area.
• 03:38 And right now it goes Down, Then Over.
• 03:40 Well, right now it's just going down.
• 03:42 It says how many filter fills you want per column?
• 03:44 And it says 0.
• 03:45 What, 0?
• 03:45 It says, just use whatever you feel the default should be.
• 03:49 We're going to go over and then down instead of going down and over.
• 03:52 And we'll say 0 and let's take a look at what happens.
• 03:56 At this point, it actually gives us a nice little layout horizontally for
• 04:00 our filters.
• 04:01 So that takes a little bit less space at the top of the page.
• 04:04 Can we tweak this even more?
• 04:06 Absolutely.
• 04:07 We can come back and say, hey, you know what, if we showed one field per row,
• 04:10 then obviously that's going to give us the original view we saw because it's
• 04:13 only going to have one and then it'll wrap the next row.
• 04:15 But we could say two.
• 04:17 And now, it's going to wrap that POSCHitHour under class because it's only going to
• 04:21 put two of these on every row.
• 04:23 So that's just a nice little formatting option that we can play
• 04:27 around with in order to make this look a little bit better.

Lesson notes are only available for subscribers.