When creating reports within Report Builder you can use filters to help you to report on the specific data you need, for example you may only be interested in employees from a specific site or department or employees who have been off sick in a given time frame.

---

Filter Options

When adding filters to your reports you have the choice of 3 options :

• AND

• OR

• GROUP FILTERS

When adding more than one filter to your report, you will be presented with the option to select from AND / OR / GROUP FILTERS as can be seen below. By default AND will be selected but you can change this to whichever option is most appropriate. You can also use a combination of AND, OR and GROUP FILTERS in the same report.

‘AND’ Filters

Selecting ‘AND’ allows you to add more than 1 filter to your report and see employees who meet all of the filter criteria you have specified.

E.g. Show me all employees who are in the HR department AND have a start date in the previous Year.

[Department] = HR   AND   [Start date] = Previous Year

Using the AND option will result in only employees who meet all of the criteria specified appearing in your report results. In this example, it will only show employees who are in the HR department and started the previous year. Anyone who is in the HR department but has a start date which falls outside of the previous year WILL NOT be included in the results.

‘OR’ Filters

Selecting ‘OR’ allows you to add more than 1 filter to your report and see employees who meet any of the filter criteria you have specified.

E.g. Show me all employees who are either in the HR department OR have a start date the previous year.

[Department] = HR OR [Start date] = Previous Year

Using the OR option will result in employees who meet any of the specified criteria appearing in your report results. In this example, it will show both employees who are in the HR department (regardless of if they started in the previous year) and employee’s who started in the previous year (regardless of if they are in the HR department).

‘GROUP FILTERS’

Group Filters allows you to take things a step further by adding more complex filtering to your reports. You can think of Group Filters like the brackets you would find in a mathematical equation. Whatever is in the brackets is calculated independent of the rest of the equation.

Example 1.

E.g. Show me all employees who are Managers but only if they are in the HR department OR the Finance department. 

In this example, firstly we want to identify anyone who a Manager. Then we want to identify, of those people, who is in the HR or Finance department.

If we were to just use the AND and OR filters, our results would not be what we need:

As you can see from the results, we have identified all employees who are Managers in the HR department but we are also seeing an employee who is not a Manager but is in the Finance department:

Our report is showing us : 

• Anyone who is a Manager AND is in the HR Department

• AND anyone who is in the Finance Department (regardless of if they are a Manager).

Instead we need to tell the report to firstly find those who are Managers and THEN find anyone within that group who is also in the HR OR Finance Department. We do this by using Group Filters aka adding in our brackets :

[Manager?] = Yes AND  ( [Department] = HR OR [Department] = Finance )

By selecting Group Filters we are adding brackets around the criteria before and after (see red brackets above).

With this in mind, it is very important to ensure you add your criteria in the correct order so you are able to Group Filters / add brackets in the right place. 

Now when we run our report again, we see only those employees who are Managers AND are in HR OR Finance :

Example 2.

Looking at another example, we may want to identify the number of starters and leavers in a particular department in the previous year.

E.g. Show me all employees in the Sales department who have a Start date OR Leave date in the previous year

Again in this scenario we will need to make use of the Group Filters functionality.

If we were to create the report without using Group Filters…

…we will be shown results for :

• Anyone who is in the Sales department AND has a start date in the previous year

• AND also anyone who has a leave date in the previous year (regardless of their department)

We need to use Group Filters to tell the report that firstly we want to identify anyone in the Sales Department. From those employees we want to see anyone with a Start date or Leave date in the previous year :

[Department] = Sales AND( [Start date] = Previous Year OR [End date] = Previous Year )

As you can see, the results have dramatically reduced with this small change and we can now only see our 1 employee who is in Sales and started in the previous year :

Example 3.

So far we have combined Group Filters with OR but we may also want to combine Group Filters with AND.

E.g. Show me all employees in the HR department along with any employee who is a Manager AND started in the previous year. 

We need to use Group Filters to tell the report that we first want to identify anyone in the HR Department and in addition to that we also want to identify any Managers who started in the previous year (regardless of their Department) :

[Department] = HR  OR( [Start date] = Previous Year AND [Manager?] = Yes ) 

As you can see, we have 2 groups of employees in our report results, those who are in the HR department AND those who are Managers who started in the previous year.

Example 4.

In our final example we are going to look at using the Group Filters functionality more than once in the same report. This would be used when creating more complex reports with multiple different requirements.

E.g. Show me all ‘active’ employees who are full time Contracts Managers in the Sales department OR are full time Accounts Assistants in the Finance department.

So in this scenario we need to make use of 2 sets of Group Filters.

Firstly we need to identify all of our active employees. From there we want to identify anyone who is a full time Contracts Manager in the Sales department OR anyone who is a full time Accounts Assistant in the Finance department. Both sets of criteria are completely independent of each other.

[Active] = Active AND([Job title] = Contracts Manager AND  [Department] = Sales AND [Hours per week] = 37.5 ) OR( [Job title] = Accounts Assistant AND [Department] = Finance AND  [Hours per week] = 37.5)

 

 

Looking at our results you can see we have 2 key groups of employees, those who are full time Contracts Managers in Sales and those who are full time Accounts Assistants in Finance. You can also see that ALL of the employees in the results are Active employees. By keeping the ‘Active’ element of the criteria outside of the Group Filters/brackets, this has been applied to the entire query.

It’s important when using AND / OR / Group Filters to always double check the selections you have made. We would recommend you build up your report bit by bit, reviewing the results after each change. This will make it much easier to identify where you have gone wrong if the results produced aren’t what you were expecting.