Parent Queries

Last modified on 2022/05/28 10:54

Definition

A parent query defines a set of standardized data retrieved from a database for working on a given theme.

EndDefinition
  • It is used to help build different Service Manager objects by providing access to a list of fields from SQL views and tables.
  • You can add other fields manually to parent queries as long as you have the appropriate rights.

Scope of the parent query

  • A parent query is defined using a data model table which will be the main table of the query.
  • The scope of this main table can be extended to include SQL views and tables joined to the main table.
    By extension, it can also include tables joined to these joined tables.
  • All of the fields in SQL views and tables within the scope can be made available in the parent query.
  • You can add other fields manually:
    • Fields belonging to a parent query table or join.
    • Fields referenced in a table joined to one of the parent query tables. 
    • Fields referenced in an SQL view containing a join with one of the parent query tables. 

(1) Select the main table, SD_REQUEST. All table fields will be available in the parent query.

(2) Add the joined table, SD_URGENCY. The key field that establishes the join between the tables is the field called URGENCY_ID.

(3) Add the SD_SLA table that is joined to SD_URGENCY. The key field that establishes the join between the two tables is the field called SLA_ID.

==> The parent query can include all fields from the main table, SD_REQUEST, as well as all fields from the joined tables, SD_URGENCY and SD_SLA.

Parent query - Main table and related tables.png

Type of parent queries

Parent queries used to define standard Service Manager objects, e.g. forms, lists of values, filters, quick filters, views, wizards.

  • They are associated with menu items and are defined by EasyVista.
  • All menu items associated with a given parent query can use the same fields defined for the query.
  • You can add other fields manually to the parent query of a menu item (Note: Except for wizards).
    • These fields will be available only for this menu item.
    • They will not be available for other menu items or for Service Manager internal reporting tools.

Parent queries used to define Service Manager internal reporting tools, e.g. trends, alerts, reports.

  • Internal reporting tools can use all parent queries used to define standard Service Manager objects.
  • They can also use parent queries specific to reporting tools.
  • You can add other fields manually to the parent query of a reporting tool.
    • These fields will be available for all reporting tools.
    • They will not be available for standard Service Manager objects.

Parent queries used to define purges

  • Purge can only use a specific list of parent queries used to define certain Service Manager objects, e.g. contracts, CIs, requests, incidents, licenses, etc.
  • You can add other fields manually to the parent query of a purge.
    • These fields will be available for all purges and for all Service Manager internal reporting tools.
    • They will not be available for standard Service Manager objects.

Examples

  • Parent queries used to define standard Service Manager objects
    • The Incidents parent query is associated with the Incident form, with the Incidents Not Solved This Week filter and with the List of Incidents view.
    • These three objects can use all fields available in the scope of the parent query.
    • The Recipient VIP field is manually added to the parent query so that it can be displayed in the filter.
  • Parent queries used to define trends
    • Number of Incidents created this Month trend built using the Incident parent query
    • The Incident Number Related to the Problem field is added to the query.
      • It will be available only for reporting tools that use this query.
      • It will not appear for objects associated with a menu item using the Incident parent query.

