Page tree
Skip to end of metadata
Go to start of metadata

The User Online Item Status report shows you, for the users and for the items you specify, each user's current progress based on online items taken by the user. A user's progress through online courses is reported at the content object level, so that if a course consists of three content objects and the user has only completed the first two, the report will show that the user has not yet finished the third content object.

Note: Online Item Status Report is intended to report on the latest (most recent) status of all ASSIGNED online items. “Assigned” means the item is on the user’s learning plan (use the “All” filter on the Admin view of the user’s learning plan). This is not a history report, and will only report on the most recent, and will not include other prior history records. Customers use this report to track the status of a current assignment.

  1. The 'Time Entered' column value is triggered by the 'Launch Content' button. As long as the user hit that button, that will be counted as one entry.
  1. However the 'Time (HH:MM:SS)' column value is trigged by the communication sent back from the online content once you launch it, which means if the user hit the  'Launch Content' button but there's a  browser pop-up blocker that blocked the content from even launching or other behaviors stop the user launching the content, there will be no trigger to start the calculating of the session time. But this will still be counted as a Time Entered of this online item.  This 'Time' column is a cumulated calculation of the time the user spent with this course.

Item Status Report will include all history records for items. This report will therefore include duplicate history records, and records that were recorded as complete but never assigned to the user, and never added to the learning plan. This report is used by customers to answer the question – what is the completion status of an item for a group of users?The numbers could be different from the other report for good reason. The customer may see a discrepancy because they may have a lot of legacy completions that do not have corresponding assignments on the LP. Perhaps due to data migration, data import, or manual recording of a LE for items that were never launched. OR admins may have removed the rows of assignments from the learning plan not understanding their purpose.

 

Report NameOnline Items Status CSV
REPORT_NAME_IDUserOnlineItemStatusCSV
REPORT_ID235
CATEGORY_IDUserManagement
GROUP_IDUserOnlineItemStatus
WORKFLOW_IDRun User Online Item Status Report
TYPEBIRT /PRD
USER_TYPEAdmin
SHOW_IN_MENU

Yes

 

SQL Query used

