Forum Stats

  • 3,815,780 Users
  • 2,259,079 Discussions
  • 7,893,227 Comments

Discussions

Need to know the table name where to find number of times each discoverer report was run

User_IF3GH
User_IF3GH Member Posts: 61 Blue Ribbon
edited Mar 17, 2020 9:33AM in Discoverer

Hello,

need help to find out answers for below questions, in which table I can find out these answers

Discoverer Reports: For all the disco reports.

    1. Number of times each report was run.
    2. Number of different users running the Report,’
    3. If possible, by username number of times they
      have run the Report.
    4. The last time the Report was run.

Oracle Report: Similar information for each Oracle report

Regards,

Meetu

Alexander Pavlik

Answers

  • mrigg
    mrigg Member Posts: 98 Blue Ribbon
    edited Dec 5, 2019 7:44AM

    For Discoverer, this is the "View SQL" for the report I run for query stats.  Not sure if it will run as-is for someone else, but the relevant table is EUL5_QPP_STATS, owned by the Discoverer Administrator account.

      SELECT NVL (O1176226.QS_ACT_ELAP_TIME, 0) / 60,

            TRUNC (O1176226.QS_CREATED_DATE),

            O1176226.QS_DOC_NAME || '-' || O1176226.QS_DOC_DETAILS,

            O1176226.QS_ACT_CPU_TIME,

            O1176226.QS_ACT_ELAP_TIME,

            O1176226.QS_COST,

            O1176226.QS_CREATED_BY,

            O1176226.QS_CREATED_DATE,

            O1176226.QS_DOC_DETAILS,

            O1176226.QS_DOC_NAME,

            (DECODE (O1176226.QS_STATE,

                      0, 'Cancelled',

                      1, 'Timed Out',

                      2, 'Completed')),

            (O1176226.QS_NUM_ROWS),

            SYS.DATABASE_NAME

        FROM DISCADM.EUL5_QPP_STATS O1176226

      WHERE    (TRUNC (O1176226.QS_CREATED_DATE) BETWEEN

                  DECODE ( UPPER ( :"Start_Date"),'TODAY', TRUNC ( SYSDATE),  TO_DATE ( :"Start_Date"))  AND :"End_Date")

            AND ( ( (  UPPER (O1176226.QS_CREATED_BY) LIKE :"User"

                      OR UPPER (O1176226.QS_CREATED_BY) IN ( ( :"User")))))

            AND (UPPER (O1176226.QS_DOC_DETAILS) LIKE :"Worksheet")

            AND (UPPER (O1176226.QS_DOC_NAME) LIKE :"Report Name")

    ORDER BY O1176226.QS_CREATED_DATE DESC

  • User_IF3GH
    User_IF3GH Member Posts: 61 Blue Ribbon
    edited Dec 6, 2019 10:23AM

    Thank you so much let me check

  • User_IF3GH
    User_IF3GH Member Posts: 61 Blue Ribbon
    edited Dec 6, 2019 10:24AM

    do you how can we get these number for Oracle Reports?

  • mrigg
    mrigg Member Posts: 98 Blue Ribbon
    edited Dec 6, 2019 10:33AM

    No, sorry, can't help on that one.

  • Andy Haack
    Andy Haack Member Posts: 16 Blue Ribbon
    edited Mar 17, 2020 9:33AM

    There is a Blitz report SQL in our library showing this information: https://www.enginatics.com/reports/dis-worksheet-execution-history/

    With regards to the Oracle reports history, these are run in EBS as background concurrent processes, and you can use a SQL like this to review the history (restrict to execution method='Oracle Reports'): https://www.enginatics.com/reports/fnd-concurrent-requests/

    Alexander Pavlik