Notes

  • Rights based on the type of parent query:
    • Parent queries used to define standard Service Manager objects, e.g. forms, lists of values, filters, quick filters, views, wizards:
      • Each object is associated with a parent query. You cannot select another one.
      • Parent queries are provided by EasyVista. You cannot add new ones.
      • Their scope is defined by EasyVista
      • You can add joined tables, SQL views and other fields manually (Note: Except for wizards), as long as you have the appropriate rights. Open url.png See User Profiles > Edit rights and display rights.
         
    • Parent queries used to define Service Manager internal reporting tools, e.g. trends, alerts, reports:
      • The parent query is selected from the queries used to define standard Service Manager objects and those defined in reporting tools.
      • You can only create a query without any specific user rights when creating a reporting tool.
      • You can modify a query as long as you have the appropriate rights, and only in each reporting tool. Open url.png See User Profiles > Edit rights and display rights.
      • The parent queries you create or modify will only be available in reporting tools.
         
    • Parent queries used to define purges:
      • Each purge is associated with a parent query selected from a list defined by EasyVista.
      • The queries are limited to the following main tables: Contracts, CIs, Incidents, Service Requests, History (Discovery), Equipment (Discovery), Employees, Events, Licenses, Equipment (Asset Management), Knowledge Base search results, Equipment Life Cycle.
      • You can modify a query as long as you have the appropriate rights, and only in each purge. Open url.png See User Profiles > Edit rights and display rights.
         
  • You can create a parent query only using a data model table which will be the main table of the query.
    • It cannot be created using an SQL view.
    • Tables whose name starts with V_ may appear in the list of data model tables. These are SQL views that were converted into tables but that kept their original view name.
              SQL view names.png

      example  

      • V_PRIORITY, V_SD_CATALOG tables: The name of the SQL view has been kept.
      • T_V_PM_TASK table: The name starts with T_V, to avoid any confusion with SQL views.
  • Fields included in the original scope of the parent query cannot be deleted.
  • Only fields manually added to the parent query can be deleted by clicking Delete icon.png.

Caution

  • Do not confuse SQL views (used as virtual tables for adding fields to parent queries) with Service Manager views.

Best Practice

  • When you join a main table with a joined table or SQL view, you can specify which table rows you want to display using OUTER or INNER.
    • Keep the default value, OUTER to display all rows in the main table, even if there are no matches with the joined table or SQL view. 
    • If you select INNER, this will return only records from the main table that have a match in the joined table. Rows with no matches in the joined table or SQL view will be hidden. This may lead users to think that these records have been lost.

Parent query - Main table and related tables - Inner Outer options.png

Inner: This will return only records from the main table that have a match in the joined table (intersection of the two tables).

Outer: This will return all records from the main table, even if there are no matches with the joined table.

  • Manually added fields must comply with the rules below to ensure that they will not be affected by a version upgrade.
    • Type of field:
      • It must be one of the following types: nvarchar, nvarchar(max), float, int, datetime, uniqueidentifier, bit, real, smallint
    • Field name:
      • It must start with E_.
      • It must be in the upper case.
      • It must not contain accents.
      • It must not contain spaces. If you want to insert a space, you must use the underscore, _.
      • It must not exceed 31 characters (Report Designer constraint).

example  E_DATE_BIRTH

Characteristics specific to versions 2016 and earlier

The parent queries in versions 2016 and earlier are different.

  • To create or modify parent queries, your user profile must be authorized to edit display presentations. This is assigned in the module associated with the menu item using the parent query. Open url.png See User Profiles.

Access

  • Parent queries used to define standard Service Manager objects (Note: Except for wizards): In List mode, Tools icon.png in the title bar > Edit (Note: You must be authorized to edit display presentations)
  • Parent queries used to define Service Manager internal reporting tools and purges: Via the object's form > Parent Query field

Screen description

       Parent query.png

Name: List of fields defined in one of the tables included in the parent query, joins with SQL views and manually added joined tables.

  • Click Multilingual labels icon.png to enter the alias in different languages.

Field Name (Note: Non-modifiable): Physical name of the field in the data model.

Available for Quick Filters (Note: Not available for fields generated by calculations): Used to indicate whether the field can be used as a quick filter criterion (by clicking Quick filter icon.png in List mode) (box is checked) or not (box is not checked).

Default Field (Quick Filters): Used to indicate whether the field is proposed by default as a quick filter criterion (radio button is selected) or not (radio button is not selected).

AddViewTableField

Add View: Used to link a field from an SQL view with a field in a table joined to the parent query. Open url.png See How to modify a parent query.

  • You will be able to select the SQL view just like any other table in the parent query.
  • You can select the type of rows to be returned (Note: Depending on the parent query).
    • OUTER option: This will return all records from the main table, even if there are no matches with the SQL view.
    • INNER option: This will return only records from the main table that have a match in the joined table. Rows with no matches in the SQL view will be hidden.
  • You can give the SQL view an alias when saving.
     

