- Created by Deepak Kumar Shah on Feb 04, 2015
This is a statistical summary report for details like active state of user, login details, item completion details etc
Report Name | Customer Usage Data |
---|---|
REPORT_NAME_ID | CustomerUsageData |
REPORT_ID | 427 |
CATEGORY_ID | SystemAdministration |
GROUP_ID | |
WORKFLOW_ID | Run Customer Usage Data Report |
TYPE | BIRT /PRD |
USER_TYPE | Admin |
SHOW_IN_MENU | Yes |
DMN_ID | PUBLIC |
adminLoginDetails
Expand source
SELECT admin_logins, admin_unique_logins, day_count, ( CASE WHEN m.day_count > 0 AND m.admin_logins > 0 THEN ( m.admin_logins / m.day_count ) ELSE 0 END ) AS admin_avg_logins, ( CASE WHEN m.day_count > 0 AND m.admin_logins > 0 THEN ( m.admin_unique_logins / m.day_count ) ELSE 0 END ) AS admin_avgunique_logins FROM (SELECT /*+INDEX (pa_login_session_info IX_LOGIN_SES_IN__LOGIN) */ Count( login_id) AS admin_logins, /** ROUND(CAST( [SubmitDateTo] AS DATE)- CAST( [SubmitDateFrom] AS DATE)) AS day_count */, Count(DISTINCT( login_id )) AS admin_unique_logins FROM pa_login_session_info WHERE login_type = 'A' /** AND login_time >= [SubmitDateFrom] AND NOT login_time > [SubmitDateTo] */ )m
concurrentAdminLoginDetails
Expand source
SELECT ( CASE WHEN c_count_sum > 0 AND c_count_count > 0 THEN (( c_count_sum / c_count_count )) ELSE 0 END ) AS avg_admin_clogin, ( CASE WHEN max_admin_clogin > 0 THEN max_admin_clogin ELSE 0 END ) AS max_admin_clogin FROM (SELECT ( SUM(c_count) ) AS c_count_sum, ( Count(c_count) ) AS c_count_count, Max(c_count) AS max_admin_clogin FROM (SELECT c_count FROM (SELECT /*+parallel(8) */ a.login_time, Count(a.login_time) AS c_count FROM pa_login_session_info a, pa_login_session_info b WHERE a.login_type = 'A' AND b.login_type = 'A' /** AND a.login_time >= [SubmitDateFrom] AND a.login_time <= [SubmitDateTo] */ /** AND b.login_time >= [SubmitDateFrom] AND b.login_time <= [SubmitDateTo] */ AND a.login_time >= b.login_time AND a.login_time <= b.logout_time GROUP BY a.login_time)))
concurrentUserLoginDetails
Expand source
SELECT ( CASE WHEN c_count_sum > 0 AND c_count_count > 0 THEN (( c_count_sum / c_count_count )) ELSE 0 END ) AS avg_user_clogin, ( CASE WHEN max_user_clogin > 0 THEN max_user_clogin ELSE 0 END ) AS max_user_clogin FROM (SELECT ( SUM(c_count) ) AS c_count_sum, ( Count(c_count) ) AS c_count_count, Max(c_count) AS max_user_clogin FROM (SELECT c_count FROM (SELECT /*+parallel(8) */ a.login_time, Count(a.login_time) AS c_count FROM pa_login_session_info a, pa_login_session_info b WHERE a.login_type = 'U' AND b.login_type = 'U' /** AND a.login_time >= [SubmitDateFrom] AND a.login_time <= [SubmitDateTo] */ /** AND b.login_time >= [SubmitDateFrom] AND b.login_time <= [SubmitDateTo] */ AND a.login_time >= b.login_time AND a.login_time <= b.logout_time GROUP BY a.login_time)))
itemCompletionDetails
Expand source
SELECT student_count AS total_users, ( CASE WHEN total_items > 0 AND student_count > 0 THEN (( total_items / student_count )) ELSE 0 END ) AS avg_item_complete, ( CASE WHEN total_items > 0 THEN total_items ELSE 0 END ) AS total_item_complete FROM (SELECT SUM(internal_completions + external_completions) AS total_items FROM (SELECT (SELECT SUM(c_count) FROM (SELECT stud_id, Count(*) AS c_count FROM pa_cpnt_evthst /** WHERE compl_dte >= [SubmitDateFrom] AND NOT compl_dte > [SubmitDateTo] */ GROUP BY stud_id)) AS internal_completions, (SELECT SUM(c_count) FROM (SELECT stud_id, Count(*) AS c_count FROM pa_cpnt_evthst /** WHERE compl_dte >= [SubmitDateFrom] AND NOT compl_dte > [SubmitDateTo] */ GROUP BY stud_id)) AS external_completions FROM dual)), (SELECT Count(stud_id) AS student_count FROM (SELECT stud_id FROM (SELECT stud_id FROM (SELECT /*+ NO_INDEX(ph IX_PH_STUD__TSTMP) */ stud_id FROM ph_student ph /** WHERE PH.HIST_CREATE_TSTMP >= [SubmitDateFrom] AND PH.HIST_CREATE_TSTMP <= [SubmitDateTo] */ UNION ALL SELECT stud_id FROM (SELECT /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ a.* FROM ph_student a, (SELECT /*+ NO_INDEX(ph_student IX_PH_STUD__TSTMP) */ Max(hist_create_tstmp) AS hist_create_tstmp, stud_id FROM ph_student /** WHERE hist_create_tstmp <= [SubmitDateTo] */ GROUP BY stud_id)b WHERE a.stud_id = b.stud_id AND a.hist_create_tstmp = b.hist_create_tstmp) z WHERE action IN ( 'I', 'M' ) /** and z.hist_create_tstmp < [SubmitDateFrom] */ ) UNION ALL SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ stud_id FROM pa_student /** WHERE lst_upd_tstmp < [SubmitDateFrom] */ /** AND [security:PA_NO_DOMAIN_SECURITY_2 pa]*/ ) a GROUP BY stud_id))
totalActiveUsers
Expand source
SELECT Count(stud_id) AS ACTIVE_USERS FROM (SELECT stud_id FROM (SELECT /*+ NO_INDEX(ph IX_PH_STUD__TSTMP) */ stud_id FROM ph_student ph WHERE notactive = 'N' /** AND PH.HIST_CREATE_TSTMP >= [SubmitDateFrom] AND PH.HIST_CREATE_TSTMP <= [SubmitDateTo] */ UNION ALL SELECT stud_id FROM (SELECT /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ a.* FROM ph_student a, (SELECT /*+ NO_INDEX(PH_STUDENT IX_PH_STUD__TSTMP) */ Max(hist_create_tstmp) AS hist_create_tstmp, stud_id FROM ph_student /** WHERE hist_create_tstmp <= [SubmitDateTo] */ GROUP BY stud_id)b WHERE a.stud_id = b.stud_id AND a.hist_create_tstmp = b.hist_create_tstmp) z WHERE action IN ( 'I', 'M' ) AND notactive = 'N' /** and z.hist_create_tstmp < [SubmitDateFrom] */ UNION ALL SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ stud_id FROM pa_student WHERE notactive = 'N' /** and lst_upd_tstmp < [SubmitDateFrom] */ ) GROUP BY stud_id)
totalInactiveUsers
Expand source
SELECT Count(stud_id) AS INACTIVE_USERS FROM (SELECT stud_id FROM (SELECT /*+ NO_INDEX(S IX_PH_STUD__TSTMP) */ S.stud_id FROM ph_student S WHERE S.notactive = 'Y' /** AND S.HIST_CREATE_TSTMP >= [SubmitDateFrom] AND S.HIST_CREATE_TSTMP <= [SubmitDateTo] */ AND NOT EXISTS (SELECT /*+ NO_INDEX(S1 IX_PH_STUD__TSTMP) */ stud_id FROM ph_student S1 WHERE S1.notactive = 'N' AND S1.stud_id = S.stud_id /** AND S1.HIST_CREATE_TSTMP >= [SubmitDateFrom] AND S1.HIST_CREATE_TSTMP <= [SubmitDateTo] */ ) UNION ALL SELECT stud_id FROM (SELECT /*+ PARALLEL(8) NO_INDEX(a IX_PH_STUD__TSTMP) */ a.* FROM ph_student a, (SELECT /*+ NO_INDEX(ph_student IX_PH_STUD__TSTMP) */ Max(hist_create_tstmp) AS hist_create_tstmp, stud_id FROM ph_student /** WHERE hist_create_tstmp <= [SubmitDateTo] */ GROUP BY stud_id)b WHERE a.stud_id = b.stud_id AND a.hist_create_tstmp = b.hist_create_tstmp) z WHERE action IN ( 'I', 'M' ) AND notactive = 'Y' /** and z.hist_create_tstmp < [SubmitDateFrom] */ UNION ALL SELECT /*+ NO_INDEX(PA_STUDENT IX_PA_STUD__TSTMP) */ S.stud_id FROM pa_student S WHERE S.notactive = 'Y' /** AND LST_UPD_TSTMP < [SubmitDateFrom] */ ) GROUP BY stud_id)
userLoginDetails
Expand source
SELECT user_logins, user_unique_logins, day_count, ( CASE WHEN m.day_count > 0 AND m.user_logins > 0 THEN ( m.user_logins / m.day_count ) ELSE 0 END ) AS user_avg_logins, ( CASE WHEN m.day_count > 0 AND m.user_logins > 0 THEN ( m.user_unique_logins / m.day_count ) ELSE 0 END ) AS user_avgunique_logins FROM (SELECT /*+INDEX (pa_login_session_info IX_LOGIN_SES_IN__LOGIN) */ Count( login_id) AS user_logins, Count( DISTINCT( login_id )) AS user_unique_logins, /** ROUND(CAST( [SubmitDateTo] AS DATE)- CAST( [SubmitDateFrom] AS DATE)) AS day_count */ FROM pa_login_session_info a WHERE login_type = 'U' /** AND login_time > [SubmitDateFrom] AND NOT login_time > [SubmitDateTo] */ )m
Link: http://wiki.scn.sap.com/wiki/x/koERG