Enabling Excel Functions in BI Publisher Report Excel Output
May 22, 2018
Posted by on
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.