DIS Worksheet Execution Summary

Description

Categories: Enginatics
Repository: Github Columns: Execution Count, User Count, Workbook Count, Sheet Count, Folder Count, 1 Folder Count, 2 Folder Count, 3 Folder Count, 4 Folder Count, 5 Folder Count ...

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.

Copy

select distinct
&month_column
count(*) over (&partition_by) execution_count,
count(distinct x.qs_created_by) over (&partition_by) user_count,
count(distinct x.workbook_owner||x.workbook) over (&partition_by) workbook_count,
count(distinct x.workbook_owner||x.workbook||x.sheet) over (&partition_by) sheet_count,
count(distinct x.folders) over (&partition_by) folder_count,
count(distinct decode(x.folder_count,1,x.folders)) over (&partition_by) "1 Folder Count",
count(distinct decode(x.folder_count,2,x.folders)) over (&partition_by) "2 Folder Count",
count(distinct decode(x.folder_count,3,x.folders)) over (&partition_by) "3 Folder Count",
count(distinct decode(x.folder_count,4,x.folders)) over (&partition_by) "4 Folder Count",
count(distinct decode(x.folder_count,5,x.folders)) over (&partition_by) "5 Folder Count",
count(distinct case when x.folder_count>5 then x.folders end) over (&partition_by) ">5 Folder Count"
from
(
select distinct
eqs.qs_id id,
eqs.qs_created_by,
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 start_date,
eqs.qs_num_rows row_count,
listagg(eo.obj_name,', ') within group (order by eo.obj_name) over (partition by eqs.qs_id) folders,
length(eqs.qs_object_use_key)-length(translate(eqs.qs_object_use_key,'x.','x'))+1 folder_count,
xxen_util.meaning(case when eqs.qs_object_use_key like '%.%' then 'Y' end,'YES_NO',0) multiple_flag,
eqs.qs_object_use_key use_key
from
(
select
trim(regexp_substr(eqs.qs_object_use_key,'[^\.]+',1,rowgen.column_value)) obj_id,
greatest(nvl(eqs.qs_act_cpu_time,0),nvl(eqs.qs_act_elap_time,0)) seconds,
eqs.*
from
&eul.eul5_qpp_stats eqs,
table(xxen_util.rowgen(regexp_count(eqs.qs_object_use_key,'\.')+1)) rowgen
where
1=1
) eqs,
&eul.eul5_objs eo
where
2=2 and
translate(eqs.obj_id,'x0123456789','x') is null and
eqs.obj_id=eo.obj_id(+)
) x
&order_by

Parameter Name

SQL text

Validation

Submitted by User

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

LOV

Show By Month

x.month,

LOV

End User Layer

<parameter_value>

LOV

Business Area

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_us1.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_us1.eul5_bas eb,
eul_us1.eul5_ba_obj_links ebol
where
eb.ba_name=:business_area and
eb.ba_id=ebol.bol_ba_id)
)

LOV

Workbook

eqs.qs_doc_name=:workbook

LOV

Folder

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_us1.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_us1.eul5_objs eo
where
eo.obj_name=:folder
)
)

LOV

View Name

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_us1.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_us1.eul5_objs eo
where
eo.sobj_ext_table=:view_name
)
)