Lesson
6: Pivot Function
Lesson
6-1 Creating a Pivot Report
Goal
- The goal of this lesson is to create a report that uses the Pivot function to
display data in a grid based format.
The Izenda
reports Pivot feature allows users to generate analytical data grids from
within Izenda reports. The Pivot feature essentially adds extra pivot
columns to the right side of the report. This is useful for comparing data over multiple categorical dimensions.
Pivot View of the average amount spent on freight:
Create initial report:
- Click on the "Data Sources" tab and select desired data sources. (Ex. Orders)
- Click on the "Fields" tab. Select the desired Field(s) names & attributes (Sorting, VG, Function, & Format) for the report. (Ex. ShipCountry)
Before
Pivot:
Create pivot view on above report:
- Under Fields tab, click the "Add Pivot" button.
- Drop down "Add pivot columns for". Select the field you wish to display across the top of the pivot view (Ex. OrderDate), also select the function type from drop down (Ex. Group(Year & MN)).
- Drop down "Calculate cell values on". Select the field you wish to display down the left side of the pivot view (Ex. Freight), also select the function & format types from the drop downs (ex. Average & $0.00). **This field's data will also populate the rows within the pivot view.
- Optional: Select the "Add subtotals using" drop down. Select the desired function. (Ex. Sum)
- Click Preview
After Pivot:
Essentially, the pivot capability adds additional columns based on the data to
the right of the report. In this example, the first column is essentially
a standard report and the 2006, 2007 and 2008 columns were added by the pivot
capability
Standard Report View:
Columns added by Pivot:
Let's use these concepts to develop a more sophisticated report that shows us a
monthly time-series report for each county visually grouped by employee.
First
let's setup the non-pivot part of the report:
Non-Pivot Report View:
Here's
how we do it:
- Here
we select and visually group by employee (by clicking the VG checkbox for that
field).
We
sort on ShipCountry and Sum the Freight.
- We
then add subtotals using the Sum function to give us a summary line for each
employee.
Report Designer Setup without Pivot:
Now that
the non-pivot is complete, let's do a monthly pivot:
- We
select the OrderDate to pivot on.
Select
the Group(Year & MN) for the formatting of the column names.
Select
the sum of freight for the cells.
Finally,
here are the results of the report with Pivot:
**Note: The Pivot function is by default turned off. To activate the Pivot function see Show Pivot".