Add Table: Used to join a table in the parent query with a joined table in the data model. Open url.png See How to modify a parent query.

  • You will be able to select the joined table just like any other table in the parent query. 
  • For each joined table, the list of joins in the data model will appear as follows: joined table key field / parent query table key field.
  • You can select the type of rows to be returned (Note: Depending on the parent query).
    • OUTER option: This will return all records from the main table, even if there are no matches with the joined table.
    • INNER option: This will return only records from the main table that have a match in the joined table. Rows with no matches in the joined table will be hidden.
  • You can give the joined table an alias when saving.
     

Add Field: Used to add a field to those in the parent query tables, manually added joined tables or SQL views. Open url.png See How to modify a parent query.

  • The field will appear with its alias (or logical name) at the bottom of the list of fields in the parent query.
  • It is preceded by the Delete icon.png icon that enables you to delete the field from the query.
  • 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
EndAddViewTableField

Procedures

How to create a parent query

   Note

  • You can only create a parent query for a Service Manager internal reporting tool.
  • You can create it only when you create a new form for this tool.
     

Step 1: Create the new parent query.

1. Go to one of the Service Manager internal reporting tools.

  • Trends: Administration > Trends > Trends
  • Reports: Administration > Reports > Reports
  • Alerts: Administration > Alerts > Scheduled Alerts

2. Click Add icon.png to create a new form.

3. Click Add icon.png next to the Parent Query field.

  • All tables in the data model will appear.
  • Note: Tables whose name starts with V_ may appear in the list of data model tables. These are SQL views that were converted into tables but that kept their original view name.

Step 2: Create the new parent query.

1. Enter the name of the parent query.

2. Select the main table from the data model tables.
Note: You can create a parent query only using a data model table which will be the main table of the query. It cannot be created using an SQL view.

3. Click Add Field to make the relevant fields available in the parent query. Open url.png See How to modify a parent query.

  • You can select the fields in tables joined to the main table as well as tables joined to these joined tables. You must first add these tables to the scope of the parent query by clicking Add Table. Open url.png See the example in Scope of the parent query.
  • You can select fields from SQL views. You must first add these views to the scope of the parent query by clicking Add View.

4. Click Save.

  • The new parent query will be created. It can be used by all reporting tools.
  • You will return to the form of the reporting tool. The query will be available in the Parent Query drop-down list.

How to modify a parent query

Step 1: Open the parent query.

Parent queries used to define standard Service Manager objects, e.g. forms, lists of values, filters, quick filters, views.

   Note: Your user profile must be authorized to edit display presentations.

1. Go to the menu item that uses the parent query to be modified.
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.

Parent queries used to define Service Manager internal reporting tools, e.g. trends, alerts, reports, or purges

1. Select the menu item of the object.

2. Click Edit icon.png next to the form.
The list of fields from the parent query associated with the object's form and other manually added fields will appear.

Step 2: Specify the information for quick filters and mobile display.

1. In the Available for Quick Filters column, select the fields to be used as criteria in quick filters, and indicate the default field in the Default Field (Quick Filters) column.

2. In the Mobile Display column, select the fields to be displayed on smartphone screens (Note: Depending on the selected menu item, this column may or may not appear).

Step 3 (optional): Add a joined table or SQL view to the parent query.

Note: You should perform this step if you want to add fields that do not belong to a parent query table or join. Users will then be able to select the joined table or SQL view just like any other table in the parent query. 

Add a table joined to one of the parent query tables

1. Click Add Table.
The list of tables joined to each parent query table will appear.

  • Click Plus black icon.png next to the joined table containing the field you want to add to the parent query.
    The list of tables joined to this table will appear.
  • Select the joined table containing the field you want to add.
  • Go to the bottom of the screen and select the type of rows to be returned using INNER or OUTER.
             Parent query - Add table.png

