Using Connected Query Manager
Connected Query Manager enables a report developer to create a new connected query, edit an existing connected query, copy existing connected queries, and delete a connected query object, as well as run connected queries in three modes:
Preview XML
Run to window
Schedule
The queries you defined in a connected query must exist as PeopleSoft queries in the database. In addition, you cannot specify a child query in a connected query that is defined in a union.
Connected Query Manager supports XML output format and user-defined output format (XFORM, which triggers a transformation XSLT). Connected Query structure consists of a set of PeopleSoft queries that are linked by related fields. No limits exist for the number of fields being linked between two queries. arent and child pairs of queries can be linked together using sets of correlated fields, though those linkages are not required.
Each query being selected in a connected query structure has a set of image icons, which enables you to perform various actions:
Field or Control |
Definition |
---|---|
![]() |
Click the Insert Child Query icon to insert a new query as a child of the selected query. |
![]() |
Click the Insert Sibling Query icon to insert a new query as a sibling of the selected query. |
![]() |
Click the Delete Query icon to delete a selected query. |
![]() |
Click the Select Related Field icon to open a related field selection section. |
![]() |
Click the Open Query icon to open a selected query in Query Manager in read-only mode. |
Note: While deciding which tool to use for reporting purposes, report developer should consider using a single query with joins and unions first. Using Connected Query could affect the performance of the reporting application.
The main contributing factor to the Connected Query performance is the number of nested levels. Application developer should try to minimize the depth of nesting in a Connected Query structure. Another contributing factor is the number of rows being returned from each query. To achieve a better performance, queries that are positioned higher in a Connected Query hierarchy should be limited in the number of rows being returned. This limitation could be achieved by means of using filtering in parent queries.
Use the Connected Query Manager page (PSCONQRSBUILDER) to create and maintain connected queries.
Select
Select the Add a New Value tab.
Alternatively, click the Edit link.
Image: Connected Query Manager page
This example illustrates the fields and controls on the Connected Query Manager page. Definitions for the fields and controls appear following the example.
![Connected Query Manager page](img/i7cb09f96n-7f9c.png)
Field or Control |
Definition |
---|---|
Connected Query |
Enter a name for the connected query. |
Transformations |
Click to access the Connected Query Transformation page, where you can view, add, or modify the list of transformation program XSLTs that are defined for the connected query. |
Public |
Indicate the query ownership. Select to indicate that this is a public query. Clear this check box to indicate that this is a private query. Note: Public connected queries can be run by any user with adequate permissions. Private connected queries can be run and edited only by the owner. |
Status |
Select the status of the connected query. Values are:
|
Properties |
The Properties button is active only after you select a parent query. Click to access the Connected Query Properties page. |
Description |
Enter a description for the connected query. |
Comments |
Enter comments for the connected query. |
Parent Query Selection |
Click the search icon to select a parent query from an existing list. Note: You must use the search icon to search for and select a parent query. After you select a parent query, all buttons become active and the Connected Query Structure section appears with active icons, which enables you to continue building a connected query. |
View Query |
Click to open the selected query in read-only mode in the Query Manager component. Query Manager opened from Connected Query is fully functional but does not allow you to save any changes. |
Connected Query Structure |
This section appears after you select a parent query. It displays the image icons that you can use to further develop the Connected Query structure. See the Understanding Connected Query Manager section earlier in this topic for a description of the icons. Note: If parent and child queries are not linked with some fields, each row of data for a parent query will have all rows from a child query as no filtering from a parent to child will happen. For example, Project is a parent query and Project Description is a child query. The Project and Project Description queries are linked by the Project_ID field that will assume one-to-one relationship (assuming no EFFDT field exists in a second query). Therefore, if the Project query returns 100 rows, the Project Description query will return a single row for the Project query, and resulted data set will have 100 rows of data. If no field is selected as a mapped field (Project_ID field), Connected Query returns 100 x 100 = 10000 rows. Sometimes mapped fields are not needed, in which case the system displays a warning message when you save the connected query (Connected Query design time). |
Use 'Order By' processing |
This option is only available when the SupportsOrderBy option in the Connected Query Properties page is set to True. Select to apply the OrderBy processing option at runtime. Note: This option is always selected for the first query and you cannot modify it. For the following queries, this option becomes active when you click the query name in the Connected Query Structure column. The OrderBy processing option is not used if the connected query is running in the Preview XML mode. When you reopen the Connected Query Property page and set the SupportsOrderBy property to False, the Use OrderBy Processing column is not available in the Connected Query Manager page. If you later set the SupportsOrderBy property back to True, the system will display the processing property that you previously selected. See the Using the OrderBy Processing Option section in Using Connected Query Manager. |
Preview XML |
This button becomes active after you select a parent query. Click this button at any time to preview the XML report in a separate window. |
Save |
This button becomes active after you select a parent query. Click this button to save your connected query. You can save the connected query as soon as you have added one parent query. |
Steps Used to Create a Connected Query
To create a connected query using Connected Query Manager:
Select
Select the Add a New Value tab.
The Connected Query Manager page appears.
Enter a name in the Connected Query field.
The connected query name must be unique to all private and public queries.
Define query ownership using the Public check box.
By default, the Public check box is selected to indicate that this connected query is a public connected query. You can clear the Public check box if you want this connected query to be private .
Select the status of the connected query using the Status drop-down list.
Note: The Inactive status indicates that a connected query does not pass a validation routine. When a connected query is not valid, this status is set automatically during the Save process.
Select the Active option to enable the user to schedule a connected query.
Select the In Progress option to indicate that this query can be previewed in Connected Query Manager, but can not be scheduled.
The In Progress status is useful when a connected query is not ready for end user to view using the Connected Query Viewer page.
Optionally, enter description and comments for the connected query using the Description and Comments fields.
If the Description field is blank, Connected Query populates the description using the connected query name.
In the Parent Query field, click the search icon to select a parent query from an existing list.
Note: You must use the search icon to search for and select a parent query. After you select a parent query, all buttons become active and the Connected Query Structure section appears with active icons, which enables you to continue building a connected query.
Optionally, click the View Query button to view the selected query.
Note: You can preview a connected query at any point without having previously saved it.
Optionally, continue building your connected query by adding child queries, linking fields between parent and child queries, and so on.
Save your connected query.
Because connected query can be created with one parent query presented, you can save your connected query and use it as a new connected query.
Use the Connected Query Transformation page (PSCONQRSXSLT) to view, add, or modify the list of transformation program XSLTs that are defined for the connected query.
Navigation
Select
Click the Transformations button.
Image: Connected Query Transformation page
This example illustrates the fields and controls on the Connected Query Transformation page.
![Connected Query Transformation page](img/i-13cd3337n-7e8f.png)
Note: When you click the Preview button to view sample output of the connected query with transformation, the maximum number of rows that will appear in the output depends on the value that you set in the Max Rows Fetched for Query field in the Connected Query Manager page.
See Extracting and Transforming Data from PeopleSoft Pure Internet Architecture.
Report developers use the Connected Query Properties page (PSCONQRSPROP) to associate a set of properties with a connected query definition, modify values of execution or output XML file based on user-selected properties, and ensure that user-selected properties (with PeopleTools projects) are compatible with PeopleTools 8.50 and later releases.
Navigation
Select
Select the Add a New Value tab, or click the Edit link.
On the Connected Query Manager page, click the Properties button.
Image: Connected Query Properties page
This example illustrates the fields and controls on the Connected Query Properties page. You can find definitions for the fields and controls later on this page.
![Connected Query Properties page](img/iac7763n-f8a.png)
Field or Control |
Definition |
---|---|
ExecutionLog |
The valid values are:
If ExecutionLog is set to True, an execution log is recorded in an appropriate log file. Recording includes regular logging and output statistics section. |
IgnoreRelFieldOutput |
The valid values are:
If IgnoreRelFieldOutput is set to True, the output XML file will not repeat mapped field nodes that are associated with a child query. For example, if a parent query has a field DEPTID linked to the child query field DEPARTMENT, node DEPARTMENT will not appear in the XML output file. |
IsDebugMode |
The valid values are:
If IsDebugMode is set to True, an execution log is recorded in an appropriate log file. Recording includes regular logging and output statistics section. In addition, if application server executes the connected query (for example, PreView XML), the True mode preserves a directory that contains temporary files after a connected query is executed. Log also contains SQL statement that is generated by each member query as well as values of related fields used to link parent query to its child. |
ShowEmptyXML |
The valid values are:
Set the property to True to generate an empty XML data file when a connected query does not retrieve data while running the parent query. You can override this value for a specific connected query using the Connected Query Manager page. If this property is set to True and a connected query is being used as a data source for a BI Publisher report, an empty report is generated. |
ShowFormattedXML |
The valid values are:
Available options are True and False (default). If ShowFormattedXML is set to True, an output XML file will contain node indentations used to format XML. This output is used for XML output shown in the Connected Query Quick Start. You should set the SHOWFORMATTEDXML property to True if you want to use non-XML editors to work with output XML files. However, if you set the SHOWFORMATTEDXML property to False (default option), the output files will be smaller. |
SupportsOrderBy |
The valid values are:
|
Note: You define all connected query properties as strings using the name-value pairs concept. These properties are stored in database-managed tables as a part of the connected query definition.
You can preview a connected query XML without storing its properties in the database.
If you move the mouse pointer to the Help icon (question mark), a pop-up window appears showing detailed information about all properties.
The PeopleCode-based Preview XML and C++ based traditional CQRuntime run each child query multiple times based on data rows that the parent query returns, so the Connected Query application runs the child SQL statement for every parent row with the different bind values. These two running options make the Connected Query application a universal solution that enables users to map multiple queries together without limiting the resulting object structure. Connected Query enables users to map fields between different queries, provide a number of nesting levels, and so on. If the OrderBy run-time optimization is not used, these two running options run plenty of SQL statements that could affect your system performance. The number of generated SQL statements depends on the number of nested levels and on the number of data rows that are returned by higher level queries. The following example describes the difference in executions with and without using of the OrderBy processing option.
Suppose that the connected query EMP_ADR consists of these two hierarchical queries that are linked by the field EMPL_ID:
EMP_DET (Level 0)
EMP_ADDRESS (Level 1)
Applying the PeopleCode-based Preview XML and C++ based CQRuntime options, each of the above queries performs a SELECT from a single table. Connected Query application generates the following SQL statements:
Level 0: SELECT EMPLID, NAME FROM EMP_DET
Level 1: SELECT EMPLID, ADDRESS FROM EMP_ADDRESS WHERE EMPLID=:1
In the previous SQL statements, Level 1 query (EMP_ADDRESS) runs for every row returned from the Level 0 query (EMP_DET) using a bind variable that is dynamically substituted with the EMPLID field value being returned from the Level 0 query.
If you apply the OrderBy Processing option, both queries are dynamically altered to include the ORDER BY clause, and the Connected Query application generates the following SQL statements:
Level 0: SELECT EMPLID, NAME FROM EMP_DET ORDER BY EMPLID
Level 1: SELECT EMPLID, ADDRESS FROM EMP_ADDRESS ORDER BY EMPLID
In the previous SQL statements, every SQL statement runs once. The data set being generated is created by the associated code that processes child query rows based on a concatenated set of related field values being returned from a parent query.
Note: Not all queries in the Connected Query application can apply the OrderBy processing option.
To use the OrderBy processing option, each query with the OrderBy processing turned on should meet the following requirements:
Parent and child queries must have one-to-many relationships.
Every row of the related field values of the parent query can correspond to none, one, or many rows of child query data with the same set of related field values. Every row of the related field values of the child query can have only one row of parent data for the same set of related field values.
For example, query DEPARTMENT is linked to query EMPLOYEE by the related field DEPTID. Each department has one or more employees, and each employee works in a single department, which is a one-to-many relationship. You can use the OrderBy processing option for this structure if DEPARTMENT is a Level 0 query and EMPLOYEE is a child query. However, if query EMPLOYEE is set at Level 0 and query DEPARTMENT is set as a child query, then you cannot use the OrderBy processing option because multiple employees work in a department, and that is a many-to-one relationship.
In another example, a connected query has query JOB and query EMPLOYEE, and these queries are joined by the JOB_ID field. You can use the OrderBy processing option if:
JOB_ID is assigned to multiple employees and
Each employee has only one JOB_ID.
However, you cannot use the OrderBy processing option if:
JOB_ID is assigned to multiple employees and
Each employee has multiple JOB_ID assignments.
All child queries (sibling queries) that have the same parent query must share the same set of related fields.
For example, assume that a connected query has the following structure:
DEP_EMP (Level 0) EMPL (Level 1) EMPL_DETAILS (Level 1)
You can use the OrderBy processing option if:
DEP_EMP query is linked with EMPL query by the EMPL_ID field and
DEP_EMP query is linked with EMPL_DETAILS query by the EMPL_ID field.
You cannot use the OrderBy processing option if:
DEP_EMP query is linked with the EMPL query by the EMPL_ID field and
DEP_EMP query is linked with the DEP_DETAILS query by the DEPTID field.
Child queries that constitute a single hierarchy should be joined by the same set of related fields as their parent and any other fields.
For example, assume that a connected query has the following structure:
DEP_EMP > EMPL > EMPL_ADDR
You can use the OrderBy processing option if:
DEP_EMP query is linked with the EMPL query by the EMPL_ID field and
Query EMPL is linked to the EMPL_ADDR query by the related fields EMPL_ID and ADDRESS_ID.
Related fields that are identified as parent and child query relationships must follow the same field order in each query.
Parent queries on any level should not be restricted to the amount of data they return if the same restrictions do not exist for their child queries.
Note: This rule is not required, but it is highly recommended.
If the parent query limits the number of rows that are returned by any filter (for example, the WHERE clause) in the query and the child query does not use this filter; then Connected Query performance may be less efficient.
If the parent query uses a prompt to limit the amount of data returned, you should add the same prompt to the child query to eliminate extra string processing and improve processing time.
Note: To improve Connected Query performance when creating a connected query, report developers should compare the Connected Query results by running connected queries using the traditional execution algorithm and the OrderBy processing options. It is recommended that Connected Query developer sets the connected query property ExecutionLog to True to analyze the results, and then use the OrderBy processing option only if both processing options return the same number of nodes and improve processing speed.
Setting the SupportsOrderBy Property
To set the SupportsOrderBy property:
Select Reporting Tools, Connected Query, Connected Query Manager.
Open an existing query and click the Properties button.
Select the True option from the Property Value list for the SupportsOrderBy property.
Click the OK button to return to the Connected Query Manager page.
Image: Connected Query Manager page
The Use 'Order By' processing column is available in the Connected Query Structure section.
In this example, the Use ‘Order By’ processing option for the first query PSMENUDEFN is always selected and you cannot modify it. The Use ‘Order By’ processing options for the following queries become active when you click the query name in the Connected Query Structure column.
If you reopen the Connected Query Property page and set the SupportsOrderBy property to False, then the Use ‘Order By’ processing column is not available in the Connected Query Manager page. If you later set the SupportsOrderBy property back to True, the system will display the processing option that you previously selected.
Note: The OrderBy processing option is not applied if the connected query is running in the Preview XML mode.
Select the appropriate options in the Use ‘Order By’ processing column.
If the Use ‘Order By’ processing option is selected for a specific query (for example, PSMENUITEM), then:
The parent query (in this example, PSMENUDEFN) SQL will be modified by adding the ORDER BY clause. Data returned from the parent query will be ordered using a set of related fields defined to link a child query to its parent. In this example, it is the field A.MENUNAME.
If the parent query (PSMENUDEFN) has an existing ORDER BY clause defined inside the query definition, it will be removed and replaced with the ORDER BY clause being generated by the processor.
A single SELECT statement based on a query definition will be issued for a child query (PSMENUITEM) with NO FILTERING based on related field values received for a parent query.
Output XML data will be written row by row for a child query with a reference to the data that is returned from related fields used for a parent query.
Click the Save button.
When the SupportsOrderBy property is set to True and you click the Save button on the Connected Query Manager page, the following validation are performed:
If all members of the connected query have their related fields defined?
This validation is performed for the CQRuntime with or without the OrderBy processing option.
If each parent query is already ordered by another child and the OrderBy sequence of the fields is not the same for the different child siblings, this message appears noting the bind variables are being replaced with the run-time values: "User selected field %1 in query %2 conflicts with user selected field %3 used in query %4."
If all related fields being used in the OrderBy processing option include data type other than NUMBER or CHAR, this message appears: "Cannot use 'OrderBy' processing on query %1 as field %2 has unsupported data type.”
If any related field being used in the connected query structure does not exist in the Select List of the correspondent query, this message appears: "Cannot use 'OrderBy' processing on query %1 as related field %2 is not used in query %3 select list."
If any related field being used in the connected query structure does not exist in the correspondent query, this message appears: "Cannot use 'OrderBy' processing on query %1 as related field %2 does not exist in the query %3."
Note: If an error message appears, the connected query is not saved. You should evaluate the use of the OrderBy processing option on each query or on the connected query, and then save the connected query again.
Effective date operator is available in the Connected Query Manager page as a part of the field mapping process. You can select an effective date operator for any of the member queries if a child query has an EFFDT value selected as a related field.
Image: Connected Query Manager page and the Eff Date Operator list
This example shows the Connected Query Manager page and the Eff Date Operator (effective date operator) list.
![Connected Query Manager page and the Eff Date Operator list](img/i-13cd3337n-7bd9.png)
Note that the effective date operator list:
Is available only if a selected field has a format of EFFDT.
Is available in the Connected Query Manager but not available in the Connected Query Quick Start.
Has a default operator—an equal operator—like other fields.
Need not be saved in the database before you preview the XML of a connected query.
Use the Connected Query Manager search page (PSCONQRSSRCH) to edit a connected query, copy an existing connected query, delete a connected query, preview results of a connected query, schedule a connected query to run immediately with results displayed in a separate window, and schedule a connected query to run at the preset time.
Navigation
Image: Connected Query Manager search page
This example illustrates the fields and controls on the Connected Query Manager search page.
![Connected Query Manager search page](img/i7cb09f96n-7fb8.png)
To edit existing connected queries:
Access the Connected Query Manager page by selecting
Enter your search criteria and click the Search button.
In the Connected Query Manager search page, click the Edit link on the row with the connected query you want to edit.
Optionally, change the connected query structure, edit description fields, and change the connected query status.
Use the Copy a Connected Query page (PSCONQRSCOPY) to copy a connected query.
Navigation
Select
Click the Copy link for the connected query to copy.
Image: Copy Connected Query page
This example illustrates the fields and controls on the Copy Connected Query page.
![Copy Connected Query page](img/i7cb09f96n-7f9b.png)
To copy a connected query:
Access the Copy Connected Query page by selecting
Enter your selection criteria and click the Search button.
On the Connected Query Manager page, click the Copy link next to the connected query to copy.
To save a copy of the connected query:
Change the connected query name in the Target section with a new unique name.
Optionally, change ownership, descriptions, or the status of the query.
Click the OK button.
To delete a connected query:
Access the Connected Query Manager page by selecting
Click the Delete link next to the connected query to delete.
A confirmation message appears asking if you want to delete the existing connected query.
Click the OK button to continue and delete the connected query.
Alternatively, click the Cancel button to cancel the deletion and return to the Connected Query Manager page.
Image: Example of the confirmation message
This is an example of the delete confirmation message.
![Example of the delete confirmation message](img/i7cb09f96n-7f9a.png)
Use the Definitional References to Connected Query page (PSCONQRS_DEFN_REF) to view all PeopleSoft definitional objects that use this particular connected query as the data source. The results show the definition ID, definition type, and definition sub-types if they are available.
These object types are included in the Definitional References to Connected Query page:
BI Publisher (with the Report Definition or Data Source Definition subtypes)
PeopleSoft Search Framework
Navigation
Select
.Click the Search button.
Click a Lookup References link.
Image: Definitional References to Connected Query page
This example illustrates the fields and controls on the Definitional References to Connected Query page.
![Definitional References to Connected Query page](img/i7cb09f96n-7ef9.png)
Note: The Lookup References feature specifically lists only definitional references, which are defined PeopleSoft objects of specific types. This feature is not used to find the references that are not definitional references, such as Connected Query API references in PeopleCode or any static text references to that Connected Query name, whether in SQL objects, scripts, PeopleCode, and so on.
The Lookup References links are available in the Query Manager search page, Query Viewer search page, Connected Query Manager search page, and Connected Query Viewer search page.
When you open a PeopleSoft query using the Connected Query Manager, the Query Manager component appears in a read-only mode along with a message informing you that no changes are allowed to the query that is opened. The Query Manager that is being opened through the Connected Query Manager is fully functional. It enables you to run a query, view SQL that is being generated by query, view the query structure, view query prompts, view query criteria, and so on, but it doesn't allow you to save any changes for the opened query.
Image: Sample message received when you open a PeopleSoft query in the Connected Query Manager
This is an example of the message received when you open a PeopleSoft query in the Connected Query Manager.
![Sample message received when you open a PeopleSoft query in the Connected Query Manager](img/ia2cf27cn-7449.png)