Difference between revisions of "Pivot Grid"
| Line 1: | Line 1: | ||
| − | A pivot grid report allows you to summarize | + | A pivot grid report allows you to summarize data into a table-based view with grouping by rows and/or by columns. |
| − | + | The pivot grid has four areas: | |
| − | *''' | + | *'''Data area''' – the top left corner of the pivot grid is for the field(s) that will display in the data area. These are the fields that are summarized, e.g. the total hours, or the employee count. |
| − | *''' | + | *'''Row area and Column area''' – running down the left side, and across the top are the field(s) that affect how the data is grouped and summarized. The cell at the intersection of a row and a column represents the summary of the data that matches to both the row and the column values, e.g. the total of the REG hours for Department 3838. |
| − | + | *'''Filter area''' – the field(s) used in the filter area do not affect how the report data is grouped or summarized, but limits what data is being used in the pivot grid. | |
| + | |||
| + | Run the pivot grid reports from the Reports ribbon tab. You are given the standard report selection screen where you can do an initial filtering of the employees and the date range. If you are running historical reports then you will most likely want to include inactive employees (Current Status tab). Click on the Run button and the pivot grid screen displays with the default layout. | ||
[[image:PivotGridRibbon.png]] | [[image:PivotGridRibbon.png]] | ||
| Line 13: | Line 15: | ||
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 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 | + | The pivot grid itself occupies the majority of the screen. |
[[image:PivotGrid.png]] | [[image:PivotGrid.png]] | ||
| − | Shown above is the Hours pivot grid. In this example, the | + | 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 are the Data fields. In this example, we are summarizing the Hours. |
| − | Below the "Hours" field, there is the "Employee Name" field. This is the | + | 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 | + | To the right of the "Hours" field are two fields. This is the Column area. The fields are "Week Ending" and "Pay Type ID". The data is being summarized by the week ending date of the hours, and 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 | + | At the intersection of a row value and a column value is the summary of the data. For example, in the week ending 5/12/2007, employee "Armitage, Erin" has 22.50 REG hours and 15.00 SIC hours. |
| − | + | You can modify the pivot grid by removing the "Week Ending" field from the column area. Place your mouse over the "Week Ending" field and right click to see the context menu. Click on Hide so that the field is removed from the Column area. | |
[[image:PivotGridFieldMenu.png]] | [[image:PivotGridFieldMenu.png]] | ||
| Line 33: | Line 35: | ||
[[image:PivotGrid2.png]] | [[image:PivotGrid2.png]] | ||
| − | The employee "Armitage, Erin" now has 96.75 REG hours because this is the total REG hours of all the data | + | The employee "Armitage, Erin" now has 96.75 REG hours because this is the total REG hours of all the data. Without the "Week Ending" field, we are seeing the hours summarized only by "Employee" and "Pay Type ID." |
'''Adding Fields''' | '''Adding Fields''' | ||
| − | If you right click anywhere outside of the | + | If you right click anywhere outside of the Data area you will get a menu where you can select Show Fields List. |
[[image:PivotGridFieldList.png]] | [[image:PivotGridFieldList.png]] | ||
| − | From the | + | 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 summarize the data by Department and then by Employee. |
[[image:PivotGrid3.png]] | [[image:PivotGrid3.png]] | ||
| Line 63: | Line 65: | ||
[[image:PivotGridFilterDropDown.png]] | [[image:PivotGridFilterDropDown.png]] | ||
| − | 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 | + | 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''' | '''Formatting''' | ||
| Line 71: | Line 73: | ||
'''Output''' | '''Output''' | ||
| − | The pivot grid can be printed, saved | + | The pivot grid can be printed, saved or emailed as a PDF or an Excel file. |
| − | + | ||
'''Layouts''' | '''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. | + | After modifying the report, you can save the layout to a file. The layout does not include any data. It includes only the arrangement and 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. |
Revision as of 14:46, 23 June 2015
A pivot grid report allows you to summarize data into a table-based view with grouping by rows and/or by columns.
The pivot grid has four areas:
- Data area – the top left corner of the pivot grid is for the field(s) that will display in the data area. These are the fields that are summarized, e.g. the total hours, or the employee count.
- Row area and Column area – running down the left side, and across the top are the field(s) that affect how the data is grouped and summarized. The cell at the intersection of a row and a column represents the summary of the data that matches to both the row and the column values, e.g. the total of the REG hours for Department 3838.
- Filter area – the field(s) used in the filter area do not affect how the report data is grouped or summarized, but limits what data is being used in the pivot grid.
Run the pivot grid reports from the Reports ribbon tab. You are given the standard report selection screen where you can do an initial filtering of the employees and the date range. If you are running historical reports then you will most likely want to include inactive employees (Current Status tab). Click on the Run button and the pivot grid screen displays with the default layout.
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 itself occupies the majority 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 are 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. This is the Column area. The fields are "Week Ending" and "Pay Type ID". The data is being summarized by the week ending date of the hours, and then by the pay type that was used to pay the hours.
At the intersection of a row value and a column value is the summary of the data. For example, in the week ending 5/12/2007, employee "Armitage, Erin" has 22.50 REG hours and 15.00 SIC hours.
You can modify the pivot grid by removing the "Week Ending" field from the column area. Place your mouse over the "Week Ending" field and right click to see the context menu. Click on 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. Without the "Week Ending" field, we are seeing the hours summarized only 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 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 or emailed as a PDF or an Excel 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 arrangement and 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.
