Filters


Note: This page describes how to edit autonomous filters but the description of criteria, their conditions and the data entry mode is identical than the one for filters attached to an object.

Filters allow to extract a subset of records answering selection criteria.

Each filter:

  • Is built on a parent query, that allows to work on a group of homogeneous information of the databases;
  • Can be elaborated in two modes:
    • Assisted mode: Conditions will be applyed to the fields of the parent query and possible additional fields added manually. According to the field type, an assistance for the completion of the conditions can be available (eg.: delimiters for dates, value lists, tree structures);
    • Advanced mode: The filter is completed as an SQL query which can call any field of the data model; no assistance for the completion.
  • Can be attached to:
    • a menu option. The filter operates in autonomous and represents a functionality that is part of Product name - ev itsm.png. It can be static (the criteria cannot be changed), interactive (the user is invited to select the criteria values himself), or both at once.

      Example documentation icon EN.png  The filter Equipment in Service by Location available in the equipment inventory

    • a tool reporting (trend, alert, reporting). The filter operates through this object and forms integral part of it. It delimits the perimeter of the object and it is always static.

      Example documentation icon EN.png  Filter defined for the trend Number of Incidents late this Week

Example

The interactive filter Equipment in service by Location is attached to the menu option Equipment Inventory. The following selection criteria are defined:

  • Static criteria: Equipment = In Service  ;  Discard Date of the equipment = Empty
  • Dynamic criteria: Location

In consultation, the user is invited to enter a location (eg.: France): 

  • The filter displays all equipment in service attached to this location.
  • Once the consultation finished, the user can enter another location.

Notes

  • Only a profile owning edit rights to parameter filters to the module to which is attached the related option of menu using the filter may change this one.
  • For autonomous filters (attached to a menu option), the access rights are defined in the profile.
  • You may add other fields manually to the parent query of the filter (under the condition to own the edit rights for the displays of the module to which it is attached).
    • Do this operation before editing the filter.
    • Fields added manually appear under their alias name (eg.: Phone); if the alias is empty, the field name in the database is displayed (eg.: PHONE_NUMBER).
  • SQL query defined in advanced mode:
    • is checked when the filter is saved;
    • needs to include 2 opening parenthesis and 2 closing parenthesis: ((SQL instructions));
    • can imbricate simple queries with count or regroup functions; 
    • can use SQL functions;
    • if the query relates to fields that are not available in the parent query and fields added manually, it is not possible any more to switch into the assisted mode.
  • If you define a field as dynamic, the condition entered in the filter is not taken into account.

Best Practice big icon.pngBest practice

  • Give an explicit name to the filter so that you can easily find out which criteria is applied using the filter.
  • In order to display only the criteria already entered, use the assisted mode.
  • If you use a filter with a few criteria, use the assisted mode to select the fields to filter in the list of available fields of the parent query.
  • Start building the filter in assisted mode and switch to advanced mode to fine tune the selection criteria using the SQL query.
  • To define the access rights to filters by profile, use the fast access by clicking on Password close icon.png.

Screen description

      Filter.png

Menu access: on any screen EasyVista, in List mode, Edit icon.png on the right side of the drop down-list Filter

Existing Filter: List of all filters defined for the menu option. To display a filter, select it in the drop down-list and click on [ EDIT EXISTING FILTER ].

Filter Name: Description of the filter. Click on Password close icon.png to define the access rights by profile.

Default Filter: Indicates if the filter is the one displayed by default when a menu option is selected (box is checked - eg.: Displaying the filter None when the option Equipment Inventory is selected) or not (box is not checked).

Fields: List of fields related to the parent query attached to the filter, and possible additional fields added manually.

Interactive: Indicates if the conditions related to the field are dynamic and will be entered by the user when he uses the filter (box is checked - in this case, the conditions entered into the filter will not be taken into account) or if the conditions are static (box is not checked - enter them into the field Conditions).

