Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Enabling Excel Functions in BI Publisher Report Excel Output

When displaying numerical data in BI Publisher report using the Excel format, leading zeros in the output values is removed automatically by MS Excel. One way of preventing this is to use fo:bidi-override.


This, however, leads to the situation where Excel functions such as SUM, AVERAGE etc do not work on the values which are displayed using the fo:bidi-override option. This is because of the special characters which are automatically added before and after the actual values.
So how do we get around this.
Well, a workaround is to write the form field in the RTF in this format =”<Form Field>”

This leads to a new problem – if an user chooses to display the report in PDF format, the values are surrounded by the = and “” symbols.

The final solution to this problem then is to query the FND_CONC_PP_ACTIONS table at runtime to determine the output type of the report. If the output type is Excel, use the =”<Form Field>” workaround and if it is PDF then display it normally since the values wont be modified anyway.

<?if:OUTPUT_TYPE="EXCEL"?><?concat('="',TRX_NUMBER,'"')?><?end if?>
<?if:OUTPUT_TYPE="PDF"?><?TRX_NUMBER?><?end if?>

Oracle is proactive

Oracle being proactive about out-of-support systems, patch availability and expiring passwords is nothing new. But this takes it to a different level altogether.


Invoking BI Publisher Web services from BPEL Process to run a report

BI Publisher exposes four public Web services which can be used to perform a variety of activities including running a report.

The screenshot below is of a simple SOA composite which calls the runReport() method on the ReportService Web service to run a report with parameters. While Oracle’s official documentation is sufficient enough to make this a trivial exercise but there are still a couple of points to remember which is why this blog post is here in the first place.


Here is the entire synchronous BPEL process.

BPEL Process

The AssignValues activity assigns the values required for running the report. I am hard-coding most of the values except for the reportAbsolutePath which I am passing as an input to BPEL process request. The reportAbsolutePath, of course, is the report path in the Catalog Folders in BI Publisher.


In the TransformInput activity, I am again hard-coding the parameters required for the BI Publisher report and mapping them to the variable that will be passed to  the Web service. But more importantly, this activity is required for two reasons:

  • The call to the BI Publisher Web service should not contain any empty tags otherwise it will throw errors. So, I am mapping only the non-empty elements to the target variable which will be passed during the call to the Web service.
  • One of the parameters for the runReport() method is the ReportRequest.  The ReportRequest data type in turn contains the item data type which stores the name-value pair for a report parameter. If multiple parameters need to be passed to a report, the item node  must be duplicated.



The AssignSuccessMessage concatenates the elements in the response returned by the call to the ReportService and assigns it to the response message of the BPEL process.


That is about it. As I mentioned earlier it is a pretty simple process and the only thing that you need to take care of is that you are not passing any empty tags in the Web service call.