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.

BIP_Excel

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>”

BIP_Excel2
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.

BIP_Excel3
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?>

Comments are closed.

%d bloggers like this: