The Report Builder allows you to create, edit and share custom reports - it can be accessed via:
Reporting > Report builder
When you first access Report builder you will need to click the ‘New report’ button at the bottom left of your screen to start building your first report. When you do so, you will see a screen like the one below containing 9 sections:
Depending on your edition of Natural HR and your terms and conditions, you may only see Table as a selectable report type. However, other report types may also be available as you can see here:
This is the data on which you want to report - for example, Employees, Expenses, Timeoff, Training and so on. This is sometimes referred to as a dataset.
Note: When you select a data source, the fields in Select fields will change accordingly.
Every report needs a name before it can be created, so enter the name for the report here - if you do not enter a name you will not be able to save the report.
This is not a mandatory field, but you can use it to add additional information about the report.
This is the main area of report builder. There are three main areas within this section,
- Search fields
- Field list
- Column ordering/Name editing
Depending on the Data source you choose, there may be many fields to choose from and you may not be able to find the field you want to use in your report.
You can search for the field you want. For example, if you want to include Department but cannot find the field, start typing ‘dep’ (without the quotes) into the search box, this will then display only those fields which contain the letters ‘dep’ as you can see here:
The field list is where you choose the fields you want to appear in your report. In this example, we have chosen Timeoff requests as our data source. This gives us access to the Timeoff fields (1) but also to the Employee fields (2) which allows us to then filter and sort etc. based on the employee values as well as those of the timeoff record itself.
Click on the fields you want to appear in the report and they will appear in Column Ordering/ Name Editing.
Note: whilst there is no limit to the number of fields you can select, it is worth remembering that these fields will be displayed horizontally on your report and, the more fields you include, will mean that either the columns will get squashed or the report will scroll horizontally.
Column ordering/Name editing
You can change the order of the fields by dragging them to a new position as well as deleting the fields or edit the field name to how you want it to appear in your report.
Moving a field within the selected fields will determine the order the columns are displayed in the end report, click the cross to the left of the field name (1) and drag the field up or down to the new position.
Deleting a selected field
To delete a selected field, click the bin icon (2) to the right hand side of the box containing the selected field and it will be removed from the list.
Occasionally, you may not want to refer to the fields using their default names in your reports - perhaps your company has specific terminology it uses and you want that to be reflected in the created report.
To rename a selected field, click on the field name and enter a new name. You do not need to click save etc. to update the field as this gets changed as soon as you finish typing.
For example if we wanted to change Department to Team it would then look like this:
Grouping allows you to take a list of data and group rows together based on a common criteria.
For example, in the this report, with no grouping, we would see a list of all employees with their relevant time off.
By using grouping, we can then group the rows together by any of the selected fields - in this instance we might choose to group together by Reason.
After clicking on 'Edit report' and then 'View report' the report is now grouped by Reason highlighted above each group.
An aggregate is simply a value which is made up from calculations on other values. The aggregates that we support are:
Count - this is the number of instances
Minimum - this is the minimum value
Maximum - this is the minimum value
Sum - this is the total of all the values
Average — this is the average or mean value
The last four aggregates are only visible if you are applying them against a non-text field — for example you cannot perform a sum against employee names but you can apply a count.
You can only apply an aggregate against a selected field in your report.
When the aggregate is chosen, it will then get displayed at the bottom of the chart.
Whilst you can use groups and aggregates on their own, they can also be used together and when you do so this will change the way they both work.
For example if you wanted to group by the Reason and then show a count for your aggregate against the reason, you can do this and the output then changes to show the aggregate against the group rather than against the whole dataset.
You can add as many aggregates as you need to the report and they can each be different, so you could choose to show the total duration and the count of reasons to get two values from the same dataset.
Select order by fields
Ordering is simply the ability to control the sort order of the output data. You can add up to three order elements to each report - the sequencing of these is important as, should a duplicate be detected in the first item (which will happen quite commonly when using things like names) the next selected ordering option will be used to determine which gets displayed next and so on.
Within ordering, you can choose to order either ascending or descending.
Ascending means going from 0 to 9, oldest to newest or A to Z whilst descending means going from 9 to 0, newest to oldest or Z to A.
One of the most powerful parts of the report builder, is the ability to add filters to the output data. A filter is way of refining the result set based on criteria you choose.
To add a new filter, click the plus icon within the "dotted" rectangle which then will display a drop down where you can search for the field in question.
When you choose the filter type you will then be presented with a value box where you need to enter or choose the actual filter value. In this example we have added:
Cold/Flu AND Doctor/Hospital Appointment
So our report now only shows these two time off types Cold/Flu or Doctor/Hospital Appointment.
You might want to show all sickness for this year so far so you would add a second filter for:
Time off start date
Greater than or equal to
Note. For absolute date options, you will be shown a date picker which will allow you to choose your specific date or dates.
This allows you to then only see the records which match those criteria, in this case only dates for this year from 1st January 2020.
You can add as many filters and you like and they are NOT limited to the fields chosen on the report - for example, we could add an additional filter where:
even though we had not actually chosen to display Site as a selected field in the report.
The report now only shows Cold/Flu and Doctor/Hospital Appointment from 1st January 2020 and only for those occurrences from Witheridge, one of our sites.
Instead of the whole year from 1st January 2020, you might might to see absence for just one month, in this example, only February, in this case we would set our Time off start date filter to:
Time off start date
Our report will then look like this
It is possible to create invalid filter combinations and this is something you should be cautious of.
The most commonly encountered invalid filter is where you have two AND filters applied where one makes the other no longer be valid. For example, Cold/Flu and Doctor/Hospital Appointment are both Unauthorised time off types. So if you add a filter like this:
it would be "invalid" as that filter could never be true, so your report will return no results.
Saving and Viewing reports
While you are building your report, adding your groups, aggregates, filters and so on, you can click on the 'Edit report' button at the bottom left of the screen.
If this is successful you will then see a View Report popup button which allows you to view your data:
Once you have built and saved your report, it will load 10 rows per page. You can change this by clicking the dropdown at the top of the screen containing numbers to show 10, 25, 50, 100, 250, 500, 1000, 2000 and Show all.
Additionally if you want to simply jump to another page you can use the page numbering at the bottom right of the screen as well as the previous and next buttons.
Caution: Depending on the volume of data returned and the filters applied this may create a very large report. If you choose to use Show all or even 1000/2000 rows per page you will suffer a delay as your browser will need to draw all the rows on the screen. Depending on your computer, browser etc. this may even cause your browser to crash so use Show all with caution.
You can export the data either to your clipboard (copy) or to CSV or PDF using the buttons at the top right of the screen.
Please note: exporting will only export the currently visible rows so you may need to change the number of displayed rows if you wish to export these all to one file.
Additionally, if you choose PDF and have a very wide report it may get cut off during the export as there is physically not enough space to fit all the columns into the page size.
As an Administrator, you will have access to all data sources and all fields within those data sources within the report builder. However, for other user groups you may not want them to have that same access. Giving report builder access below is done at a user group level - this means if you give access to a data source (dataset) to a user group then ALL users of that user group can then access that data source. In the case of a manager, for example, they would only see their employees but they will still see and be able to report on the data source none the less.
To this end there are three main elements to consider:
To give access to a data source, go to:
Administration > Company > Report builder permissions
For example, you may choose to give Managers access to this Time off request report.
When HR or manager level users access reporting (again either to build or share) they will not be able to access any fields which they do not have permissions to view
In the Manager column, select the data source the report is based on, in this case it is the Time off requests, and then click on 'Submit'.
At this point we have only given permissions to Managers to view any reports based on this data source, however the Managers will not yet be able to view the report. We also need to share the actual report.
When you create a new report in report builder, you are the only person who can access that report.
Reporting > Report builder
Find the report you want to share then click on the 'Share' icon for that report.
Here you will see the permissions screen for this report. Permissions (sharing) means you still keep just one copy of your report but multiple people can access the report. You can choose to give those people (or user groups) either view access or editor access or a combination of both.
After adding the user group or individual as a Viewer, click on 'Update permissions' and you will see the Report permission updated! banner.
If you try to share a report where any of the recipients are not allowed to view that data source you will get an error as they would never be able to see that report, in this case you will need to return to the Report builder permissions option and add the user group you want to share the report with:
In this example, Managers will be able to view your report but will not be able to edit, delete or share the report. Administrators will be able to edit and delete and, any change they make, will then be reflected to allow those who are accessing that shared report. The manager would see something like the below with only the action available to view the report.
When anyone from HR level down access a report (either as the builder or when shared with them), they will only ever see the employees they have permissions to see, in the case of a Manager, the employees that report to them.
You can also copy or duplicate your report. In this instance, your report gets copied in its entirety but it creates a new totally independent version of the report with no connection to the original, so the Managers can make changes to their report without impacting any other users' report:
You will also get an error if you try to copy the report without first giving the Manager group permissions to access reports based on the data source.
If there is no error, a Manager will see this view from their reporting option, they will see a copy of the 'master' report that they can only view, but they now also have a report that they can view, edit and also share (as long as the individual has permissions to view the data source the report is based on).
If the user group you are sharing the report with have access to the data source but field restrictions have been applied, ( via Administration > Company > Settings > Display settings > Manager views )
then how this is dealt with, depends on the restricted field or fields are used within the report context.
For example, if you are sharing a report with Managers and that report has the National Insurance field included, but managers are not able to view this field, you will still be able to share the report, but the manager will not see the restricted field(s) when they view the report. This is the Administrators' view:
And this is the Manager's view i.e. all the fields are shown with the exception of National Insurance:
Note: When sharing reports with restricted fields, and the restricted field is used for a group, aggregate, sort or filter, you will not be able to share the report at all as, doing so, would be meaningless as the report would lose all context.
In this example the report is grouped by National Insurance number, the Administrator has no field restrictions:
But the Manager will see this error if they try to view the 'master' report.
If you try to share or copy a report to a a user group with restricted fields then you will see either or both of these errors: