Before I begin, I would like to mention that though this article is in the continuation to the article Creating Query Report with PS Query in PeopleSoft but it still give enough insight on how exactly a PS query is created in PeopleSoft.
PS(PeopleSoft) Query is a tool to create simple as well as complex queries to extract the data from PeopleSoft tables. That been said, PS query is used extensively in creating Query Reports and it also serve as data source for other reporting tools like XML Publisher, nVision etc.. XML publisher is rather an advance reporting tool introduced in PeopleSoft.
For beginners or even those who are experienced though but haven't yet actually got their hands on the PS Queries it's quite difficult to deal with them so let's get down to it and create one for this requirement.
To make it more simple, think of creating a PS query as creating a Select SQL in any of the SQL Editors (SQL Developer, Toad etc...). So, what are all the steps we do to create a select SQL:
Well, this tab is not just to select fields from the records but more than that. See the various join link on the right for instance the link 'Join DEPT_TBL' which enable us to join other records with the added records (JOB and NAMES in this case) based on the common occurrence of key fields across these records. For example, the records JOB and DEPT_TBL can be joined based on the existence of the field DEPTID in both tables and it being a key field in DEPT_TBL.
You can expand and collapse the list of fields for each record in this page. In the image above, we have selected all the required fields from JOB record and once done we will collapse it and expand the NAMES record to select the desired fields from that record. For our requirement we only have to select the field Name from NAMES record.
So far we have just Joined the tables JOB and NAMES on the key field EMPLID but there are couple of more criteria yet to be added in order for the query to be able generate the accurate report and that can be done from the tab 'Criteria'. Click on this tab:
It's little surprising that even though we haven't added any criteria till now, the three can be seen above. Well, it's not at all surprising because when we add a record which is effective dated into our PS Query, an effective dated criteria is automatically added. Since we have added JOB and NAMES record and since both are effective dated that's why two effective dated criteria have automatically been added into PS Query. But we can still delete the ones we don't need hence will go ahead and delete the effective dated criteria on JOB table (A.EFFDT). Another criteria on EMPLID is visible too because we had joined the JOB and NAMES record in the beginning.
We still have to add one more criteria to make this query work for the requirement and that will be on the filed 'LAST_HIRE_DATE' which tracks the last hire date of employees. Click on 'Add Criteria' button on top of this page.
Complete all the steps marked in red in sequence. Remember our goal here is to add the criteria 'WHERE A.LAST_HIRE_DT BETWEEN :1 AND :2' where :1 and :2 are bind variables for From Date and To Date respectively so that HRs can specify the date range during which the new hires have to be fetched into the report.Click on 'OK' and with this our PS Query will be completed.
In the cases where the reports are run by Cross Country users, the SQL resulted from this exercise will contain some row level security related criteria which will cause troubles. For more details visit - Removing Auto Generated Row Level Security Criteria from PS Query
Complete both the steps in sequence i.e first select the field which will take the input for From Date and then change the Heading Text to something more relevant and then click on 'OK'. Repeat the above two steps for To Date as well. Once done the prompts against each bind variable will be visible as shown below:
Lastly,
we have to review the fields which are required to be displayed in the
report whether they are in correct sequence - Employee ID, Name,
Employee Record, Effective Date, Effective Sequence. Click on the tab
'Fields' to open the Fields page:
Clearly the order is not correct, we have to bring up the field 'Name' on the second place. Click on the button 'Reorder/Sort'
Reorder the fields as shown above and click on 'OK', the next thing you will see is the fields have been ordered correctly.
Finally save the query as 'TEST_QUERY_NEWHIRE'.
In The following article know how to secure PS Query - Security Configuration for PS Query or PeopleSoft Query Security
PS(PeopleSoft) Query is a tool to create simple as well as complex queries to extract the data from PeopleSoft tables. That been said, PS query is used extensively in creating Query Reports and it also serve as data source for other reporting tools like XML Publisher, nVision etc.. XML publisher is rather an advance reporting tool introduced in PeopleSoft.
For beginners or even those who are experienced though but haven't yet actually got their hands on the PS Queries it's quite difficult to deal with them so let's get down to it and create one for this requirement.
To make it more simple, think of creating a PS query as creating a Select SQL in any of the SQL Editors (SQL Developer, Toad etc...). So, what are all the steps we do to create a select SQL:
- Identify Records
- Select Fields to be used
- Define Selection Criteria
- Define Having Criteria if any
- Finally run the Query
Step 1: Identify Records
Go to the navigation PeopleTools -> PS Query -> Query Manager and click on the link 'Create a New Query' to open below page:
As
shown in the image we have to add the records that are going to be used
in the PS query. In our case the records would be JOB and NAMES hence,
first search them and add in the list by clicking on 'Add Record' link
shown in the screenshot. When the first record JOB is added followed by
NAMES and if there is a possibility of join among them then PeopleSoft
displays a link 'Join Record' which when clicked, the join criteria is
automatically added into the query.
Once
clicked on 'Join Record' link, below page will appear which will ask to
choose another record with which the current record needs to be joined.
Click on that record as shown below.
Step 2: Select the Fields
After
the records have been added in the query, the next step is to identify
the fields required to be displayed in the report. In our case the
fields are - Employee ID, Name, Employee Record, Effective Date and
Effective Sequence. Click on the 'Query' tab
to select the fields to be added from the added records as shown below:
Well, this tab is not just to select fields from the records but more than that. See the various join link on the right for instance the link 'Join DEPT_TBL' which enable us to join other records with the added records (JOB and NAMES in this case) based on the common occurrence of key fields across these records. For example, the records JOB and DEPT_TBL can be joined based on the existence of the field DEPTID in both tables and it being a key field in DEPT_TBL.
You can expand and collapse the list of fields for each record in this page. In the image above, we have selected all the required fields from JOB record and once done we will collapse it and expand the NAMES record to select the desired fields from that record. For our requirement we only have to select the field Name from NAMES record.
Step 3: Add the Criteria
So, we have done pretty much well so far. We have added records to be used in the query, fields to be displayed in the report and Joins wherever necessary. However, we can still add more records into the query and fields as well from the pages (in other word tabs) 'Records' and 'Query' respectively if needed. If you want to see the progress made in the query so far then go to the tab 'View SQL' where you will see the SQL resulted from the work done so far on this page.So far we have just Joined the tables JOB and NAMES on the key field EMPLID but there are couple of more criteria yet to be added in order for the query to be able generate the accurate report and that can be done from the tab 'Criteria'. Click on this tab:
It's little surprising that even though we haven't added any criteria till now, the three can be seen above. Well, it's not at all surprising because when we add a record which is effective dated into our PS Query, an effective dated criteria is automatically added. Since we have added JOB and NAMES record and since both are effective dated that's why two effective dated criteria have automatically been added into PS Query. But we can still delete the ones we don't need hence will go ahead and delete the effective dated criteria on JOB table (A.EFFDT). Another criteria on EMPLID is visible too because we had joined the JOB and NAMES record in the beginning.
We still have to add one more criteria to make this query work for the requirement and that will be on the filed 'LAST_HIRE_DATE' which tracks the last hire date of employees. Click on 'Add Criteria' button on top of this page.
Complete all the steps marked in red in sequence. Remember our goal here is to add the criteria 'WHERE A.LAST_HIRE_DT BETWEEN :1 AND :2' where :1 and :2 are bind variables for From Date and To Date respectively so that HRs can specify the date range during which the new hires have to be fetched into the report.Click on 'OK' and with this our PS Query will be completed.
In the cases where the reports are run by Cross Country users, the SQL resulted from this exercise will contain some row level security related criteria which will cause troubles. For more details visit - Removing Auto Generated Row Level Security Criteria from PS Query
Step 4: Add Prompts
Though we are done with the SQL part of the PS Query, there are still couple of things we need to work on. Since we have used bind variables :1 and :2 which will read the dates specified by HRs during run time hence, we have to define the prompt fields which will enable HRs to enter Date range. Note that this particular part is specific to our requirement of Query Report which we are creating at the moment otherwise this step wouldn't be required. Click on 'Prompts' tab to open the Prompts page and then click on 'Add Prompt' Button.Complete both the steps in sequence i.e first select the field which will take the input for From Date and then change the Heading Text to something more relevant and then click on 'OK'. Repeat the above two steps for To Date as well. Once done the prompts against each bind variable will be visible as shown below:
Clearly the order is not correct, we have to bring up the field 'Name' on the second place. Click on the button 'Reorder/Sort'
Finally save the query as 'TEST_QUERY_NEWHIRE'.
In The following article know how to secure PS Query - Security Configuration for PS Query or PeopleSoft Query Security
You can gain in-depth knowledge on PS Query with a live example by just paying below amount
I have a complete session in two parts which explains:
- How CI works - Overview, all the elements of CI etc..
- Creating an Inbound interface, that loads data on a multi level component using CI and App Engine
PS Query Part 1
PS Query Part 2
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
No comments:
Post a Comment