2320765 - LMS Standard Reports Knowledge Support and Tips

REPORTS KNOWLEDGE SESSION FOR CUSTOMERS, PARTNERS AND SAP PRODUCT SUPPORT

1. Overview LMS Reports

1.1. What is reporting?

1.2. Who uses it and how is it used?

1.3. What types of reports are available?

 

2. Report Files in the LMS

2.1. Overview of Files

2.2. Report Design File

2.3. Report Library File

2.4. Themes Library file

 

3. Permissions

3.1. Workflows

3.2. Domain Restrictions

 

4. Troubleshooting

4.1. SQL notes

4.2. Time zones

4.3. Timeouts

4.4. System Reports vs. Custom Reports

4.5. Importing / Exporting LMS reports

4.6. Various Issues / Errors seen

5. Plateau Report Designer
6. Knowledge Base Articles created for Reports

1. Overview Reports

1.1. What is Reporting?

Reporting is an important piece of the LMS because it allows customers to gather data on users participation in Learning courses, completions, assignments, the users attributes, etc.

The main function of Reporting in the LMS is to monitor users compliance to assigned and/or required learning.  Customers can also monitor and manage users in the system as well as report on other entities such as Assignment Profiles (the main way learning is assigned in the LMS).

 

1.2. Who uses it and how is it used?

How is it used?

Reporting is an important piece of the LMS because it allows customers to gather data on users participation in Learning courses, completions, assignments, users attributes, etc.

Reports can be ran two different ways, Online or as a Scheduled Job.  Online reports are ran on a separate reports server node (chunk of the server, node name will contain ‘r’ like ‘’pcappp05r02’) and are usually ran when not much data is expected to be returned.  For example, a User running a report on just themselves would be online.

Large reports where the data returned could potentially be massive should be scheduled as a job and the system will warn you if a scheduled job is needed.

Reports can also be scheduled so that an admin can specify the exact criteria on what they would like reported and have it generated and delivered to them on a daily, weekly, or monthly basis.  These are called recurring reports.  All reports scheduled as a job are ran on the background jobs node (node name has ‘b’, like ‘pcappp05b02’) in SuccessFactors hosted instances.

Who used it?
Reporting in the LMS is a large module of the LMS and is used by every customer.  LMS reporting can be categorized in 3 main groups based on who will be running them:

Users can run reports on themselves from their Home page and can obtain data on what they have requested, been assigned and completed, their attributes, etc.

Supervisors have the same ability as users, though they can also run reports on their subordinates, direct and indirect, to ensure their employees have the correct assignments and are compliant.

Admins with the necessary permissions can run reports on almost all entities in the LMS and are the most important of the 3 groups.  They base company wide assignments on the data obtained from reporting and take the results very seriously since their companies training and compliance depends on it.

1.3. What types of reports are available?

Below are some examples of reports that users, supervisors, and admins would frequently run:

• Reports on users attributes such as name, address, domain, contacts, organization, etc.
• Information on users assignments, completed and not completed, enrollment, attendance, as well as their progress on any items they started.
• Information on items, scheduled offerings, curricula, programs etc. so that admins can keep track of what data is currently in their LMS.
• Data on entities within the LMS such as Assignment Profiles, approval processes, system requests.
• Reports on historical data for various entities such as items and offerings, where admins can see who/when/and what was changed.
• Progress/Results on any content items users participated in as well as results of any post item surveys or LMS exams (PQE exams) that were taken by users.
• Reports on Customers Usage of the LMS.

Often requested but are not currently available are reports that can provide supervisor hierarchy information and show a supervisors direct and indirect subordinates information, show users session information, and reports on all background jobs ran in the LMS.

2. Report Files in the LMS

2.1. Overview of Files

Reports in the LMS are imported and exported as .zip files.  In these zip files are the report design file, the report library, and sometimes the report themes library file if the report is NOT in CSV format.  Reports are generated in the below formats:

2.2. Report Design File (.rptdesign)

