How To: Use Smart Filters

Smart Filters and Data Query is an Advanced User feature.

Smart filters allow the user make make running an SQL report much more user-friendly as it displays a popup which the user can enter the parameters in each time a report is executed, instead of having to amend the SQL.

The filter types that can use smart filters are:

• DATE
• TEXT
• INTEGER
• CHECKBOX

You can add a caption in the popup that prompts the user for the correct format or input value.
Note: do not use colons in the caption, as they are used as separators in the query code.

The Syntax

The syntax for adding a filter for a field in the SQL is {%filtertype:Caption:defaultvalue%}, examples of using this are:

• DATE, there are two options:
  1. A start and / or end date, i.e. {%DATE:Start Date:01/04/2016%} and {%DATE:End Date:30/04/2016%}
  2. A number of days to add to today's date, for example -10 to set the default to 10 days before today, or 1 to set it to tomorrow. These are the dates that will appear in the popup by default.
• TEXT: the default text value
• INTEGER: the default numeric value
• CHECKBOX: -1 or 0 for ticked and unticked respectively

Examples

For examples of how the Smart Filters work In Khaos Control:

1. Open a Data Query screen (show me how).
2. Enter following sample queries in the query area one by one and execute:

• SELECT * FROM SORDER WHERE POST_DATE between {%DATE:Date from:-7%} AND {%DATE:Date To:%}

  Finds all orders where the post_date (this is when the order is saved but cannot be changed so it keeps it as a stamp of when the order was originally saved/created) is between 7 days ago and today.
  

• SELECT * FROM SORDER WHERE POST_DATE >= {%DATE:Start Date:-10%} AND TAXABLE = {%CHECKBOX:Show taxable orders?:0%}

  Finds all orders where the post_date is greater than or equal to the date entered and the user can opt to view taxable orders.
  

• SELECT * FROM CONTACT WHERE SURNAME LIKE '%{%TEXT:Surname filter:%}%'

  Finds all contacts with the surname entered into the popup.
  

• SELECT * FROM STOCK WHERE LEAD_TIME >= '{%INTEGER:Lead Time Greater Than:%}'

  Finds all stock items with a lead time greater than or equal to the number entered into the popup.
  

• SELECT * FROM COMPANY WHERE CREATED_DATE between {%DATE:Start Date:01/04/2016%} AND {%DATE:End Date:01/04/2017%}

  Finds all new companies created between the dates entered into the popup.
  

Points to Note

1. On the second query (Contacts), we've added a wildcard parameter at either end of the value. This means that you don't need to use the Wildcard character (%) in the value you enter in the dialog.
2. When running a SQL report with such filters, a dialog is popped up to prompt the user to fill in values for each of the filters.
3. The values the user chooses are substituted back into the SQL string. For the checkbox type, it uses -1 or 0. Since that's what we use for most of our boolean fields, you can compare this directly to many fields in the database.
4. Smart Filters don't work with the Workflow (Email Trigger).
5. If you run regular reports between specific dates then using the method of default dates ...between {%DATE:Date from:-7%} AND {%DATE:Date To:%}... is time saving.

See Also

• Data Query Overview