/support/notes/service/sap_logo.png){kind=logo}
SAP Knowledge Base Article - PublicSymptom
Custom reports that display program completion percentage were using the GetProgramCompletionPercentage operator, which calculates the percentage at report runtime. This approach:
- Causes performance degradation for reports with large datasets
- Adds unnecessary computational overhead - Results in slower report generation times
This guide documents the changes required to fix program completion percentage calculation in custom reports. The fix addresses performance issues by using the pre-calculated PV_STUD_PROG_SEC_ENTRY_COMPL_DATA database view instead of calculating completion percentage at runtime using the GetProgramCompletionPercentage operator. This change can only be implemented on 2H 2025 versions of SuccessFactors Learning.
Note: SAP Support does not assist with modifying custom report queries, if you need assistance with updating any existing reports with these changes, please reach out to a report developer.
Environment
SAP SuccessFactors Learning
Resolution
The solution is to use the PV_STUD_PROG_SEC_ENTRY_COMPL_DATA database view to retrieve pre-calculated completion percentages directly from the database layer.
Prerequisites:
Before applying this fix, ensure:
- Your report uses program-related data from pa_stud_program table
- Your report displays program completion percentage
Step-by-Step Implementation Guide:
Step 1: Identify SQL Queries in Your Report
In ProgramStatusCSV.rptdesign, there were 2 main data sets:
- ProgramStatusDataSet - Main dataset without activity details
- ProgramStatusWithActivity - Dataset including program activities
Step 2: Modify SQL Queries - Add Table and Column
For EACH SQL query in your dataset, you need to make the following changes:
2.1 Add New Column to SELECT Statement
Location: In the SELECT clause
Action: Add spsec.completion_percentage as a new column
Before:
SELECT DISTINCT pc.seq_num,
p.program_id,
cpnt.cpnt_title AS program_title,
-- ... other columns ...
p.START_DTE,
sp.STUD_PROGRAM_SYS_GUID
FROM pa_stud_program sp,
-- ... other tables ...
After:
SELECT DISTINCT pc.seq_num,
p.program_id,
cpnt.cpnt_title AS program_title,
-- ... other columns ...
p.START_DTE,
sp.STUD_PROGRAM_SYS_GUID,
spsec.completion_percentage -- NEW LINE ADDED along with comma in the above line
FROM pa_stud_program sp,
-- ... other tables ...
2.2 Add New Table to FROM Clause
Location: In the FROM clause
Action: Add pv_stud_prog_sec_entry_compl_data spsec table
Before:
FROM pa_stud_program sp,
pa_program p,
pa_student s,
pa_stud_cpnt pc,
ps_program_type pt,
pa_cpnt cpnt
WHERE -- ...
After:
FROM pa_stud_program sp,
pa_program p,
pa_student s,
pa_stud_cpnt pc,
ps_program_type pt,
pa_cpnt cpnt,
pv_stud_prog_sec_entry_compl_data spsec -- NEW LINE ADDED along with comma in the above line
WHERE -- ...
2.3 Add JOIN Condition to WHERE Clause
Location: In the WHERE clause
Action: Add outer join condition to link the new view
Before:
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
AND cpnt.CPNT_TYP_ID = p.CPNT_TYP_ID
-- ... other conditions ...
AND pt.PROGRAM_TYPE_ID = p.PROGRAM_TYPE
/** and p.program_id in [ProgramSearch] ...
After:
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
AND cpnt.CPNT_TYP_ID = p.CPNT_TYP_ID
-- ... other conditions ...
AND pt.PROGRAM_TYPE_ID = p.PROGRAM_TYPE
AND sp.STUD_PROGRAM_SYS_GUID = spsec.STUD_PROGRAM_SYS_GUID(+) -- NEW LINE ADDED along with comma in the above line
/** and p.program_id in [ProgramSearch] ...
Important Notes: - The (+) syntax indicates an outer join - Use this to ensure programs without completion data are still displayed
Step 3: Apply Changes to ALL Query Variations
The ProgramStatusCSV report had 4 different SQL queries that needed modification:
- Query 1: Programs NOT completed (without activities)
- Query 2: Programs completed (without activities)
- Query 3: Programs NOT completed (with activities)
- Query 4: Programs completed (with activities)
Action Required: Repeat Step 2.1, 2.2, and 2.3 for EACH query in your report.
Step 4: Update Data Bindings - Add Column Definition
After modifying the SQL queries, you need to add the new column to the dataset's result set definition.
4.1 Locate the Data Binding Section
In the XML structure of your .rptdesign file, find the <list-property name="computedColumns"> section for each affected dataset.
4.2 Add COMPLETION_PERCENTAGE Binding
Before:
<structure>
<property name="name">PROGRAM_TYPE_LABEL</property>
<text-property name="displayName">PROGRAM_TYPE_LABEL</text-property>
<expression name="expression" type="javascript">dataSetRow["PROGRAM_TYPE_LABEL"]</expression>
<property name="dataType">string</property>
</structure>
<!-- End of list -->
After:
<structure>
<property name="name">PROGRAM_TYPE_LABEL</property>
<text-property name="displayName">PROGRAM_TYPE_LABEL</text-property>
<expression name="expression" type="javascript">dataSetRow["PROGRAM_TYPE_LABEL"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">COMPLETION_PERCENTAGE</property>
<text-property name="displayName">COMPLETION_PERCENTAGE</text-property>
<expression name="expression" type="javascript">dataSetRow["COMPLETION_PERCENTAGE"]</expression>
<property name="dataType">string</property>
</structure>
<!-- End of list -->
Before:
<structure>
<property name="name">ENTRY_COMPL_DTE_1</property>
<text-property name="displayName">ENTRY_COMPL_DTE</text-property>
<expression name="expression" type="javascript">dataSetRow["ENTRY_COMPL_DTE"]</expression>
<property name="dataType">date-time</property>
</structure>
<!-- End of list -->
After:
<structure>
<property name="name">ENTRY_COMPL_DTE_1</property>
<text-property name="displayName">ENTRY_COMPL_DTE</text-property>
<expression name="expression" type="javascript">dataSetRow["ENTRY_COMPL_DTE"]</expression>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="name">COMPLETION_PERCENTAGE</property>
<text-property name="displayName">COMPLETION_PERCENTAGE</text-property>
<expression name="expression" type="javascript">dataSetRow["COMPLETION_PERCENTAGE"]</expression>
<property name="dataType">string</property>
</structure>
<!-- End of list -->
Repeat this for BOTH data sets: - ProgramStatusDataSet - ProgramStatusWithActivity
Step 5: Update Report Display Elements - Change Operator
This is the most critical step where we switch from runtime calculation to using the pre-calculated value.
5.1 Locate the Completion Percentage Display Element
Find the data element or text element that displays the completion percentage. Look for properties like: - OPERATOR_NAME: GetProgramCompletionPercentage - OPERATOR_COLUMNS: STUD_PROGRAM_SYS_GUID
5.2 Change Operator Properties
Before:
<property name="SHOW_INPUPT">true</property>
<property name="OPERATOR_COLUMNS">STUD_PROGRAM_SYS_GUID</property>
<property name="OPERATOR_NAME">GetProgramCompletionPercentage</property>
<property name="whiteSpace">normal</property>
<property name="contentType">auto</property>
<text-property name="content"><![CDATA[[COMPLETION_PERCENTAGE]]]></text-property>
After:
<property name="SHOW_INPUPT">true</property>
<property name="OPERATOR_COLUMNS">COMPLETION_PERCENTAGE</property>
<property name="OPERATOR_NAME">FormatPercentageOperator</property>
<property name="whiteSpace">normal</property>
<property name="contentType">auto</property>
<text-property name="content"><![CDATA[[COMPLETION_PERCENTAGE]]]></text-property>
Key Changes: - OPERATOR_COLUMNS: Changed from STUD_PROGRAM_SYS_GUID to COMPLETION_PERCENTAGE - OPERATOR_NAME: Changed from GetProgramCompletionPercentage to FormatPercentageOperator
Why? - Previously, the operator took STUD_PROGRAM_SYS_GUID as input and calculated the percentage - Now, it takes the already-calculated COMPLETION_PERCENTAGE and just formats it
Apply this change to ALL locations where completion percentage is displayed (in ProgramStatusCSV, there were 2 locations).
Complete Change Summary
Changes Per Dataset (Apply to Each):
| Step | Action | Count in ProgramStatusCSV |
|---|---|---|
| 1 | Add spsec.completion_percentage to SELECT | 4 queries |
| 2 | Add pv_stud_prog_sec_entry_compl_data spsec to FROM | 4 queries |
| 3 | Add JOIN condition to WHERE clause | 4 queries |
| 4 | Add COMPLETION_PERCENTAGE data binding | 2 datasets |
| 5 | Change operator from GetProgramCompletionPercentage to FormatPercentageOperator | 2 display elements |
Database View Information
View Name
PV_STUD_PROG_SEC_ENTRY_COMPL_DATA
Key Column
completion_percentage - Pre-calculated percentage value
Join Key
STUD_PROGRAM_SYS_GUID - Links to pa_stud_program.STUD_PROGRAM_SYS_GUID
View Purpose
This view provides pre-calculated program completion percentages, eliminating the need for runtime calculation.
Quick Reference Card
SQL Changes (for each query):
- Add to SELECT clause: spsec.completion_percentage
- Add to FROM clause: pv_stud_prog_sec_entry_compl_data spsec
- 3. Add to WHERE clause: AND sp.STUD_PROGRAM_SYS_GUID = spsec.STUD_PROGRAM_SYS_GUID(+)
XML Changes:
<!-- Add data binding: -->
<structure>
<property name="name">COMPLETION_PERCENTAGE</property>
<text-property name="displayName">COMPLETION_PERCENTAGE</text-property>
<expression name="expression" type="javascript">dataSetRow["COMPLETION_PERCENTAGE"]</expression>
<property name="dataType">string</property>
</structure>
<!-- Change operator properties: -->
<property name="OPERATOR_COLUMNS">COMPLETION_PERCENTAGE</property>
<property name="OPERATOR_NAME">FormatPercentageOperator</property>
Appendix: Example Diff
Below is an example of the exact changes for one query:
SELECT DISTINCT pc.seq_num,
p.program_id,
cpnt.cpnt_title AS program_title,
-- ... other columns ...
p.START_DTE,
- sp.STUD_PROGRAM_SYS_GUID
+ sp.STUD_PROGRAM_SYS_GUID,
+ spsec.completion_percentage
FROM pa_stud_program sp,
pa_program p,
pa_student s,
pa_stud_cpnt pc,
ps_program_type pt,
- pa_cpnt cpnt
+ pa_cpnt cpnt,
+ pv_stud_prog_sec_entry_compl_data spsec
WHERE p.PROGRAM_SYS_GUID = sp.PROGRAM_SYS_GUID
AND cpnt.CPNT_TYP_ID = p.CPNT_TYP_ID
-- ... other conditions ...
AND pt.PROGRAM_TYPE_ID = p.PROGRAM_TYPE
+AND sp.STUD_PROGRAM_SYS_GUID = spsec.STUD_PROGRAM_SYS_GUID(+)
Keywords
program, completion, calculation, percentage, custom, report, performance, guide, correction, ptch-48629, programstatuscsv , KBA , LOD-SF-LMS-CRI , Custom Report Issues , Problem
/support/notes/service/facebook.svg)
/support/notes/service/twitter.svg)
/support/notes/service/youtube.svg)
/support/notes/service/linkedin.svg)
/support/notes/service/instagram2.svg)