2. (optional) Replace the default alias with a meaningful alias to help users identify the new join easily.

  • You will return to the list of available fields.
  • You can now click Add Field to select any field in the joined table. See step 4.

Add an SQL view

1. Click Add View.

  • Select the relevant SQL view.
  • Select the parent query table to which it will be joined.
             Parent query - Add view.png

2. Click Join.

  • Select the fields that have matches in the SQL view and the table.
  • Select the type of rows to be returned using INNER or OUTER.
             Parent query - Add view - Join.png

3. Click Create Join.

4. (optional) Replace the default alias with a meaningful alias to help users identify the new join easily.

  • You will return to the list of available fields.
  • You can now click Add Field to select any field in the SQL view. See step 4.

 

Step 4: Add the fields to the parent query.

1. Click Add Field.
The list of all tables and joins will appear.
         Parent query - Add field.png

2. Click Plus black icon.png next to the table or join containing the field you want to add.

3. Select the field you want to add to the parent query.

  • You will return to the list of available fields.
  • The field will be added to the bottom of the list. It is preceded by the Delete icon.png icon that enables you to delete the field from the query.
  • Note: Fields included in the original scope of the parent query cannot be deleted.

 

Step 5: Save the parent query.

1. Click Save.

Manually added fields will be available in the context for which they were added. This can be in standard Service Manager objects, in Service Manager internal reporting tools or purges.

How to retrieve parent query joins

1. In the CONFIG schema, run the query used to retrieve the contents of the XML field in the C_QUERY table.

2. Right-click the results window and select Save result as.

3. Save the result in Text format.

example  Query used to retrieve parent query joins, HD_ACTIONS (QUERY_ID = 49)

SELECT [query_id],[name_fr],[xml]
FROM [EVO_CONFIG40000].[EVO_CONFIG40000].[c_query]
WHERE query_id = 49  

Result:
Parent query - Example relations.png

Use case: Display the employee language in the List of Employees view

  • The List of Employees view uses the Employees parent query.
  • By default, the AM_LANGUAGE table is not present in the list of tables for this parent query.
  • To display the employee language in the List of Employees view, you must:
    • Create a join between the AM_LANGUAGE table and the AM_EMPLOYEE table (key field Language ID)
    • Add the field containing the multilingual labels of the employee language to the parent query, Employees
    • Add the field to the view

   Note

  • Your user profile must be authorized to edit display presentations in order to modify the parent query.
  • Your user profile must be authorized to edit views in order to modify the List of Employees view.
     

1. Select Asset Management > Directory > Employees.
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 Employees parent query and other manually added fields will appear.

3. Add the AM_LANGUAGE table to the parent query.

  • Click Add Table.
  • Click Plus black icon.png next to the AM_EMPLOYEE table.
    The list of tables joined to the AM_EMPLOYEE table will appear.
  • Select the join, AM_LANGUAGE ===> AM_LANGUAGE.LANGUAGE_ID = AM_EMPLOYEE.LANGUAGE_ID.
  • (optional) Replace the default alias with a meaningful alias to help users identify the new table easily.
  • Click OK.
    The AM_LANGUAGE table will become available in the Employees parent query.

4. Add the field containing the multilingual labels of the employee language.

  • Click Add Field.
  • Click Plus black icon.png next to the AM_LANGUAGE table.
    The list of fields available in the AM_LANGUAGE table will appear.
  • Select the field called LANGUAGE_SYSTEM_ALIAS_$lng.
    The field will be added to the bottom of the list of fields in the parent query.
  • (optional) Rename the alias of the field (by default, its physical name).
  • Click Save.
    The field will become available in the Employees parent query.

5. Add the language to the List of Employees view.

  • Click Edit icon.png to the right of the View drop-down list.
    The view editor will appear.
  • Add the field called LANGUAGE_SYSTEM_ALIAS_$lng to the view.
  • Click Save.
    The employee language will appear in the List of Employees view.
Tags:
Powered by XWiki © EasyVista 2022