Oracle BI Publisher Blog

I exchanged several mails with Scott from General Dynamics regarding FSG outputs with BI Publisher. He was looking at using the Financial Report Template Editor (in Excel)solution from the Report Manager team for FSG reports and had some questions. He had an issue with loading an updated Excel template into the template manager via their UI - theres a bug open for this that he is now hopefully attached to. The discussion opened up a little into the capabilities within the Excel template solution - I provided some insight which I think is worth sharing with a wider audience.

As some of you know there is a true Excel template solution in the works - its not quite generally available - we are working through some kinks and requirements with a customer at the moment and once they are sorted it will hit metalink without a password requirement. More on those tomorrow. To clear things up on the Report Manager Excel template solution - this is not a binary Excel solution.

You use an Excel interface to build the layout for the FSG data - under the covers an XSLFO template rather than an Excel file is created to format that output. At runtime the XSLFO is applied to the FSG data and the user can pick the output format they want e.g. Excel, PDF, etc. The fact to know here, is that the Excel we generate is not actually Excel - its HTML or more properly XHTML. We then set the mime type such that Excel is used to open the file. There are obvious limitations - no native Excel functions are going to work so you can use a formula function on your data or embed a macro in the template - we do not interpret VBA macro language into XSL code ... sorry. The advantage, is that you get a familiar Excel interface to build the templates in and you get multiple output choices from the template as well as the Excel output. Under the Report Manager module you also get drilldown on those FSG numbers into the GL balance inquiry pages.

So why not just use an RTF template? Why not indeed - you can build everything you can in the Excel interface the only missing piece right now would be the drill links but they are coming soon. Scott did ask about the page width limitations? Those of you that know FSGs will know that the FSG engine can generate reports with almost as many columns as you wish. How do you print that out without getting data truncated on the right hand edge of the paper? The FSG team ship you a template out of the box that wraps the data to a new page when it hits the edge of the 'paper' no matter how many columns of data you have. This was designed with the idea in mind that you wanted to print out the FSG where the page size is determined by the paper you feed into the printer. But, if you just want to get the content out to Excel then that paper size can be adjusted.

Think about it, when you first open a new Word doc it makes an assumption that you want 'Letter' or 'A4' sized paper depending on where you are in the world. There is nothing to stop you going in and setting the page width and height to their maximum 22" x 22" - now as long as your output is destinined for Excel or HTML where there is no concept of page size (until you want to print) then you can use this custom width and height and avoid the wrapping that we do for you. With the generic FSG template just extend the table width to the width of the page and you're done.

Your users who want to work in Excel will be happier - they will have a 30 column report laid out in Excel (without wrapping) and they can beaver away using familiar native Excel features to dot the i's and cross the t's. If they want to print their modified report - Excel will handle it with its 'Z' or 'N' print format. Once they are happy with the numbers in the original report they can just apply a new template that expects to be printed ie its Letter or A4 sized and you get all the high fidelity reporting and wrapping from a regular sized RTF template.

Tomorrow, I'll tempt you with some features that are coming with the new binary Excel templates ...

Yesterday, I wrote about the possibilities and limitations of using BIP templates with the FSG engine from GL to get Excel formats out. That was what was possible 'today', today's post is about what is going possible 'tomorrow.' Now, dont get excited, thats not a literal 'tomorrow' ... we have been working with a customer on the templates. We are a little ways away from prime time - Im happy that they are ready for non-FSG applications but the existing FSG Excel solution ie ADI (not the web version) has a raft of cool features that we need to replicate - I'll get into those.

The basic functionality lets you map cells and groups of cells to your incoming XML data source and then add almost any native Excel functionality to the template. The report can then be executed via the concurrent manager and then, via the OPP the Excel template applied to the data and the final Excel file generated. Users can then access the output just as they would any other report in the system via the SRS forms/pages.

Those of you that know the FSG engine will know that you can run a 'content set' on top of a given FSG report. For the uninitiated this is the ability to run the report for a given set of say companies or cost centers within EBS - generating a single data set for how wver many members there are in the content set. The neat FSG feature here is that we 'burst' the result for each member of the content set onto its own sheet in the workbook and allow you to control the name of the sheet tab.

On the FSG product side they have implemented some very neat drill down features. Those of you using the ADI or WebADI products will know that your outputs can have links on every number in the Excel output that when clicked drill you into the GL inquiry pages. This will be available in the Excel and RTF templates when generally released. Enabling users to drill back into the application to discover where that huge and erroneous looking number in the report came from.

We are working on other enhancements to the FSG solution namely the ability to run an FSG Report Set and have each report and its content set members (if applied) burst on to their own sheets in the Excel output - this is a tough one to implement so no promises at this point.

Sorry, I can not commit to a GA date right now. We are working on R12 and there are plans to back port to 11i10 when its all wrapped up - but its coming and it aint far off.