How do I create a Pivot table report?
The Natural HR Report builder analytics feature provides many data sources and several report types to choose from, giving you the ability to create reports in different formats:
- Column chart
- Bar chart
- Pie chart
- Line chart
- Pivot table
To create a Pivot table report we go to:
Reporting > Report builder > New report
Choose Pivot table from Select a report type.
Typically a pivot table is used to display summary numerical data and is used to compare data against two axes or a row and a column.
In this example, we are going to look at how to show average salary broken down by department and site so, in this instance, we are going to use the Employees dataset.
After giving the report a name, we then come to the fields which are specific to Pivot tables - as per above there are three fields.
Row axis - this is the value which you want to display in the rows (i.e. going from left to right) and therefore each row field name is displayed in the left most column.
Column axis- this is the value which you want to display in the columns (i.e. going from top to bottom) and therefore each column field name is displayed in the first row.
Value - this is the value you wish to display where the row and column intersect. You can choose Average, Sum, Count or Values.
(If you choose Values as the 'aggregate' then the pivot table will return all the actual values with a comma between them rather than performing a calculation.)
Note: as with charts and tiles, this field will show ALL fields available in the dataset including those which are not numeric fields - this is so you can do, for example, a count on the number of employees based on the name. For non-numeric fields, only COUNT will work as a value operator - you cannot for example do an average on 3 text fields.
Using our example above we end up with something like the below:
The only other option you may wish to adjust is the number of decimal places the value field will display to which defaults to 2.
Once you have done this you can add filters like any other report to narrow down the data which gets returned - for example you might only want to report on full time employees or only on employees who have been with you more than 5 ears and so on.
However, the filter itself does not influence how the Pivot table works - simply the data which we consider we working out the values.
Once completed, you then click on Edit report and the report output will get displayed:
In the above report, we can see that the average salary for HR employees in Witheridge is 22787.00 whilst the average for Corporate department in Head Office is 21213.33 and so on.
Please sign in to leave a comment.