The PivotTable Life Cycle

Quick reference

• 00:04 Before we jump in and start actually building and configuring pivot tables,
• 00:08 I just like to cover off the main differences between building a PivotTable
• 00:12 solution and building what I call an ad hoc reporting solution.
• 00:16 Ad hoc reporting solutions are generally what we're used to where we take some data
• 00:20 and we use a lot of formulas to manipulate it and update it in the worksheet grid.
• 00:24 PivotTable solutions are very different.
• 00:26 When we build a PivotTable solution,
• 00:28 we actually have a process that we end up going through.
• 00:31 The very first thing that we're going to do is we're going to go and we're going to
• 00:35 source our data and then we're going to lay out the pivot table design.
• 00:41 We'll add some calculations to our pivot table.
• 00:44 We'll do a little bit of formatting, polishing and
• 00:47 whatnot to make it look really, really good.
• 00:49 And then at that point in time, what we'll do is we'll actually release our
• 00:53 pivot table to our audience to use and that's a little bit different maybe than
• 00:57 the way that we build some of our original stuff.
• 00:59 I mean, the overall kind of goal is kind of the same.
• 01:02 I mean, normally when we're building an ad hoc solution will still source our data
• 01:05 and we'll build our report, and add our calculations and polish up and
• 01:08 released to the audience.
• 01:10 But the big difference is what the pivot table solution gives us next.
• 01:15 So when we're actually working with a solution that's actually built using pivot
• 01:20 tables.
• 01:20 The way that we do that is we'll send our report off to our user.
• 01:24 They'll open up the file.
• 01:26 And, when they do that,
• 01:27 they will go through the process of refreshing the data.
• 01:30 And, this is the big difference rather than having a report that's static and,
• 01:35 never changes the user can actually open it up and,
• 01:38 refresh to pull the latest data from whatever the data sources are so
• 01:42 that they can understand what's happening now or just happened?
• 01:47 At that point, the user has the control to go and slice and
• 01:50 filter the data in the way that they would like to use it.
• 01:52 So they construct drilling in to the specific areas that they want to see.
• 01:57 Once they have done that of course they going to make some observations and
• 02:00 then hopefully if our business process supports it they'll go on act on those
• 02:04 observations and then this is where the pivot table really changes the game.
• 02:08 Once they've acted on those observations and
• 02:10 they want to see what kind of effect they had, they will refresh the data and
• 02:15 we end up getting into a cycle where they can re-use the same piece
• 02:19 of infrastructure or the same reporting system.
• 02:22 Over and over and over again, without always have to sending it back to
• 02:26 the original report developer to do the updates.
• 02:29 And this is the big beautiful thing about working with pivot tables,
• 02:33 driving our business intelligence versus the static report.
• 02:36 The user has to constantly go and update themselves.
• 02:39 Now the big differences is that we see in these, in the classic reporting solution.
• 02:43 Again, we've got a manual report design, which makes sense.
• 02:47 We've got manual report updates, that's the killer.
• 02:50 And most of the time when we actually get those report updates,
• 02:53 we have to go back and we have to modify things to fit.
• 02:56 Whether it's the source data has to be recleaned again or brought in and
• 03:00 copied and pasted and
• 03:01 cut down in order to drive those reports, something new comes out of it.
• 03:05 It's not in a structure that can be reused.
• 03:10 And for that reason, the classic report is very much what I call developer driven.
• 03:13 And I'm not talking about your programmer in the organization.
• 03:16 I'm talking about the business analyst person.
• 03:19 He is the developer of that specific report.
• 03:23 In the case of a PivotTable solution things are a little bit different,
• 03:25 we design the reports once we refresh it often and often.
• 03:29 Now, that design is done by our developer, but the refresh,
• 03:33 the actual digging into the data to see what's going on, by the slicing and dicing
• 03:37 of it is all user driven, and that is the big benefit of a pivot table right there.
• 03:42 Is that we can actually use our developers to develop more reports.
• 03:47 And again, I'm talking about the business analyst or your accountants or
• 03:49 your engineers that are actually trying to drill in and figure out what's going on.
• 03:53 They can build that report.
• 03:55 And then they can send it off to their manager or to their subordinate to say you
• 03:58 just refresh and this will give you the answers to the questions that you need.
• 04:01 I'm going to go and
• 04:02 spend my time developing another analysis that the business needs to understand.
• 04:06 So that's where pivot tables can really start to add
• 04:11 to our overall reporting environment and
• 04:15 why they're so important to business intelligence.

Lesson notes are only available for subscribers.