Jump to content
emolloy17

Discoverer - shared reports verses base reports, how to differentiate

Recommended Posts

First, I should ask if my understanding is correct... 

I understand Discoverer users can generate a report and once they like the results they are getting, they can share it with other users so they don't have to write their own report.  To keep them straight, I refer to the original report as the "base report" and the copies that were shared to other users as the "shared report".  

So, here are a few questions:

1) Can the user who receives the shared report change it? 

2) Is the shared report a separate copy of the base report (or just a reference to the base report)?

3) If the base report is changed, will the shared report also change?

4) Is there a way to determine which reports are base reports and which ones are shared?

 

Unrelated, is there a way for the DBA to access all the reports, by all users?  I understand you can log in as a particular user and see their reports, but as a DBA, can you see all reports made by all users?

 

Thanks for the help.

Liz

 

 

Share this post


Link to post
Share on other sites

Hi Liz

First of all. The terminology "base report" is not a term within Discoverer. The correct term is a Workbook, and a Workbook contains one or more Worksheets. Each worksheet is essentially a report. So, if you think in terms of an Excel file, the file itself is the Workbook, with the Excel sheets being the Worksheets. If a workbook is shared with another user then ALL of the worksheets within that workbook are also shared. The worksheets are in essence separate reports, just like Excel sheets are separate, so care has to be taken to make sure that a shared workbook only contain worksheets that really need to be shared.

Let me try and answer your questions one by one:

Q1) Can the user who receives the shared report change it?

A1) No, let me explain. The "owner" of a Discoverer workbook is the only one allowed to save changes to that workbook in the database. Shared users can, however, save a copy of the workbook under their own account, if they have the correct privileges, but it then becomes a separate and distinct workbook. Any changes subsequently made to the original will not be replicated within any saved user workbooks.

Q2) Is the shared report a separate copy of the base report (or just a reference to the base report)?

A2) It is not a separate workbook but a reference to the original workbook.

Q3) If the base report is changed, will the shared report also change?

A3) Yes, if the "owner" makes a change to a worksheet that it is a shared workbook, all of the users with whom the workbook was shared will see the change

Q4) Is there a way to determine which reports are base reports and which ones are shared?

A4) It is possible, using SQL, to query the underlying EUL (End User Layer) tables to determine whether a workbook has been shared or not. If need be, I can share some SQL with you

 

Best wishes

Michael Armstrong-Smith

 

Share this post


Link to post
Share on other sites

Thank you.  This is very helpful and clear.  If you could share the SQL, I'd appreciate it.  We set up a test scenario.  User A creates a report on Thursday and shares it with user B and user C.  Both user B and user C view the report on Friday.  The following query is run:

SELECT 
    USERS.EU_USERNAME
    , DOCUMENTS.DOC_NAME WORKBOOK_NAME
    , ACCESS_PRIVS.AP_UPDATED_DATE LAST_UPDATED
    , DOCUMENTS.DOC_DEVELOPER_KEY WORKBOOK_KEY
    , DOCUMENTS.DOC_DESCRIPTION WORKBOOK_DESCR

FROM
    EUL5_ACCESS_PRIVS ACCESS_PRIVS
    ,EUL5_DOCUMENTS DOCUMENTS
    ,EUL5_EUL_USERS USERS
WHERE
    DOCUMENTS.DOC_ID = ACCESS_PRIVS.GD_DOC_ID
    AND USERS.EU_ID  = ACCESS_PRIVS.AP_EU_ID
    AND DOCUMENTS.DOC_CONTENT_TYPE = 'application/vnd.oracle-disco.wb'
    AND DOCUMENTS.DOC_NAME = '01_OBIE_test_tbj'
ORDER BY 
 USERS.EU_USERNAME, DOCUMENTS.DOC_NAME;
 

The results return 2 rows.  We only see the share with user B and user C on Thursday.  (I don't have access to play around in the data yet, so I'm asking someone else to send me results.)  This suggests that the EUL5_ACCESS_PRIVS table only identifies who the report was shared with... thus providing me a clear roadmap of who the reports have to be shared with in OBIEE.  I'm wondering if I should send a query with an outer join so I can see the creator as well as the users the report is shared with.  Then any entry without an "ACCESS_PRIVS.AP_UPDATED_DATE" value would be the originator and the others could be eliminated as "shared copies".

If I was to just query the EUL_DOCUMENTS table for this one report, would I get 1 entry or 3?  (I've just asked the DBA to query this one report in the EUL_DOCUMENTS table.)  If it is just one entry, then I know all reports in EUL_DOCUMENTS identifies uniquely saved reports... aka, all the reports we need to convert.

Sadly, I'm still just trying to figure out how to identify the scope of the project and how we will know when we have successfully completed it.  My next question is whether there is a way to pull the SQL without having to click through the GUI to display it for each report individually.  I know there are some queries that return SQL, but without documentation on the underlying tables, who knows what it's returning.  Definitely not enough data to indicate it's the SQL for all reports.

Thank you for your help!   

Share this post


Link to post
Share on other sites

So I has been confirmed through tests that the EUL_ACCESS_PRIVS table only identifies who the report is shared with.  To continue our test, UserB saved the report as his own and shared it with User A and User C.

Now when we run the report above it shows the following:

WORKBOOK_NAME  EU_USERNAME  WORKBOOK_KEY

ReportA  UserB  ReportA

ReportA  UserC  ReportA

ReportA  UserA  ReportA1

ReportA  UserC  ReportA1

This shows that the report can be saved with the same name, but the key shows that it is a different version.  The original owners of the report do not appear in this query, but if we look in the EUL5_DOCUMENTS table, we see the 2 entries representing the owners of the reports ( ReportA UserA and ReportA UserB).  

Google is an amazing thing.  I just searched for "desc EUL5_QPP_STATS" and found the following query from you...

Quote

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;

 

Share this post


Link to post
Share on other sites

Do you know how we can get access to view reports generated by other users?  As a conversion/migration team, we need to have access to all reports that need to be converted.  Is there an administrative method for getting access?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×