Monday, December 18, 2006

When did those reports last run?

Here's an interesting piece of code that I developed recently that you may find to be of use. It is aimed at administrators and uses a combination of the statistics (EUL5_QPP_STATS) and the documents (EUL5_DOCUMENTS) EUL tables to determine whether a workbook that has been saved to the database has been run recently.

SQL Code:

SELECT DISTINCT
DOCS.DOC_CREATED_BY DOC_OWNER,
DOCS.DOC_NAME DOC_NAME,
(SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) LAST_RUN
FROM
EUL5_QPP_STATS STATS,
EUL5_DOCUMENTS DOCS
WHERE
DOCS.DOC_NAME NOT LIKE 'Workbook%' AND
STATS.QS_CREATED_DATE(+) > :CUTOFF AND
DOCS.DOC_CREATED_DATE < :CUTOFF AND
DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)
HAVING :RUN_DATE >=
NVL((SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')
GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_NAME, STATS.QS_DOC_NAME
ORDER BY LAST_RUN DESC, DOCS.DOC_CREATED_BY, DOCS.DOC_NAME;


Note: The DOCS.DOC_NAME NOT LIKE 'Workbook%' code omits workbooks that were saved using the generic name that Discoverer always users. You might want to find these later and discuss removing them with your users!

Code explanation
The above code has a number of facets about it that I think I ought to explain a little more in depth. These are:
  • The reason for the DISTINCT is to make sure that if the workbook was run more than once during a day that it is not counted twice.

  • The :CUTOFF parameter prompts for a date. Whatever date is entered here is used to find workbooks that have been saved to the database prior to that date and have either run since that date or never run at all

  • The :RUN_DATE parameter prompts for a date also. It is used to find workbooks that have not been run after this date.

  • The following code is interesting because it finds both workbooks that have been run and those which have never run, returning NULL if the workbook did not run at all in the period between the two parameters

HAVING :RUN_DATE >=
NVL((
SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME),'01-JAN-2000')

Sample Output
Here's some sample output, from TOAD, that I ran this evening against my own test database using 01-JUN-2006 for :CUTOFF and 01-SEP-2006 for :RUN_DATE.



Note: If you have never used TOAD or have always wanted to try it but never got around to it please let me know by email at michael@learndiscoverer.com. I have partnered with the manufacturer and have a trial license which you can use for evaluation purposes.

5 comments:

Anil Passi said...

Lovely blog on Discoverer, some of my readers asked me to write on Disco ! but now I know where to refer them to next time.

I have added your entry to my blog roll at this link.
Please let me know if you are OK with that.

http://oracle.anilpassi.com/news/index.php

Thanks,

zack said...

Hi,

I am trying to create a report which shows the diffrent priveliges a user/responsibilities have.I am havingg a tough time trying figure out the EUL_US tables. Can you please help me this?

Thanks
Zack

Michael said...

Hi
I have no problem with this. I'm glad to be able to help.
Best wishes
Michael

Anitha said...

Michael,

I am looking for a query that displays a list of users running discoverer reports and the last time they ran a report.

thanks,
Anitha.

Also when I ran the query

SELECT DISTINCT
DOCS.DOC_CREATED_BY DOC_OWNER,
DOCS.DOC_NAME DOC_NAME,
(SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
FROM EUL5_QPP_STATS DM
WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) LAST_RUN
FROM
EUL5_QPP_STATS STATS,
EUL5_DOCUMENTS DOCS
WHERE
DOCS.DOC_NAME NOT LIKE 'Workbook%' AND
STATS.QS_CREATED_DATE(+) > sysdate - 365 AND
DOCS.DOC_CREATED_DATE < sysdate AND
DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)

I see doc_owner as a number. Should this ID be convereted to a user_name.

iceman said...

Is there any was to see the parameters that the report was run with? Thanks, Bill

Followers