Creating a Pivot Table Report

Before you can create a PivotTable, you need to prepare the data. You can get the data from one of the following sources:

■ A range on an Excel worksheet (type in your data or paste from other sources)

■ External data source (such as a Microsoft Access or SQL Server database)

■ Multiple consolidation ranges

■ Another PivotTable or PivotChart report

Figure A-1 displays the data that was dumped into a Microsoft Excel worksheet from an SQL Server database. The workbook file named WarrantyCounts.xls is located in the Appendix A folder on the companion CD-ROM. This file contains approximately 1,5000 rows of data that would be difficult to summarize if it weren't for the built-in Excel PivotTable feature.

0 Microsoft Excel -

WarrarttyCaunls.ids

HI3E3I

S} De Edii View Insert Fûiîm*

lools

üaia

Help

Tyoe a question foi heb » - t? X |

□ d y A a ^ fÄe ■

<f n

Z

- èi Ii a & - ^ ▼

: TaKoma

- 9 „ U /

U

= =

= m $ %

•-.0 .00 iff jj» „ yHfc „ A 1 .on * ,o _ - ü

11

fx

2 'Expert installers

Equipment Type

; Laptop_

Equipment Id

WarrParts Warranty Type

2 Parts & Labor

Total Units ^

3 - Expert Installers

Laptop

102

8/31/2004

3

2 Parts & La bor

A ABC Hardware

MiniTower Meritor

7/21/2001

3

2 Non-Warrerty

5 ¡ABC Hardware

MiniTower Mcritcr

7/22/2001

3

2 Non-Warrar*y

6 :ABC Hardware

M inflow er Meritor

7/23/2001

3

2 fooivWarranty

7 ABC Hardware

MiniTower Monitor

7/29/2001

3

2 Won-Warrarty

8 ABC Hardware

MiniTower Meritor

7/30/2001

3

2 ton-Warranty

9 jABC Hardware

MiniTower Meritor

s; 17/2001

3

2 l^ofvWarratTfcy

tO ABC Hardware

MWTower Meritor

8/27/2001

3

2 Korv-Warranty

11 IflBC Hardware

MiniTower Meritor

9/17/2001

3

2 Non-Warrarfcy

12 "ABC Hardware

MiniTower Meritor

9/18/2001

3

2 Non-Warranty

13 [ABC Hardware

MiniTower Meritor

10/9/2001

3

2 Non-Warnrty

! ijABC Hardware

Monitor

79

11/1/1991

3

_2 IPtoo-Warranty

15 ABC Hardware

Monitor

86

2/22/2002

3

2 ItorvWarranfcy

IB ABC Hardware

Monitor

94J

2/1/2002

3

2 Non-Warr<snty

17 ¡Expert Installers

Monitor

S

5/8/2004

3

2 Parts Si Labor

18"Expert Installers

Monitor

S

5/9/2004

3

2 Parts & Labor

19 'Expert Installers

Monitor

5

5/10/2004

3-1

2 Parts & Labor

20 J Expert Installers

Monitor

S

5/14/2004

3

2 Parts St Labor

21 Expert Installers

Monitor

5

5/15/2004

3

2 Parts EkLabor

22 Expert Installers

Monitor

5

5/16/2004

-3]

2 Parts & La bor

