Composite Query Overview

Composite Query enables you to combine data from existing queries and then apply filters, aggregates, and so on before presenting the report results, which show the combined data set. Composite Query retrieves multiple levels of related information on existing queries and presents the combined data as a single and flattened query result.

You can create BI Publisher reports with composite 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.

Using Composite Query, you also have the option to:

• Select the SQL pruning option to remove tables and fields that are not needed base on users’ attribute selections.

• Set the ORDER BY and GROUP BY clauses.

• Set the WHERE clause as a filter over the result set of a composite query.

  Note:

  Fields in the WHERE clause cannot be pruned.

• Prune the base queries at runtime based on the selected output fields, join criteria, filter criteria, and ORDER BY clause.

• View pruned SQL statements for the composite query.

• Run the composite queries using Application Server.

• Reuse a composite query definition.

• Specify join criteria, field dependencies within a query, and field or table pruning options at query design time.

  See Defining Record and Field Dependencies.

• Set the result limit by selecting the attributes in the base queries.

Note:

Composite queries created based on web services requests are not persisted in the database. The base queries can be the existing queries that were created using Query Manager, Query API, or QAS.