3 Replies Latest reply: Jan 13, 2013 3:22 AM by user476771 RSS

    report statistics - number of runs

    user476771
      Hi,

      I had asked awhile ago about the the eul5_documents table and the eul5_qpp_stats table. If I understood correctly...

      The owner of the report should be taken from the documents table and not from the stats table.

      Each qs_created_date in the stats table is the date a report was run. If I want the number of times a report was run, I count the number of qs_created_date s for the report.

      My problem: I want the number of times a report was run according to the owner. Right now, there are users who saved a report using the same name so that a report can appear more than once. Each time, however, the number of times the report was run is the same. In other words, I can have a report:

      RepName Owner #OfRuns
      AA------------A----------100
      AA------------B----------100

      Is there a way to get accurate information as to the number of times a report by a specific owner was run?

      Thanks.
      Leah

      Edited by: user476771 on Dec 17, 2012 5:45 AM
      I would just like to add that I did see an sql in this forum but in calculating the number of runs it had the owner in the stats file = to the owner in the documents file. If the owner in the stats file is not necessarily correct, then I don't understand how to get the accurate information.
        • 1. Re: report statistics - number of runs
          user7417334
          Use the following Query in Discoverer Folder.
          In Discoverer Report use count on "Duration" That would give you No. of Runs.


          SELECT QS.QS_ID
          , trunc(qs.qs_created_date) created
          , qs.qs_created_date datestamp
          , fu.description Who
          , fu.user_name
          , QS.QS_ACT_ELAP_TIME actual
          , cast((QS.QS_ACT_ELAP_TIME/60) as decimal(5,1)) duration
          , QS.QS_NUM_ROWS num_rows
          , qs.qs_doc_owner doc_owner
          , QS.QS_DOC_NAME workbook
          , QS.QS_DOC_DETAILS worksheet
          , qs.qs_object_use_key object_use_key
          , decode(instr(qs.qs_object_use_key,'.',1),0, o.obj_name, 'Multi') folder
          From EUL5.eul5_qpp_stats qs
          , apps.fnd_user fu
          , (SELECT to_char(obj_id) ch_obj_id, obj_name FROM EUL5.eul5_objs) o
          WHERE substr(qs.qs_created_by, 2) = fu.user_id (+)
          AND qs.qs_object_use_key = ch_obj_id (+)
          AND qs.qs_created_date between '01-JAN-2013' and '10-JAN-2013'
          AND fu.user_name = 'USERNAME'


          hope this helps you.

          Edited by: user7417334 on Jan 8, 2013 12:53 PM

          Edited by: user7417334 on Jan 8, 2013 12:53 PM
          • 2. Re: report statistics - number of runs
            user476771
            I just wanted to let you know that I won't be at work until Sunday.

            In the meantime, thanks for your reply. I will let you know how it works out.

            Leah
            • 3. Re: report statistics - number of runs
              user476771
              Hi,

              The select does work, but it is also based on the owner in the stats file. I read somewhere in the forum that that is not accurate.

              Can I rely on the owner in the stats file? If yes, then there is no problem.

              Thanks.
              Leah