Blitz Report Execution Summary

select
y.count,
y.report_name,
xxen_util.time(avg_seconds) avg_time,
xxen_util.time(max_seconds) max_time,
xxen_util.time(sum_seconds) sum_time,
y.avg_seconds,
y.max_seconds,
y.sum_seconds,
y.users,
y.most_frequent,
y.category,
y.created_by,
xxen_util.client_time(y.creation_date) creation_date,
y.last_updated_by,
xxen_util.client_time(y.last_update_date) last_update_date,
decode(y.type,'S','System','P','Protected') type,
y.total_users
from
(
select distinct
count(x.run_id) over (partition by x.report_id) count,
x.report_name,
round(sum(x.seconds) over (partition by x.report_id)/count(*) over (partition by x.report_id),2) avg_seconds,
round(max(x.seconds) over (partition by x.report_id),2) max_seconds,
round(sum(x.seconds) over (partition by x.report_id),2) sum_seconds,
count(distinct x.xrr_created_by) over (partition by x.report_id) users,
max(xxen_util.user_name(x.xrr_created_by)) keep (dense_rank last order by x.count_per_user, x.xrr_created_by) over (partition by x.report_id) most_frequent,
x.category,
x.created_by,
x.creation_date,
x.last_updated_by,
x.last_update_date,
x.xrv_type type,
count(distinct x.xrr_created_by) over () total_users
from
(
select
xrv.report_name,
xrv.type xrv_type,
xxen_util.user_name(xrv.created_by) created_by,
xrv.creation_date,
xxen_util.user_name(xrv.last_updated_by) last_updated_by,
xrv.last_update_date,
(xrr.completion_date-xrr.creation_date)*86400 seconds,
xrr.run_id,
xrr.report_id,
xrr.created_by xrr_created_by,
xxen_api.category(xrv.report_id) category,
count(xrr.run_id) over (partition by xrr.report_id, xrr.created_by) count_per_user
from
xxen_reports_v xrv,
xxen_report_runs xrr
where
1=1 and
xrr.completion_date is not null and
xrr.completion_message is null and
xrv.report_id=xrr.report_id(+)
) x
) y
order by
y.count desc nulls last

select y.count, y.report_name, xxen_util.time(avg_seconds) avg_time, xxen_util.time(max_seconds) max_time, xxen_util.time(sum_seconds) sum_time, y.avg_seconds, y.max_seconds, y.sum_seconds, y.users, y.most_frequent, y.category, y.created_by, xxen_util.client_time(y.creation_date) creation_date, y.last_updated_by, xxen_util.client_time(y.last_update_date) last_update_date, decode(y.type,'S','System','P','Protected') type, y.total_users from ( select distinct count(x.run_id) over (partition by x.report_id) count, x.report_name, round(sum(x.seconds) over (partition by x.report_id)/count(*) over (partition by x.report_id),2) avg_seconds, round(max(x.seconds) over (partition by x.report_id),2) max_seconds, round(sum(x.seconds) over (partition by x.report_id),2) sum_seconds, count(distinct x.xrr_created_by) over (partition by x.report_id) users, max(xxen_util.user_name(x.xrr_created_by)) keep (dense_rank last order by x.count_per_user, x.xrr_created_by) over (partition by x.report_id) most_frequent, x.category, x.created_by, x.creation_date, x.last_updated_by, x.last_update_date, x.xrv_type type, count(distinct x.xrr_created_by) over () total_users from ( select xrv.report_name, xrv.type xrv_type, xxen_util.user_name(xrv.created_by) created_by, xrv.creation_date, xxen_util.user_name(xrv.last_updated_by) last_updated_by, xrv.last_update_date, (xrr.completion_date-xrr.creation_date)*86400 seconds, xrr.run_id, xrr.report_id, xrr.created_by xrr_created_by, xxen_api.category(xrv.report_id) category, count(xrr.run_id) over (partition by xrr.report_id, xrr.created_by) count_per_user from xxen_reports_v xrv, xxen_report_runs xrr where 1=1 and xrr.completion_date is not null and xrr.completion_message is null and xrv.report_id=xrr.report_id(+) ) x ) y order by y.count desc nulls last