"Composite Query is an amazingly cool tool. I learn something new every time I work with it, and I’m continually trying to figure out different ways to use it."
- Tom Johnson, Duke University
What is it?
Interested in creating complex query reports not allowed using standard PS Query Manager? Composite Query was featured with PeopleSoft Reporting Tools 8.54 and it allows you to combine multiple existing queries and present the data as a single, flattened result.
Highlights
- Used to bring back a maximum of 99,999. PeopleBooks recommends to keep the max at 10,000.
- More reliable than PS Query in terms of displaying accurate hierarchical data structure.
- Filters and aggregates are easily applied to a Composite Query.
- Unlike Connected Query, Composite allows for viewing results within the tool.
- Left outer joins are created by default.
- Prompts are not allowed in the base queries (parent or child). You will get a message (277, 46) letting you know to remove the prompt.
- Prompts are created on the Composite Query itself.
- The Distinct clause is added to the Composite Query itself.
- Composite Query allows for siblings to be defined at any level.
- In PT 8.55, Composite Query can be used as a Pivot Grid source and allows for results to be downloaded to MS Excel.
- Composite Query cannot be used as a source for a BI Publisher report.
- Composite Query cannot be scheduled.
- The query will perform much better if most of the criteria are defined in the source queries.
Tips
- Keep base queries as simple and refined as possible. Make sure to include the key fields required for joining and filtering.
- Use the SQL view to see how the composite query can be executed in the database.
- If you want the SQL to use only the subset of fields selected from the source, select to prune the SQL.
Check out some HEUG presentations:
Connected Query and Composite Query
https://www.heug.org/p/do/sd/topic=3011&sid=21797
Making Sense of PS Reporting Tools: Query, Composite, Connected, BI Publisher, Pivot Grid, Dashboard
https://www.heug.org/p/do/sd/topic=2719&sid=22768