Report Builder
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 11 sections:
Report type
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:
Data source
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.
Report name
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.
Report description
This is not a mandatory field, but you can use it to add additional information about the report.
Report category
This gives the option to add the report into a category.
Select fields
This is the main area of the report builder. There are three main areas within this section,
- Search fields
- Field list
- Column ordering/Name editing
Search fields
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:
Field list
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
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.
Renaming fields
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
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.
Aggregates
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.
Filtering
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:
Reason
Equals
Sickness & Absence AND Covid-19 With Symptoms
So our report now only shows these two time off types selected.
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
01/01/2023
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 2023.
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:
Site
Equals
Farndon Fields
even though we had not actually chosen to display Site as a selected field in the report.
The report now only shows Sickness & Absence and Covid-19 With Symptoms from 1st January 2023 and only for those occurrences from Farndon Fields, one of our sites.
Instead of the whole year from 1st January 2023, you might might to see absence for just one month, in this example, only April, in this case we would set our Time off start date filter to:
Time off start date
Between
01/04/2023
and
30/04/2023
Our report will then look like this
Invalid filters
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, Sickness & Absence and Covid-19 With Symptoms are both Unauthorised time off types. So if you add a filter like this:
Authorised?
Equals
Authorised
it would be "invalid" as that filter could never be true, so your report will return no results.
Select table options
This allows you to vary the CSV field separator from the standard comma option to one of the following options.
- Comma
- Tab
- Space
- Pipe
- Semicolon
- Other
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:
Pagination
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.
Exporting
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.
Report permissions
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.
For further information on Report Builder Permissions please read this article.
Comments
0 comments
Please sign in to leave a comment.