How to Create a Pivot View – TrackVia

A Pivot View is a tool that lets you summarize large amounts of data quickly and easily into a meaningful report. Pivot Views are useful when you have many data points to aggregate, for comparing subsets of data, and identifying trends. They are flexible and applicable to a wide variety of situations.
Given a data set of employee expenses, here are some examples of what a Pivot View can do:

Sum total expenses by department
Count the number of expenses submitted by department
Determine total expenses by department and employee
Create a summary of expenses by department, employee, and year

In the following example, we'll create a Pivot View using a data set of employee expenses, and go over the basic features of one- and two-dimensional Pivot Views.

Create a One-Dimensional Pivot View

Before jumping into Pivot Views, create a new grid view that contains only the records and fields necessary for the pivot. Keeping in mind that the purpose of a pivot view is to report on meaningful data, we omitted fields such as description and images of receipts, as they are not relevant for a cost analysis.

For this example, we simply want to see the total expenses incurred by each department. A grid view of our sample data is displayed below.

Set Up Your Pivot

Step 1: From the view menu, select Pivot.

Step 2: Enter a Pivot Name for the new view.

Step 3: Under "Which View Type?" the "Pivot" option will be checked by default. To toggle between view types, check both Pivot and Grid. The Default View setting lets you choose whether the view should first display in the Grid, or Pivot format. Note: Pivot Views will display as a grid view in mobile applications.

Step 4: Each field in the view can be used one time, as either a Row, Column, or Value. In this example, we will be grouping the data by the department name, and summing the total expenses.

To view each department name in a vertical list, the field Department will be added as Row (highlighted in red box below). The department names can be sorted alphabetically in ascending or descending order and you have the option to check Show Grand Totals (see red arrow below) to add an extra row with a comprehensive sum of the expenses.

To display each department horizontally, the field Department would be set as a Column.

The Value being summed is a currency field, labeled Expense Report Total (highlighted in green box).

Click Save Settings to see your new Pivot View.

Create a Two-Dimensional Pivot View

We can also create multi-dimensional summaries that break down our data into two values rather than one. Currently, we can see the breakdown of expenses by department. By adding the Expense Date field we have the ability to compare department expenses over periods of time.

Edit a Pivot View

Step 1: Click the arrow to expand the Pivot View designer:

Step 2: Click Add Column, then select Expense Date as your Field (see red box below). From Roll Up Dates By, choose the best option to organize your data (the "Year" option is selected in this example - see green box below).

Step 3: Click "Save Settings" to apply these changes to your view.

Below is the newly created two-dimensional Pivot view:

Limitations

*Note that Pivot Views have certain limitations when it comes to the number of data points permitted per pivot. If you reach any of the following, the system would display an error message and your Pivot View configuration would not be loaded.

2500 cells, all containing data
10,000 cells, if more than 7500 of them are empty
750 columns, if there is only 1 field specified under 'Columns'
300 columns, if there is more than 1 field specified under 'Columns'

Create a One-Dimensional Pivot View

Set Up Your Pivot

Create a Two-Dimensional Pivot View

Edit a Pivot View

Limitations

Related articles