This file contains all of the formatting (fonts, sizes, colors, etc) for the HTML and PDF versions of the reports (does not apply to .CSV).  It also contains the logo for the Header of the report in the below xml property:

 <list-property name="images">       
     <structure>           
        <property name="name">successfactors_SAP_R_fc_pos1.png</property>           
        <property name="type">image/png</property>           
        <property name="data">

Below the data section are the raw values PRD creates and represents the image itself:

 

3. Permissions

3.1. Workflows

Reports in the LMS require various permissions, or workflows that will allow users or admins to search for, view, edit, delete, and most importantly, run reports.

EACH user side report and custom report has its own workflow, so admins can restrict others from being able to view (see) and run reports on an individual basis.  Once a report is imported, a corresponding workflow is created based on the reports name, ie import ‘Class Roster’ report, the ‘Run Class Roster Report’ workflow is created.

When troubleshooting Report issues related to workflows, you will want to ensure that the workflow exists in the table PS_WORKFLOW and ensure that the workflow_id has no trailing spaces (the LMS will create them if the admin imported a report name with an extra space).

Below are some of the more important workflows (and sections) related to reports.

System admin > Security > Role mgmt > search for your role > workflow tab

User role (users/Supervisors)

• Access Reports
• any report specific permission

Admin role

Expand System Admin:

• View All Background Job
• View User Background Job

Expand Reports:

• Delete Background Report Job
• Access Recurring Report Job (needed in order to delete or edit scheduled reports)
• Edit Reports
• Import/Export Reports
• Publish/Unpublish Reports
• Search Reports (can’t view reports as an admin without)
• any report specific permission

3.2. Domain Restrictions

Each workflow, or groups of workflows, can restrict the data returned from reports by Domain using Domain Restrictions.  For example, if you apply a domain restriction to the ‘Run Curriculum Status Report’ workflow for the ‘Virginia’ domain, the results will only return data that is in that ‘Virginia’ domain, or any subdomains if the option is chosen on the restriction.

This is often used so that if a company choses to use domains like geographical regions, they can setup admins roles and restrict the workflows within the role to only return data on LMS entities related to the region they are in.

Its important to remember that, in the scenario where an admin would have more than one role assigned to him/her and that these roles contain the same workflows but might be set up with or without restrictions, certain rules apply.
More details can be found in this KBA: Domain restrictions behavior.

 

If an admin is questioning why the data returned for them from a specific or group of reports is much less than expected, a domain restriction on the reports workflow (reminder that each report has its own workflow for view/run) is often the cause.

 

In this example, this restriction will only show the data in the RTL-BA-SE domain, and since ‘Include Sub Domains’ is checked, it will show the data in the children domains below the RTL-BA-SE domain

Domain Restrictions in terms of the rptdesign file are found within the query.

If a report is to return data only on users that are in the same domain as on the domain restriction on the workflow assigned to the admin running the report, the security tag must be specified in the report query to be able to be ran in the LMS.

For example, if the report is on user data, the user data information is found in the PA_STUDENT table in the database, So the tag [security:pa_student] must be within the query so that if a domain restriction is in place, it restricts the data returned in that table to only the domains specified in the domain restriction applied to the workflow(s).

 

4. Troubleshooting

4.1. Basic SQL notes

Basic SQL notes:
SELECT - The SELECT statement is used to select column data from a database.  The columns within the tables are specified in the SELECT statement.

FROM - The FROM clause will list all of the tables that the data will be queried from.  This is the most important clause in terms of seeing which tables the query in the rptdesign file will be pulling from

WHERE - The WHERE clause is used to extract only those records that fulfill a specified criterion. The FROM clause will list all of the tables that the data will be queried from.

Aliases are used in SQL when two separate tables contain the same column and tables need to be joined. So table PA_STUDENT contains the column DOMAIN and PA_STUD_CPNT also has the DOMAIN column.  To specify a specific one, you would use an alias like the ‘a’ and ‘b’ example below:

SELECT a.fname, a.lname, b.cpnt_id, b.domain_id
FROM PA_STUDENT a, PA_STUD_CPNT b
WHERE a.stud_id = b.stud_id;

