Refine query criteria

Using the AND/OR option

When you add multiple criteria against several fields in a query, you see an option to select AND/OR. This option works in the same way as Boolean logic.

For example, if you want to search for the number of full-time employees who work in the Accounts department, your search criteria need to include Full Time AND Accounts in the Hierarchy field.

If you want to find the number of full-time employees who work in the Accounts department OR the Payroll department, you need to include Accounts OR Payroll AND Full Time. (If you use AND here it does not return the correct results, as a full-time employee cannot work in two departments at the same time.)

You can add as many criteria to fields as you need to filter the results, but it is best to build queries in stages, so that if anything goes wrong it’s easy to spot which criteria caused the problem.

Using brackets

You can use brackets to refine your query criteria.

For example, if you want to search for any employees who started this year for whom you do not have Current Position or Location details, you might build the query as shown in the screenshot below:

However, when you use a combination of AND and OR, your true intention is not clear to Query Builder. It interprets these criteria as Current Position is equal to nothing, OR Location is equal to nothing, AND the employee has started this year, and will include every employee with no current position in the results, regardless of when they started, and every employee who started this year with no location recorded.

However, if you use brackets, Query Builder will look at everything in the brackets first, before doing anything else (similar to the theory behind writing Microsoft Excel formulas). If you build the query as shown below, Query Builder will first find all employees that have Current Position equal to nothing OR Location equal to nothing, AND then filter this list for employees who started this year.

Add brackets and position them in your criteria using the icons:

Including or excluding leavers

If you use the ‘I want to query employees’ options at the top of the screen, the Include Leavers option is ticked and leavers are included in queries, but otherwise, Query Builder excludes leavers from queries. If you want to include leavers in a query, select Include Leavers at the top right-hand side of the Query screen (circled in red in the screenshot below).

IRIS Cascade is designed to recognise when leavers should be included in queries, regardless of their employment status, for example, a headcount report as at the end of the last quarter or a report showing cost of absence or Bradford Factor. In this case the Include Leavers box is greyed out, to make sure that leavers are included in the results. If you need to override this (and exclude leavers from your historical data), you can add the Leaver field from the Leaver screen to the query and set the criteria to equal No.

Using time criteria

IRIS Cascade includes additional options to look for results for specific periods of time:

• At a Point in Time.

• Over X Weeks.

• Over a Date Range.

At a Point in Time

There are two ways to find historical information about your employees for a specific date, for example, the position and salary of employees at 31 December 2021.

The easiest way is to use the filter selection at the top of the Query Builder screen.

Alternatively, you can add dates to the query criteria. The screenshot below shows the effective date should be on or before 31 December 2021, and the end date for historical records should be on or after 31 December 2021, or blank (i.e. the record is current).

Over the Last X Weeks

You can use the Over the Last X Weeks filter option at the top of the Query Builder screen to find information about your employees over a rolling period of weeks. The query only runs to the last 52 weeks, although you can select a higher number.

The screenshot below shows settings for a query to find Bradford Factor scores for the last 52 weeks. You need to use the Absence (Calculated) screen to search for this information.

Over a Date Range

You can use the Over a Date Range filter option at the top of the Query Builder screen to find information about your employees over a specific period, for example, employees with a start date in the first six months of a year.