Advanced Filter

How To use the Advanced Filter tab

Note: Users should have a working knowledge of SQL before attempting to use Advanced Filters. For most users, the Build-A-Filter screen will be sufficient.

1. Enter a SQL statement into the box starting with the table to select records from. The statement will always include Select * from at the beginning.
2. To save the filter make sure the name is entered in the Filter Name field and click Save.

   Note: Filters saved from the Advanced tab will be saved as Advanced Filters. This designation may means that only NON-Advanced Filters can be re-displayed in the Build-A-Filter screen.

3. OR click Run to immediately run the filter.

Reserved Words

• Reserved words can be used to add data to a filter based on when, and by whom it was run.

  Example: A user wants a filter of all the work that was completed in the last week. They could create a filter that said:

  SELECT * FROM WKORDER WHERE WKORDER.WO_END_DT >= '6/18/2012' AND WKORDER.WO_END_DT <= '6/22/2012'

  However, this would be out of date the next week, and the user would have to adjust the dates. Using the %PREVIOUSWEEK% reserve word they could create a filter that said

  SELECT * FROM WKORDER WHERE WKORDER.WO_END_DT = %PREVIOUSWEEK%

  This filter would the work orders with an end date during the week previous to it being run.

• Reserved words can be added easily using the dropdown list in the bottom right corner of the Advanced tab.
• To add a Reserved Word, simply select one from the list. It will be added to the filter statement.
  • %FISCALYEAR% - The agency must own Work to use this string. It uses the current fiscal year from the Planning and Budgeting, Fiscal Year module in the desktop application.
  • %CURRENTYEAR% - Inserts the current calendar year.
  • %CURRENTQUARTER% - Inserts the current quarter.
  • %CURRENTDAY% - Inserts the current day.
  • %CURRENTWEEK% - Inserts the current week.
  • %PREVIOUSDAY%- Inserts the previous day.
  • %PREVIOUSWEEK%-Inserts the previous week.
  • %PREVIOUSMONTH%-Inserts the previous month.
  • %PREVIOUSYEAR%-Inserts the previous year.
  • %FIRSTHALFOFTHEMONTH% - Inserts the first half of the month (days 1-15).
  • %SECONDHALFOFTHEMONTH% - Inserts the second half of the month (days 15-31).
  • %CURRENTMONTH% - Inserts the current month.
  • %ONEWEEKAHEAD% - Inserts the next week.
  • %TWOWEEKSAHEAD% - Inserts the next two weeks.
  • %ONEWEEKAHEADANDBEFORE% - Inserts seven ahead plus everything from the previous timespan. For example, users may use this string to query for all existing open work orders as well as those that are open/due in the next week.
  • %TWOWEEKSAHEADANDBEFORE% - Inserts the next two weeks plus previous timespan.
  • %LOGGEDINUSERID% - Inserts the current users ID.
  • '%LOGGEDINEMPCODE%' - Inserts the current users employee code from the Work > Work Flow Setup > Employee module. This only works if the users login id is entered in the Login ID field on their employee record.

    Note: When using the reserved words above, enter them in all uppercase letters and enclose them in percent symbols (%)