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 to add in Scheduled Offering Start and End Date. This version will demonstrate how you would bring in a custom user and custom item field for non-referenced custom field data. 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.
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 two custom fields I will be pulling in for this example will be the user custom field "Crew Number," and the item custom field "Course Provider." 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 item and clicked on search. I find that the "Course Provider" is item custom field number 10.
Once you know the column number you can start figuring out the SQL code you will need to write to obtain the custom field data. The two tables you are going to use for this are PV_CPNT_CUSTOM_COLUMN and PV_STUD_USER. Why do they use different names? Good question, but it is a bit confusing and no good answer. If you are looking for these types of tables I recommend using the SQL editor window in PRD and typing in the first part of your guess for the table name and then the wildcard % sign.
Here is a sample of how I found the Item (CPNT) view table.
As you can see it also has the field names I will need to call as well as the key to link into the table. In the case of the item I will be using REV_DTE, CPNT_ID and CPNT_TYP_ID since those are what compose the primary key for items. I know that the field I want corresponds to the number of the custom column from above, which is 10. So CC_CPNT_10 will be "Course Provider."
When you repeat the steps for this the user custom field you will find out that "Crew Number" is column 10. You will need to use the primary key STUD_ID and the filed name you need will be COL_NUM_10.
Below is the field listing for PV_STUD_USER
So the code I will be adding in the three areas of the previous report will be:
SELECT
.
.
psu.col_num_10 as CrewNum,
pccc.cc_cpnt_10 as CoursePro,
.
.
FROM
.
.
pv_stud_user psu,
pv_cpnt_cust_column pccc,
.
.
WHERE
.
.
AND e.stud_id = psu.stud_id (+)
AND e.cpnt_id = pccc.cpnt_id (+)
AND e.cpnt_typ_id = pccc.cpnt_typ_id (+)
AND e.rev_Dte = pccc.rev_dte (+)
.
.
UNION
SELECT
.
psu.col_num_10 as CrewNum,
NULL as CoursePro,
.
FROM
.
pv_stud_user psu,
.
WHERE
.
AND e.stud_id = psu.stud_id (+)
Note: I will be adding this code in two places (for the user field)since there is a union for external versus regular item history
For my example on how to implement this I will be adding in these two fields to the previous report from this post, which can be downloaded here. Originally based on the SF LMS Learning History (CSV.)
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 scheduled offering id that will be used to join in with the new table. The Learning History (CSV) report is broken up into two unions, the first pulls in history data from the PA_CPNT_EVTHST table, and the second union pulls in data from the PA_XCPNT_EVTHST table.
The first part (union) is pulling in standard history of items/scheduled offerings and the second union is pulling in history for external events. We will be doing our join for the both custom fields in the first union, because the regular item history has both user and item data to join into. In the second part (union) the student data and code will be the same. Student ID will link into the custom table. Wwill set the custom item field name to NULL since there is not an item id to pull custom item fields off of in external history items.
The student id for this report we are concerned with comes from the PA_CPNT_EVTHST table which is defined as table e in this report. This means that e.stud_id identifies the student so this is what we will use in our initial SQL join info. The item key also comes from PA_CPNT_EVTHST, so we will also be using e.cpnt_id, e.cpnt_typ_id and e.rev_dte to join into the item custom field table.
PV_STUD_USER psu and PV_CPNT_CUST_COLUMN pccc need to be added in the first union's FROM section, and the join them need to be added to the WHERE section of the query which are circled below:
Now the fields in the PV_STUD_USER and PV_CPNT_CUST_COLUMN tables are accessible and the two fields(columns) we are going to add are PSU.COL_NUM_10 and PCCC.CC_CPNT_10 .
This code will be added first in the first union area after the select statement:
Before you preview and test this you will need to add in the custom student table PV_STUD_USER in the 2nd union area in the FROM section and the join for that table in the WHERE section.
Finally you need to add the psu.col_num_10 as CrewNum and the NULL as CoursePro to the SELECT section in the second union area in the same spotand order as they were in the first union to ensure you have the same number of rows (in the same order) on both sides of the union.
After these changes have been made you can check your preview to see if any of the records display the item custom field "Course Provider" and the user custom field "Crew Number. 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 these columns are now available to be added into the report and the SQL portion is complete. You simply add the fields, save the report ZIP file and import and test.
Conclusion:
As always, if you have other solutions or issues with this post, please leave them in the comments. One thing this sample does not do is cover the PA_ table way of bring in non-referenced custom fields. I will cover that in the next topic when I go over the way to bring in custom fields that are referenced.
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.
In the next part of this series I will look at expanding on this solution and will create a referenced sample as well as go into the differences in those custom fields, and alternate tables to use for non referenced custom fields.
Thanks for your attention and feedback,
Dwayne Phillips - Veritas Prime
- SAP Managed Tags:
4 Comments
