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
Set Up Your Pivot
2. Enter a Pivot Name for the new view.
3. Which View Type? By default, Pivot will be checked. 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.
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. To display each department horizontally, the field Department would be set as a Column. The department names can be sorted alphabetically in ascending or descending order and you have the option to check Show Grand Totals to add an extra row with a comprehensive sum of the expenses. The Value being summed is a currency field, labeled Report Total. Click Save Settings to see your new Pivot View.
Create a Two-Dimensional Pivot View
Edit a Pivot View
2. Click Add Column, then select Date as your Field. From Roll Up Dates By, choose the best option to organize your data.
3. Click Save Settings to apply the changes to your view.
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'