select
x.report_name,
x.view_name,
x.express_count+x.xl_connect_count+x.fsg_count count,
x.users,
x.express_count,
&email_dist_count
x.xl_connect_count,
x.fsg_count,
x.express_users,
&email_dist_users
x.xl_connect_users,
x.fsg_users,
x.total_users
from
(
select
coalesce(err.report_name,err2.report_name,rr.name) report_name,
coalesce(err.view_name,err2.view_name) view_name,
count(distinct erp.created_by) over (partition by coalesce(err.report_name,err2.report_name,rr.name)) users,
count(distinct erp.created_by) over () total_users,
case when erp.submission_source like 'Email Distribution%' then 'Email Distribution' else erp.submission_source end submission_source,
erp.created_by
from
xxeis.eis_rs_processes erp,
xxeis.eis_rs_sessions ers,
xxeis.eis_rs_reports err,
xxeis.rg_reports rr,
xxeis.eis_rs_reports err2
where
erp.creation_date>=sysdate-:submitted_within and
1=1 and
erp.session_id=ers.session_id(+) and
decode(erp.submission_source,'FSG',null,erp.report_id)=err.report_id(+) and
decode(nvl(erp.submission_source,'FSG'),'FSG',erp.report_id)=rr.report_id(+) and
case when erp.submission_source like 'Email Distribution~%' then substr(erp.submission_source,20) end=err2.report_id(+)
)
pivot (
count(*) count,
count(distinct created_by) users
for
submission_source in (
'eXpress' express,
'Email Distribution' email_distr,
'XL Connect' xl_connect,
'FSG' fsg
)
) x
order by
count desc |
