DIS Worksheet Execution Summary

Description

Categories: Enginatics
Repository: GitHub

Discoverer worksheet access statistic summary from table eul5_qpp_stats to show the number of active Discoverer users, number of different workbooks and worksheets executed and the number of different folder (combinations) that these are based on.

SQL 53 lines

select
y.month,
y.user_count,
(select count(distinct eqs2.qs_created_by) from &eul.eul5_qpp_stats eqs2 where eqs2.qs_created_date>=y.month-60 and eqs2.qs_created_date<y.month) user_count_60_days,
&qs_created_by
y.execution_count,
y.workbook_count,
y.sheet_count,
y.use_key_count,
y."1 Folder Count",
y."2 Folder Count",
y."3 Folder Count",
y."4 Folder Count",
y."5 Folder Count",
y.">5 Folder Count"
from
(
select distinct
x.month,
count(distinct x.qs_created_by) over (partition by x.month) user_count,
&qs_created_by
count(*) over (partition by &qs_created_by x.month) execution_count,
count(distinct x.workbook_owner||x.workbook) over (partition by &qs_created_by x.month) workbook_count,
count(distinct x.workbook_owner||x.workbook||x.sheet) over (partition by &qs_created_by x.month) sheet_count,
count(distinct x.use_key) over (partition by &qs_created_by x.month) use_key_count,
count(distinct decode(x.folder_count,1,x.use_key)) over (partition by &qs_created_by x.month) "1 Folder Count",
count(distinct decode(x.folder_count,2,x.use_key)) over (partition by &qs_created_by x.month) "2 Folder Count",
count(distinct decode(x.folder_count,3,x.use_key)) over (partition by &qs_created_by x.month) "3 Folder Count",
count(distinct decode(x.folder_count,4,x.use_key)) over (partition by &qs_created_by x.month) "4 Folder Count",
count(distinct decode(x.folder_count,5,x.use_key)) over (partition by &qs_created_by x.month) "5 Folder Count",
count(distinct case when x.folder_count>5 then x.use_key end) over (partition by &qs_created_by x.month) ">5 Folder Count"
from
(
select
eqs.qs_id id,
eqs.qs_created_by,
upper(eqs.qs_doc_owner) workbook_owner,
eqs.qs_doc_name workbook,
eqs.qs_doc_details sheet,
trunc(eqs.qs_created_date,'month') month,
eqs.qs_created_date,
length(eqs.qs_object_use_key)-length(translate(eqs.qs_object_use_key,'x.','x'))+1 folder_count,
eqs.qs_object_use_key use_key
from
&eul.eul5_qpp_stats eqs
where
1=1
) x
) y
order by
y.month desc,
y.execution_count desc,
y.workbook_count desc

select y.month, y.user_count, (select count(distinct eqs2.qs_created_by) from &eul.eul5_qpp_stats eqs2 where eqs2.qs_created_date>=y.month-60 and eqs2.qs_created_date<y.month) user_count_60_days, &qs_created_by y.execution_count, y.workbook_count, y.sheet_count, y.use_key_count, y."1 Folder Count", y."2 Folder Count", y."3 Folder Count", y."4 Folder Count", y."5 Folder Count", y.">5 Folder Count" from ( select distinct x.month, count(distinct x.qs_created_by) over (partition by x.month) user_count, &qs_created_by count(*) over (partition by &qs_created_by x.month) execution_count, count(distinct x.workbook_owner||x.workbook) over (partition by &qs_created_by x.month) workbook_count, count(distinct x.workbook_owner||x.workbook||x.sheet) over (partition by &qs_created_by x.month) sheet_count, count(distinct x.use_key) over (partition by &qs_created_by x.month) use_key_count, count(distinct decode(x.folder_count,1,x.use_key)) over (partition by &qs_created_by x.month) "1 Folder Count", count(distinct decode(x.folder_count,2,x.use_key)) over (partition by &qs_created_by x.month) "2 Folder Count", count(distinct decode(x.folder_count,3,x.use_key)) over (partition by &qs_created_by x.month) "3 Folder Count", count(distinct decode(x.folder_count,4,x.use_key)) over (partition by &qs_created_by x.month) "4 Folder Count", count(distinct decode(x.folder_count,5,x.use_key)) over (partition by &qs_created_by x.month) "5 Folder Count", count(distinct case when x.folder_count>5 then x.use_key end) over (partition by &qs_created_by x.month) ">5 Folder Count" from ( select eqs.qs_id id, eqs.qs_created_by, upper(eqs.qs_doc_owner) workbook_owner, eqs.qs_doc_name workbook, eqs.qs_doc_details sheet, trunc(eqs.qs_created_date,'month') month, eqs.qs_created_date, length(eqs.qs_object_use_key)-length(translate(eqs.qs_object_use_key,'x.','x'))+1 folder_count, eqs.qs_object_use_key use_key from &eul.eul5_qpp_stats eqs where 1=1 ) x ) y order by y.month desc, y.execution_count desc, y.workbook_count desc

Parameters 10

Parameter

Type

List of values SQL

Workbook

LOV

eqs.qs_doc_name=:workbook

Submitted by User

LOV

eqs.qs_created_by in (select '#'||fu.user_id from fnd_user fu where fu.user_name=:user_name)

Business Area

LOV

eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.obj_id in (
select
ebol.bol_obj_id
from
eul_us.eul5_bas eb,
eul_us.eul5_ba_obj_links ebol
where
eb.ba_name=:business_area and
eb.ba_id=ebol.bol_ba_id)
)

Folder

LOV

eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.obj_id in (
select
eo.obj_id
from
eul_us.eul5_objs eo
where
eo.obj_name=:folder
)
)

View Name

LOV

eqs.qs_id in
(
select
eqs0.qs_id
from
(
select
eqs.qs_id,
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id
from
eul_us.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
) eqs0
where
eqs0.obj_id in (
select
eo.obj_id
from
eul_us.eul5_objs eo
where
eo.sobj_ext_table=:view_name
)
)

Accessed within Days

Number

eqs.qs_created_date>sysdate-:days

Start Date From

Date

eqs.qs_created_date>=:start_date_from

Start Date To

Date

eqs.qs_created_date<:start_date_to+1

End User Layer

LOV

<parameter_value>

Show User Details

LOV

qs_created_by,

Description

SQL 53 lines

Parameters 10

Find out more

Features

Download

FAQ