Smart Report Filters
Useful Information! Filters limit the data included in a report. For example, you could apply a filter to a report to include data for only two partners, or only billed amounts greater than £1,000. You can apply filters to a report using either predefined filters or a filter prompt.
Predefined filters
To add predefined filters to a report, use the Filter and Or columns in the Change Report window. When you want to insert predefined filters in a report, depending on the type of data in the selected field, you are offered:
| A list | To select from the list, click the down arrow and select a value. |
| Ellipsis button | The Ellipsis button appears for client, supplier, employee and assignment searches, allowing you to access the Search window. Click the Ellipsis button to open the Search window. |
| A blank field | Enter a few characters in the Search for field and click the Search button. Select a value from the available options and click OK. |
To apply a filter in a blank field, type the statement that will select the required data. For example, to select all billed amounts greater than £ 1000, type: >1000.
You can use the following filters:
Tip! You can add up to five predefined filters to a report.
Filter prompts
You can specify a Prompt in the report layout so that when you run a report, the Report Fields window prompts you to specify the data you want to include in the report prior to opening the Preview window.
To add a filter prompt in the Change Report window, select the Prompt check box alongside the field or fields that data is to be filtered by.
You can specify as many filter prompts as you want. All prompts are displayed in a single Report Fields window when the report is generated.
Note: You can choose to display all data by clicking OK on the Report Fields window, bypassing the filters.
Include a Filter prompt
Ensure you are in the Change Report window.
- In the field listing, select the Prompt check box alongside a field.
- Click on the Save icon,
to save your report .
Inserting pre-defined filters
Ensure you are in the Change Report window.
- Click in the Filter column alongside a field.
- In the Search window, type a filter and click on the Search button. This displays any client whose code starts with the filter that you entered.
- Click on the row where code matches what you want the filter to be and click on OK.
- Click on the Save icon to save the predefined filter to your report.
How to add Filters
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 *
Examples:
To search for clients with last names beginning with the letter 'C', type into the Filter field, [Like C*]. This expression returns all clients with a name beginning with the letter 's'.
In this example, using an asterisk in combination with the words or letters shown in the Field column, returns the results shown.
|
Example |
Result |
|
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'. Like ad* finds Adam, Adrian, Adeline... |
|
Like *ad |
Displays results that end with the letters 'ad'. Like *ad finds Chad, Vlad, Brad... |
|
Like *ad* |
Displays results that contain the letters 'ad' in the middle of the word. For example, this will bring up Sadie and Madison, but not Adam or Brad. |
|
Not like adam |
Displays all records except those beginning with the letters specified. |
Note: 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.
Note: The characters are not case sensitive. This means that ad* would bring up the same results as AD*.
Null
Null is a filtering expression that will locate missing or unknown data in a field.
Examples:
In the following example, entering 'is null' in the Filter column searches for clients who do not have a post code.
In this example, using Postcode in a Field column and then entering any of the Expressions below, displays the results shown.
|
Expression |
Result |
|
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 you use to return a list of records that have values within a specified range.
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')
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 unallocated cash in the filter column:
-
Between 50 and 100 — Note: both figures are included in the results
Or, you could locate invoices between certain dates — Between 01/01/20 and 31/01/20.
Note: 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 won't work.
Note: You can also use Not between...and...to return records that fall outside those post codes.
Delete filters
Ensure you are in the Change Report window.
- Click in the cell where the predefined filter is entered.
- Press [Delete].
- Click the Save icon to save your report.