Modifying BI Publisher reports in Oracle Enterprise Manager 13c, e.g. ‘Database Usage Tracking Summary’

Recently I bumped into an annoying bug in the code of a report, wrote about it a while ago, notified Oracle. And they has confirmed that a new version of this specific report is in development phase.

But.. a report can be modified, so if it’s not that hard, why wait for a patch when you want to use a report you need in the meantime? It is is not advisable to modify a standard report, because undoubtedly there will be other changes in a patch to come (e.g. the datamodel), but as long you are aware of this…..

So I chose to modify my favourite report ‘Database Usage Tracking Summary’ for this blog.  And it turned out to be surprisingly simple.

My goal is to modify the query of the report, and for fun the header-default. Just for those who are interested in this specific query I will first give a short description of the change.

The query is using two important views:

– SYSMAN.MGMT$EM_LMS_DBA_FEA_INFO_USAGE

– SYSMAN.MGMT$EM_LMS_DBA_FEA_USAGE_STAT

By the way: the old view SYSMAN.MGMT$DB_FEATUREUSAGE has NOT been deprecated.

Description of the problem: in a specific record, the content of the  column ‘feature_info’ in the view SYSMAN.MGMT$EM_LMS_DBA_FEA_INFO_USAGE does not contain the string ‘compression used’.

That’s why the following code in the report gives a false result :

trim(substr (to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2)) != '0'

this code gives the result ‘ta’ , and that’s indeed not equal to ‘0’.

Solution I want to implement (not beautiful but effective for the moment):

decode(
 instr(to_char(feature_info), 'compression used: ',1,1),
 0,0,
 trim(substr(to_char(feature_info), instr(to_char(feature_info), 'compression used: ',1,1) + 18, 2))
 ) != '0'

Navigate to the BI Publisher Enterprise Reports:

There are (at least I think) two ways to navigate to the editing part of a report :

– Just run the report and then edit the report. This is what I did.

– Go to BI Publisher and choose the ‘Catalog’  option – I’ll use this later on.

Choose the report you want and edit this at the upper right corner:

When in the editor screen, just save the report under another name and in a map of your choice.

There’s also a possibility to copy reports while in the Catalog screen (lower left corner), but I chose not to use this:

When choosing  ‘Catalog’ you’ll find the report where you saved it. I named the report DB_Usage_Tracking_Summary_JO1.

When opening this report through the catalog you will be directed to the editing screen. Don’t edit, but choose the title above the report as shown below.

Modify the data set as shown below.

Now you’re able to modify the code. But..  you are editing a data-set which belongs to the original report, so be aware of saving your modifications under another dataset!!

After you’re completely done, don’t forget to save the report.

Just for fun, I modified the lay-out of the reports. This has been well documented at youtube:

https://www.youtube.com/watch?v=-_bSWNbQ2XU

The result of this code and lay-out modification:

Resources:

OEM13c as SAM-tool: https://technology.amis.nl/2016/01/31/usage-tracking-reports-using-oracle-enterprise-manager-13c-sam-tool/

How to customize an existing BI report: https://www.youtube.com/watch?v=-_bSWNbQ2XU