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 the last post’s solution for reporting on scheduled offering start and end times by developing a custom scheduled offering report.  You can download the report import file here to follow along or for use.

A common report I have seen requested is a simple listing of scheduled offerings with their start and end times as well as some basic enrollment information.  There are a few reports that have some information like this in them.  The Scheduled Offering Data (CSV) report and the Scheduled Offering Roster (CSV) report come to mind.  Both of these report however bring in segment information which make the reports clunky for those admins that just want basic scheduled offering information.


The Scheduled Offering Roster (CSV) report will provide the basic frame for the report that will be built.


In the custom report the following fields will be coming through:

Scheduled Offering ID

Scheduled Offering Description/Title

Scheduled Offering Start Date /Time

Scheduled Offering End Date / Time

Scheduled Offering Location ID

Scheduled Offering Primary Instructor

Scheduled Offering Waitlist Total

Scheduled Offering Enrolled Total

Scheduled Offering Pending Total

Scheduled Offering Cancelled Total



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.)

PRDvers.jpg

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




Actions:

The steps involved in this process are:

  1. Export the Scheduled Offering Roster (CSV) report.
  2. Clear out the existing table from the view.
  3. Clear out the existing SQL, and replace the the SQL code specific to the new custom report.
  4. Copy the new SQL data to the layout.
  5. Clean up labels, and setup data formatting for the report in the layout.
  6. Import the report to the LMS Instance and test.

I will go into details on steps two through five, the Export and Import of the report is something you should be familiar with, and if you are not I recommend the following post.

The reasons I chose the Scheduled Offering Roster (CSV) report as simply to get the basic report parameters and framework of the type of report I am creating.

After you have opened the roster report in Plateau Report Designer (PRD) you will highlight and delete the main table from the layout.  Once the SQL has been rewritten a new table will be created.  The reason I choose to remove the table is to stop any column binding issues that can arise from removing old SQL code.  It provides a cleaner starting point (especially if you plan to add to this report later.)

To do the deletions highlight the entire table and press your delete key,  You can right click and delete if you click on the Table area circled below, but the delete key works just as well. 

/wp-content/uploads/2016/04/first_941662.jpg

Once you have deleted the table the layout should appear completely blank like below:


/wp-content/uploads/2016/04/2nd_941682.jpg

There are two more quick things to do to get the report ready, the first is to save this report as a new name.  You want to do this to ensure you do not overwrite your standard report when you import it back into your LMS instance.

After you have saved the report as a new name you will want to click on the report parameter drop down in the Outline area of PRD and delete the paramenter MaskStudentID.  This report will not be using any student/user data other than a calculation of enrollments.

Right click on MaskStudentID and select delete as pictures below:

/wp-content/uploads/2016/04/3rd_941683.jpg

After this is done it is time to get on to working on the SQL code for this report.  You will go into the existing data set SOUsers (You can rename this if you like) and clear out the existing SQL code by highlighting all of the code in the editor and deleting it.

When you double click or right click and edit the SOUsers data set (in the Data Sets area of your Outline Pane in PRD,) the SQL editor will appear like below:

/wp-content/uploads/2016/04/4th_941687.jpg

The next several steps of this process will be editing the SQL text in the Query Text area of the SQL editor window.

The first data and most basic that is required for this custom report is the scheduled offering (SO) id.  To get this data you will need to use the

PA_SCHED table.  So the first code to put in the query text in this process will be:

SELECT

     ps.schd_id as SOID

FROM

     PA_SCHED ps

I recommend starting out and doing previews along the way to make sure you are getting the data you expect.  Below is a sample of the data I get from the code above as the starting point for this report.

/wp-content/uploads/2016/04/5th_941688.jpg

The only other data that is needed from this table is the SO description, and a couple of timezone fields (for use later) which changes the code to:


SELECT

     ps.schd_id as SOID,

     ps.schd_desc as SODESC,

     ps.timezone_id TIMEZONE,

     ps.display_in_schd_tz displaytimezone

FROM

       PA_SCHED ps

For the next few pieces of data that are needed for this report we will be joining the PA_SCHED table with the PS_SCHD_RESOURCES table.  The columns we need from the resources table are: instructor id (inst_id,) location id (locn_id,) start date/time  (start_dte) and end date/time (end_dte.)

The code for the report now looks like:

SELECT

     ps.schd_id as SOID,

     ps.schd_desc as SODESC,

     ps.timezone_id TIMEZONE,

     ps.display_in_schd_tz displaytimezone,

     psr.inst_id as INSTID,

     psr.locn_id as LOCID,

     psr.start_dte as SOSDANDTIME,

     psr.end_dte as SOEDANDTIME