23 (Expert Installers

Monitor

5

5/23/2004

3

2 Parts & Labor

1 VI

< ► n|\ PivctTabte \Soürte Dald /

HI 1 HIT

Read/

Figure A-1: Source data for the PivotTable

Figure A-1: Source data for the PivotTable

Once you have the data, it's time to create your first PivotTable report. The easiest way to create this report is with the PivotTable Wizard (accessed from the Data menu). Before starting the wizard, select any cell anywhere in the data range. This will cause the data range to be automatically selected by Excel. When you choose Data | PivotTable and PivotChart Report, the Pivot Table Wizard - Step 1 of 3 dialog box appears. Choose the source of your data (Microsoft Excel List or Database) on this screen and click the Next button. The PivotTable Wizard - Step 2 of 3 dialog box appears. Ensure that the range displayed in the Range field incorporates all the data on which you want to report. The range will appear automatically if the active cell is within the data range. If the currently selected cell is outside of the data range, you will need to make your own selection.

When you click the Next button, the Step 3 of 3 dialog box appears. Here you can indicate where you would like to place your PivotTable report (select New Worksheet for this exercise). If you press the Finish button at this time, you will see a new worksheet with an empty PivotTable and a list of fields (columns in your source data), which you can drag to the indicated PivotReport layout. If, however, you press the Layout button in the Step 3

of 3 dialog box, you will see a PivotTable layout, as shown in Figure A-2, that explains how to place required fields in the pivot diagram.

Figure A-2:

PivotTable Layout

Figure A-2:

PivotTable Layout

A PivotTable report has four areas where you place fields:

■ The Row area should contain the fields that you want to display your data "by." For example, if you want to produce the report by Vendor, place the Vendor field in the Row area. The Row area can contain more than one field. In our example report (see Figure A-3), we also want to see the report by Equipment Type, so the Equipment Type field is placed in the Row area as well. If you position the Equipment Type field to the right of the Vendor field, the data will be grouped first by Vendor and then by Equipment Type within those vendors.

■ The Column area should contain fields that answer the question "what." For example, what type of information do you want to display for each of the fields in the Row area? Our example PivotTable reports on the Warranty type. Because we want to see all types of warranties for each vendor and equipment type, we placed the Warranty Type field in the Column area.

However, if you want to view your data from a different perspective, you can place the fields from the Row area in the Column area and vice versa. It is up to you.

■ The Data area displays the data that you want to analyze. In our example, we want to find out the total number of units (Equipment Type) covered by each of the Warranty types. The Data area must contain a field that has numeric data. Once we place the field containing numeric data in the Data area, we can choose what calculation (sum, count, average, and so on) we want to perform on the data.

■ The Page area is optional. Page fields add a third dimension to your data analysis. For example, if you want to look at the total units under each warranty type for a particular Equipment Id, you would place EquipmentId in the Page area. This would allow you to drill down your

PivotTable by the Equipment Id or show the data for all the equipment. Later in this appendix, when you generate a PivotTable programmati-cally, you will add a field to the Page area and be able to experiment with the data.

Note that you do not have to place all the fields in the PivotTable. Place only those fields that you need; you can easily add other fields at any time via PivotTable toolbar buttons or a shortcut menu. If you position the fields as shown in Figure A-2 and press the Finish button in the PivotTable Step 3 of 3 dialog box, you should see a PivotTable report, that resembles Figure A-3.

£¿4 Microsoft bxcei - WarrantyCounts.wls ll] £te Ed* Insert femat loda fiaia Help

M fx Sum of Total Unit?

ll] £te Ed* Insert femat loda fiaia Help

M fx Sum of Total Unit?

A

a c

D

E

F

G

1

i

Equipment Warranty Counts as of 4/3/ 2fl07

s

4

Sum ofTotal Units

Warranty Type ~

5

Vendor ▼

Equipment Type ▼

Full Warranty

Norv-Warranty Psrt;

& Laboi Rait? Onlv'

Grand Tjtal

G

Expert Installers

Laptop

344

344

7

Monitor

1333

23

15

1436

8

Scanner

42

42

9

Server

19

30

15

54

10

Server Monitor

9

76

85

11

WS

3901

24E0

10405

16766

11

Expert Installers Total

5369

2923

10435

16727

ia

ABC Hardware

Laptop

6933

EB33

1J

MinrTower Monitor

41

41

Iii

Monitor

10251

10251

IS

Scanner

110

110

M

Server Monilor

61

61

1

WS

24780

24700

t9

ABC Hardware Total

42176

42176

V

Experts R Us

Monitor

5883

5800

Hl

Server

1S

