Definition

EndDefinition

• It is used to help build different Service Manager objects by providing access to a list of fields from SQL views and tables.
  • Define standard objects: forms, lists of values, filters, quick filters, views, wizards.
  • Define Service Manager internal reporting tools: trends, alerts, reports.
  • Define purges.
• 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. 

Click to see an example

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

    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 .

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.

Click to see an example

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

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

• Parent queries are also used to define global search criteria.

• Graphic interface

Access

• Parent queries used to define standard Service Manager objects (Note: Except for wizards): In List mode,  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

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

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  to create a new form.

3. Click  next to the Parent Query field.

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

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.

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

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

Add an SQL view

Step 4: Add the fields to the parent query.

1. Click Add Field.
The list of all tables and joins will appear.
         

2. Click  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.

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.

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