Peoplesoft - Drill Down PS Query

1. Introduction

How often  users run queries and write  down values returned to navigate to the appropriate page and view the underlying details of those vouchers, journals, vendors, etc?Wouldn't it be nice if  just on a click of  a hyperlink embedded within the query results ,have a page pop up in a separate browser window instead. As a solution to this,Drill down PS query can be used .

Drill down PS Query is a type of expression which returns the respective URL for the column along with the PS Query Search Results.By clicking the URL or Mapped Fields users are redirected to the respective peoplesoft page.

2. Business Benefits

Eliminates the need of noting down the value and navigating to the component manually to enter the data and open up the page for editing, resulting in a time saver

3. Types of  Drill down PS Query

• Query URL 
• Component URL
• External URL     
• Attachment URL 
• Free Form URL

           4. Development of a  Drill Down PS Query

        A Drill Down PS Query can be developed in few simple steps:

       Step1: Once the query is created, Click Expression -> Add Expression Button.

         Step2: Select the expression type as Drilling URL.

       Step3: Once Drilling URL is selected as the Expression type, types will be      highlighted.Choose anyone URL type based on the requirement.

         Step4: After all the fields were entered, click the search keys button to populate all the search keys which is in record.

       Step5: After search keys are populated, provide the respective key value. The Selection flag for the particular field name should be selected to fill their key value.These values will be used to By-pass the search key values while the component is redirected.

       

      Step6: Click the Map column button to populate all the fields from the query record.

      Select the field that needs to be mapped. While the query is executed, on clicking a mapped field the component will be redirected in a separate tab .

       

     Step7 (optional):“Use as field” hyperlink will be used to make the drilling URL expression as a new field .

<     

      Step8: Click the mapped fields or drilling URL hyperlink to redirecting the respective component in a new tab in our browser. 

<

    Author,

   Chandru Asokan

   Kovaion-Peoplesoft Consulting Practice

   Email:chandru.asokan@kovaion.com

PeopleSoft Composite Query

What is PeopleSoft Composite Query?

Composite query is a new feature of Peoplesoft reporting tools 8.54 which provides the option to combine data from existing queries and present data as the single and flattened query result. It also allows to use apply filters, aggregates, and so on before presenting the report results.  Composite queries created based on web services requests are not persisted in the database. The based queries can be the existing queries that were created using Query Manager, Query API, or QAS.

Main Highlights:

Composite Query provides option to:

• Select the SQL pruning option to remove tables and fields that are not needed based 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.
• Prune the based 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.
•  Set the result limit by selecting the attributes in the based queries.

Creating a Composite Query

The following example illustrates creating a composite query, from two existing queries to get the combined results. In this example we are using the queries Job Data and Personal data to get the Job and personal details of all the active employees.

Step1:

Navigation: Main Menu -> Reporting Tools -> Composite Query -> Composite Query Manager 

Add a new value

Step2: 

Select two of the existing queries, the alias names once selected here cannot be changed.

Step3:

In the next step we specify the joins. In this example as we are querying the employee details, Emplid field is joined in both the queries.

Step4:

Select the output fields from both the queries.

Step 5:

In this step we select the filters, Having clauses etc if any and save the query.

Limitations of Composite Query:

• Composite Query should not be used for returning a large number of rows. The result set returned from a composite query should be 10,000 rows or less at runtime. The time-out limitation to run a composite query is the time-out limitation of the application servers. Need to make sure that construct the Join Criteria, Filter Criteria, and aggregations to reduce the amount of data returned by a composite query.
•  Composite query does not change the SQL statements or the result sets of existing queries
•   Relationships between different based queries are expressed by fields, not by expressions or prompt.
• Users must specify the field dependencies to ensure correctness of SQL pruning.
• If a query has the UNION construct, pruning is based on the main SELECT clause
• A composite query does not parse expressions.
•  If users need to sort the composite query results, they have to use Composite Query Manager to specify the Order By clause for the composite query.     

Author,

Shubha Rohit

Kovaion-Peoplesoft Consulting Practice

Email: shubha.rohit@kovaion.com