Accomplishing Common Reporting Tasks and Resolving Common Issues with Plateau Report Designer – Part 1

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 issue addressed in this post will be providing a simple way to extract a Scheduled Offering’s start and end dates.

This task is not as straight forward as it seems since the start and end times appear to be stored only as data on segments and not as a set of data that is easily accessible via the scheduled offering main table. When approaching the issue from a segment point of view it is not as simple as listing the scheduled offering’s main information and then the start and end dates.

Items Needed for Solution:

1. Working knowledge of Successfactor Learning Management Systems and Plateau Report Designer(PRD.)
2. Rights to import and export reports from your LMS instance.
3. A working knowledge of SQL as it applies to PRD.
4. A working copy of the most up to date version of Plateau Report Designer(PRD.)

Note: If you need more information on how to use Plateau Report designer, please read the following post.

Solution Background:

In your basic reports, when adding in a field, the SQL query will usually bring in (ideally) one piece of data so you will have one row per scheduled offering. If you try to join the segment table to your main scheduled offering table you will end up with all of the start/end dates of the segments of the scheduled offering in separate rows. If a scheduled offering has 3 segments, that scheduled offering would have 3 rows in your report.

Initially to solve this problem I devised a SQL subquery to calculate the minimum start date and the maximum end dates of a scheduled offering. This solution works well, and later I found a similar bit of SQL code in a standard SF report using this approach. The code for this is:

(select min(temp.start_dte) from pa_sch_seg temp
where temp.schd_id = ps.schd_id)as SOStartDate,

(select max(temp.end_dte) from pa_sch_seg temp
where temp.schd_id = ps.schd_id)as SOEndDate,

In this code snippet the ps.schd_id would be the scheduled offering id from your main query. This code works, but recently I found a new table that allows a direct query for start and end date as well as primary instructor and location. Using a table to do this will make your SQL query run faster as there is more overhead to subqueries, and in the example above two subqueries are required as opposed to a single join with the new table.

The table you want to use to join your main scheduled offering data in the PA_SCHED table to is the PS_SCHD_RESOURCES table. This table has the following fields available:

For my example on how to implement this I will be adding in scheduled offering start and end times to the standard report Learning History (CSV.)

Actions:

The steps involved in this process are:

1. Export the standard Learning History (CSV) report.
2. Edit the SQL code to pull in the new fields (start/end date for scheduled offerings.)
3. Add the new SQL column to the standard report.
4. Save the new custom report and save/export it as a ZIP to be imported back into the LMS
5. 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 and most changes in the first union, and only be adding in the columns we select in the first union as NULLs in the second union since there are no scheduled offerings in the external item tables. The start/end dates will just need to be NULL in that section to ensure the SQL executes.

The scheduled offering 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.schd_id identifies the scheduled offering so this is what we will use in our initial SQL join info.

PS_SCHD_RESOURCES psr needs to be added in the first union’s FROM section, and the join for it needs to be added to the WHERE section of the query which consists of and e.schd_id = psr.schd_id (+):

Now the fields in the PS_SCHD_RESOURCES table are accessible and the two fields(columns) we are going to add are PSR.START_DTE and PSR.END_DTE.

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 columns as NULLS to the second union. The code for this is NULL as sostart, NULL as soend, and you can see where it is inserted below:

After these changes have been made you can check your preview to see if any of the records that contain a scheduled offering (id) display the start and end dates/times in the columns sostart and soend. Below is a sample I ran in the SQL preview window. Note: I modified the code to display the schedule offering id early and restricted my query to a user I knew had one scheduled offering history record.

I mentioned another method that is a calculated way to derive this solution, I will show that code in use as well, though using the table method is more efficient and provides access to the primary instructor and location of the offering as well (which is also normally segment only information.)

First I add the code to the same areas as before, but will name the output columns differently. So in the first union I will add:

(select min(temp.start_dte) from pa_sch_seg temp
where temp.schd_id = e.schd_id)as sostart2,

(select max(temp.end_dte) from pa_sch_seg temp
where temp.schd_id = e.schd_id)as soend2,

Note that e.schd_id is how the subquery knows which scheduled offering to query.

These two new columns must be added to the second union as well as NULL as sostart2, NULL as soend2 as shown below:

The resulting preview data shows that the start and end dates/times are equivalent for both solutions:

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.