Conditions (Note: Only displayed in assisted mode and only if the field is static): All conditions applied to each field, stored as SQL queries and interpreted when the user consults the filter. Switch into the advanced mode to enter an SQL query directly without assistance for the data entry. A condition can present:

  • A precise value;
  • A date, entered through the use of a drop down-list proposing a group of predefined options, among which:
Predefined Option The selected records are those having the date
For N Days Between N days ago and the current date (inclusive)
N Days ago Equal to the date N days ago
In N days Equal to the date in N days
Since Today After or equal to the current date
After Today After the current date (not inclusive)
Until Today Before or equal to the current date
Before Today Before the current date (not inclusive)
Last Week From Monday to Sunday of last week
  • A value from a tree structure: click on Browse icon.png; select the value None to cancel a condition that was previously entered;
  • One or more values from a reference table: click on Browse icon.png; 2 possible conditions: [ EQUAL TO ] and [ DIFFERENT FROM ]. Note: Not available if there are more than 30 values in the table; in this case, enter the list of values to include or to exclude of the search as SQL query;
  • A search criteria for a field with 2 values:
    • True: Selection of records responding to the condition only;
    • False: Selection of records not responding to the condition;
    • Indifferent: The field is not taken into account (neither True, nor False).
  • A search criteria for the existence of a value for the field:
    • Checkbox Empty checked: Only the selection of records, without any value for the field;
    • Checkbox Not Empty checked: Only the selection of records, with a value for the field, whatever the value is.
  • An SQL query using the operators and reserved words:
    • Operators availables: =; <; <=; >; >=;
    • IN: Values to search for using the exact labels;
    • LIKE: Values to search for using a part of the label; The substitution character % allows to replace any character in the label;
    • NOT: Values to exclude from the search.

[ Assisted Mode ]: Allows to define conditions on all the fields of the parent query and possible additional fields added manually. 

  • Full mode: All the fields will be displayed.
  • Simple mode: Only the fields for which a condition is defined will be displayed; click on Plus icon.png to add a new field and on Minus icon.png to delete on of these.

[ Advanced Mode ]: Allows to define an SQL query that can used all the fields of the data model. Under the SQL query, the fields of the parent query (and possible additional fields added manually) that can be Interactive will be displayed.

Procedures

How to create a filter and to control its operation

1. In List mode of any EasyVista application page, click on Edit icon.png on the right side of the drop down-list Filter and on [ NEW ].

2. If you want to add other fields to the parent query :

  • close the Filter screen;
  • click on Tools icon.png then on Edit icon.png [ EDIT ] option  (Note: You need to own a profile with the display rights to the module);
  • add the desired fields (Refer to the procedure How to change a parent query) ;
  • return on the Filter screen.

3. Enter the filter parameters and save the changes by clicking on [ SAVE ].

  • In full assisted mode, enter the conditions for all the fields to include in the filter.
  • In simple assisted mode:
    • to add a condition to the filter, click on Plus icon.png; select the field on which the new condition will be applied, then click on [ ADD FIELD ]; enter the condition for this field and specify if it is an interactive field;
    • to delete one of the conditions of the filter, click on Minus icon.png.
  • In advanced mode, enter the SQL query and specify the interactive fields.
  • Select the profiles that need access to the filter by clicking on Password close icon.png.

4. Check the correct operation of the filter:

  • Click on [ FINISH ];
  • Select the new filter in the drop down-list Filter;
  • Check that the expected results are displayed.

How to create a filter from a model

1. Proceed to the desired screen, in List mode, then click on Edit icon.png on the right side of the drop down-list Filter.

2. In the list Existing Filter, select the model and click on [ EDIT EXISTING FILTER ].

3. Enter the name of the new filter and change the desired parameters.

4. Click on [ SAVE AS ] to avoid overwriting the model.

Examples for conditions

Search all the PC owning a disk drive with a capacity inferior of 4 MB:

  • In assisted mode: Field Size Drive; value < 4000
  • In advanced mode:
    (( upper(am_hdd_total.data_1) < upper('4000') ))  

