Infrastructure at your Service

Oracle Team

Creating Custom reports with BI Publisher

BI Publisher offers you the possibility to create your own custom reports, I will describe you how to create a report displaying the tablespace size and used size in a simply way.

It is advised to create a specific folder in which we will create our reports, because in case of upgrade you won’t lose your BI reports. In the catalog window, you select create folder:



We have to grant permissions to this folder to Enterprise Manager Users who will have access to this report; we give Read, Write, Delete access to the user with the EMBIPAuthor role, and only Read access to the EMBIPViewer, we also select Apply permissions to items within this folder:


We also create a data model folder in the psi_report folder and we also grant the adequate permissions to the users:


Now we can create the data model:


What do we need? We need a list of values (for the list of database instance), a parameter (the name of the database):


We select list of value to define the database list and we select Query Builder:


You have access to all the mgmt$ sysman’s view of the Enterprise Manager repository. You select for example the mgmt$dbninstanceview and the fields target_guid and instance_name:


The query is automatically generated:


Now we add parameters :


Once we have created a list of values and parameters, we can create a data set:


You select SQL Query, you give a name for the data set and you select Query Builder:


In the query builder, you select the mgmt$dbninstance and mgmt$db_tablespaces. By selecting in the right case of the field TARGET_GUID, we select the column for join, the we select the other columns we need, such as tablespace_size and tablespace_used_size:


You select the condition field in the Query Builder windows in order to add a condition to the target_guid with the list_of_database parameter:


We save the data model in the psi_report/data model folder:


You also have to save the data model as Sample Data:


Finally we can create a new report:


We select an existing Data Model:


We can select different options:


You build your report by selecting the different fields, and pushing them in the right window:


Finally you save the report file:


Your report is ready to be visualized; selecting the instance name from the menu allows you to visualize the report for each database instance:



As we have access to the repository views,there are a lot of possibilities to create reports about CPU, SGA, AVAILIBILITY_HISTORY, ALERT_HISTORY, COMPLIANCE, aso…:

For example, it is quite easy to display the SGA details of ou database instances in a few minutes:


The BI Publisher environment allows the report scheduling, there is also the possibilty to receive those reports by mail. BI Publisher reporting is a very powerful tool allowing Oracle DBAs to generate reports displaying the overall architecture of their environment.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Oracle Team
Oracle Team