Real Excel Templates 1.5

Not the next installment quite yet, just an update from what I knew yesterday. Right after I posted the Real Excel Templates I. Mike from the PM team got in touch to say he and Shirley had just had a meeting with a customer about the Excel Templates and all the fab features. He included BIPs extended functions, data pre-processing, sub templates and other functionality which was great new news.

One caveat, much of the really new stuff, is not quite out in the wild yet. Will let you know as soon as I know more.

Shirley and I shared a conversation around being able to re-group data in the templates. It's one of the most powerful features of the RTF template. Providing the ultimate flexibility in layouts. As I wrote yesterday, you need hierarchical data for Excel templates. I stand corrected, 'Of course you can do that in Excel, here's an example' said Shirley
'Very cunning Shirley, very cunning' says I.
You can basically use the hidden sheet to re-group the data using native XSL. I'll cover the 'how' later.

As you can see Excel templates are the new 'black' with lots of attention and more importantly development cycles to take them forward.

Looks like we are going to have a great weekend weather wise here in Colorado. The yard work and pond are beckoning. Maybe the trout will be rising and I can give my rusty fly casting skills a run for their money. I need some stupid fish thou :0)

See ya'll next week!


Tim Do Oracle have any docs yet on how to run this through the API's, we don't use the fornt end of the product but Excel functionality would be a huge benefit for us! Thanks Chris.

Posted by Chris on June 07, 2010 at 09:17 PM MDT #

Not yet, its on the list. Tim

Posted by Tim Dexter on June 08, 2010 at 03:33 AM MDT #

Tim, I want to use CHOOSE WHEN for calling subtemplates, but i want to use choose When.. Call tc1 End when End choose Choose when Call TC2 End when End choose Call TC3 End when End choose This is the format i needed but , its not allowing me to Use NOT IN Parameter, Can you please suggect me the syntax for using NOT IN with this choose here. Thanks, Anugna

Posted by Anugna on June 14, 2010 at 08:00 AM MDT #

Actually this is the exact choose i need END WHEN END CHOOSE

Posted by Anugna on June 14, 2010 at 08:02 AM MDT #

Hi Tim, I did a RAXINV template (Rel 12, BIP and they wanted a Carried Forward and Brought Forward total (for invoices bigger than one page). I found the exceptfirst and exceptlast display conditions don't realize that page numbers get reset with @section for each invoice. So that didn't work, the totals appear on every page except first and last of the whole invoice print. I tried setting a variable flag to 1 before the print lines loop and 0 after, and then testing the flag to decide whether to print the totals (print carried forward if flag = 1 since it's during line printing). But the flag value seems to be set in some parsing phase(?) so that didn't work either. Is the only way to use your complex line counting method? thanks, Steve

Posted by Steve White on June 14, 2010 at 11:50 PM MDT #

PS. you live in the Rockies!? cool

Posted by Steve White on June 15, 2010 at 12:02 AM MDT #

do you have any update or e.g demonstrating pre-processing xml with xsl for excel templates?

Posted by prasant on June 21, 2010 at 02:35 AM MDT #

Hi, I'm looking into this XML templates, however, I have the following issues: 1) It is not possible to map a value to more than 1 cell? Since cell names need to be unique? 2) I'm getting alot of ArrayIndexOutOfBound errors when adding mappings: [062210_130828960][][EXCEPTION] java.lang.ArrayIndexOutOfBoundsException: 1 at oracle.apps.xdo.template.excel.object.SheetObject.addComponent(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.collectAllNameObjects(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.process(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.parseExcelTemplate(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.generateXSL(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.process(Unknown Source) at oracle.apps.xdo.template.ExcelProcessor.process(Unknown Source) at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedDataForExcelTemplate( at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedServiceTemplateData( at oracle.apps.xdo.servlet.ExcelCoreProcessor.transform( at oracle.apps.xdo.servlet.CoreProcessor.process( at oracle.apps.xdo.servlet.CoreProcessor.generateDocument( at oracle.apps.xdo.servlet.ReportImpl.renderBodyHTTP( at oracle.apps.xdo.servlet.ReportImpl.renderReportBodyHTTP( at oracle.apps.xdo.servlet.XDOServlet.writeReport( at oracle.apps.xdo.servlet.XDOServlet.writeReport( at oracle.apps.xdo.servlet.XDOServlet.doGet( at oracle.apps.xdo.servlet.XDOServlet.doPost( at javax.servlet.http.HttpServlet.service( at javax.servlet.http.HttpServlet.service( at com.evermind.server.http.ResourceFilterChain.doFilter( at at com.evermind.server.http.ServletRequestDispatcher.invoke( at com.evermind.server.http.ServletRequestDispatcher.forwardInternal( at com.evermind.server.http.HttpRequestHandler.doProcessRequest( at com.evermind.server.http.HttpRequestHandler.processRequest( at com.evermind.server.http.HttpRequestHandler.serveOneRequest( at at at$ at com.evermind.util.ReleasableResourcePooledExecutor$ at Thanks, Wannes

Posted by Wannes Schols on June 21, 2010 at 10:34 PM MDT #

hello, I use excel template as they are the only entreprise-class, viable solution for excel output (95kB instead of 1.4MB with rtf templates). Can you send me the how-to for bursting across sheets or regrouping datas. that would be most helpful. thank you and don"t hesitate to contact me for further informations as I have a lot of good arguments for using these templates. regards, brice

Posted by brice on July 08, 2010 at 10:55 PM MDT #

Tim, We are using 11i here. I have a RTF template, Excel output. Excel is stripping out the leading zero's from the character filed '0340' and displaying it as 340. Whereas if I output as PDF I am getting 0340. Looks like Excel is recognizing it as a number and stripping out the leading zero's. What should I do to make Excel read it as character text and not read as a number. Thanks for all your help. Jay

Posted by Jay on July 26, 2010 at 03:39 AM MDT #

Tim, SUB: Excel Templates Patch 10023449 for BI Publisher says it supports extended XSL functions fr Excel Templates, Can you please mention syntax for them. Thanks Mythri

Posted by Mythri on August 14, 2010 at 11:03 AM MDT #

hHi Tim , I have a RTF template, which displays output in Excel .Excel is stripping out the leading zero's from the character filed '0340' and displaying it as 340. Whereas if I output as PDF I am getting 0340. Looks like Excel is recognizing it as a number and stripping out the leading zero's. What should I do to make Excel read it as character text and not read as a number. Thanks for all your help. Nisha

Posted by Nisha Mathew on August 23, 2010 at 01:47 AM MDT #

Hi Nisha, Please use ="" to avoid stripping out leading zero. ="" will not be displayed in xls output but the value will be considered as char. Hope this will resolve ur problem, Deepak

Posted by Deepak on September 10, 2010 at 02:14 AM MDT #

Thanks Deepak.

I was having same issue and tried to apply the text function, which worked fine for numeric fields, but I had same cases where the field was alphanumeric or numeric and that caused issues. I knew I was overthinking this


Pete Bardallis

Posted by guest on December 06, 2011 at 09:34 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« February 2017