Search all the PC with and end of warranty date of today:

  • In assisted mode: drop down-list of the field End of Warranty: click on Equal to Today
  • In advanced mode:
    ( {# filter_date ('am_asset.end_of_waranty', 'EQUAL', 'TODAY') #} )

Search all equipment located in London:

  • In assisted mode: Browse icon.png on the field Location (Last Level); Click on Europe/United Kingdom/London
  • In advanced mode:
    ( {# treewhere('am_location', 'location_id', '93', 'am_location') #} )

Search all equipment with a status Out of order or In repair:

  • In assisted mode: Browse icon.png on the field Equipment Status; Click on [ EQUAL TO ]; Checkboxes checked: Out of order, In repair 
  • Translation in assisted mode: IN (Out of order ', In repair)
  • In advanced mode:
    ( am_status.status_guid IN ('{08E4ABD4-ABDF-4284-A103-0396FCE64F93}','{41BDD986-AA3B-4673-BDFB-063BF4259B8D}') )

Search all the PC not owning a warranty on site:

  • In assisted mode: Browse icon.png on the field Warranty Type; Click on [ DIFFERENT FROM ]; Checkbox checked: On Site
  • Translation in assisted mode: NOT IN (On site')
  • In advanced mode:
    (( v_warranty_type.reference_guid NOT IN ('{2C5FCD40-B735-4577-8DDD-1716714E97F0}') ))

Search storage locations only:

  • In assisted mode: Field Stock; value True
  • In advanced mode:
    (( am_location.is_delivery_address = 1 ))  

Search all locations, storage location or not:

  • In assisted mode: Field Stock; value Indifferent
  • In advanced mode: Nothing, since the criteria is not taken into account

Search all equipment which has not been discarded from the installed active Assets:

  • In assisted mode: Field Discard Date; Checkbox checked: Empty
  • In advanced mode:
    (( am_asset.removed_date IS NULL ))  

Search all equipment where no intervention is scheduled:

  • In assisted mode: Field Next scheduled Intervention; Checkbox checked: Not Empty
  • In advanced mode:
    (( am_asset.next_maintenance_date IS NOT NULL ))

Search all the main users where the name starts with ALB and finishes with any characters (eg.: ALBERS, ALBINONI):

  • In assisted mode: Field Main User; value LIKE 'ALB%'
  • In advanced mode:
    (( upper(am_employee.last_name) LIKE upper('ALB%') ))

Search all the main users where the name starts with any characters and then contains DURA (eg.: GARDURA, PODURA):

  • In assisted mode: Field Main User; value LIKE '%DURA'
  • In advanced mode:
    (( upper(am_employee.last_name) LIKE upper('%DURA') ))

Search all the main users where the name starts with any characters and then contains LEG and finishes with any characters (eg.: ALLEGRI, SHEELEGER):

  • In assisted mode: Field Main User; value LIKE '%LEG%'
  • In advanced mode:
    ((upper(am_employee.last_name) LIKE upper('%LEG%') )

Define the filter In Service attached to the menu option Equipment Inventory:

  • In advanced mode:
    (( ((am_asset.removed_date IS NULL) OR (am_asset.removed_date > getutcdate())) AND ((am_status.status_id IN (8)) AND (am_status.status_id IS NOT NULL)) ))

Define the filter Re-qualified Incidents (incidents where the subject has been changed):

  • In advanced mode:
    sd_request.request_id IN
    (
              SELECT     request_id
              FROM       sd_request
              INNER JOIN am_history
              ON         sd_request.request_id = am_history.id
              WHERE      am_history.history_param_id = 52 )
    GROUP BY request_id HAVING count (request_id) > 1)
Tags:
Last modified by Unknown User on 2018/09/21 11:14
Created by Administrator XWiki on 2013/03/25 18:12

Shortcuts

Recent Updates

Haven't been here in a while? Here's what changed recently:

-   Product name - ev itsm.png
-   Product name - ev sas.png

Interesting Content

How to Automate Integration
Add a Shortcut to an App
History
Quick Dashboard
Full text search - Stop Words

Powered by XWiki ©, EasyVista 2018