15

¿2

Server Monitor

2

13

15

Zi

WS

1C0

5704

5804

24

Experts R Us Total

102

11700

11002

-

£

Grand Total

5369

4217B

3025

22135

72705

r

k 4

► M j\l»ivot labte/ Source Data /

M

1 >

Figure A-3: PivotTable report

Figure A-3: PivotTable report

PivotTables are for data analysis and presentation only. This means that you are not permitted to enter data directly into a PivotTable. To make any changes or additions to the data, you must do this in the underlying source data and then use the Refresh button on the PivotTable toolbar or the Refresh Data option on the Data menu to bring the PivotTable up to date.

To see new versions of the same information, drag PivotTable headings to new positions. Figure A-4 presents a different view of the same data.

You can examine the contributing data by double-clicking a cell containing a total. For example, if you double-click cell C8 (Figure A-3), Excel will add a new worksheet to the active workbook showing all the records that contributed to the selected total value (Figure A-5).

I Micioioit EhcbI wüijant^counl&.kís

¡-I

1

® file Edt Yiew fiS

Format toofe ßate i^indü'/f

Helo

Tjipe a question for het' 7 _ if X I

QsaaaEii J¡K»P•<•*

m T £ * -

% S -Í1

ii

[ft M

3SÜ

- 5L

Aiial T 10 » B I U =E

m mm

$ % j tdS

.'je

w m

îi ■ A ■ ,

M ▼ £

Sum oíTotal Unite

1 a

B

c

D

I

e

f

m

2 Equipment Warranty Counts as of 4/3/2002

1 3

4 jsum of Total Units

Fquiflrnenr Type '<*

5 Vendor *

Warranty Type ▼

Laptop

Monitor

Server

Grand Total

6 Expert Installers

a '

Full Warranty

1350

19

1417

Parts it Labor

344

23

20

307

Parts Only

is

15

30

1 9 Expert Installers Total

344

143g

54

163 i

1 10 abc Hardware

non-Warranty

6333

10251

17134

1 11 abc Hardware Total

3933

10251

17104

1 12 Experts r Us

Parts Only 1

5800 15

5903

13 Experts r Us Total

5s00 15

5903

m Grand Total

7277

17575

es|

24921

_LS_

« > > . iv : > til !> 1 f . . .. ; / i /

hi

I

•II"

Ready

Figure A-4: Another PivotTable view of the source data

I Microjoft Eircel - WadidíiliíCourttí.nti

HEUS]

Fie Edil jíiew Fnsat Famiat Ioofe Data

Vtfndow Help

Type a question for help

jjjs

X

□ a ti I m a &

• • •

S ' I* * M -

Fahama - Ç - [jöJ I U

b

-= e£3 SO J .00 +.Í

IS _ - Ô» - A

A1 - f. Vendar ¡

A I B I c

_l

□ 1

E 1 F

0

H

1

Vendor Equipment Type Equipmen!

tld

WarrEupDatc

WarrYears WarrParts Warranty Type Total Units]

t~

Expert installers Scamer

30

8/7/2Q0Z

1 mull

ful Warranty

6

3

Expert Installers Scamer

81

9/11/2002

1 mull

Ful Warranty

3

4

Expert Installers 5camer

93"

9/20/2002

1 null

Ful Warranty

__2

_5_

Expert Instcflers 5camer

30

5/31/2002

1 m-ll

Ful Warranty

2

Expert Installers Scamer

so

7/13/2002

j mju

Ful Warrarfcy

Expert Installers Scamer

50

7/11/2002

1 mull

Ful Warranty

Expert Installers 5camer

50

7/10/2002

1 n_ll

ful Warranty

S

Expert Installers Scanner

so"

7/5/2002

] mull

Ful Warranty

IL

Expert Installers Scamer

50

6/29/2002

1 mull

Ful Warranty

11 :

Expert Installers Scamer

50

_6/20/2002

1 nlll

Ful Warranty

12

