Designing an Advanced Filter by specifying an SQL WHERE clause

You can create, edit or remove the advanced filters by directly specifying an SQL WHERE clause.

NOTE about shared views: You can change the advanced filter of a shared view, but you cannot save your changes and update the shared view. To save your changes, create your local copy of the shared view. For details, see Working with views.

To configure an advanced filter by specifying an SQL WHERE clause:

  1. Right-click anywhere in the grid (or just click the gear icon) and choose Customize View from the pop-up menu. The Customize View window opens.

  2. Click the Advanced Filter tab. If it appears in the Design mode, click Switch to SQL Mode.

  3. In the SQL mode, type in the SQL WHERE clause (without the word WHERE), and click OK.

Your SQL WHERE clause can include the following placeholders: <Current User>, <Current User Organization>, and <Current User Location>. Before the filter is applied, the placeholders will be automatically replaced with the respective data from the Full Name, Organization, and Location fields of the current user’s Person record.

ClosedClick here to show the examples of SQL WHERE clauses for the Incidents grid

Meaning WHERE clause

Show overdue Incidents

(DATEDIFF(n, [Due_Date], [Completed_Date]) >0) OR ([Completed_Date] is NULL) AND(DATEDIFF(n, [Due_Date], GETDATE()) >0)

Show overdue Incidents which status is neither Closed nor Resolved

([Status] <> N'Resolved') and ([Status] <> N'Closed') and (DATEDIFF(n, [Due_Date], GETDATE()) >0)

Show unassigned Incidents

([Status] = N'Unassigned') and (([Assignee] ='') or ([Assignee] is NULL))

ClosedClick here to show the examples of SQL WHERE clauses for the Computers grid

Meaning WHERE clause

Show leased Computers with expired Lease Return date

[Lease] = '1' AND (DATEDIFF(n, [Lease_Return_Date], GETDATE()) >0)

Show out-of-warranty Computers

(DATEDIFF(n, [Warranty_Exp], GETDATE()) >0)

NOTE: When you switch to the Design mode, all the data entered in the SQL Mode is lost.

To remove an advanced filter created by specifying an SQL WHERE clause:

  1. The Advanced Filter tab appears in the SQL mode. Right-click anywhere in the filtered grid and choose Customize View from the pop-up menu. The Customize View window opens.

  2. Click the Advanced Filter tab and clear the criteria box, click OK.