There was a question in Oracle Cloud customer connect forum about fetching all the report names and report path in a given folder.
It is rather cumbersome to manually list the report names and path for each and every report in a given folder. In this post I want to show you the two different options that I know of.
- GL_FRC_REPORTS_B Table
- Catalog web service
Using GL_FRC_REPORTS_B Table
This is pretty straight forward. The table has details of all the custom BI reports and OTBI analyses created. The following SQL can be used to view all of the custom reports / analyses developed.
SELECT *
FROM gl_frc_reports_b
WHERE report_path like '%/Custom/%';
Using Catalog Web Service
The BI catalog web service can be used to display contents of a given folder. We will utilize the getFolderContents () method to get the desired results. The method returns many useful information including:
- Display Name
- Absolute Path
- Type
In this post I will show you how to invoke the web service using Power Shell. You can use Java, Python and many other programming languages to achieve similar results. You can read more about the Catalog Web Service here.
I am assuming you have access to the BI Server. The following information is needed to successfully invoke the Web Service:
- User Name
- Password
- URL
- Folder Name
Create the SOAP payload file as shown below. Make sure to provide correct user name, password and folder name.
https://gist.github.com/raj-arun/0790c191156831bac6a3a3d32b669f64
Save the payload file and the power shell script to a local folder. Execute the Power Shell script and you should get the results as shown below.
You can download the necessary files from my GitHub repo. If you know of another way of achieving the same results let me know. Feel free to use the code.
You may also modify the Power Shell script to generate a CSV file with the report details.
Your comments and feedback are most welcome.