Discoverer Reporting : using SQL to generate report usage statistics

Oracle Discoverer holds a set of database tables for each Discoverer EUL being used. In the following queries, the prefix eul_owner should be replaced by the database owner corresponding to the specific EUL in question.

The following reporting statistics may be of interest…

1. Discoverer Reports run in the last year (i.e. since SYSDATE-365):

SELECT qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
,      qs.qs_created_date          date_run
,      fnd.user_name               user_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
ORDER BY qs.qs_created_date DESC
;

2. Discoverer Report usage by USER:

SELECT COUNT(*)                    num_reports
,      fnd.user_name               user_name
,      ppx.full_name               employee_name
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY fnd.user_name
,      ppx.full_name
ORDER BY COUNT(*) DESC
;

3. Discoverer Report usage by REPORT:

SELECT COUNT(*)                    num_reports
,      qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
FROM   <eul_owner>.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date > SYSDATE - 365
GROUP BY qs.qs_doc_name
,      qs.qs_doc_details
ORDER BY COUNT(*) DESC
;

4. Discoverer Report ownership and assignment (users/responsibilities):

SELECT DISTINCT a.doc_name             disco_report
,      fu_owner.user_name              owner
,      d.responsibility_key            responsbility
,      fu.user_name
,      ppx.full_name                   employee_name
FROM   <eul_owner>.eul5_documents      a
,      <eul_owner>.eul5_access_privs   b
,      <eul_owner>.eul5_eul_users      c
,      fnd_responsibility              d
,      fnd_user                        fu
,      fnd_user                        fu_owner
,      per_people_x                    ppx
WHERE  a.doc_id = b.gd_doc_id(+)
AND    b.ap_type(+) = 'GD'
AND    b.ap_eu_id = c.eu_id(+)
AND    REPLACE(c.eu_username,'#','') = TO_CHAR(d.responsibility_id(+)) || TO_CHAR(d.application_id(+))
AND    TO_CHAR(fu.user_id(+)) = REPLACE(c.eu_username,'#','')
AND    TO_CHAR(fu_owner.user_id(+)) = REPLACE(a.doc_created_by,'#','')
AND    NVL(fu.end_date(+),SYSDATE + 1) > SYSDATE
AND    NVL(fu_owner.end_date(+),SYSDATE + 1) > SYSDATE
AND    ppx.person_id(+) = fu.employee_id
AND    (   fu_owner.user_name = <user>
       OR  fu.user_name = <user>
       )
ORDER BY a.doc_name ASC
;

See also…

How To Find Out Who The Owner of a Workbook is and To Whom it is Shared [ID 1076907.6]

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s