An Oracle blog about BI Publisher
-
E Business Suite
April 16, 2010
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.
There are few cool things to notice in this output.
- DEPARTMENT-EMPLOYEE master detail output. Not easy to do in the Excel analyzer.
- 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
- Salary Total - although in the data I have calculated this in the template
- Conditional formatting - this is handled by Excel based on the incoming data
- 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!
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
Is there a patch number for EBS and will there be a patch for 11i?
Thanks,
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
Thanks
Sai
Thanks,
Matt
Tim
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.
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.
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
Thanks
it is 2013 now - still no update on the documentation on excel format templates ????
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
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.