Report Filters

Filters

Date Range filter

The Last and Next filters won’t include the current time, which could explain why results are not showing even though the user thinks they should be.

For example, the expiry date (25/06/2023) isn’t Next Week at the time being looked at (20/06/2023). It would be in This Week (Sunday), so the results with that expiry won’t show if Next Week is added.

The filter name is literal, for instance, the filter “Next Week”, does not include the next 7 days from today. It would show the next 7 days instead.

Another example is the Next 3 Months, if you're in June, the filter would be This Month (if you're in June), not in the next 3 months (July, August, September).

Filters are a vital part of most Reports, and this page is used to apply rules to your Report so you only pull out relevant information.

You can filter your Report by any of the Fields within the Data Sources you have selected. The Fields you choose to filter on do not have to be Fields you have incorporated to display into your Report, although they can be.

The most commonly-used filters include date ranges, selecting specific types (i.e. Resource and course types), selecting specific courses and location filters (i.e. Venue or customer address).

Beside the name of each Field, you will notice there is a ‘(varchar)’, ‘(datetime)’ or other Field type. The options available within the Operator drop down box are dependent on this type of Field, i.e. date Fields will allow you to Filter based on Time Period & Less Than x Days Old.

 


The Operator within the filter defines how you are filtering the selected field, whilst the Value allows you to specify what you're filtering on (e.g. a date range, specified items within that field etc.).

Once you’ve selected the Operator, you then enter or select the Value(s) you wish to Filter on. Again, the Operator you have just set affects the options available to you in the next box (Values).

For example, selecting Equals (Select) will bring up a list of Values to choose from, so you can select which one you would like to use. The same applies to Equals (Multiple), where you can select multiple Values by holding 'ctrl' while you click.

Tip!

You can take advantage of the ‘Equals (Checkbox)’, 'Equals (Select)', etc. operators to select the values you want for your filter, but always remember to switch back to ‘Equals’ if this is a permanent filter, i.e. won't be changed each time the report is viewed.


Filters in practice

Bookings in the last 7 days

Filtered on the datestamp from the ap_coursescalendar_bookings_view Data Source:

Expiring Awards This Month

Filtered on the expires date from the ap_users_awards_view Data Source:

Users Details

There are a few filters in use in this Report:

  1. To only include enabled Users, a filter on the status Field from the users Data Source
  2. To be able to filter based on company, i.e. only show me Users from ABC Limited, a filter on the company name Field from the companies Data Source
  3. To be able to filter based on User type, i.e. only show me Care Workers, a filter based on the usertype label from the usertypes Data Source

 

‘Blank’ is ticked besides the company and User type filters. This allows the Report to pull out results for Users which do not have a User type or company.

Filter Rules

There is an area below the filters you choose to use in your Report, where you can add ‘rules’ to how your filters work.

For example, in the above report, if you wish to display all Users who are enabled AND in Health and Safety Plc. OR were Trainers. You would enter 1 AND (2 OR 3).

If you wish to pull into the Report any User who was enabled or were within Health and Safety Plc. or were Trainers, you would use the Filter Logic and enter 1 OR 2 OR 3.