Filters
Definition
A filter is used to extract a subset of data using selection criteria.
EndDefinition
- It is built around a parent query that accesses a set of standardized data from the database.
- It can be defined:
- In Visual Mode (or Assisted Mode): Conditions are applied to the parent query fields and other manually added fields. Certain fields have an input wizard, e.g. a calendar for dates, lists of values, tree structure, etc.
- In Advanced Mode: Users manually define SQL queries that will call all of the fields in the data model.
- It can contain different criteria:
- Static: The value of each criterion is specified within the filter and cannot be modified when the filter is applied.
- Dynamic: The value of each criterion is specified by the user when the filter is applied.
- It is available to users based on their user profile.
Types of filter
Filters in a grid in List mode
- These are associated with menu items.
example The Active Equipment by Location filter is associated with the Equipment Inventory menu item.
See the detailed example.
- Users can select them from the Filter... drop-down list.
- They can contain static and dynamic criteria.
Filters associated with Service Manager internal reporting tools, e.g. trends, alerts, reports, and the purging of archives.
- They are components of the object and cannot be used outside this object.
- They do not appear among the filters in a grid in List mode, in other reporting tools or the purging of archives.
- They can all contain static criteria.
example Number of Late Incidents This Week trend filter
- Reports can also contain dynamic criteria. The value of each dynamic criterion is specified by the user when the report is run.
example Number of Open Incidents for a Period report filter containing both static and dynamic criteria
- Number of Open Incidents: Static criterion
- Period: Dynamic criterion specified by the user when the report is run
Example
The Active Equipment by Location filter is associated with the Equipment Inventory menu item (filter in the grid in List mode). It is defined with the following criteria:
- Two static criteria: Equipment status = Active; Equipment discard date = Empty
- One dynamic criterion: Location (Last Level)
How is the filter applied?
- A user selects the Active Equipment by Location filter.
- The user will be asked to select a location by clicking
.
- The filter will display all active equipment associated with the selected location (e.g.New York) whose status is Active and whose discard date is unspecified.
- The user can select another location by deleting the active filter.
Notes
- Rights based on the type of filter:
- Filters in a grid in List mode:
- Only user profiles authorized to edit filters can create or modify these filters.
See User Profiles > Edit rights and display rights.
- Edit rights for filters are assigned for all menus.
- Only user profiles authorized to edit filters can create or modify these filters.
- Filters associated with Service Manager internal reporting tools and the purging of archives:
- Users can create or modify these filters without any rights required.
- Filters in a grid in List mode:
- You can add fields manually to a filter's parent query irrespective of the filter type, as long as you are authorized to edit display presentations.
See User Profiles > Edit rights and display rights.
- Fields are displayed with their alias (or logical name).
- If the alias is not specified, the physical field name will appear.
example
- Alias: Phone, Physical Name: PHONE_NUMBER
- Alias: Serial Number, Physical Name: AM_ASSET.SERIAL_NUMBER
- SQL queries:
- You can define them in Advanced Mode. You can also define SQL queries for a specific field in local Advanced Mode (option available in the drop-down list of fields).
See the procedure.
- A given filter can contain conditions declared both in Visual Mode and in local Advanced Mode.
See Examples of conditions.
- You can switch from Advanced Mode to Visual Mode if the query only uses the fields available in the parent query and other manually added fields.
- The syntax of the SQL query will be checked when you save the filter.
- SQL queries can contain simple sub-queries with a count or grouping level. They can use SQL functions.
- SQL queries using dynamic fields are not taken into account.
- You can define them in Advanced Mode. You can also define SQL queries for a specific field in local Advanced Mode (option available in the drop-down list of fields).
Caution
- You can create filters to manage integration models only by using the Administration > Integration > Integration Models menu.
Best Practice
- If you want to add fields to the filter's parent query, you should do it before editing the filter.
See How to modify a parent query.
- Give a meaningful name to the filter to help users identify its selection criteria easily.
- Check that the filter conditions are correctly defined. If an exclamation mark, !, appears at the end of the row, this means that the condition was not correctly defined.
- Start defining the filter in Visual Mode. You can then switch to Advanced Mode to narrow down the selection criteria using an SQL query. It will automatically be initialized using the selection criteria defined in Visual Mode.
- Click Visibility to assign access rights to the filter for each user profile. You can also define this in the User Profiles screen.
Characteristics specific to versions 2016 and earlier
The filter editor in versions 2016 and earlier is different.
- Filters in a grid in List mode: Edit rights are assigned to each user profile in the module associated with the menu item using the filter.
See User Profiles.
Access
- Filters in a grid in List mode: In any screen in List mode, click Edit Filter
at the bottom of the Filter... drop-down list (Note: You must be authorized to edit filters).
- Filters associated with Service Manager internal reporting tools and the purging of archives, e.g. trends, alerts, reports, purge (Note: No rights required).
Screen description: Visual Mode
Characteristics of filters in Visual Mode
- Each row contains one filter condition.
- A condition is made up of a field and a value applicable to the field. It can also be expressed as an SQL query using a field's local Advanced Mode.
- You can combine multiple main conditions within a given filter. They must all be linked by the same logical operator, i.e. AND or OR.
- One condition can contain several sub-conditions. They must all be linked by the same logical operator, i.e. AND or OR.
(1) Title bar
Note: Only for filters in a grid in List mode
- Used to display the name of the filter currently being edited.
- The drop-down list displays all filters defined for the menu item.
- The checkmark indicates whether this is the default filter applied when the menu item is opened in List mode (
) or not (
).
See How to define a default filter.
- Advanced Mode/Visual Mode: Used to switch between the two modes for defining filters.
Visual Mode | Advanced Mode | |
---|---|---|
Input wizard in fields for defining filter conditions |
Definition of an SQL query |
- Visibility: Used to select user profiles authorized to use the filter in menu items.
See the procedure.
- Note: This is not related to edit rights for filters.
- Note: This is not related to edit rights for filters.
- Dynamic fields: Used to select the fields used as dynamic criteria.
See the procedure.
- The value of the dynamic field is specified by the user when the filter is applied. The options available for each field depend on the type of field.
example Dynamic field Priority ==> List of values, Empty and Not Empty options
- The value of the dynamic field is specified by the user when the filter is applied. The options available for each field depend on the type of field.
(2) Filter editor in Visual Mode
Note: For all filters irrespective of their type
- Click in the field,
to create a new condition, or click in another filter field,
to display the list of available fields.
- The list will display all fields from the parent query and other manually added fields.
- The Advanced Mode option is always found at the bottom of the list and is used to define an SQL query for the field (local Advanced Mode).
- Each field is identified by its physical name and its alias (or logical name).
- The search will be run on these two elements.
example
- Alias: Phone, Physical Name: PHONE_NUMBER
- Alias: Serial Number, Physical Name: AM_ASSET.SERIAL_NUMBER
- Filter operators, e.g. Equal to or Empty, and options, e.g. Text or Date, depend on the type of field selected.
See the procedure.
Procedures
How to create a filter in a grid in List mode
Step 1 (optional): Add the fields to the filter's parent query.
Note: Your user profile must be authorized to edit display presentations.
1. Select the menu item for which you want to create the new filter.
The screen will open in List mode.
2. Click in the title bar and select Edit.
The list of fields from the parent query associated with the menu item and other manually added fields will appear.
3. Add the fields you want. See How to modify a parent query.
Step 2: Create the new filter.
1. Select the menu item for which you want to create the new filter.
The screen will open in List mode.
2. Click Edit Filter at the bottom of the Filter... drop-down list.
- The filter editor will appear.
- The current filter will appear in Visual Mode.
3. Click + New.
- A new filter will automatically be initialized.
- The first condition will appear.
Step 3: Define the new filter.
1. Enter the filter name in the title bar. Click to enter the name in different languages.
2. Initialize the first main condition of the filter by clicking in the field called Select a field.
The list of fields from the parent query associated with the menu item and other manually added fields will appear.
3. Define the condition.
Define an SQL query for the field using local Advanced mode
- Click Advanced Mode at the bottom of the list of fields.
- Define the SQL query in the field called Enter an SQL Query.Note: The syntax will be checked when you save the filter.
Input wizard for defining a condition
- Select the field to be added to the condition.
- Specify the options for the condition, such as filter operators, e.g. Equal to or Empty, and comparison values, e.g. Text or Date.
- The options depend on the type of field.
- The comparison value can be:
Literal: An alphanumeric value.
Field: The value of a field in the database.
: A value selected from a list of values.
: A value selected from a tree structure.
4. Define the other filter conditions.
- All conditions of the same level must be linked by the same logical operator, i.e. AND or OR.
- A condition can contain sub-conditions.
- You can click X to delete a condition
AND operator ![]() |
OR operator ![]() |
|
---|---|---|
All of the conditions must be fulfilled. |
Only one of the conditions must be fulfilled. |
Step 4 (optional): Narrow down the filter using Advanced Mode.
1. Click Advanced Mode.
Note: An error message will appear if one of the conditions is not correctly defined.
All filter conditions will automatically be loaded to the SQL query.
2. Make the required modifications.
3. Click Visual Mode to return to the visual filter editor.
Note: You can switch from Advanced Mode to Visual Mode if the query only uses the fields available in the parent query and other manually added fields.
Step 5 (optional): Specify interactive fields.
1. Click Dynamic fields.
2. Click next to a field in the Field List pane to make it dynamic. (1)
The field will automatically move to the Interactive Fields pane.
3. Hover over a field in the Interactive Fields pane and click to remove this field from the dynamic fields. (2)
4. Click OK.
Step 6: Select the user profiles authorized to access the filter.
Note: You can also define this in the User Profiles screen.
1. Click Visibility.
By default, all user profiles are authorized.
2. In the Visible pane, select the user profiles not authorized to access the filter and click . (1)
The user profiles will move to the Invisible pane.
3. Select a user profile from the Invisible pane and click to authorize its access to the filter again. (2)
4. Click OK.
Step 7: Save and test the new filter.
1. Click Save.
The filter will be added to the Filter... drop-down list and will be accessible to all authorized user profiles.
2. Click Apply Filter.
- The filter editor will close.
- The filter will automatically be applied to the screen in List mode.
3. Check that the results are correctly displayed.
4. Check that only authorized user profiles can access the filter.
How to create a filter using a template
Step 1: Select the template filter.
1. Select the menu item for which you want to create the new filter.
2. Click Edit Filter at the bottom of the Filter... drop-down list.
- The filter editor will appear.
- The default filter for the menu item will appear in Visual Mode.
3. Select the template filter from the drop-down list.
The filter conditions will appear.
Step 2: Create the new filter.
1. Modify the conditions as required in the template filter. See the detailed procedure.
2. Click Save as to avoid overwriting the template filter. Give a name to the new filter.
3. Click Apply Filter.
- The filter editor will close.
- The filter will automatically be applied to the screen in List mode.
How to define a default filter for a menu item
Step 1: Select the default filter.
1. Select the menu item for which you want to define the default filter.
2. Select the filter to be applied by default to the screen.
3. Click Edit Filter at the bottom of the Filter... drop-down list.
- The filter editor will appear.
- The selected filter will appear in Visual Mode. Note: You can select another filter from the drop-down list.
4. Select the checkmark, , in the filter's title bar.
The checkmark will automatically change to .
5. Click Apply Filter.
- The filter editor will close.
- The filter will automatically be applied to the screen in List mode.
Step 2: Test that the filter is correctly applied.
1. Exit and return to the menu item.
2. Check that the filter automatically applied in List mode is the default filter you just defined.
Examples of conditions
Display service requests over the last 24 hours | ||
---|---|---|
Visual Mode | Advanced Mode | |
Zoom Service requests over the last 24 hours |
{# FILTER_DATE('SD_REQUEST.SUBMIT_DATE_UT', 'LASTHOURS', '24') #} AND SD_META_STATUS.META_STATUS_GUID IN ('{C9FB9FC4-BDDB-4D31-8F5B-9913F34EE888}') |
|
Display available CIs | ||
Visual Mode | Advanced Mode | |
Zoom Available CIs |
SELECT COUNT(CMDB_UNAVAILABILITY.UNAVAILABILITY_ID) FROM CMDB_UNAVAILABILITY WHERE (CMDB_UNAVAILABILITY.ASSET_ID = AM_ASSET.ASSET_ID) AND ( (CMDB_UNAVAILABILITY.END_DATE IS NULL) OR (CMDB_UNAVAILABILITY.END_DATE > GETUTCDATE()) ) ) <= 0 AND ( {# FILTER_DATE('AM_ASSET.REMOVED_DATE', 'AFTER', 'TODAY') #} OR AM_ASSET.REMOVED_DATE IS NULL ) |
|
Display immediate resolution tickets | ||
Visual Mode | Advanced Mode | |
Zoom Immediate resolution tickets |
( SD_REQUEST.FIRST_CALL_RESOLUTION = 1 AND SD_REQUEST.FIRST_CALL_RESOLUTION is not null ) AND SD_META_STATUS.META_STATUS_GUID IN ('{FADD193C-E50F-4AB8-BE4B-928D6E2B2CD4}') AND ( SD_REQUEST.OWNING_GROUP_ID IN (SELECT GROUP_ID FROM AM_EMPLGROUP WHERE EMPLOYEE_ID IN (@EMPLOYEE_ID@)) OR SD_REQUEST.OWNING_GROUP_ID IN (SELECT GROUP_ID FROM AM_GROUP WHERE AM_GROUP.MANAGER_ID IN (@EMPLOYEE_ID@)) ) |
|
Delete drafts and input errors of a ticket list | ||
Note: Depending on the parent query used in reports, drafts and input errors may or may not be displayed. Use the query provided here in Advanced Mode if you want to exclude their display from the parent query. | Advanced Mode (SD_REQUEST.REQUEST_ID NOT IN (SELECT SD_REQUEST.REQUEST_ID FROM SD_REQUEST INNER JOIN AM_ACTION ON SD_REQUEST.REQUEST_ID = AM_ACTION.REQUEST_ID INNER JOIN AM_ACTION_TYPE ON AM_ACTION_TYPE.ACTION_TYPE_ID = AM_ACTION.ACTION_TYPE_ID WHERE (AM_ACTION_TYPE.ACTION_TYPE_GUID = '{2502C70E-D9A6-4662-AE2E-B15DA75CA763}' OR AM_ACTION_TYPE.ACTION_TYPE_GUID = '{0C654867-7C28-43A5-AF3C-C797220A8D27}' OR AM_ACTION_TYPE.ACTION_TYPE_GUID = '{E753DA6E-BE59-4619-87E4-646A1F0FD021}') AND AM_ACTION.END_DATE_UT IS NULL)) |