The Missing Reports: Item Catalog Information in SF LMS implemented through the Plateau Report Designer

[daphil01]

Overview:

There are over 130 standard reports that come with the Successfactor LMS implementation, but there are a few areas where some simple data is just not available.  In my Missing Reports series I will be going through the basic steps of how to create and implement some custom reports in the Plateau Report Designer that are not available and often asked for by customers.

An area I have been asked to provide data on for a few customers has been a simple listing of items and the catalogs they are in.  It seems simple enough, but there is not really a good standard report that meets the needs without lots of extra data the customer is usually not looking for.

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.

Solution Background:

The report will need the following fields:

1. Item ID


2. Item Title


3. Item Type ID


4. Item Revision Date


5. Item Domain


6. Catalog ID


7. Catalog Description


8. Catalog Domain

This report is very basic, but once you have this basic form you can extend it easily to bring in things like item cost center, item contact, catalog sku, and even if the item will be set to display in the catalog.

The tables that you will need to get to these fields are:

1. PA_CPNT -> Basic item information


2. PA_CATALOG_ITEM -> Table to relate the catalog sku into for the catalog info from the PA_CPNT table


3. PA_CATALOG -> Table you join into from PA_CATALOG_ITEM to get the description of the catalog

The SQL code you will need is below and commented:

SELECT 

pc.cpnt_id,            --Item ID

pc.cpnt_title,         --Item title

pc.cpnt_typ_id,        --Item Type ID

pc.rev_dte,            --Item Revision Date

pc.dmn_id as itmDMN,   --Item Domain ID

pcat.catalog_id,       --Catalog ID

pcat.catalog_desc,     --Catalog Description

pcat.dmn_id as ctlgDMN --Catalog Domain



FROM

pa_cpnt pc,            --Main Item Table

pa_catalog_item pci,   --Table relating Item to Catalog

pa_catalog pcat        --Table



WHERE pc.catalog_sku = pci.sku(+) -- Joining the Item Table to the Catalog Item table through 

                                  --the catalog_sku

AND pci.catalog_id = pcat.catalog_id(+) --Joining the catalog item table to the main catalog

                                        --table through the catalog_id

AND pc.cpnt_typ_id  not in ( 'SYSTEM_PROGRAM_ENTITY') --This statement removes all the Program type 

                                                      --items that are stored in the item table

                                                      --that you do not need for this report



/** AND (pc.CPNT_TYP_ID,pc.CPNT_ID,pc.REV_DTE) in [ItemSearch]

    AND pci.catalog_id in [CatalogSearch] */ 



/** AND [security:pa_cpnt pc] */ --This report uses item for security



ORDER BY pc.cpnt_id,pc.rev_dte,pcat.catalog_id

--This orders the report by unique item and then catalog 

As you can see from the code the security for the report is based on  PA_CPNT and the only report parameters used for filters are CatalogSearch and ItemSearch.

Whenever I create a new report I always chose a similar report that already exists as a template to edit.  For this report I recommend the Item Status (CSV) report. This report will already have many of the report parameters you need for this type of report, and with a few simple steps you can edit the SQL, report parameters and setup the new table in the report layout.

Actions:

The steps involved in this process are:

1. Download the report you will use a template (Recommend User Item Status here) 


2. Edit the SQL code to pull in the new fields


3. Add the new SQL columns to the downloaded 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

Normally I start with step 3, but for this report I will go through the steps of taking a template report and converting it to be used for this new custom report.

The first step in this process is to open up the template report, save it as the new name and then setup your jdbc connector so when you update your SQL you can test it.

Next you will open up the data set (ItemStatus in this case) and copy the new SQL code over the existing code and preview that the code executes correctly.

This is a sample of me doing this :



Once you preview and there are no errors you will need to edit the report parameters.  You will need to remove: MaskStudentID, UserSearch, CompletionStatusSearch, FromDate, and ToDate.

Here is the before and after of this removal:

Before-                                                                              After-

                       

 

Next you will need to add CatalogSearch from the Report Parameters under Shared Resources in the Resource Explorer panel.

You can see from these 2 images that you will need to drag CatalogSearch from the Resource Explorer panel to the Outline Panel:





Next you will need to click on the table tab that already exists in the template and delete the table that already exists.

This is what that should look like:



 

Once you have deleted the table you will drag the ItemStatus dataset with your new SQL over to the layout area and click the select all fields and then select ok.

Here are those steps:



You should now have all your new fields on the report layout.  All that is left to do is change the labels to your specific labels in your report, or the text you want.  For the purposes of this blog I am going to leave the table field names. 

The last important step is to double click on the CPNT_TITLE data field and edit the expression to reportContext.getMessage(dataSetRow["CPNT_TITLE"], reportContext.getLocale())

This will allow for the localized data to be presented in the locale the user is in instead of giving a code as the title.  For more information please see the PRD documentation.

You can see how I did this below:

 

Finally, you will need to save, zip and import the report into your LMS instance.  You should be familiar with this process.

Below is a verification run I did to make sure all of my steps and the report file was valid. 



I will provide the finalized file for you below in the conclusion.

Conclusion:

As always, if you have other solutions or issues with this post, please leave them in the comments.  I will be sharing more missing reports in the future.  I also plan to extend this report in another of my common report tasks.  I will be putting all of the catalogs concatenated into on field per item, and separated by a colon.  I hope this provided a good example of how to get basic catalog information from items, and helps you provide this kind of report/solution to your customers.

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

http://veritasprime.com

http://helpmycloud.com