Overview:
In this series of posts I will examine common issues that can be addressed through the Plateau Report Designer (PRD) custom reporting tool for the Successfactors Learning Management Sysetm (LMS.) The reporting task addressed in this post will be extending a previous post's solution for reporting on Learning History. The last report extended the Learning History (CSV) report and added in a couple of Non-Referenced custom fields. This version will demonstrate how you would bring in a custom user referenced field. To download the previous file click here, for the completed version from this article, please click here.
The documentation given out for the PRD classes goes about pulling in the custom fields in a different way than I will be discussing here. For this solution we will be utilizing view tables to get the reference id and then using that to access the table with the referenced id description.
Items Needed for Solution:
- Working knowledge of Successfactor Learning Management Systems and Plateau Report Designer(PRD.)
- Rights to import and export reports from your LMS instance.
- A working knowledge of SQL as it applies to PRD.
- A working copy of the most up to date version of Plateau Report Designer 4.4.2(PRD.)
Note: If you need more information on how to use Plateau Report designer, please read the following post.
Solution Background:
The referenced custom field I will be pulling in for this example will be the user custom field "Market," As a refresher, to find out the custom field's number you will need to go to System Admin -> Custom Columns and then select the type of custom column you are looking for.
In the following capture I have selected user and searched and as you can see the "Market" custom field is column number 170 and it is listed as referenced..
So what is the difference between a referenced and non-referenced custom field? Well one simple answer is if the field is non-referenced it is essentially going to allow an open text field to type in whatever is desired for the custom field. If a field is referenced you are going to create a list of reference ids and descriptions that will provide a drop down menu when the field is being updated. This sort of mapping of data can be useful for HR feed or reporting that needs to be normalized. It is also helpful since it gives the admin a list so there is less chance of typographical errors in entering data.
With that in mind to view the list of ids and description for Market you simply click on edit icon in the red number 5 in the last capture next to the Label name and you get the follow information.
So this gives us the last bit of information we need in order to pull in the description of the Market custom field. The information we will need for our SQL are: Column Number, Student ID, Reference ID for the tables we will be using.
From my previous post I have already supplied us with code that would allow us to bring in the reference id. The PV_ tables (PV_STUD_USER for user custom fields) when used on a referenced custom field yields the reference id for that column id and student id.
So the last piece we need is the table name to find out what the reference id's description is. The table for student custom fields for this is: PA_USRRF_STUD.
How did I know this field was what I needed? There is a section in this guide on page 196 that gives a complete breakdown of custom fields and how to determine the correct table based on what you need to know (custom fields for user,item,class, curricula, etc..) The guide also shows another way to get non-referenced data using PA_ table versions similar to the PV_ view tables I chose to use.
I think the PV_ tables and fields make more intuitive sense so that is why I use them. A good exercise from this blog would be to go back through the code and redo it using the PA_STUD_USER table instead of the PV_STUD_USDER table. You should be able to do it from the new document. Maybe even an item custom field that is referenced.
Below is the field listing for PA_USRRF_STUD:
So to get the information we need from this table we are going to need to join based on the column number (170,) the user_id is actually the reference id we have from the view table. So we will join on that value which will then let us select the USER_DESC field which is the reference id's description.
I am going to reuse some of the code from the previous post so show where we are getting the joins from. Also, I have removed the union area in the learning history report (CSV) for ease of use now so the external history will no longer be generated.I will be adding in the three areas of the previous report will be:
SELECT
.
.
pu1.user_desc as MarketDesc --ADDITIONAL CODE
.
.
FROM
.
.
pv_stud_user psu, -- already exists from last report
pv_cpnt_cust_column pccc, -- already exists from last report
pa_usrrf_stud pu1, --ADDITIONAL CODE
.
.
WHERE
.
.
AND e.stud_id = psu.stud_id (+) -- already exists from last report
AND psu.col_num_170 = pu1.user_id (+) --ADDITIONAL CODE
AND pu1.col_num (+) = 170 --ADDITIONAL CODE
AND e.cpnt_id = pccc.cpnt_id (+) -- already exists from last report
AND e.cpnt_typ_id = pccc.cpnt_typ_id (+) -- already exists from last report
AND e.rev_Dte = pccc.rev_dte (+) -- already exists from last report
.
Note: This code is based on the previous blog post and on the lines with --ADDITIONAL CODE are the updates to bring in the reference id description.
Actions:
The steps involved in this process are:
- Download the previous modified Learning History report here.
- Edit the SQL code to pull in the new fields
- Add the new SQL columns to the downloaded report.
- Save the new custom report and save/export it as a ZIP to be imported back into the LMS
- Import the new custom report ZIP file into the LMS for testing
I am going to focus the example on step 3, since for the purposes of this post it is assumed that the basics of adding fields, import and export of reports are already working knowledge of the reader. Once again, if you need examples on these steps or more basic PRD steps, I recommend this post to get up to speed.
The first thing needed for this report is to identify the table and reference id that will be used to join in with the new table. In the last post I left both unions in the Learning History (CSV) report, and in this one I have taken the 2nd part of the union out so there are no more unions to confuse in the report.
The reference id for this report we are concerned with comes from the PV_STUD_USER table which is defined as table psu in this report. This means that pu.col_num_170 identifies the reference id for column 170 (Market's column number), so this is what we will use in our initial SQL join info. We will also need to hard set the column number of 170 in the table we are joining into (PA_USRRF_STUD.) This will look like pu1.col_num (+) = 170 which simple means return the information whether or not there is actually data for just col_num that = 170.
PA_USRRF_STUD_ pu1 need to be added in the FROM section, and the two join statements to set col_num and user_id (reference ID) need to be added to the WHERE section of the query which are circled below:
Note: If you are adding this code into a report you have not already linked the PV_ table into you will need to do those steps from the previous blog post.
Now the field in the PA_USRRF_STUD table is accessible and the field we are going to add is PU1.USER_DESC.
This code will be added first in the area after the select statement:
After these changes have been made you can check your preview to see if any of the records display the item custom field "Market" . Below is a sample I ran in the SQL preview window. Note: I modified the code to display a specific user with a specific item on their learning history.
At this point this column is now available to be added into the report and the SQL portion is complete. You simply add the field, save the report ZIP file and import and test. One thing to note is that the description is coded as a label so you will need to modify the column binding so that it bring in the correct translated description for the user's locale who is running the report. For more information on this go to the guide here on page 154 it gives the steps for adding in the code for localized fields.
Conclusion:
As always, if you have other solutions or issues with this post, please leave them in the comments. I think a good exercise to become more familiar would be to take this report code and attempt to duplicate the results with the guide linked above and use the PA_STUD_USER table. Getting the joins right will help your problem solving a great deal I believe for future reports.
The complete report file to view and import for this example is available here for download from my dropbox. It has the fields already added to the report so you can import and test it, or open it in PRD to view the SQL.
I will take suggestions for my next topic if there are any. I will be posting a new entry in the next 1-2 weeks.
Thanks for your attention and feedback,
Dwayne Phillips - Veritas Prime
- SAP Managed Tags:
2 Comments
| User | Count |
|---|---|
| 12 | |
| 8 | |
| 4 | |
| 4 | |
| 2 | |
| 2 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |
