X

An Oracle blog about BI Publisher

Excel Template Teaser

In lieu of some official documentation I'm in the process of putting together some posts on the new 10.1.3.4.1 Excel templates. No more HTML, maskerading as Excel; far more flexibility than Excel Analyzer and no need to write complex XSL templates to create the same output. Multi sheet outputs with macros and embeddable XSL commands are here. Their capabilities are pretty extensive and I have not worked on them for a few years since I helped put them together for EBS FSG users, so Im back on the learning curve.

Let me say up front, there is no template builder, its a completely manual process to build them but, the results can be fantastic and provide yet another 'superstar' opportunity for you. The templates can take hierarchical XML data and walk the structure much like an RTF template. They use named cells/ranges and a hidden sheet to provide the rendering engine the hooks to drop the data in. As a taster heres the data and output I worked with on my first effort:

<EMPLOYEES>
<LIST_G_DEPT>
<G_DEPT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<EMP_NAME>Jennifer Whalen</EMP_NAME>
<EMAIL>JWHALEN</EMAIL>
<PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
<HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>4400</SALARY>
</G_EMP>
</LIST_G_EMP>
<TOTAL_EMPS>1</TOTAL_EMPS>
<TOTAL_SALARY>4400</TOTAL_SALARY>
<AVG_SALARY>4400</AVG_SALARY>
<MAX_SALARY>4400</MAX_SALARY>
<MIN_SALARY>4400</MIN_SALARY>
</G_DEPT>
...
</LIST_G_DEPT>
</EMPLOYEES>

Structured XML coming from a data template, check out the data template progression post.


I can then generate the following binary XLS file.


ExcelTemplate1.gif

There are few cool things to notice in this output.


  1. DEPARTMENT-EMPLOYEE master detail output. Not easy to do in the Excel analyzer.
  2. Date formatting - this is using an Excel function. Remember BIP generates XML dates in the canonical format. I have formatted the other data in the template using native Excel functionality
  3. Salary Total - although in the data I have calculated this in the template
  4. Conditional formatting - this is handled by Excel based on the incoming data
  5. Bursting department data across sheets and using the department name for the sheet name. This alone is worth the wait!

there's more, but this is surely enough to whet your appetite.

These new templates are already tucked away in EBS R12 under controlled release by the GL team and have now come to the BIEE and standalone releases in the 10.1.3.4.1+ rollup patch. For the rest of you, its going to be a bit of a waiting game for the relevant teams to uptake the latest BIP release.

Look out for more soon with some explanation of how they work and how to put them together!

Join the discussion

Comments ( 18 )
  • Ike Wiggins Friday, April 16, 2010
    Can you point me to the documentation on excel format templates or is it out yet?
  • Tim Dexter Friday, April 16, 2010
    Hi Ike
    They are out but not officially documented. Im writing some posts to at least get the bascis out there. Will be next week thou before I get the first out.
    Tim
  • Gareth Roberts Tuesday, April 20, 2010
    Hi Tim,
    Is there a patch number for EBS and will there be a patch for 11i?
    Thanks,
    Gareth
  • Tim Dexter Tuesday, April 20, 2010
    Hi Gareth
    There is but its top secret and you need to request it via support. Sadly Im not kidding, its under controlled release. Just log an SR and support should be able to get you the patch password.
    The number I have is now obselete but our stupid system does not tell me what the superceeding patch # is.
    Tim
  • Sai Reddiboyina Friday, April 23, 2010
    Hi Tim, I am visiting your blog almost every day as many times as i can just to see if you have written anything new about Excel Templates. I hope its not just me, I bet there are hunderds or perhaps thousands of developers waiting for your new blogs on Excel Templates.
    Thanks
    Sai
  • Matt B Friday, April 23, 2010
    Tim, I've been checking your blog every day much like Sai, anxiously awaiting any tidbits that would get us started building Excel templates. We are very excited about the possibilities.
    Thanks,
    Matt
  • Marco Sunday, April 25, 2010
    dido on the waiting. I just built a multi-sheet excel using xsl and i got a kick out of it. so i'm curious about the excel templates you mentioned in your blog.
  • Doug Monday, May 3, 2010
    I didn't take "Teaser" seriously enough. Be honest, is this fake? (Waiting impatiently)
  • Tim Dexter Monday, May 3, 2010
    My apologies, its got really busy here. Sorry for stirring things up, Im working on it and they are real.
    Tim
  • at home business opportunity Wednesday, May 5, 2010
    Thank you for a great post
  • Tomislav Tuesday, June 22, 2010
    Hi,
    just wondering before I apply the patch, is the output file real binary excel file or is it the mhtml with mime type xls... I'm in need of creating a real binary file so I can schedule delivery via email which is now impossible since generated files are huge...
    tnx in advance,
    Tomislav.
  • Multiverse Thursday, July 15, 2010
    When you use the link tag to join two queries, is it supposed to "JOIN" the queries where parentColumn = childColumn?
    It's not working that way. The resulting data is a cartesian product instead of a join. I'm just trying to do a master-detail, but it all the details are jointed to all the master records. There is actually no example of how to do this properly in any documentation. Furthermore, sometimes the link tag is shown with a "condition" attribute, sometimes not. I've tried using the "=" condition or omitting it with the same results.
    Furthermore, what happens in the dataStructure section when you use "link"? I mean, the groups must specify in the source attribute which query to pull from. But in a linked query, shouldn't there really be one source? I tried using the link name in the source attribute, so that should give an indication of how badly this feature is documented.
    Sincerely,
    Multiverse.
  • pandora charms Monday, October 25, 2010
    Great post full of useful tips! My site is fairly new and I am also having a hard time getting my readers to leave comments. Analytics shows they are coming to the site but I have a feeling “nobody wants to be first”.
  • Sunil Dutt Monday, November 1, 2010
    Hi tim,
    Can u tell me In EBS 11i, can i use excel as input template to the report for getting the o/p in excel rather than using the native rtf file as template .
    My requirement will be i will have template built in excel , I want to use the same as template for xml publisher in EBS and produce the o/p in EXCEL.
    I have tried builting that template format in rtf , its becoming a complex task to acheive , suddenly i cam e to know that we can give excel as input template also.
    Please advise and guide me to acheive this .
    Thanks and regards,
    Sunil Dutt.S
  • guest Wednesday, May 11, 2011
    Thanks for the posting, I've tried it and it works great. My only question is how to get the BIP report output in PDF, I've noticed that you can only get it in XL, CSV and data (XML).
    Thanks
  • guest Tuesday, August 6, 2013

    it is 2013 now - still no update on the documentation on excel format templates ????


  • Tim Tuesday, August 6, 2013

    Doc for Excel templates has been available in the main user docs for a while

    http://www.oracle.com/technetwork/middleware/bi-publisher/documentation/index.html

    Tim


  • Sana Sayyad Friday, March 10, 2017

    I needed to post you that little bit of word so as to thank you very much the moment again for all the pleasing opinions you've posted here.


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