Databases Reference
In-Depth Information
Pivot tables
Moving on to our second requirement for the report sheet, we now have to create a
table that shows enplaned passengers and departures performed across the
Carrier
Group
,
Airline
,
Year
and
Month
dimensions. This table should show totals for each
year, and subtotals for each carrier group.
To create this table we will use a pivot table, which offers more flexibility over a
straight table when working with multiple dimensions. Let's follow these steps to
create our table:
1. Right-click on an empty space in the worksheet and select
New Sheet
Object
|
Chart
.
2. On the
General
tab, select the
Pivot Table
option in the
Chart Type
section
(top-right icon) and click on
Next
.
3. On the
Dimensions
tab, select
Carrier Group
,
Airline
,
Year
, and
Month
from the
Available Fields/Groups
list and add them to the
Used
Dimensions
section by clicking the
Add>
button.
4. In the
Edit Expression
dialog enter the previously defined expression for
Enplaned Passengers
$(eEnplanedPassengers)
, and define the
Label
field
as
Enplaned passengers (millions)
.
5. Add a second expression to calculate departures performed:
$(eDeparturesPerformed)
, and define the corresponding
Label
as
Departures Performed (thousands)
.
6. Click on
Next
twice to go to the
Presentation
tab.
7. Add a drop-down selection box for the
Carrier Group
,
Airline
, and
Year
dimensions by selecting them in the
Dimensions
and
Expressions
listbox
and checking the
Dropdown Select
checkbox.
8. In the same way, enable the
Show Partial Sums
checkbox for the
Carrier
Group
and
Airline
dimensions.
9. The
Enplaned passengers (millions)
and
Departures performed (thousands)
expressions will have the
Alignment
label set to
Right
.
10. Mark the
Wrap Header Text
checkbox and set the
Header Height
option to
3
.
11. Click on
Next
three times to go to the
Number
tab.
12. For the
Enplaned passengers (millions)
expression, set the
Number Format
Settings
option to
Fixed to
and set it to
3 Decimals
.
13. For the
Departures performed (thousands)
expression, set the
Number
Format Settings
option to
Fixed to
and set it to
2 Decimals
.
