Oracle-EBS-SQL/DIS Worksheet Execution Summary/DIS Worksheet Execution Summary.sql at master · Enginatics/Oracle-EBS-SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
/*************************************************************************/
/*                                                                       */
/*                       (c) 2010-2024 Enginatics GmbH                   */
/*                              www.enginatics.com                       */
/*                                                                       */
/*************************************************************************/
-- Report Name: DIS Worksheet Execution Summary
-- Description: 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.
-- Excel Examle Output: https://www.enginatics.com/example/dis-worksheet-execution-summary/
-- Library Link: https://www.enginatics.com/reports/dis-worksheet-execution-summary/
-- Run Report: https://demo.enginatics.com/

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