WithObjectDetails
SELECT stud_comp.stud_id, 
       stud_comp.cpnt_typ_id, 
       stud_comp.cpnt_id, 
       stud_comp.rev_dte, 
       stud_comp.cmpl_stat_id, 
       stud_comp.compl_dte, 
       stud_comp.source_file, 
       stud_comp.aicc_id, 
       stud_comp.creator, 
       stud_comp.environment, 
       stud_comp.is_active, 
       stud_comp.has_access, 
       stud_comp.child_count, 
       stud_comp.force_order, 
       stud_comp.expanded, 
       stud_comp.needs_review, 
       stud_comp.initial_date, 
       stud_comp.last_access, 
       stud_comp.total_time, 
       stud_comp.total_times, 
       stud_comp.complete, 
       stud_comp.finished, 
       stud_comp.notes, 
       stud_comp.is_online_only, 
       stud_comp.has_admin_modified, 
       stud_comp.create_dte, 
       stud_comp.is_review_mode, 
       stud_comp.offline_accessible, 
       stud_comp.content_zip_url, 
       stud_comp.last_download_date, 
       stud_comp.sync_date, 
       scm.module_id, 
       scm.module_name, 
       scm.total_time                 AS scm_total_time, 
       Nvl (scm.total_times, 0)       AS scm_total_times, 
       scm.last_access                AS scm_last_access, 
       scm.complete                   AS scm_complete, 
       scm.finished                   AS scm_finished, 
       scm.has_access                 AS scm_has_access, 
       c.rev_num, 
       c.rtyp_id, 
       c.dmn_id                       AS cpnt_domain, 
       c.del_mth_id, 
       c.cpnt_src_id, 
       c.notactive                    AS cpnt_notactive, 
       c.cpnt_title, 
       c.safety_rel, 
       c.create_dte                   AS cpnt_create_date, 
       c.reviser, 
       c.apprvr, 
       c.apprvd, 
       c.apprvl_dte, 
       c.min_enrl, 
       c.max_enrl, 
       c.cpnt_len, 
       c.prep_len, 
       c.les_plan, 
       c.contact, 
       c.credit_hrs, 
       c.cpe_hrs, 
       c.contact_hrs, 
       c.stud_mats, 
       c.inst_mats, 
       c.cpnt_desc, 
       c.tgt_audnc, 
       c.comments                     AS cpnt_comments, 
       c.grade_opt, 
       c.enrl_threshold_days, 
       c.shipping_required, 
       c.auto_fill_enrl, 
       c.waitlist_remdr_sent, 
       c.auto_competency, 
       c.cpnt_classification, 
       c.chgbck_method, 
       c.catalog_sku, 
       c.schedule_can_override_price, 
       c.production_ready, 
       c.cpnt_goals, 
       c.self_record_lrngevt, 
       c.sub_record_lrngevt, 
       c.tap_def_id, 
       c.approval_reqd, 
       c.init_period_type_id, 
       c.init_number, 
       c.init_basis_type_id, 
       c.retrng_period_type_id, 
       c.retrng_number, 
       c.retrng_basis_type_id, 
       c.rating, 
       c.self_enrl, 
       c.user_req_enabled, 
       c.user_can_waitlist, 
       c.cpnt_key, 
       c.register_online, 
       c.esig_enabled, 
       c.lst_upd_usr                  AS cpnt_last_upd_user, 
       c.lst_upd_tstmp                AS cpnt_last_upd_timestamp, 
       c.cost_currency_code, 
       st.stud_id                     AS stud_stud_id, 
       st.lname, 
       st.fname, 
       st.mi, 
       st.emp_stat_id, 
       st.emp_typ_id, 
       st.jl_id, 
       st.jp_id, 
       st.target_jp_id, 
       st.job_title, 
       st.dmn_id                      AS stud_domain, 
       st.org_id, 
       st.region_id, 
       st.co_id, 
       Decode(st.notactive, 'Y', 'N', 
                            'N', 'Y') AS stud_notactive, 
       st.addr, 
       st.city, 
       st.state, 
       st.postal, 
       st.cntry, 
       st.super, 
       st.coach_stud_id, 
       st.hire_dte, 
       st.term_dte, 
       st.email_addr, 
       st.resume_locn, 
       st.comments                    AS stud_comments, 
       st.shipping_name, 
       st.shipping_contact_name, 
       st.shipping_addr, 
       st.shipping_addr1, 
       st.shipping_city, 
       st.shipping_state, 
       st.shipping_postal, 
       st.shipping_cntry, 
       st.shipping_phon_num, 
       st.shipping_fax_num, 
       st.shipping_email_addr, 
       st.stud_pswd, 
       st.pin, 
       st.pin_date, 
       st.encrypted, 
       st.has_access                  AS stud_has_access, 
       st.billing_name, 
       st.billing_contact_name, 
       st.billing_addr, 
       st.billing_addr1, 
       st.billing_city, 
       st.billing_state, 
       st.billing_postal, 
       st.billing_cntry, 
       st.billing_phon_num, 
       st.billing_fax_num, 
       st.billing_email_addr, 
       st.self_registration, 
       st.self_registration_date, 
       st.access_to_org_fin_act, 
       st.notify_dev_plan_item_add, 
       st.notify_dev_plan_item_mod, 
       st.notify_dev_plan_item_remove, 
       st.notify_when_sub_item_complete, 
       st.notify_when_sub_item_failure, 
       st.LOCKED, 
       st.password_exp_date, 
       st.security_question, 
       st.security_answer, 
       st.role_id, 
       st.image_id, 
       st.gender, 
       st.past_service, 
       st.lst_unlock_tstmp, 
       st.lst_upd_usr                 AS stud_last_upd_user, 
       st.lst_upd_tstmp               AS stud_last_upd_timestamp, 
       st.manage_sub_sp, 
       st.manage_own_sp, 
       scm.stud_cpnt_id               AS scm_stud_cpnt_id, 
       scm.node_level, 
       scm.node_no, 
       scm.exam_name, 
       scm.app_id, 
       scm.developer_id, 
       scm.module_type, 
       scm.is_active                  AS scm_is_active, 
       scm.aicc_id                    AS scm_aicc_id, 
       scm.aicc_lesson_loc, 
       scm.parent_id, 
       scm.child_count                AS scm_child_count, 
       scm.complete_date, 
       scm.initial_date               AS scm_initial_date, 
       scm.force_order                AS scm_force_order, 
       scm.expanded                   AS scm_expanded, 
       scm.needs_review               AS scm_need_review, 
       scm.questions_asked, 
       scm.questions_correct, 
       scm.score, 
       scm.mastery_score, 
       scm.comments                   AS scm_comments, 
       scm.app_cmd_opt, 
       scm.play_all_scrns, 
       scm.exam_revw_enabled, 
       scm.exam_revw_prompt, 
       scm.complete_on_launch, 
       scm.has_admin_modified         AS scm_has_admin_modified, 
       scm.exit_component, 
       scm.trigger_cpnt_pass, 
       scm.trigger_cpnt_fail, 
       scm.freeze_cpnt_objs_during_launch, 
       scm.freezing_other_cpnt_objects, 
       scm.cmi_completion_status, 
       scm.success_status, 
       scm.progress_measure, 
       scm.cmi_entry, 
       scm.lesson_location, 
       scm.score_raw, 
       scm.score_min, 
       scm.score_max, 
       scm.score_scaled, 
       scm.cmi_exit, 
       scm.activity_id, 
       scm.offline_accessible         AS scm_offline_accessible, 
       scm.exam_duration, 
       scm.exam_review_option, 
       scm.aicc_data 
