• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:May 18, 2023
  • Reading time:5 mins read
You are currently viewing How to get the list of all BI Reports from an instance?
How to get the list of all BI Reports from an instance?

In this article, we will look into a couple of approaches using which we can fetch the list of all reports from an instance.

  1. GL_FRC_REPORTS_B – This table contains the list of all BI/OTBI Dashboards present in an instance. Although this is a Finance related table, if both HCM and Finance are implemented in an instance, we would also get the HCM-related reports out of this table. If only HCM is implemented, then it will not produce any results.
select Report_Path, Report_Type_Code, created_by, creation_date, last_updated_by, last_update_date 
from GL_FRC_REPORTS_B
where report_path like '%Payroll%'

2) Export Captions – We have already seen this in previous article. The drawback of this approach is, we cannot get the list of reports from the top-level folder like /Custom. We need to go down 2 or 3 levels where the actual BI Reports reside.

3) SOAP Service – We can use the SOAP WSDL ExternalReportWSSService.WSDL to get the list of reports, however, this approach is also limited to only specific folders and does not traverse to the folders beneath the current folder.

https://abcd-dev1.fa.us6.oraclecloud.com/xmlpserver/services/ExternalReportWSSService?WSDL

Sample Payload:

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
   <soap:Header/>
   <soap:Body>
      <pub:getFolderContents>
         <pub:folderAbsolutePath>/Custom/Human Capital Management/Payroll</pub:folderAbsolutePath>
      </pub:getFolderContents>
   </soap:Body>
</soap:Envelope>

Sample Output:

<env:Envelope xmlns:env="http://www.w3.org/2003/05/soap-envelope">
   <env:Header/>
   <env:Body>
      <ns2:getFolderContentsResponse xmlns:ns2="http://xmlns.oracle.com/oxp/service/PublicReportService">
         <ns2:getFolderContentsReturn>
            <ns2:catalogContents>
               <ns2:item>
                  <ns2:absolutePath>/Custom/Human Capital Management/Payroll/Data Models</ns2:absolutePath>
                  <ns2:creationDate>2023-01-31T15:25:54.000Z</ns2:creationDate>
                  <ns2:displayName>Data Models</ns2:displayName>
                  <ns2:fileName>Data Models</ns2:fileName>
                  <ns2:lastModified>2023-01-31T15:25:54.000Z</ns2:lastModified>
                  <ns2:lastModifier>Unknown</ns2:lastModifier>
                  <ns2:owner>TECH.ADMIN</ns2:owner>
                  <ns2:parentAbsolutePath>/Custom/Human Capital Management/Payroll</ns2:parentAbsolutePath>
                  <ns2:type>Folder</ns2:type>
               </ns2:item>
               <ns2:item>
                  <ns2:absolutePath>/Custom/Human Capital Management/Payroll/Misc Data</ns2:absolutePath>
                  <ns2:creationDate>2023-01-31T15:25:55.000Z</ns2:creationDate>
                  <ns2:displayName>Misc Data</ns2:displayName>
                  <ns2:fileName>Misc Data</ns2:fileName>
                  <ns2:lastModified>2023-01-31T15:25:55.000Z</ns2:lastModified>
                  <ns2:lastModifier>Unknown</ns2:lastModifier>
                  <ns2:owner>TECH.ADMIN</ns2:owner>
                  <ns2:parentAbsolutePath>/Custom/Human Capital Management/Payroll</ns2:parentAbsolutePath>
                  <ns2:type>Folder</ns2:type>
               </ns2:item>
               <ns2:item>
                  <ns2:absolutePath>/Custom/Human Capital Management/Payroll/Employees with More than one Payment Method.xdo</ns2:absolutePath>
                  <ns2:creationDate>2023-01-31T15:25:54.000Z</ns2:creationDate>
                  <ns2:displayName>Employees with More than one Payment Method</ns2:displayName>
                  <ns2:fileName>Employees with More than one Payment Method.xdo</ns2:fileName>
                  <ns2:lastModified>2023-01-31T15:25:54.000Z</ns2:lastModified>
                  <ns2:lastModifier>tech.admin</ns2:lastModifier>
                  <ns2:owner>TECH.ADMIN</ns2:owner>
                  <ns2:parentAbsolutePath>/Custom/Human Capital Management/Payroll</ns2:parentAbsolutePath>
                  <ns2:type>Report</ns2:type>
               </ns2:item>
               <ns2:item>
                  <ns2:absolutePath>/Custom/Human Capital Management/Payroll/Personal Payment Methods.xdo</ns2:absolutePath>
                  <ns2:creationDate>2023-01-31T15:25:55.000Z</ns2:creationDate>
                  <ns2:displayName>Personal Payment Methods</ns2:displayName>
                  <ns2:fileName>Personal Payment Methods.xdo</ns2:fileName>
                  <ns2:lastModified>2023-01-31T15:25:55.000Z</ns2:lastModified>
                  <ns2:lastModifier>tech.admin</ns2:lastModifier>
                  <ns2:owner>TECH.ADMIN</ns2:owner>
                  <ns2:parentAbsolutePath>/Custom/Human Capital Management/Payroll</ns2:parentAbsolutePath>
                  <ns2:type>Report</ns2:type>
               </ns2:item>
            </ns2:catalogContents>
         </ns2:getFolderContentsReturn>
      </ns2:getFolderContentsResponse>
   </env:Body>
</env:Envelope>