Advanced Filtering for Queries

This article is the end of a series. To start with an overview of the Query Tool, please click here to go to the beginning of the series.

When adding a filter, there is an operator to show the relationship between the filter field and the value: the filter field must equal the value, be less than the value, greater than the value, etc.

Breakdown of Operators

• Equals: For some fields, this operator will look at the value and if the field matches this value, it will be included in the results.
• Not Equal: For some fields, this operator will look at the value and if the field does NOT match this value, it will be included in the results.
• Greater Than: For some fields, this operator will look at the value and if the field is greater than this value, it will be included in the results.
• Less Than: For some fields, this operator will look at the value and if the field is less than this value, it will be included in the results.
• Contains: For some fields, this operator will look at the value and if the field contains this value, it will be included in the results.
• Does Not Contain: For some fields, this operator will look at the value and if the field does NOT contain this value, it will be included in the results.
• In the Period: For some fields, this operator will look at the value and if the field matches this value, it will be included in the results. This will only be available for filter fields that involve a date.
  • Last 1 Day: The filter field will show profiles or records if the chosen date field has a value that falls on yesterday or today.
  • Last 7 Days: The filter field will show profiles or records if the chosen date field has a value that falls on any day from 7 days ago through today.
  • Last 30 Days: The filter field will show profiles or records if the chosen date field has a value that falls on any day from 30 days ago through today.
  • Month To Date: The filter field will show profiles or records if the chosen date field has a value that falls on the first day of the month through the current date.
  • Previous Month: The filter field will show profiles or records if the chosen date field has a value that falls on any day from the first day of last month to the last day of last month.
  • Year To Date: The filter field will show profiles or records if the chosen date field has a value that falls on any day from the January 1 of this year through the current date.
  • Previous Year: The filter field will show profiles or records if the chosen date field has a value that falls on any day from the January 1 of last year through December 31 of last year.
  • Next 7 Days: The filter field will show profiles or records if the chosen date field has a value that falls on any day from today through 7 days later.
  • Next 30 Days: The filter field will show profiles or records if the chosen date field has a value that falls on any day from today through 30 days later.
  • Next 60 Days: The filter field will show profiles or records if the chosen date field has a value that falls on any day from today through 60 days later.

Multiple Filters

If you add more than one filter, notice that there is an AND/OR drop down between rows:

In the example above, the query should filter down to profiles that are members AND those profiles must be organizations.

If we want to add another filter that filters down to profiles in a specific county, we would use another AND, and if we want to add another filter for a different possible county, we would use OR:

For the example above, the query should filter down to profiles that are members, AND those profiles must be organizations, AND those profiles must be in Maricopa county, OR in Marion county.

There is a problem though:

• Profiles that are members...
• AND that are organizations...
• AND that are in Maricopa county...
• OR simply profiles that are in Marion county.

This would return profiles in Marion county whether they are members organizations or not.

For this to filter properly, you need to put the counties under the same filter group.

This adds parenthesis around them, so that they are grouped, and stick together:

• Profiles that are members...
• AND that are organizations...
• (AND that are in Maricopa county OR Marion county).

Any additional counties would need to be in the same group number to keep them all together.

 

Custom SQL Code

You can add/edit a custom SQL query to execute through the Query Tool by clicking "View the SQL Code" on any new or existing query. Check "Use my custom SQL" and the text box with the SQL for the query will enable, allowing you to add and edit SQL code. 

Note that for security purposes to ensure this tool cannot be used to alter or break your database through the browser interface, certain SQL keywords cannot be used anywhere in the query, including in any comments or field labels. If any of the words below are contained in the query as a standalone word (and not directly adjacent to a character listed below), an error will display and the query will not execute unless that keyword is removed or changed to follow the rules below. 

The following keywords are prohibited, UNLESS they are directly adjacent to the characters listed below: 

• CREATE
• UPDATE
• INSERT
• DELETE
• DROP
• ALTER
• GRANT
• EXEC
• DENY
• MODIFY
• RENAME
• LOAD
• REVOKE
• UNLOCK
• LOCK
• Unless one of the following characters appears directly before or after the word with no space in between:
  • Any alpha character a-z (or A-Z)
  • Any numeric value 0-9
  • %
  • '

For example, the following field label is NOT allowed: 

select profile.memberactivitytype as 'Member Drop Type'

but if the prohibited keyword is next to a single quote, it will be an exception and it WILL be allowed: 

select profile.memberactivitytype as 'Drop Type'