FROM   (SELECT * 
        FROM   (SELECT sc_sub.stud_id, 
                       sc_sub.cpnt_typ_id, 
                       sc_sub.cpnt_id, 
                       sc_sub.rev_dte, 
                       sco.stud_cpnt_id, 
                       sco.cmpl_stat_id, 
                       sco.compl_dte, 
                       sco.source_file, 
                       sco.aicc_id, 
                       sco.creator, 
                       sco.environment, 
                       sco.is_active, 
                       sco.has_access, 
                       sco.child_count, 
                       sco.force_order, 
                       sco.expanded, 
                       sco.needs_review, 
                       sco.initial_date, 
                       sco.last_access, 
                       sco.total_time, 
                       sco.total_times, 
                       sco.complete, 
                       Nvl (sco.finished, 'N')           AS finished, 
                       sco.notes, 
                       sco.is_online_only, 
                       sco.has_admin_modified, 
                       sco.create_dte, 
                       sco.is_review_mode, 
                       sco.offline_accessible, 
                       sco.content_zip_url, 
                       sco.last_download_date, 
                       sco.sync_date, 
                       Row_number () 
                         over ( 
                           PARTITION BY sc_sub.stud_id, sc_sub.cpnt_typ_id, 
                         sc_sub.cpnt_id, 
                         sc_sub.rev_dte 
                           ORDER BY sco.create_dte DESC) AS rnum 
                FROM   (SELECT sc.cpnt_typ_id, 
                               sc.cpnt_id, 
                               sc.rev_dte, 
                               sc.stud_id 
                        FROM   pv_stud_course sc, 
                               pa_cbt_cpnt cbt 
                        WHERE  cbt.cpnt_typ_id = sc.cpnt_typ_id 
                               AND cbt.cpnt_id = sc.cpnt_id 
                               AND cbt.rev_dte = sc.rev_dte 
                        /** and sc.stud_id       in [UserSearch]                              and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]                          */ 
                        UNION ALL 
                        SELECT sc.cpnt_typ_id, 
                               sc.cpnt_id, 
                               sc.rev_dte, 
                               sc.stud_id 
                        FROM   pa_stud_qual_cpnt sc, 
                               pa_cbt_cpnt cbt 
                        WHERE  cbt.cpnt_typ_id = sc.cpnt_typ_id 
                               AND cbt.cpnt_id = sc.cpnt_id 
                               AND cbt.rev_dte = sc.rev_dte 
                       /** and sc.stud_id       in [UserSearch]                             and (sc.cpnt_typ_id, sc.cpnt_id, sc.rev_dte) in [ItemSearch]                           */ 
                       ) sc_sub, 
                       pa_cbt_stud_cpnt sco 
                WHERE  sco.cpnt_typ_id(+) = sc_sub.cpnt_typ_id 
                       AND sco.cpnt_id(+) = sc_sub.cpnt_id 
                       AND sco.rev_dte(+) = sc_sub.rev_dte 
                       AND sco.stud_id(+) = sc_sub.stud_id) 
        WHERE  rnum = 1)stud_comp, 
       pv_course c, 
       pa_student st, 
       pa_cbt_stud_cpnt_mod scm 
WHERE  stud_comp.cpnt_typ_id = c.cpnt_typ_id 
       AND stud_comp.cpnt_id = c.cpnt_id 
       AND stud_comp.rev_dte = c.rev_dte 
       AND stud_comp.stud_id = st.stud_id 
       AND stud_comp.stud_cpnt_id = scm.stud_cpnt_id(+) 
/** and stud_comp.initial_date >= [InitialDateFrom]      and stud_comp.initial_date < [InitialDateTo]      and stud_comp.last_access >= [LastAccessDateFrom]      and stud_comp.last_access < [LastAccessDateTo]      and stud_comp.compl_dte >= [CompletedDateFrom]      and stud_comp.compl_dte <= [CompletedDateTo]      and [security:pa_student st]  */ 
ORDER  BY c.cpnt_typ_id, 
          c.cpnt_id, 
          c.rev_dte, 
          st.stud_id, 
          scm_last_access DESC 

 

 Link: http://wiki.scn.sap.com/wiki/x/wgAIG