Difference between revisions of "Pivot Grid"
(Created page with "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 ...") |
m |
||
| (10 intermediate revisions by 5 users not shown) | |||
| 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. | |
| − | [[image: | + | 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]] | ||
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. | + | [[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 7/01/2017, employee "Baldwin, Andy" has 7.00 REG 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: | + | [[image:PivotGridFieldMenu.png]] |
The pivot grid now looks like this: | The pivot grid now looks like this: | ||
| − | [[image:PivotGrid2. | + | [[image:PivotGrid2.png]] |
| − | The employee " | + | The employee "Baldwin, Andy" now has 37.50 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: | + | [[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. | + | [[image:PivotGrid3.png]] |
As well as grouping the data by department, it is also giving us departmental totals. | As well as grouping the data by department, it is also giving us departmental totals. | ||
| Line 49: | Line 51: | ||
If we were to hide the Employee Name field, then we would be summarizing only by Department and Pay Type. | If we were to hide the Employee Name field, then we would be summarizing only by Department and Pay Type. | ||
| − | [[image:PivotGrid4. | + | [[image:PivotGrid4.png]] |
'''Sorting''' | '''Sorting''' | ||
| Line 59: | Line 61: | ||
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. | 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. | ||
| − | [[image: | + | [[image:PivotGridFilter.png]] |
| − | [[image: | + | [[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 69: | Line 71: | ||
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. | 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. | ||
| − | ''' | + | '''Advanced Options''' |
| − | The pivot grid can be printed, saved | + | The advanced properties of the pivot grid and the advanced properties of each field is available from the Fields group by clicking on the Display Property Grid button. This gives you access to all the underlying settings that are available in the Pivot Grid. Some examples are: |
| + | |||
| + | * When exporting to Excel, if you want the group values to repeat on every row then select Pivot Grid as the Field and in the property grid change the value of '''Options'''/OptionsPrint/MergeRowFieldValues to False | ||
| + | |||
| + | * If you do not want totals for a group then select the field that you are grouping by, and in the property grid change '''Behaviour'''/TotalsVisibility to None. | ||
| + | |||
| + | * You can change the name of the field caption (i.e. the row or column header) from '''Appearance'''/Caption. | ||
| + | |||
| + | * To change the filter type from Include to Exclude, in the Pivot Grid property grid change '''Options'''/OptionsFilterPopup/AllowFilterTypeChanging to True | ||
| + | |||
| + | 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. |
| + | |||
| + | '''Saved Reports''' | ||
| + | |||
| + | You can save the pivot grid report like any other report. See [[Saved Reports]]. To have a saved layout automaticaly apply to a saved report, it can be selected on the Options tab under Filename. You can then Schedule the report and it will email the pivot grid with the saved layout applied. | ||
| + | |||
| + | ==See Also== | ||
| + | *[[Reports]] | ||
| + | *[[Hours Reports]] | ||
| + | *[[Attendance Reports]] | ||
| + | *[[Budget Reports]] | ||
| + | *[[Scheduling Reports]] | ||
| + | *[[Saved Reports]] | ||
Latest revision as of 18:04, 13 November 2018
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 7/01/2017, employee "Baldwin, Andy" has 7.00 REG 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 "Baldwin, Andy" now has 37.50 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.
Advanced Options
The advanced properties of the pivot grid and the advanced properties of each field is available from the Fields group by clicking on the Display Property Grid button. This gives you access to all the underlying settings that are available in the Pivot Grid. Some examples are:
- When exporting to Excel, if you want the group values to repeat on every row then select Pivot Grid as the Field and in the property grid change the value of Options/OptionsPrint/MergeRowFieldValues to False
- If you do not want totals for a group then select the field that you are grouping by, and in the property grid change Behaviour/TotalsVisibility to None.
- You can change the name of the field caption (i.e. the row or column header) from Appearance/Caption.
- To change the filter type from Include to Exclude, in the Pivot Grid property grid change Options/OptionsFilterPopup/AllowFilterTypeChanging to True
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.
Saved Reports
You can save the pivot grid report like any other report. See Saved Reports. To have a saved layout automaticaly apply to a saved report, it can be selected on the Options tab under Filename. You can then Schedule the report and it will email the pivot grid with the saved layout applied.
