Composite Query - A clever way to filter and prune data from existing queries

As the name suggests the composite query can simply combine the existing queries and can add filters, aggregates, sorts the data for presenting.

All you just have to do is, 

1. Create PS Queries
2. Combine two or more of them
3. Join them based on relationship
4. Apply additional filters/prompts needed
5. Provide Group By or Order By clauses
6. View and schedule to get the results

You might ask why would we create composite query when we already have so many PS Queries and Connected Queries which we can use to get the data we want, right?. To you the answer would be you can RE-USE the existing queries and add controls on top of it.

Consider an example of having separate queries for fetching Employee Details, Salary Information and Login Details, to fetch the high paid employees login details you don't have to create a new query. Simple combine these three and create it as a composite query. Or you have Customer Information and Aging Report and wanted to get the summary report to give a follow-up call for each customer.  

For better understanding I have taken a simple use case of fetching the recently logged employee details along with their Component Interface accesses to use in my Audit Analysis.

To do that I have created 3 PS Queries with simple SQL (no fancy filters or GROUP BY or ORDER BY clauses),

Then combine the first two base queries, Reporting Tools > Composite Query > Composite Query Manager

Added the 3 queries with necessary joins (here I used left out join as I wanted to skip those who don't have CI access. And I used Row Security Class for simplicity, instead I could have used PSROLECLASS and PSROLEUSER to fetch the actual security.

Select the needed fields only, and then added some filters.

Also added some order by clause to get the recent rows on top.

Note: GROUP BY can be used in reference to the aggregate functions used in other selected fields.

Following aggregates are available for you Average, Sum, Count, Count Distinct, Min and Max.

There, your composite query is ready and change the status as "Active" to use it. 

You can directly download results via Reporting Tools > Composite Query > Composite Query Viewer or schedule it via Composite Query Scheduler.

When your query is not correctly build like having incorrect expression or prompt, you can see the errors in process log.

And of course there are some limitations in using the Composite Query, but I think those will be ok for the intended purpose. Which you can refer on latest PeopleBook link.

Follow my blog for more tutorials and insights on PeopleSoft applications and PeopleTools.

Thanks