Expert Installers Scamer

50

6/19/2002

i m-ll

Ful Warranty

IB

Expert Installers Scamer

50

6/15/2002

1 Mull

Ful Warranty

14

Expert Installers Scamer

50

6/14/2002

1 nlll

Ful Warranty

ts

Expert Installers Scamer

50

6/13/2002

1 null

FUlWtfrtflty

16

Expert Installers Scamer

50

6/11/2002

J null

Ful Wsrrarty

2d r

, t

► V\sheetl / PivotTable / Source Data /

i-1.

1 >1

Ready

Sum-1237133

Figure A-5: You can obtain details of any summary figure by double-clicking on a data field in the PivotTable.

Drilling down on the data is a nice feature, except for the fact that if you do a lot of double-clicking, you will end up with many additional and most likely unwanted worksheets in your workbook. You may want to delete the drill-down worksheet after examining the detail data. You can do this manually, or you can perform the cleanup programmatically by writing VBA procedures for the Workbook_SheetBeforeDoubleClick and Work-book_SheetActivate events. To try this out, switch to the Visual Basic Editor screen and double-click the ThisWorkbook object in the Project Explorer window. In the Code window WarrantyCounts.xls - ThisWorkbook (Code) window, enter the global variable declaration and event procedures shown on the following page.

' Global variables Dim flag As Boolean

Dim pivSheet As String

Dim drillSheet As String Dim pivSource As String

Boolean variable to indicate whether to delete a drill-down worksheet

String to hold the name of the sheet containing the PivotTable

String to hold the name of the drill-down sheet

String to hold the name of the worksheet with the PivotTable source data

Private Sub Workbook_SheetActivate(ByVal Sh As Object) If pivSheet = "" Then Exit Sub If Sh.Name <> pivSheet Then If InStr(1, pivSource, Sh.Name) = 0 Then If MsgBox("Do you want to Delete " & Sh.Name & _ " from the workbook" & vbCrLf _ & "upon returning to PivotTable?", _ vbYesNo + vbQuestion, _ "Sheet: Delete or Keep") = vbYes Then flag = True drillSheet = Sh.Name

Else flag = False Exit Sub End If End If End If

If ActiveSheet.Name = pivSheet And flag = True Then Application.DisplayAlerts = False Worksheets(drillSheet).Delete Application.DisplayAlerts = True flag = False End If End Sub

The Workbook_SheetActivate event procedure shown above will ask the user whether the drill-down worksheet should be deleted when the user returns to the worksheet containing the PivotTable. If the user answers "Yes" in the message box, the Boolean variable flag will be set to True. Because Excel by default displays a confirmation message whenever the worksheet is about to be deleted, the procedure code turns off the application messages so that deletion can be performed without further user intervention. After the deletion, don't forget to turn the alerts back on.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _ ByVal Target As Range, Cancel As Boolean)

With ActiveSheet

If .PivotTables.Count > 0 Then pivSource = ActiveSheet.PivotTables(1).SourceData If ActiveCell.PivotField.Name <> "" And IsEmpty(Target) Then

MsgBox "There is no data in the selected cell - cannot drill down" Cancel = True Exit Sub End If flag = True pivSheet = ActiveSheet.Name End If End With End Sub

The Workbook_SheetBeforeDoubleClick procedure shown above will disable the drill-down if the user clicks on a PivotTable cell that is empty; otherwise, it will set the Boolean variable flag to True to indicate that a drill-down was requested. At the same time, the name of the worksheet containing the PivotTable will be written to a global variable. Also, because we do not want to delete the worksheet containing the PivotTable source data, we will use the SourceData property of the PivotTables collection to store the name of the source data worksheet and the underlying data range in a variable. To find out exactly how these two event procedures work together, use some of the skills that you acquired in Chapter 13.

0 0

Responses

  • Andrew Gibson
    Wow! Fantastic work. Can you give me some tips to create another one please? Thanks mate!
    7 years ago

Post a comment