FROM

       PA_SCHED ps,

       PS_SCHD_RESOURCES psr


WHERE

     ps.schd_id = psr.schd_id ( + )


You should test your code after changes like this to make sure you are pulling in the data as expected.  When I hit preview the following information populates so I know I do not have errors in my code.

/wp-content/uploads/2016/04/7th_941689.jpg

The report now almost has all the data we set out to fetch, except for the calculated data on the various enrollment status types.  In order to get this data we will need to use the PA_ENROLL_SEAT table.  We will be using this table inside of four SQL subqueries.  The ease of use for the subqueries is why I chose to use them.  The code will look like this now:

SELECT

     ps.schd_id as SOID,

     ps.schd_desc as SODESC,

     ps.timezone_id TIMEZONE,

     ps.display_in_schd_tz displaytimezone,

     psr.inst_id as INSTID,

     psr.locn_id as LOCID,

     psr.start_dte as SOSDANDTIME,

     psr.end_dte as SOEDANDTIME,

     (select count(q.enrl_stat_id)

        from pa_enroll_seat q

        where q.schd_id = ps.schd_id

        and   q.enrl_stat_id = ‘ENROLL’) as enrolled,

    (select count(q.enrl_stat_id)

      from pa_enroll_seat q

      where q.schd_id = ps.schd_id

      and q.enrl_stat_id = ‘WAITLIST’) as waitlisted,

    (select count(q.enrl_stat_id)

     from pa_enroll_seat q

     where q.schd_id = ps.schd_id

     and q.enrl_stat_id = ‘PENDING’) as pending,

    (select count(q.enrl_stat_id)

     from pa_enroll_seat q

     where q.schd_id = ps.schd_id

     and q.enrl_stat_id = ‘CANCELLED’) as cancelled  

FROM

       PA_SCHED ps,

       PS_SCHD_RESOURCES psr

WHERE  ps.schd_id = psr.schd_id ( + )

When I run this code against my instance data in the preview I get the following output which lets me know there are no SQL errors and that the data is coming through as expected:

/wp-content/uploads/2016/04/14th_941707.jpg

The only thing left to add to the code at this point before adding this new SQL Table to the report layout are the report filter and security statements.  For this report the completed SQL Code with these will look like:

SELECT

     ps.schd_id as SOID,

     ps.schd_desc as SODESC,

     ps.timezone_id TIMEZONE,

     ps.display_in_schd_tz displaytimezone,

     psr.inst_id as INSTID,

     psr.locn_id as LOCID,

     psr.start_dte as SOSDANDTIME,

     psr.end_dte as SOEDANDTIME,

     (select count(q.enrl_stat_id)

        from pa_enroll_seat q

        where q.schd_id = ps.schd_id

        and   q.enrl_stat_id = ‘ENROLL’) as enrolled,

    (select count(q.enrl_stat_id)

      from pa_enroll_seat q

      where q.schd_id = ps.schd_id

      and q.enrl_stat_id = ‘WAITLIST’) as waitlisted,

    (select count(q.enrl_stat_id)

     from pa_enroll_seat q

     where q.schd_id = ps.schd_id

     and q.enrl_stat_id = ‘PENDING’) as pending,

    (select count(q.enrl_stat_id)

     from pa_enroll_seat q

     where q.schd_id = ps.schd_id

     and q.enrl_stat_id = ‘CANCELLED’) as cancelled  

FROM

       PA_SCHED ps,

       PS_SCHD_RESOURCES psr

WHERE  ps.schd_id = psr.schd_id ( + )

/**  and s.SCHD_ID in [ScheduleSearch]

     and [security:PA_SCHED ps] */

For the next part we will be copying our newly generated SQL table to the layout portion of the report in preparation for importing it into the LMS instance for testing.

First you simply drag the SOUsers Data set onto your blank layout:

/wp-content/uploads/2016/04/9th_941697.jpg

You will get a popup like pictured below,  Simply choose select all and click Ok,

/wp-content/uploads/2016/04/10th_941698.jpg

Your layout should now look like this(I have already deleted the TIMEZONE and displaytimezone fields in the picture below):

/wp-content/uploads/2016/04/11th_941699.jpg

Note: The order of the table is the order in which we formed the SQL query.

So there are a few small steps before this report is ready to be re-imported.  Some I will show and some I will not.

  1. Delete the two timezone data fields. (Highlight the columns, right click and delete, this is already done in the image above.)
  2. Localize the labels where possible, change them to the text you want otherwise. (You should be familiar with this.)
  3. Change the time/date fields so that the report will generate times from the timezones of the SOs themselves as opposed to the system time of the SQL server.
  4. Ensure the SO Description field is formatted for localized data returns.

