Overview
Using Custom Reporting, you can create filters to tell AMS+ exactly what data you want to pull into your reports. The more filters you add, the more specific the focus on your data becomes. Learn more about adding filters to custom reports in this article.
IN THIS ARTICLE
Adding filters
To add filters on a custom report:
- In the Field Options panel, select a field to add as a filter.
- Fields are the basic building blocks that you’ll use to create filters for a query. See the Fields section for more details.
- Select the Add Filter(s) button or drag and drop the field into the Report Filters panel.
- In the Report Filters panel, select the text next to the field name and choose from the list of operators.
- The operator compares the selected field to a value that you’ll specify in the value editor. See the Operators section for more details on operator types.
- Use the value editor to input a value (optional).
- The value editor is a control that lets you specify a desired value. See the Value Editors section for more details on value editor types.
- Add more filters (optional).
- Follow steps 1-3 to add more filters. If adding more than one filter, you’ll need to set up connectors. See the Connectors section for more details.
Filter anatomy
Filters are sets of instructions that influence a query, which pulls data into a report. A filter consists of a field, an operator, and a value editor. Multiple filters will include connectors.
Building a filter is like writing a sentence. Think of the field as the subject, the operator as the verb, and the value editor as the object. You’ll use different combinations of them to build your filters. When creating multiple filters and groups of filters, you’ll use connectors like conjunctions to specify how they are combined in the query.
Fields
Fields are the basic building blocks that you’ll use to create filters. You can find all the fields you need in the Field Options panel of the Custom Reporting feature.
Operators
The operator compares the selected field to a value that you’ll specify in the value editor. The selected field’s data type influences the types of operators you can use.
Most operators have a value editor but some do not. For example, the ‘is empty’ and ‘is not empty’ operators do not have value editors. See table below for a list of all operators, their descriptions, and data types.
Operator | Description | Data types |
---|---|---|
is empty | Use to include results that have blank values for the selected field. | Word, Number |
is not empty | Use to exclude results that have blank values for the selected field. | Word, Number |
is equal to | Use to include results that exactly match the selected field. | Word, Number |
is not equal to | Use to include results that do not match the selected field. | Word, Number |
is less than | Use to include results that have a numeric value lower than the input value. | Number |
is less than or equal to | Use to include results that have a numeric value matching or lower than the input value. | Number |
greater than | Use to include results that have a numeric value higher than the input value. | Number |
greater than or equal to | Use to include results that have a numeric value matching or exceeding the input value. | Number |
is 'yes' | Use to include results that have the checkbox field selected. | Boolean |
is 'no' | Use to include results that don’t have the checkbox field selected. | Boolean |
is in list | Use to compare a list of values. For fields with pre-defined field options, select a set of values from the pre-populated list, or enter a list of values separated by commas. Any results that exactly match one or more of the values specified will be returned. | Words |
is not in list | Use to compare a list of values. For fields with pre-defined field options, select a set of values from the pre-populated list, or enter a list of values separated by commas. Results that do not exactly match any of the values specified will be returned. | Number |
is between | Use to filter on ranges of values. For each range, the filter returns results that are greater than or equal to the minimum value and less than or equal to the maximum value. | Number |
is not between | Use to filter on ranges of values. For each range, the filter returns results that are greater than the maximum or less the minimum value. | Number |
starts with | Use to include all results that begin with a certain set of characters. | Word |
does not start with | Use to exclude all results that begin with a certain set of characters. | Word |
contains | Use to include results that contain the input value but could also have other text. | Word |
does not contain | Use to exclude results that don't contain the input value. | Word |
in sub query | Use to return results with a field value that is included in a subset of records defined by separate filter criteria. | Word, Number |
not in sub query | Use to return results with a field value that is not included in a subset of records defined by separate filter criteria. | Word, Number |
equal to date | Use to return results with a date value that is equal to date specified. | Date |
in month | Use to return results with a date value that falls in specified months, regardless of day or year. | Date |
this week | Use to return results with a date value that falls in the calendar week that includes the current date. | Date |
this month | Use to return results with a date value that falls in the calendar month that includes the current date. | Date |
previous month | Use to return results with a date value that falls in the calendar month that directly precedes the current month. | Date |
this year | Use to return results with a date value that falls in the calendar year that includes the current date. | Date |
before | Use to return results with a date value that falls before the date specified. | Date |
after | Use to return results with a date value that falls on or after the date specified. | Date |
custom period | Use to return results with a date value that falls after beginning date and on or before the ending date. | Date |
within radius | Use to return results that specify a radius in miles from a specific zip code. | Zip Code |
Value Editors
The value editor allows you to input value(s) using an empty form field, a list, or a calendar. The value editor type determines how you input values.
There are three types of value editors you will encounter:
- Text editor: Enter any text or numerical value.
- List editor: Select one or more predefined values.
- Date editor: Enter or select a date.
The type of value editor is determined by the selected field. For example, if you select a field that is a date, then a calendar will appear for you to specify a date. If further input is not required, a value editor will not appear after your selected operator.
Connectors
When using more than one filter, the system automatically adds connectors. Connectors include ‘And/Or’ conjunctions and ‘All/Any/None/Not All’ conditions. Connectors determine how filters and groups of filters are combined in the corresponding query.
‘And/Or’ Conjunctions
The ‘and’ conjunction tells the query to consider every filter whereas the ‘or’ conjunction will consider at least one of the filters.
‘All/Any/None/Not All’ Conditions
‘All/Any/None/Not All’ conditions influence the conjunctions that combine your filters and groups of filters.
- All (and) - Records that possess data meeting all of the query’s filters.
- Any (or) - Records that possess data meeting any of the query’s filters.
- None (not and) - Records that do not possess data meeting any of the query’s filters.
- Not All (not or) - Records that do not possess data meeting all of the query’s filters.
A parent condition is set near the top of the Report Filters panel. This condition influences the filters in its immediate coverage, as well as all new groups of filters. When adding a group of filters, another parent condition appears. This condition only affects the filters and groups of filters nested in its immediate coverage. Like a waterfall effect, this condition won't impact filters or groups of filters nested above it.
Note: When adding a field to the Report Filters panel, it will automatically place into the same group of filters as the selected active line. Once added, you cannot move the field.
Creating a subquery
A subquery is a query embedded within a more extensive query, relating only to the data returned by the parent query. You can use a subquery to return data of one record type based on conditions that apply to data of another record type.
When do I create a subquery?
Up until now, you’ve probably dealt mostly with equality operators. For example, an agent status is or an agent status is not. However, there may be times when you need a more complex comparison to determine if an agent has a certain set of related records or does not have a certain set of related records.
If you find yourself asking the 'has' or 'does not have' question, you’ll likely need a subquery to continue building filters for your report. This is because ‘has’ and ‘does not have’ implies a comparison. You can run comparisons between different record types and compile the results into your report by using subqueries.
For example, let’s say you want a list of all agents (with agency name and agency number) who do not have policies with life coverage. In this scenario, you would set up a subquery to compare all of the agents in your agency who do not have policies with life coverage against all of the agents who have policies with life coverage.
To get this data without subqueries, you would need to make multiple reports with different filters. You would take the results from the first report and plug them into subsequent reports until you eventually get your desired list.
With subqueries, you would only need one report.
How do I create a subquery?
First, you add a field to the Report Filters panel. This field will serve as the base record type that the subquery compares against. Then, you select a subquery operator. There are two subquery operators available: ‘is in subquery’ and ‘is not in subquery’.
In general, if you’re making a positive ‘has’ comparison, you’ll use the 'is in subquery' operator. If you’re making a negative ‘does not have’ comparison, you’ll use the ‘is not in subquery’ operator. See the Operators section for more information on the subquery operators and their data types.
Finally, you select the value editor to open a dialogue box where you’ll set up the subquery. As shown in the images below, the subquery dialogue box has many of the same features as the Report Filters panel. Once satisfied, you select the OK button in the dialogue box and preview the report.
Note: The only way to view the subquery conditions, after it has been created, is to select the value editor and re-open its dialogue box.