Skip to main content
Skip table of contents

Filtering options

To add filters to a report, you can enter a value into the Filter and Or columns in the New Report or Change Report page. See Creating reports.

You have five filter columns available in each report you create or edit.

When you want to insert filters in a report, depending on the type of data in the selected field, you may see one of the following:

Option

Description

Drop-down list

To select from the drop-down list, click

and select a value.

Ellipsis

The ellipsis button is displayed for client, supplier, employee and assignment/job type searches, enabling you to access the Search window.

Click the ellipsis

 to open the Search window.

A blank field

Enter the value you want to filter on and click OK. For example, to select all billed amounts greater than $1000, type: >1000.

For details on the type of filters you can use, see below:

Filter commands

Less than (<)

Less than (<) is a filtering expression used with numeric figures to return filter results under a specific amount. For example, typing < 1000 into the filter field filters the report to only include results where the value of the filtered field is less than 1000.

  • < can be used with = (equals) to return less than or equals to values. For example, typing <= 1000 into the filter field filters the report to only include results where the value of the filtered field is less than or equal to 1000.
  • < can be used with > (greater than) to return values that are not equal to a specified amount. This expression has the same function as not. For example, typing <> 1000 into the filter field filters the report to only include results where the value of the filtered field is not 1000.

 

Greater than (>)

Greater than > is a filtering expression used with numeric figures to return filter results over a specific amount. For example, typing > 1000 into the filter field filters the report to only include results where the value of the filtered field is greater than 1000.

  • > can be used with = (equals) to return greater than or equals to values. For example, typing >= 1000 into the filter field filters the report to only include results where the value of the filtered field is greater than or equal to 1000.
  • > can be used with < (less than) to return values that are not equal to a specified amount. This expression has the same function as not. For example, typing <> 1000 into the filter field filters the report to only include results where the value of the filtered field is not 1000.
Equals (=)

Equals = is a filtering expression used to return filter results for a specific value. The value can be text or numeric. For example, typing = 1000 into the filter field filters the report to only include results where the value of the filtered field is 1000.

  • = can be used with < (less than) to return less than or equals to for numeric values. For example, typing <= 1000 into the filter field filters the report to only include results where the value of the filtered field is less than or equal to 1000.
  • = can be used with > (greater than) to return greater than or equals to for numeric values. For example, typing >= 1000 into the filter field filters the report to only include results where the value of the filtered field is greater than or equal to 1000. 
Like /(Not Like)

Like is a filtering expression you use with a word or letters to define the results you want.

You may want to use like when you:

  • know only part of the result you want.

  • want to find values that start or end with a specific letter or match a certain pattern.

Like is usually used with the wildcard character *.  In the example below, we're filtering for postcodes that start with 40. This would return all clients with a postcode of 4000, 4001, 4002 etc.

Examples

To search for clients with names beginning with the letter s, type like s* in the Filter field.

This expression returns all clients with a name beginning with the letter s.

The following is a list of examples of the different ways you can use a wild card character in the filter:

  • Like adam—Displays all clients beginning with the letters ADAM such as Adam and Co, Adam Smith.

  • Like ad—Displays results that begin with the letters ad, such as Adam, Adrian, Adeline.

  • Like *ad—Displays results that end with the letters ad, such as Chad, Vlad, Brad.

  • Like *ad*—Displays results that contain the letters ad in the middle of the word, such as Sadie and Madison, but not Adam or Brad.

    The example below filters all job types (AO) / assignment types (AE) in an Employee Activity report to include only the job /assignment types that have leave in their names. This information can be used for payroll purposes. The results could return the following, depending on your set up:

    Annual Leave, Sick Leave, Maternity leave or

    Leave - Sick, Leave - Annual, Leave - Maternity, Leave - Public Holiday.

  • Not like adam—Displays all records except those beginning with the letters specified.

    The example below will exclude all clients with names starting with 'anderson'.

Notes

  • You can use either * or % as wildcard characters. Place them either at the start or at the end of the search text, not in the middle, i.e. not ad*am.

  • The characters are not case sensitive. This means that the same results are obtained using ad* and AD*.

  • You can use not to return the opposite results. For example, to search for clients with names that do not begin with the letter s, type not like s* into the Filter field.

  • You can only use the like or not like filter once for a field/row.
Null /(Is Not Null)

Null is a filtering expression that locates missing or unknown data in a field.

Examples

In the following example, entering is null in the Filter column searches for clients who have a contact type of Individual and don't have a date of birth (DOB) attached to them.

 

In this example, using Postcode in a Field column and then entering any of the Expressions below, displays the results shown.

Is Null, =Null, = Null displays postcodes with null (blank) values.


Is Not Null, <>Null, <> Null displays postcodes that contain a value.

In

In is a filtering expression used to return a list of records that have values within a specified range.

  • You would use this when you need to filter more than 5 items in a reporting field.

You may want to run employee reports and report on >5 employees but not all employees.

The example below will only return data for Daniel Samson, Lidia Gibson, Malcolm Green, Lauren Angood, Rod Angood, Eric Berry and Alex Bantin.

Examples

You could use In to filter reports so that information for a specified area is displayed.

In this example, using In filters reports of the same records:

  • In (adam01, adrian01, adel01)

  • In adam01, adrian01, adel01

  • In 'adam01', 'adrian01', 'adel01'

  • In ('adam01', 'adrian01', 'adel01').

You can return a list of records that do not have values within a specific range by using not . For example, not in (adam01, adrian01, adel01) will return a list of records that do not include the defined information.

Between

Between is a filtering expression you use to return a list of records that have values between a specified range of values.

Examples

You could use Between (insert the start value) and (insert the end value) to extract reports that fall within the specified range of values.

For example, to find out if there are any clients within a range of Postcodes you could enter in the Filter column:

Between 2130 and 2150

Both postcodes are included in the results.

Or, you could locate bills (fees) between certain dates—Between 01/01/09 and 31/12/06.

The example below filters on clients that were closed between 01/07/2017 and 30/06/2018.

You can use single or double quotes around the values or none at all. If you use quotes, use the same type of quotes marks consistently. Otherwise the filters will not work.

You can also use Not between (insert value) and (insert value) to return records that fall outside a range of postcodes.

Applying multiple filters

To use the Or filter

The OR filter is applied when you add or select a filter in the Filter column as well as in one or more Or columns.

An example where this could be used is in a practice WIP report where you want to report on Partner A or Partner B. In the example below, we're reporting on clients with a wip balance that are linked to either Simon Bache or Eric Berry as their partner.

To apply two filters in one column

You can filter on more than one field in a report by using combinations of the Filter column and the Or columns.

In the example below, we're filtering the report for all clients belonging to Client Partner - Simon Bache AND where they owe money >$1000.00 by using the Filter column.

 

Another example of multiple filters is in an Aged Debtor report where you are reporting on a specific partner where his clients have an aged balance outstanding 30 days and over.

In this second example, we're filtering the 30 day balance that is <>0, or 60 day balance <>0 or 90 day balance <>0 and Client Partner = Simon Bache.

The rule is if you're filtering a value in the Filter column and an Or column,the filter must be applied across all columns.

If we select Simon Bache as the Client Partner in the Filter column and don't select him under the Or columns, the data is filtered correctly in the Filter column for 30 days for Simon Bache but the filtering in the Or columns (60 days and over) will return the data for all Client Partners. This will return incorrect results.

Filters are based on the detail line and not on any grouping that has been applied to a report.

If you are filtering on a $ value for clients, the report module will determine if the client meets the value specified and if so, will display the client $ value. Grouping is then taken into account on how to display the data on the report.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.