Creating Query Report with PS Query in PeopleSoft

PeopleSoft ERP provides various reporting tools to cater to the requirement of the reports of different  kinds as in an organization there are numerous cases where simple  as well complex data extracts or reports are required. Today we will talk about some simple as well as complex reports.
Query reports are simple data extract in excel format created through PS Query with no advance formatting.  
for example:

• A simple report on overall status of Performance Document created for employees
• An absence report to extract the leave requests in a specific period 

Above two reports typically are excel reports with one header row and the data rows as shown below:

Though we are discussing about Query Reports in this article and I just mentioned comparatively that these are very simple reports just like shown in above image so it becomes important that we also get a brief understanding of complex reports with advance formatting and which can't be created with PS Query for example reports created with XML Publisher tool.


Let's have a glimpse of such a report:

Well, I wish you guys don't intend to create such a complex report using PS Query. Like I said it can only be created with XML publisher. Hope I was able to distinguish between the Simple Query Reports and the complex XML Publisher reports.

Let's cut to the chase and see how a simple query report is created in PeopleSoft.

Will now exercise the mentioned steps. Assume that you got a requirement to create a simple report which should fetch the basic details of new hires in a given period with the fields Employee ID, Name, Employee Record, Effective Date and Effective Sequence. The first step would be to create the PS query for this report.

The reports which I have created so far broadly involves following steps:

1. Create PS Query 
2. Security Configuration
3. Adding a Content Reference in the Portal

Let's go through each one of these steps in detail. 

1. Creating PS Query

Creating the PS Query in PeopleSoft

2 .Security Configuration for PS Query

Security Configuration for PS Query 

3. Adding the Content Reference in the Portal

Creating a Content Reference in PeopleSoft 

After everything above is done then we just need to finally run the report as explained below.
Open the run control page for the report from Root > My Report > Query Reports > Get List of New Hires:

Provide the from date and to date value as inputs and click on view results. It will generate the result.

Process Security in PeopleSoft

During my entry level days whenever I used to define a process through process definition page (Navigation: PeopleTools > Process Scheduler > Process) I kind of remembered what values has to be provided for the fields to create a process definition hence I never payed attention to what is the significance of these fields in this page.

But very soon I started realizing how vital are these attributes especially in terms of process security and the following page drew my attention:

PeopleSoft HRMS Online Training

The highlighted area above decides which component the process will be run from and which group this process will belong to. In the image above the values provided to these two fields indicate that the process XRFWIN can only be accessed through the component System Process Request (PRCSMULTI) and belongs to the process group TLSALL. In order to prove this point, we have to exercise the process run.

Open the System Process Request component from the navigation PeopleTools > Process Scheduler > System Process Request, give a Run Control ID and click on add. Following page will appear.


This is the System Process Request Page that we are talking about. Click on the button 'Run' which will take you to the process list page where you will see list of processes you have access to and from the list the desired process has to be chosen to run. As mentioned earlier you are able to access the process XRFWIN here because it's registered with the component PRCSMULTI (System Process Request).

You are able to see all the processes here when you hit the 'Run'  button because you probably are super user hence have access to the component System Process Request (PRCSMULTI) to which most of the processes (Interfaces, Reports etc...) are registered. Moreover, no Process Group is mentioned in the permission lists tagged to your OPRID which makes all the processes from all the groups visible to you provided they are registered with the component System Process Request.

But what if there is a country specific report to which only the respective HR should have access ?

That's where process security comes in play so let's first define what Process Security is.

Process Security in PeopleSoft

Its about securing process definitions so that users can't access and run the processes they aren't supposed to. Let's understand it with a live example.
Assume that you have been asked to develop 2 Core HR reports 'New Hires Report' and 'Terminations Report' meant for the UK HRs where UK is one of the regions where the company has operations. The tricky part of the requirement is, there are two HRs- HRA and HRB. HRA should have access to only 'New Hires Report' but HRB should have access to both.

This is how it's gonna be implemented:

Step 1: Develop the Reports
Develop the two reports PRCS_NHIR (New Hires Report) and PRCS_TER (Termination Report).

Step 2: Create Run control page from where they will be run
Create a run control page UKREPORTS to run these processes. Add fields on this page for providing input values for the report output if needed. Make sure that both the HRs - HRA and HRB have access to the run control page UKREPORTS which is nothing but a component. for more details on how to provide a page access visit - Simplified Way to Provide a Page Access in PeopleSoft

Step 3: Create process definition for both the reports.

PRCS_NHIR definition:

PRCS_TER definition:

Since the field 'Process Groups' is mandatory in this page so we have to provide some values here hence, mention the process group HRALL which means this process will be added into the process group HRALL.

Step 4: Assign the process group to a permission list
Create a new permission list or use an existing one from the page PeopleTools > Security > Roles and Permission Lists > Permission List. Go to the tab 'Process' and click on the link 'Process  Group Definition' to open the Process Group Definition page. Assign the Process group UKPRCSGROUP under which the report 'New Hires Report' has been defined. Save the permission list changes.

Step 5: Assign the Permission List to the user profile
Assign the permission list ADHOCUSER to an appropriate role assigned to the user HRA through user profile page (Nagivation: PeopleTools > User Profiles > User Profiles)

Summery:
Both the HRs have access to the run control page UKREPORTS and both the reports have been registered with this component but since the HR - HRA should have access to only one report 'New Hires Report' hence this report has been defined under a new process group UKPRCSGROUP. The HR - HRA then has been given access to this group through permission list definition. But no process group has been mentioned in the permission list definition for the HR - HRB hence it has access to both the reports.

You can gain in-depth knowledge on PS Security with a live example. I have a complete session in 6 parts that covers all the aspects of PS Security

Below is the link to videos in YouTube.
PS Security

Click here to see the course contents
Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training