Excel Template Teaser
- by Tim Dexter
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!
