Adding custom column in Schedule offering in Custom Plateau Report for LMS
This is my first blog post ?
In this blog post, I will share my experience of developing custom report in Plateau Report Designer. Recently my client came up with requirement of creating a custom report for schedule offering data which was not existing as part of standard report. I was given requirement from client to create new custom report for schedule offering with some fields from Schedule offering table and add custom column Remarks to display in the report.
We can download the closest report from existing standard report and create our own report. We need to ensure security requirements of the report by adding security fields for import in LMS to be successful.
LMS custom reports can be developed in Plateau Report designer. Once report is developed it can be imported by selecting import tab under reports.
Prerequisites for developing custom reports can be referred below,
- Basic Knowledge of LMS standard reports and Plateau Report Designer (PRD).
- Basic understanding of SQL Query.
- Required permissions to import and export reports from your SuccessFactors LMS instance.
- Open a ticket with SuccessFactors support to create/enable a connection between LMS, staging instance with the PRD Tool for more details to go through KBA 2171592.
- Report Developer Role needs to be created in the instance if it is missing.
- Download Latest version of PRD through LMS Instance or KBA 2318900. In this KBA I will be downloading from LMS instance.
- You will also need LMS Data dictionary to find tables names and column names (database column name), you can get it through KBA 2326249.
- Download Data Dictionary of LMS from help.sap.com
- For trouble shooting issues with PRD refer to KBA 2777238
- For custom Column in Schedule offering refer to KBA 2600769
Remarks is custom column in Schedule Offering as shown in screen shot below. Custom Column Number of Remarks is 10.This custom colulmn is maintained in table PA_SCHD_USER
We can see data of Remarks in extended summary in schedule offering.
As per KBA 2600769 I have used table PA_SCHED_USER for adding custom column in
Schedule offering report.
System use user_value from the PA_SCHED_USER of the schedule offering. Below columns are part of schedule offering table PA_SCHED_USER.
As COL_NUM for Remarks is 10 there is also random data in USER_VALUE for COL_NUM 99 as per below screen shot.
In table PA_SCHED_USER Custom Col has value 99 other than 10. In my query I want values of Custom Colum 10 and not 99 and display value if maintained for schedule offering else display value blank
Use below query to display custom column Remarks with actual value.
Here DECODE function will clear value of custom column other than 10 as we don’t want to display data which does not belongs to field Remarks.
For column which do not have Remarks maintained they should also appear in query with data maintained for Remarks.
Apart from the above scenario there might be more requirements from the customer, but one need to gain more knowledge of SQL Query and data dictionary of LMS to understand fields in tables to develop requirements. However, scenario that i have covered will help you to understand how to create custom report in LMS using Plateau Report Designer and SQL Query.
I hope this blog post will be helpful to gain more knowledge in SQL and also to handle complex reports in PRD.