1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

List of discoverer reports - export to excel

Discussion in 'Oracle Discoverer' started by lizm, May 31, 2018.

  1. lizm

    lizm Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Virginia
    Hello,

    This seems like the most basic of questions... how many reports do I have? I have spent large amounts of time trying to determine this and I still can't. In the end, I need to get a list of current reports that need to be converted along with the report owner's name in and Excel spreadsheet. I expect there to be a couple thousand.

    * I've posted to MOSC and been told to install the EUL Status Workbooks. The doc says to run the eul5.sql and eul5.eex files which were installed with Oracle Discoverer Administrator. The DBA has searched his computer and we've searched the Unix server... no files like this exist.

    * I've asked how to handle this without the EUL Status Workbooks since I no doubt can't get them installed on the production machine... to which the Oracle rep suggested I install it on the test system, pull the SQL from the workbook and then run the SQL against the production database to get the list. Sounds good, but without the files mentioned above, I can't do this. I asked if they could post the SQL from the workbook for me but... no response. (Can anyone reading this post it??)

    * I've searched and found a number of helpful individuals who offer queries into the underlying tables (which Oracle says are proprietary so there is no documentation). The queries they suggest do not work with our data. The workbook data is in the EUL5_DOCUMENTS table and the worksheet data is supposedly in the EUL5_QPP_STATS table. The stats table appears to be a log of every time someone runs a report (so you can see the owner and the actual executor of the report). Some of the problems I have encountered: I have workbooks with no worksheet and worksheets with no workbook. Furthermore, I have a user who sees a set of 6 workbooks and a total of 10 worksheets in the Discoverer Plus view... but when I query this information in the database, I only find 7 of the worksheets. (I even wrote a program to find every character based column in tables owned by DISCOVERER and searched each one for the workbook name... no luck. But I also identified these two tables are the only place where this data is stored.).

    Here is the query that is supposed to join the tables (notice I have joined on doc_name AND doc_owner... the queries found online only joined by doc_name but our users have some duplicated workbook names so it requires both values in order to be unique).

    SELECT DOCS.DOC_CREATED_BY DOC_OWNER, DOCS.DOC_UPDATED_BY DOC_UPDATED,
    DOCS.DOC_NAME WORKBOOK_NAME, STATS.QS_DOC_DETAILS WORKSHEET,
    (SELECT TRUNC(MAX(DM.QS_CREATED_DATE))
    FROM EUL5_QPP_STATS DM
    WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME AND
    DM.QS_DOC_OWNER = DOCS.DOC_UPDATED_BY) LAST_RUN,
    count(*)
    FROM EUL5_QPP_STATS STATS, EUL5_DOCUMENTS DOCS
    WHERE
    DOCS.DOC_NAME = STATS.QS_DOC_NAME(+) AND
    DOCS.DOC_UPDATED_BY = STATS.QS_DOC_OWNER(+)
    GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_UPDATED_BY, DOCS.DOC_NAME,
    QS_DOC_DETAILS, STATS.QS_DOC_NAME
    ORDER BY DOC_UPDATED_BY, COUNT(*), LAST_RUN DESC, DOCS.DOC_NAME;

    Any new suggestions or assistance would be really appreciated.
    Thanks.
     
  2. lizm

    lizm Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Virginia
    Ha! I found it... but I can't really do much with it.

    So first, understand the problem fully:

    I had a user who in Discoverer viewer displayed 10 reports across multiple workbooks, but when I queried the database, I only saw 6 reports. If I used the query above, I could see one of the workbooks showed 2 worksheets instead of the expected 3, and 2 workbooks didn't show any worksheets (blank value for qs_doc_details).

    I wrote a program to parse every table definition owned by Discoverer, looking for character based columns and then searched each of those for a worksheet name that correctly being retrieved (or just a portion of it). Only 3 columns returned... EUL5_DOCUMENTS.doc_name, EUL5_QPP_STATS.qs_doc_name and EUL5_QPP_STATS.qs_doc_details. I then queried each of these for a worksheet name that was not being correctly retrieved... nothing. But, it HAS to be in the database, SOMEWHERE.

    Solution:

    There is a field called DOC_DOCUMENTS which is of type LONG RAW. I've had some trouble converting this data so I can try to parse through it. I am working with Toad. A colleague mentioned that you just click on the results (which is listed as (BLOB)) and Toad will pop up a window to convert the contents to Hex and Text and then you can choose to view it in the viewer or save it to a file.

    *** This is where the actual worksheet data is stored.***

    It is embedded in the LONG RAW value along with all sorts of other information. It isn't like I can just parse the value and yank the data out, because there are a variable number of worksheets and no clear indication of which byte the values start on.

    So, just to be clear about what the above query provides you:

    The EUL5_QPP_STATS table is actually just a log of the report executions. If the users has saved the worksheet from someone else and even edited it or created a new one, but never run it... you won't see it in this log table. Only if they run it and then exit correctly will it be logged. So, you can't rely on this table to give you a complete listing of the worksheets for each user.

    I don't have a solution for how to pull this data from the DOC_DOCUMENT field, but at least now I know where it is coming from. I could not sleep knowing that the data HAS to be there but I can't find it. Now I can sleep.

    Final thought:

    There are reports that users have saved but they have never run, so they are not showing up in this report. Well... perhaps it is wise to question the value of these reports if they have never actually even been executed. It looks like a cleanup opportunity to me.
     
  3. lizm

    lizm Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Virginia
    Is there any way to mark this as "answered"?