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!

Comments:

Can you point me to the documentation on excel format templates or is it out yet?

Posted by Ike Wiggins on April 16, 2010 at 06:57 AM MDT #

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

Posted by Tim Dexter on April 16, 2010 at 07:14 AM MDT #

Hi Tim, Is there a patch number for EBS and will there be a patch for 11i? Thanks, Gareth

Posted by Gareth Roberts on April 20, 2010 at 06:33 AM MDT #

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

Posted by Tim Dexter on April 20, 2010 at 06:48 AM MDT #

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

Posted by Sai Reddiboyina on April 23, 2010 at 12:23 PM MDT #

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

Posted by Matt B on April 23, 2010 at 01:27 PM MDT #

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.

Posted by Marco on April 25, 2010 at 09:44 AM MDT #

I didn't take "Teaser" seriously enough. Be honest, is this fake? (Waiting impatiently)

Posted by Doug on May 03, 2010 at 05:10 AM MDT #

My apologies, its got really busy here. Sorry for stirring things up, Im working on it and they are real. Tim

Posted by Tim Dexter on May 03, 2010 at 05:22 AM MDT #

Thank you for a great post

Posted by at home business opportunity on May 04, 2010 at 10:24 PM MDT #

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.

Posted by Tomislav on June 22, 2010 at 02:11 AM MDT #

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.

Posted by Multiverse on July 15, 2010 at 12:26 PM MDT #

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”.

Posted by pandora charms on October 24, 2010 at 08:20 PM MDT #

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

Posted by Sunil Dutt on November 01, 2010 at 02:48 AM MDT #

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

Posted by guest on May 11, 2011 at 05:51 AM MDT #

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

Posted by guest on August 06, 2013 at 02:59 PM MDT #

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

Posted by Tim on August 06, 2013 at 03:21 PM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

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!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today