This would return users first name, last name, item ID assigned, and domain the item is in.  The ‘a’ is referring to PA_STUDENT and the ‘b’ is referring to PA_STUD_CPNT.  Since two tables are being queried, the WHERE clause (WHERE a.stud_id = b.stud_id;) joins them both by a common column (stud_id) that they both contain (ie it connects the two tables).

4.2. Timezones

An LMS database will records involving DATE values in the timezone the database is set to.  This is found in the PA_APP_ADMIN table in the DB_TIMEZONE column.

However, the LMS will return report output based on the time zone set in the admins time zone preference. If an admins’ time zone preference found in the Preferences section of the admins account is America/New York, the output will reflect DATE values in that timezone.

The only exception to this currently is the audit reports.  They will display output in the timezone set in PA_APP_ADMIN.DB_TIMEZONE column (ie the timezone where the database is installed).

4.3. Timeouts

Reports will timeout when either the query is poorly written or too much data is being queried.

Some questions that need to be considered are…

• Does this happen with all reports, some, or a specific report?
• If it’s a specific report, does the same result happen when ran online (very small amount of data)? This will help narrow down that its an issue with the potential results.
• Does this happen when ran as a Scheduled Job (jobs are meant for data intensive reports)?
• How much data is involved?  Does the report run fine on less criteria/data returned?
• What is the STATUS (database column) in PA_SCHEDULED_JOB? Is it 'Initializing', or did the report begin to generate data (ie data present in progress columns) and then timeout?

Timeout settings:

There are 3 timeout settings for reports.  It is important to know which is being reached before troubleshooting to determine why.

JobProgressTimeout is found on the hosted server in the .tms folder (SAP internal only).
It is an Operations configuration that is usually reached when the report job is initializing, or sizing up the job it’s about to begin. Usually if its reached, it is a very large job and therefore a possible system limitation put in place to avoid any performance issues with the node (high CPU, disk usage, etc.)  This timeout setting is the last fail safe for the LMS to basically cancel a job so that the applications other nodes’ performance is not affected.

Timeout Settings (Found in System Admin > Configuration > System Configuration > REPORT_SYSTEM):

# Timeout period for running reports online (not as a scheduled background job) in seconds. If a report takes longer than this
# to generate, it is automatically timed out.
onlineTimeoutPeriod=300
 
# Timeout period for running reports as a scheduled background job in seconds. If a report takes longer than this
# to generate, it is automatically timed out.
backgroundTimeoutPeriod=1000

If the value in the Timeout error is neither of the two above (300,1000), then the Ops derived JobProgress Timeout setting was reached.

It is not advisable to increase "backgroundTimeoutPeriod=" more than 600 seconds as it will hit the server side timeout setting which is by default 600 seconds.

4.4. System Reports vs. Custom Reports

Usually Standard reporting issues and/or errors are due to either a incorrectly developed query that’s returning incorrect data, a change in the database structure such as a new column or table, or most importantly, a change in the .rptlibrary file. Customers/Partners often change the library to accommodate custom reports, and either incorrectly alter a parameter or rule, or remove others that standard reports require entirely.

Compare the report in questions’ .rptdesign and .rptlibrary files to a standard/unaltered to determine if they are custom or standard.

Often issues with a group of reports are related to changes in the report library since ALL REPORTS USE THE SAME LIBRARY. Beyond Compare is great comparison tool to confirm any changes in the Library or Design file of the customers vs. a standard/unaltered report.

4.5. Importing / Exporting LMS reports

When customers want to modify existing reports using PRD, they will need to export the report from the LMS and save the .zip file.

When customers want to add reports back or add new ones, they must import them back into the LMS and choose the Target Users (User, Supervisor, Administrators). Overwriting the library can potentially erase any changes made to the library and should be taken into consideration if there is no potential issue with the current library. Its important to note that only published reports can be ran in the LMS and the report must be published once imported to become active.

4.6. Various Issues / Errors seen

 Commun Errors:

• ‘Required parameter XXXXX not set’ – This is an issue with the library and a parameter was either incorrectly altered, added, or removed.  Overwriting the library back to the default (a server restart may also be needed) will resolve this error.
• Error encountered in executing the query java.sql.SQLSyntaxErrorException: ORA-00904: “XXXX".“XXXX": invalid identifier – This usually means there was a change in the DB tables’ columns and a column is missing or altered.  These usually end up being defects if the report is standard
• ORA-02291: integrity constraint (FPL_STAGE_TMS.FK_CPNT_XXX) violated - parent key not found – This is usually due to a child record in the database that either exists and shouldn’t, or doesn’t exist and should.  Its best to look at all tables related to one in question and validate related data.  It could also be due to an un-compiled database Trigger (Would be an Operations CO ticket) or bad data in the database that defies the constraint in place. (ie column is 2000 bytes, a value is 2500 and should not be present).
• Error: This report is not available. Please contact your administrator for assistance.  This error that the report is not published.  If  an error is received when running a system report such as the Certificate of Completion Learning Event (System) , its best practice to first check if the report is published or not.  In older versions a java NullPointerException was thrown, and in recent and current versions, the report is simply removed from the UI preventing it from being ran.

 

 

5. Plateau Report Designer

 Plateau Report Designer is a tool that customers will use to create their own custom reports or modify existing reports so they can import and publish them in the LMS.  Mostly, they will be using admin side reports but customers can also overwrite existing LMS reports on the user side.

If a customer wishes to use their own report, say a custom Certificate of Completion report, they will need to import their custom report .zip file with the same exact name as the standard report .zip file and then un-publish the standard, and publish their newly imported custom report so that it overrides the standard.  The LMS will then use their custom report instead.

When Customers/Partners request the PRD connection this is setup by our internal Operations team and managed by them.

Any issues with the connection that can be also replicated on our side will be troubleshot by our internal Operations Team.

Issues that  we cannot replicate maybe related to customers networks and/or proxys setup and its best to verify on their end if they can connect or not from an outside network.

Its important to KNOW that SuccessFactors will ONLY setup a PRD VJDBC connection for staging environments since an open port is required and Ops will not open ports in Productions.

To login to PRD, all that’s needed is an active PRD connection (URL), class set to ‘VirtualDriverBirtWrapper’ and an admin account with their working credentials.

Lastly, the admin must have the ‘REPORT_DEVELOPER’ role assigned to them in the LMS.

Example of a valid PRD connection:

6. Knowledge Base Articles created for Reports

 

KBA Number	Title
2318900	Plateau Report Designer (PRD) Tool version 4.4.1.2
2318510	Unknown user error when connecting to PRD
2074219	How to configure PRD to connect to the Hosted Staging database through a Proxy
2104424	How to connect to SuccessFactors Learning stage instance of hosted customer in Plateau Report Designer?
2162079	How to setup the Report Export Job
2162837	How to remove the reccurring report jobs created by an admin who has left the company?
2186196	How do I export the zip file for certificate from Learning to Plateau Report Designer?
2192625	Pipe export option in the Plateau Report Designer (PRD)
2246293	Looking for a list of standard reports available in LMS
2277602	Report for catalog and assignment profile association
2269282	What tables are used for a user's custom columns
2297973	Error when importing a report into LMS
2320177	HTTP response code 504 error when trying to view 'Preview Results' within PRD
2303225	Program Title and description data fields null in custom reports
2236274	How to import a report
2238848	How to export a report
2199407	Scheduled Report in LMS not displaying in Report Jobs Tab of LMS
2149942	Learning: How to run a report on multiple entities easily and dynamically?
2286760	LMS reports are aborting

Standard list of reports can be found at http://wiki.scn.sap.com/wiki/x/jxbMFw

More KBA's related to Reports on the next link.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

2409988 - How to delete LMS reports from the system

LMS Reports; PRD; LMS report design; It is not advisable to increase "backgroundTimeoutPeriod=" setting under REPORT_SYSTEM configuration file more than 600 seconds. , KBA , LOD-SF-LMS-REP , Reporting Data , LOD-SF-LMS , Learning Management System , LOD-SF-LMS-PRD , VJDBC Setup or Connection Issues , How To

SAP SuccessFactors Learning all versions