So the first and second steps of localizing/renaming and delete two fields I am not going to get into.  You will see I have done this and edited the labels from the additional screen captures.

Changing the data for the two time fields is a little bit of a process.  The easiest way I have found is to copy a data element from another report that already has the formatting.  In this case the original Scheduled Offering Roster (CSV) had a SO date/time field that can be copied and edited for this purpose.  Simply open up the original roster report in a tab.  You will delete the SOSD and SOEDANDTIME fields from the custom report and paste in the SEGMENT_STARTDATE from the original report.

Right click on the field element and copy like below:

/wp-content/uploads/2016/04/12th_941700.jpg

You are going to navigate to you custom report, right click in both the fields and paste in this data element.

/wp-content/uploads/2016/04/13th_941705.jpg



Next you will want to right click on the SEGMENT_STARTDATE in the Start Date/Time column.  Navigate in the properties tab to Advanced and change the Content to SOSDANDTIME, and change the OPERATOR_COLUMNS field to SOSDANDTIME:TIMEZONE:DISPLAYTIMEZONE.


You will also repeat this process for the end time field, but instead of SOSDANDTIME you will substitute SOEDANDTIME.

The following picture is before the change:

/wp-content/uploads/2016/04/15th_941708.jpg

This is what it looks like after both changes:

/wp-content/uploads/2016/04/16th_941709.jpg

Finally you need to ensure that the localized date in the SO description is formatted correctly so that it will generate the right localized data when the report is run.  For example, if you run the report without doing this the SO Description might appear as: title.Item.47190fd9f383fa06c453e5ba3f18465e, however after you add the formatting it could be: Microsoft Word Tutorial Session in your English locale or MSFT Word Tutorial in your English (UK) locale.


The way to fix this issue is to add reportContext.getMessage(dataSetRow[“SODESC”], reportContext.getLocale()) into the expression field of the data binding for the SO Description.  This will replace what is already there: dataSetRow[“SODESC”].

You simply double click on the SODESC data element to bring up the data binding window and paste in the reportContext above to fix this issue, as pictured below:

/wp-content/uploads/2016/04/17th_941710.jpg

After completing this step you are ready to save, save to zip and import your report into the system for testing.  As I mentioned above this part of the process you should already be familiar with.

Some same data I generated in an LMS instance from the report looked like this:

/wp-content/uploads/2016/04/18th_941717.png

Conclusion:

There are some more things you could do to this report (which I will attach for you to edit/work with) that I would recommend to further your knowledge and experience.  This report simply brings in ID values for location and instructor.  It would be a good exercise for you to add in the tables for both of those ids and add in the description/names where applicable for these two pieces of data.

One thing to remember from my testing above is that you need to have your vjdbc code and your REPORT_DEVELOPER role assigned to you admin id you use in PRD.  I assumed for purposes of this post that most would have this information/setup.  To get the vjdbc for your staging instance you simply put in a ticket to SF and make sure to request the REPORT_DEVELOPER role be in your instance if it is missing.

This post is a good first step in creating your own custom reports with clean SQL that you can then build on rather than always trying to make the SF versions work.

I plan on adding more functionality to this report (including some work with custom columns) in the upcoming Part 3 of this series.

As always if you have questions or comments please feel free to add them into the comments or direct message me. 

For a copy of this report to import / work on please follow this link to my dropbox.

Thanks for your attention and feedback,
Dwayne Phillips – Veritas Prime

http://veritasprime.com

http://helpmycloud.com

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Bram Goolaerts

    Hi Dwayne,

    Great post! Very useful information for someone like me who’s still a bit new to PRD.

    With regards to the retrieval of descriptions, is it possible to somehow force the locale when running a report? I had a request to create different versions of the same report but with the retrieval of the descriptions in different languages (French, Dutch, English, …). I’m wondering if something like that is feasible. I was using so far the functionality you’ve used as well: “reportContext.getMessage(dataSetRow[“SODESC”], reportContext.getLocale())“. But can this also be used to force the retrieval of a specific Locale?

    Any thoughts?

    Thanks in advance.

    KR,

    Bram

    (0) 
  2. Rameshkumar Sethuramakrishnan

    Hi Dwayne,

    Thanks for sharing this useful information. I am new to the PRD. So far I have created few reports with just normal SELECT with JOIN statements using this tool.

    Have you ever used PL/SQL in your PRD? If yes, could you please share one example?

    Thanks & Regards,

    Ramesh

    (0) 
    1. Dwayne Phillips Post author

      Hi Rameshkumar-

      I have not really used that syntax since most of my SQL has come on the job through this old notation.  However if you go into a SF LMS instance and look at the newer quiz reports they use SELECT/JOIN.
      Thanks,
      Dwayne

      (0) 

Leave a Reply