Overview
This blog post is to address a specific scenario that I encountered and found quite interesting. The requirement was to pull the data of only those classes whose Class start date would be one day from today. So we can say this is a kind of dynamic report wherein, if the report is scheduled or run everyday, then it would pull the data of the classes that are set to start the next day. This report can be used for Integration purposes, or any other requirements as suited appropriate.
Though the requirement is very simple and is easy to achieve, but as functional consultants we sometimes have limited knowledge of SQL and I thought that this blog might help those who might encounter same or similar scenarios.
Prerequisites
- Conceptual knowledge of SAP SuccessFactors Learning Management System
- Knowledge of SQL as suited for Plateau Report Designer
- The REPORT_DEVELOPER role linked to the admin ID used to develop the report
- Permission to import reports into the system
Development
The simple logic to develop this report would be to compare the Current date with the Class Start Date and pull only those Class data whose Start Date is one day from the Current date.
For this purpose we would pull the data of Class Start Date from the table
PS_SCHD_RESOURCES. The current date would be pulled using the '
sysdate' field in the query. we would pull the following fields -
- User ID
- Class ID
- Item ID
- Item Type
- Class Start Date
- Class End Date
The SQL query that I used is -
select
s.stud_id,
s.schd_id,
a.act_cpnt_id,
a.cpnt_typ_id,
b.start_dte,
b.end_dte,
sysdate
from
pa_enroll_seat s,
pa_sched a,
ps_schd_resources b
where
s.schd_id = a.schd_id(+)
and s.schd_id = b.schd_id(+)
and b.start_dte >= sysdate+1
and b.start_dte < sysdate+2
/** and [security:pa_student] */
I am not adding any extra filter for sake of convenience. I have just added the report parameter to extract the report in CSV format and upload it to the system.
Now if you would take a look at the above code snippet, you will find that we have specifically mentioned the condition that the start date/time be greater than or equal to one day from the current date/time but less that two days from the current timestamp of the system. So basically we are asking the system to pull only the data only for the classes which starts the very next day.
Two classes were created with start date the immediate next day, but one class starting from 9:00 AM in the morning and the other starting from 5:00 PM in the evening with reference to my current timezone.
(Please make sure to register at least one user to be able to pull the report for the above given code snippet)
Now if we pull the report and the general expectation would be that the system would pull the data for both the above classes. But the report pulls that data for only the second class as can be seen below :
Cause
The general cause can be tracked down to the runtime of the report. The system along with the start date of class also consider the start time of the class. Now lets assume the report is run on 9th of March at 12:00 PM. That would essential mean that the system would consider only the data for those classes whose start time would be equal to or later than 10th of March 12:00 PM. And that is why the data for the class with the start time of 10th March at 9:00 AM is not considered.
Resolution
To counter this setback, we need to eliminate the start time consideration and compare only the start dates. For that we need to convert the dates into string format and match the start date exactly with next day's date. The code that is used would be as follows :
select
s.stud_id,
s.schd_id,
a.act_cpnt_id,
a.cpnt_typ_id,
b.start_dte,
b.end_dte,
sysdate
from
pa_enroll_seat s,
pa_sched a,
ps_schd_resources b
where
s.schd_id = a.schd_id(+)
and s.schd_id = b.schd_id(+)
and to_char(b.start_dte,'DD-MM-YYYY') = to_char((sysdate+1),'DD-MM-YYYY')
/** and [security:pa_student] */
Now modifying the query and uploading the report into the system, when we pull the report, the system only compares the date and not the time associated with it. This time the report has both the data irrespective of the start time.
Conclusion
Of the above two solutions you can use any one as per your requirement scenario, but since I had a hard block that the report I developed was scheduled to run midnight daily and thus my report was missing out on data that were expected to be in the report.
The above logic can be used to develop reports where -
- For Integrations purpose where the LMS needs to send data on a daily basis.
- The report data can also be used as an instance delivery report where report delivers the data of classes set to start from a given set of days from the current date.
Please do let me know your thoughts in the comments and I will try to share my experience on any such situations in future blog posts.
Thank You !