Oracle BI Publisher reports not showing up data unless we change the report output format

We have an Oracle Retail Sales Audit 14.1 (ReSA) application which has some pre-built operational insights reports.  These are OBIEE/Oracle BI Publisher reports for Sales Auditors or Financial managers.

While validating the ReSA application one of the user comes up with a question asking "When I try to view a report from the ReSA application it shows a blank BI Publisher report and if only I change the report format to PDF or html it shows up the report data.

After doing some R&D, I found the reason - it is because of the report level property "Auto Run" marked unchecked

 Auto Run - This feature will not allow the report to execute immediately when user opens the report.  This will prompt the user to select parameter values before it actually runs the report

Oracle BI Publisher Plug-in not visible

Here is my first blog on BI Publisher.  After working for years in Oracle BIEE of different versions, I got an opportunity to work in BI Publisher 11g now.

To start with I install Oracle BI Publisher 11g Trial Edition (v11.1.1.7.1) on my 64 bit windows machine – All good.  Then I went on installing the Oracle BI Publisher Desktop 11.1.1.7.0 for 64 bit Office on Windows, unfortunately, the installation asked to install a 32 bit version of the BI Publisher Desktop.  I suspect it could be due to the version of the Microsoft office tools.

I then downloaded the below desktop version and tried installing - Oracle BI Publisher Desktop 11.1.1.7.0 for 32 bit Office on Windows.  The installation successfully completed in less than 2 minutes, but I could not see the BI Publisher plug-in on the windows document.  I tried installing different versions of Oracle BI Publisher Desktop, but no luck

I finally find a way !!

All we need to do is to navigate to the below path and run the setup.exe

C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\DotNetInstallFiles  (This path could be different for your installation)

In few seconds you will notice the BI Publisher add-in available on your MS Word or MS Excel office tools.

Top N analysis

I had a requirement to display the number of orders each state is making in a specific time period. This report should show only Top 5 states plus one record for all orders from rest of states. 

Though there are several ways of doing this, but I did using combined with similar request.

1) Create a request which is having states and orders count. Apply filter on "Orders count" where rank(orders) is greater than 5.  Save the request under shared folders in some name.

2) Create another request with necessary columns needed and apply filter on "Orders count" such that it will display only the Top 5 states. Add one dummy column to the report and hard code value ‘1’.  I will use this dummy column for sorting purpose later

3) Now select combined with similar request option, create the request like as earlier mentioned and apply filter on state, Using Advanced -> "filter based on result of another request" and point to the first request (item 1) using the Browse. Add one blank column to the report and hard code value ‘2’ for sorting purpose and edit column formula of reporting state as follows;

case when CUSTOMER.STATE = 'X' then 'Y' Else 'OTH' end 

(Make sure you really don't have a valid state_cde as 'X' now or in future, otherwise results may go wrong)

4) Sort the report using the dummy column

5) Finally hide the dummy column using column properties -> Column format -> Hide

 

Avoid Null in OBIEE dashboard prompts

It is always possible that NULL or white spaces appear in the dimensional attributes.  If we create a prompt with control type "multi-select" for that dimension, we will see blanks appearing in the list of values.  My customer said for any prompt they don't want to see NULL appearing, instead they want to see some text, something like, say "NULL VALUE" .

How to do this?

Add the below in Show "SQL Results" of the prompt

SELECT CASE WHEN TRIM(TABLE."COL NAME") IS NULL THEN 'NULL VALUE' ELSE TABLE."COL NAME" END FROM SUBJECT AREA

TRIM function in the above SQL will convert white spaces to NULL which is then decoded to 'NULL VALUE' using the CASE statement