Accomplishing Common Reporting Tasks with Plateau Report Designer (CSV Aggregate Fields) – Part 5

[daphil01]

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 catalogs associated with items.  You can download the report import file here to follow along or for use.

A common issue I have run in to is trying to generate a single field of data that contains multiple fields worth of data or a list.  In the report that was created for the last blog we created a list report that shows all of the catalog ids and descriptions for every item.  In order to see every catalog id/description for one item you would need to do a data filter in excel for a particular item and then the next column would have all of the catalog information.

I will discuss how to change that report into one where for each row there is only one item id, and an aggregate field is create that contains all of the catalog ids/descriptions in the same row.  The catalog id's and descriptions will be separated by a semicolon (:) and will be in the format: catalog id1(catalog description1):catalog id2(catalog description 2):...

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 4.4.2(PRD.)

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. Download and open the Item Catalog report from the last blogs post.


2. Remove the fields relating to catalogs (id, description, and catalog domain.)


3. Add in a group to the detail row using item id, type and revision date.


4. Move all the data elements up to the group header row and delete the detail row and group footer row


5. Add in an aggregation field based on the grouping.  The aggregate field will be the String data type, and the separator will be ":".  The Aggregation function you will choose is Concatenate.  You will also want to filter where there are no null values in the catalog id.  Make sure this aggregation is based on the previously defined grouping for items and not the entire table.


6. Import the report to the LMS Instance and test.

For this blog I have created a guided video that walks you through the entire process.  It is hosted on youtube and I recommend you use 1080p viewing for it.  Some of the beginning audio got chopped off, but it was a review of the scenario from above.  This video goes from editing all the way through import of the report.



 

Conclusion:

This solution can be used for many issues you will run into with LMS reporting.  If you needed a report with Subject Areas per item it would also be applicable.  Another use of the aggregation would be an efficient way to have a total number generated via using Count instead of Concatenate.  You can easily generate information like total number of subject areas per item.

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.

 

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

http://veritasprime.com

http://helpmycloud.com