Connected Query Overview

Connected Query enables you to create a single XML file based on a set of queries with parent-child relationships.

A connected query is a hierarchical object built with existing PeopleSoft queries. A parent query can nest n levels of child queries and any child query can have m sibling queries within a hierarchy. No artificial limits exist for n and m, but typical use involves a single parent-child relationship or a few levels of nesting. Fields in a child query are mapped to related fields in the immediate parent query. A connected query returns a hierarchical data set in which data returned by child queries is filtered by the results of its immediate parent query.

Connected queries are used to analyze data, supply to other systems with PeopleSoft data using Web Services, and create BI Publisher reports that use connected query as a Data Source.

See BI Publisher for PeopleSoft: Creating Data Sources, BI Publisher for PeopleSoft: Registering Data Sources, BI Publisher for PeopleSoft: Running BI Publisher PeopleSoft Query Reports.

Connected queries are significantly useful:

  • To simplify task for a business user.

    User create a set of linked simple queries to collect data from multiple tables instead of creating a complicated query with multiple equal, outer joins, and grouping conditions.

  • As a tool to combine the main report and its subreports in the same template for using with BI Publisher reports.

  • When outputs need to be in structured XML.

    A simple query (including a query with multiple joins) creates a tabular plain layout. To have a hierarchical output, you need to use multiple grouping conditions that involve complicated logic and is not always straight forward. Connected Query processing creates a hierarchical output where a single row of data from parent query results in a set of rows in a child query. This data is processed row by row and produces structured data. It has similarity with SQR and Application Engine nested loops processing.

    Instead of using Application Engine or SQR to collect data for file processing by BI Publisher, use Connected Query as the data collection tool because no conditional logic involves in this process. However, use SQR for processes with high volume of data or when high performance is required.

You can quickly create a single parent-child relationship using the Connected Query Quick Start component (CQ_WIZARD), and create complex connected queries using the Connected Query Manager (PSCONQRS).

Note:

Queries with In-Tree prompt cannot be used in Connected Query.