Many times when writing reports there will be too much raw data, and it will be necessary to filter that information down so that the information that is needed will come into the report. Data may need to be limited by a specific TouchPoint, a date range, or a specific TouchPoint taken within a specific program in a specific date range.
We'll cover some of the more common uses of the Query Filters for building reports, the qualifiers, and the options to re-query or prompt.
Frequently Used Qualifiers
In List/Not in List
Use more than 1 potential filter requirement
Example: Site Names, TouchPoint Names, Unflattened Demographics, Caseworkers
Equal to/Not Equal to
Use for a single filter requirement
Example: Single answer value, a single participant/staff name, a single identifier
Between/Not Between
Use for an inclusive or exclusive date range
Example: TouchPoint date range, Program Start/End Date range, DOB range, Response Creation Date range
Matches pattern/Different from pattern
Use if there are several similar potential filter requirements to include or exclude. Note: patterns require a % for a wildcard
Example: Exclude anyone with the last name fake - Last Name Different from pattern %fake%
Example: Include several types of similar questions - Question Matches pattern %testing%
Greater than/Greater than or Equal to
Use if the data must be greater than the filter requirements
Example: Program start dates that must be after the listed date
Example: Case Numbers larger than the listed value
Less than/Less than or Equal to
Use if the data must be less than the filter requirements
Example: Program start dates that must be before the listed date
Example: TouchPoint Response IDs less than the listed value
Is Null/Is not Null
Use if the data must be without a value, or only have a value
Example: Response Unique ID Is not Null will only bring in participants with TouchPoint Responses recorded
Example: Program End Date Is Null will only bring in participants without a program end date recorded
Filter Options
Constant
A single value that must be matched exactly, and is typed manually
Example: Site Name, TouchPoint Name, a single Answer value
Value(s) from list
Several values that must be matched exactly. Note: choosing this option will bring up a list of options to choose from
Example: A list of TouchPoint names, a list of Program Names, a list of unflattened Demographic Names
Prompt
Creates a prompt when the report is refreshed
Example: A non-static date range for TouchPoints, Program Start Dates, Caseworker Names
Object from this query
Uses an object from the query to filter on. Note: cannot be used with In list qualifier
Results from another query (Any)
Uses the results from another query to filter data on. Note: the query being referenced must be refreshed before it will be available to use