Pivot Grid
A pivot grid report allows you to summarize the report data into a table-based view. The pivot grid has four areas:
Data area – the field(s) that are used in the data area are the fields that will be summarized, e.g. the total hours, or the exception count.
Row area and Column area – the field(s) that are used in the row and column area affect how the data is grouped and summarized. The cell at the intersection of a row and a column represents the summary of the report information.
Filter area – the field(s) used in the filter area do not affect how the report data is summarized but affect what data is used for the summary.
You run the pivot reports from the Reports ribbon tab, selecting the report from the drop down. You are given the standard report selection screen where you can do an initial filtering of the employees and the date range. Click on the Run button and the report data appears in the pivot grid screen.
The ribbon at the top of the screen provides you with the ability to output the report, modify the font and manage the layouts.
The pivot grid occupies the main part of the screen.
Shown above is the Hours pivot grid. In this example, the filter area does not contain any fields. Below the filter area is the "Hours" field. The field(s) in the corner area represents the Data fields. In this example, we are summarizing the Hours.
Below the "Hours" field, there is the "Employee Name" field. This is the row area. In this example, we are summarizing the Hours by the Employee Name.
To the right of the "Hours" field are two fields. "Week Ending" and "Pay Type ID". The data summarizes by the week ending date of the hours, then by the pay type that was used to pay the hours.
At the intersection of a row and a column is the summary of the report data. For example, in the week ending 5/12/2007, employee "Armitage, Erin" has 22.50 REG hours and 15.00 SIC hours.
We are going to customize the pivot grid by removing the "Week Ending" field from the column area. Place your mouse over the "Week Ending" field and the right click context menu is displayed. Select Hide so that the field is removed from the column area.
The pivot grid now looks like this:
The employee "Armitage, Erin" now has 96.75 REG hours because this is the total REG hours of all the data used in the report. Without the "Week Ending" field, we are seeing the hours summarized by "Employee" and "Pay Type ID."
Adding Fields
If you right click anywhere outside of the data area you will get a menu where you can select Show Fields List.
From the fields list you can drag a field into the pivot grid. In this example, we will drag the Department Description field to the left of the Employee Name field. This will now summarize the data by Department and then by Employee.
As well as grouping the data by department, it is also giving us departmental totals.
If we were to hide the Employee Name field, then we would be summarizing only by Department and Pay Type.
Sorting
If you click on a field name, the sorting will switch, from ascending to descending, or from descending to ascending. The arrow on the right of the field indicates if the field is ascending (up arrow), or descending (down arrow).
Filtering
When you place the mouse over a field, the filter icon appears in the top right corner. If you click on this icon, a drop down list appears. You can then select or unselect items to apply filtering on the data.
If you want to filter the data but do not want that field used to affect how the data is summarized, then place the field in the filter area at the very top of the pivot grid.
Formatting
Columns can be resized so that you get more data across the screen. The font can be changed so that the pivot grid grows or shrinks with the font size.
Output
The pivot grid can be printed, saved to an Excel worksheet, or saved as a PDF file.
Layouts
After modifying the report, you can save the layout to a file. The layout does not include any data. It includes only the formatting of the report. Layouts are safe to share with other users who are summarizing different employees' data. The layouts are saved to a common folder for each type of pivot grid report.
