An Oracle blog about BI Publisher

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!

Join the discussion

Comments ( 14 )
  • Chris Tuesday, June 8, 2010
    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!
  • Tim Dexter Tuesday, June 8, 2010
    Not yet, its on the list. Tim
  • Anugna Monday, June 14, 2010
    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.
  • Anugna Monday, June 14, 2010
    Actually this is the exact choose i need

  • Steve White Tuesday, June 15, 2010
    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?
  • Steve White Tuesday, June 15, 2010
    PS. you live in the Rockies!? cool
  • prasant Monday, June 21, 2010
    do you have any update or e.g demonstrating pre-processing xml with xsl for excel templates?
  • Wannes Schols Tuesday, June 22, 2010
    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(ExcelServiceTemplate.java:718)

    at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedServiceTemplateData(ExcelServiceTemplate.java:737)

    at oracle.apps.xdo.servlet.ExcelCoreProcessor.transform(ExcelCoreProcessor.java:49)

    at oracle.apps.xdo.servlet.CoreProcessor.process(CoreProcessor.java:293)

    at oracle.apps.xdo.servlet.CoreProcessor.generateDocument(CoreProcessor.java:80)

    at oracle.apps.xdo.servlet.ReportImpl.renderBodyHTTP(ReportImpl.java:562)

    at oracle.apps.xdo.servlet.ReportImpl.renderReportBodyHTTP(ReportImpl.java:265)

    at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:264)

    at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:244)

    at oracle.apps.xdo.servlet.XDOServlet.doGet(XDOServlet.java:172)

    at oracle.apps.xdo.servlet.XDOServlet.doPost(XDOServlet.java:195)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)

    at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

    at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)

    at oracle.apps.xdo.servlet.security.SecurityFilter.doFilter(SecurityFilter.java:97)

    at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621)

    at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:368)

    at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:866)

    at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:448)

    at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:216)

    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:117)

    at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:110)

    at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)

    at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)

    at java.lang.Thread.run(Thread.java:595)
  • brice Friday, July 9, 2010
    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.
  • Jay Monday, July 26, 2010
    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.
  • Mythri Saturday, August 14, 2010
    SUB: Excel Templates
    Patch 10023449 for BI Publisher says it supports extended XSL functions fr Excel Templates, Can you please mention syntax for them.
  • Nisha Mathew Monday, August 23, 2010
    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.
  • Deepak Friday, September 10, 2010
    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,
  • guest Tuesday, December 6, 2011

    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

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.