Filters

Last modified on 2022/12/21 18:14

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.Open url.png See the detailed example.

  • Users can select them from the Filter... drop-down list.
        Filter via list.png
  • 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 Tree open icon.png.
             Dynamic fields - Example.png
  • 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.
             Dynamic fields - Example result.png
  • The user can select another location by deleting the active filter.
              Dynamic fields - Example refresh.png

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. Open url.png See User Profiles > Edit rights and display rights.
      • Edit rights for filters are assigned for all menus.
    • Filters associated with Service Manager internal reporting tools and the purging of archives:
      • Users can create or modify these filters without any rights required.
  • 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. Open url.png 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). Open url.png See the procedure.
    • A given filter can contain conditions declared both in Visual Mode and in local Advanced Mode. Open url.png 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.

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. Open url.png 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. Open url.png See User Profiles.

Access

  •  Filters in a grid in List mode: In any screen in List mode, click Edit Filter Edit icon.png at the bottom of the Filter... drop-down list (Note: You must be authorized to edit filters).
             Edit rights - Filter.png
  •  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.

    Open url.png See Examples of conditions.

        Filter editor.png

(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 (Default filter icon - On.png) or not (Default filter icon - Off.png). Open url.png 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

Filter editor - Visual mode.png

Definition of an SQL query

Filter editor - Advanced mode.png

  • Visibility: Used to select user profiles authorized to use the filter in menu items. Open url.png See the procedure.
    • Note: This is not related to edit rights for filters.
       
  • Dynamic fields: Used to select the fields used as dynamic criteria. Open url.png 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

      Dynamic fields - Example options.png

(2) Filter editor in Visual Mode
Note: For all filters irrespective of their type

  • Click in the field, Select new field area.png to create a new condition, or click in another filter field, Select existing field area.png to display the list of available fields.
             New filter procedure - List fields.png
    • 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. Open url.png See the procedure.
        Select new field area - Contextual options.png

Procedures

How to create a filter in a grid in List mode

Note: Your user profile must be authorized to edit filters. Open url.png See User Profiles > Edit rights and display rights.

Caution: You can create filters to manage integration models only by using the Administration > Integration > Integration Models menu.

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 Tools icon.png 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. Open url.png See How to modify a parent query.

Step 2: Create the new filter.

Best Practice icon.png  You can initialize the new filter using a template filter.Open url.png See the procedure.

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 Edit icon.png at the bottom of the Filter... drop-down list.
         Edit rights - Filter.png

  • 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.

         New filter procedure - Initialization.png


Step 3: Define the new filter.

1. Enter the filter name in the title bar. Click Multilingual labels icon.png 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.
         New filter procedure - Select field.png
The list of fields from the parent query associated with the menu item and other manually added fields will appear.
         New filter procedure - List fields.png

3. Define the condition.

Caution: An exclamation mark, !, appears at the end of the row if the condition was not correctly defined.

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.
             New filter procedure - Enter SQL query.png
     

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:
      • New filter procedure - Comparison option field - Literal icon.png Literal: An alphanumeric value.
      • New filter procedure - Comparison option field - Database field icon.png Field: The value of a field in the database.
                 New filter procedure - Comparison option field - Literal or field value.png
      • List open icon.png: A value selected from a list of values.
                 New filter procedure - Comparison option field - List of values.png
      • Tree open icon.png: A value selected from a tree structure.
                 New filter procedure - Comparison option field - Tree structure.png

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
             New filter procedure - Delete condition.png
     
AND operator   AND condition icon.png OR operator   OR condition icon.png

All of the conditions must be fulfilled.

New filter procedure - AND condition.png

Only one of the conditions must be fulfilled.

New filter procedure - OR condition.png

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 Plus icon.png 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 Dynamic fields - Deselected icon - On.png to remove this field from the dynamic fields. (2)

4. Click OK.

         Dynamic fields - Selection.png

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 Next icon.png. (1)
The user profiles will move to the Invisible pane.

3. Select a user profile from the Invisible pane and click Previous icon.png to authorize its access to the filter again. (2)

4. Click OK.
         Profiles - Selection.png

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

Note: Only for filters in a grid in List mode

Step 1: Select the template filter.

1. Select the menu item for which you want to create the new filter.

2. Click Edit Filter Edit icon.png 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. Open url.png 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

Note: Only for filters in a grid in List mode

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 Edit icon.png 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, Default filter icon - Off.png, in the filter's title bar.
The checkmark will automatically change to Default filter icon - On.png.
    Default filter - Information.png

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

Example conditions visual mode - Example 1.png

Zoom

{# 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

Example conditions visual mode - Example 2.png

Zoom

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

Example conditions visual mode - Example 3.png

Zoom

( 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))
Tags:
Powered by XWiki © EasyVista 2022