Forum Stats

  • 3,855,322 Users
  • 2,264,499 Discussions
  • 7,905,968 Comments

Discussions

how to share reports by updating EUL4 tables in discoverer 4i?

534414
534414 Member Posts: 58
edited Sep 15, 2008 5:07PM in Discoverer
Hi All,

We are upgrading from 3i to 10g. 3i to 4i is in progress.
Has anyone tried to modify the sharing properties of a 4i disco report by updating any of the eul tables.
Right now users have the reports saved on their local systems. We have asked them to save it to the database.

I am tryign to update all the report properties from the back end to share them with the test acc so that they can be tested after each upgrade.
The other manual method is to physically save all the report using the test acc which I want to avoid but will persue if I have to.

Any feedback is appreciated.

Thanks.

Best Answer

  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    Answer ✓
    Hi Paul
    Here are a couple of scripts that will show you how to query who is sharing and what is being shared.

    Discoverer 4
    SELECT
    AP_CREATED_BY "Owner",
    DOC_NAME "Workbook Name",
    EU_USERNAME "Shared With"
    FROM
    EUL4_EUL_USERS USRS,
    EUL4_DOCUMENTS DOCS,
    EUL4_ACCESS_PRIVS PRIVS
    WHERE
    PRIVS.GD_DOC_ID = DOCS.DOC_ID AND
    USRS.EU_ID = PRIVS.AP_EU_ID
    ORDER BY
    "Owner", "Workbook Name", "Shared With" ;


    Discoverer 10g
    SELECT
    AP_CREATED_BY "Owner",
    DOC_NAME "Workbook Name",
    EU_USERNAME "Shared With"
    FROM
    EUL5_EUL_USERS USRS,
    EUL5_DOCUMENTS DOCS,
    EUL5_ACCESS_PRIVS PRIVS
    WHERE
    PRIVS.GD_DOC_ID = DOCS.DOC_ID AND
    USRS.EU_ID = PRIVS.AP_EU_ID
    ORDER BY
    "Owner", "Workbook Name", "Shared With" ;

    As you can see, the only difference is the EUL number, where it changes from 4 to 5.

    The sharing is maintained wholly within the EULx_ACCESS_PRIVS table. In theory, inserting rows into this table with the right settings and links allows a workbook to be shared. In reality, the issue is more complex because there is a sequence number generator behind the scenes managing the ID values for every item within the EUL.

    Thus, while I think it is possible to automate sharing I personally don't recommend it. All you would be doing is propogating one issue to become an even bigger issue. My recommendation would be to take a step back and determine what it is your company wants to do. I believe you should have a library of reports shared with either user roles or responsibilities, depending upon whether you are using a standard or Apps mode EUL. The library would own all of the workbooks and would be the only place from where sharing should take place.

    You see if you have allowed users to save workbooks to the file system you will more than likely have users who have re-invented the wheel over and over again. Many workbooks will be duplicates of others, many will be almost identical, but most will be redundant. To simply copy all of these into the database may not be the best for you as this could be a nightmare to maintain.

    I have a white paper on sharing on my downloads page (http://learndiscoverer.com/downloads/downloads.htm) which you are free to download and read. It may help.

    Best wishes
    Michael

Answers

  • Michael Armstrong-Smith
    Michael Armstrong-Smith Member Posts: 4,403
    Answer ✓
    Hi Paul
    Here are a couple of scripts that will show you how to query who is sharing and what is being shared.

    Discoverer 4
    SELECT
    AP_CREATED_BY "Owner",
    DOC_NAME "Workbook Name",
    EU_USERNAME "Shared With"
    FROM
    EUL4_EUL_USERS USRS,
    EUL4_DOCUMENTS DOCS,
    EUL4_ACCESS_PRIVS PRIVS
    WHERE
    PRIVS.GD_DOC_ID = DOCS.DOC_ID AND
    USRS.EU_ID = PRIVS.AP_EU_ID
    ORDER BY
    "Owner", "Workbook Name", "Shared With" ;


    Discoverer 10g
    SELECT
    AP_CREATED_BY "Owner",
    DOC_NAME "Workbook Name",
    EU_USERNAME "Shared With"
    FROM
    EUL5_EUL_USERS USRS,
    EUL5_DOCUMENTS DOCS,
    EUL5_ACCESS_PRIVS PRIVS
    WHERE
    PRIVS.GD_DOC_ID = DOCS.DOC_ID AND
    USRS.EU_ID = PRIVS.AP_EU_ID
    ORDER BY
    "Owner", "Workbook Name", "Shared With" ;

    As you can see, the only difference is the EUL number, where it changes from 4 to 5.

    The sharing is maintained wholly within the EULx_ACCESS_PRIVS table. In theory, inserting rows into this table with the right settings and links allows a workbook to be shared. In reality, the issue is more complex because there is a sequence number generator behind the scenes managing the ID values for every item within the EUL.

    Thus, while I think it is possible to automate sharing I personally don't recommend it. All you would be doing is propogating one issue to become an even bigger issue. My recommendation would be to take a step back and determine what it is your company wants to do. I believe you should have a library of reports shared with either user roles or responsibilities, depending upon whether you are using a standard or Apps mode EUL. The library would own all of the workbooks and would be the only place from where sharing should take place.

    You see if you have allowed users to save workbooks to the file system you will more than likely have users who have re-invented the wheel over and over again. Many workbooks will be duplicates of others, many will be almost identical, but most will be redundant. To simply copy all of these into the database may not be the best for you as this could be a nightmare to maintain.

    I have a white paper on sharing on my downloads page (http://learndiscoverer.com/downloads/downloads.htm) which you are free to download and read. It may help.

    Best wishes
    Michael
  • Rod West
    Rod West Member Posts: 4,025 Gold Trophy
    Hi,

    This is the SQL I have used to share all reports with the PUBLIC user in Disco 4i. You can adapt for a different user.

    insert into eul4_access_privs
    (AP_ID,
    AP_TYPE,
    AP_EU_ID,
    AP_PRIV_LEVEL,
    GP_APP_ID,
    GBA_BA_ID,
    GD_DOC_ID,
    AP_ELEMENT_STATE,
    AP_CREATED_BY,
    AP_CREATED_DATE,
    AP_UPDATED_BY,
    AP_UPDATED_DATE,
    NOTM)
    select EUL4_ID_SEQ.nextval, 'GD', EU.EU_ID, 0, NULL, NULL, ED.DOC_ID, 0,
    'EUL_US', SYSDATE, 'EUL_US', SYSDATE, 0
    from (select doc_id
    from eul4_documents
    minus
    select gd_doc_id doc_id
    from eul4_access_privs
    where ap_eu_id =
    (select EU_ID from eul4_eul_users
    where EU_USERNAME = 'PUBLIC' AND EU_SECURITY_MODEL = 0) ) ED,
    eul4_eul_users EU
    where EU.EU_USERNAME = 'PUBLIC' AND EU.EU_SECURITY_MODEL = 0


    Rod West
    Rod West
  • Hi Rod

    That's a cool script

    Michael
  • 534414
    534414 Member Posts: 58
    Hi Rod,

    Thx a bunch. You just made my life a lot easier. I really appreciate you sharing the script.

    Thanks.
This discussion has been closed.