How to add or update advanced WHERE clauses

Topic

This article describes how to add or update advanced WHERE clauses in Autotask.

Environment

  • Autotask

Description

The advanced WHERE clause is an SQL query option that allows you to filter out data that you don't want to include in your workbook analysis. Below are a few examples of how you can use SQL advanced WHERE clauses.

  • The Service Desk workbooks will download all the service ticket data for the last ten weeks or ten days by default. You may want to limit the download to specific queues, issue types, or other ticket criteria.
  • The Sales workbook will download all closed opportunities for the current and previous year and all forecasted opportunities within a specified date range. You may want to limit the closed opportunities to specific sales teams or accounts or change the date range for forecasted opportunities.
  • The Financial workbook will download all revenue and costs for the current and previous year by default. You may want to restrict the download to certain contract types, contract categories, accounts, work types, etc. The financial workbook will also download all resource hours for the current and previous year by default. You can use an advanced WHERE clause to restrict the download to specific resources, work types, etc.
  • The Projects workbook will download all project and task data for the last ten weeks by default. You may want to limit the download to include only client projects or open tasks.

Creating an advanced WHERE clause requires knowledge of SQL. If you have experience in forming SQL queries, you can create or edit an advanced WHERE clause on the Config worksheet tab as follows:

  1. Open your workbook and click the Config tab.
  2. In the Configuration worksheet, scroll down to Advanced Where Clause.
    If you already have one or more WHERE clauses in place, the Advanced Where Clause field displays the current clause. You can edit or replace the clause.
  3. Click Update Where Clause.
  4. If you want to edit the current clause, make changes as needed.
  5. To add a clause, type the clause into the text box or copy and paste your prepared WHERE clause into the field.
  6. Click Update Where Clause.

NOTE  Autotask support does not provide individual training or customization for performance analytics workbooks, including the configuration of advanced WHERE clauses. We can, however, assist with technical issues such as an inability to access workbooks or errors encountered while using them.