Real Excel Templates I
- by Tim Dexter
As promised, I'm starting to document the new Excel templates that I teased you all with a few weeks back. Leslie is buried in 11g documentation and will not get to officially documenting the templates for a while. I'll do my best to be professional and not ramble on about this and that, although the weather here has finally turned and its 'scorchio' here in Colorado today. Maybe our stand of Aspen will finally come into leaf ... but I digress.
Preamble
These templates are not actually that new, I helped in a small way to develop them a few years back with Excel 'meistress' Shirley for a company that was trying to use the Report Manager(RR) Excel FSG outputs under EBS 12. The functionality they needed was just not there in the RR FSG templates, the templates are actually XSL that is created from the the RR Excel template builder and fed to BIP for processing. Think of Excel from our RTF templates and you'll be there ie not really Excel but HTML masquerading as Excel.
Although still under controlled release in EBS they have now made their way to the standlone release and are willing to share their Excel goodness. You get everything you have with hte Excel Analyzer Excel templates plus so much more. Therein lies a question, what will happen to the Analyzer templates? My understanding is that both will come together into a single Excel template format some time in the post-11g release world. The new XLSX format for Exce 2007/10 is also in the mix too so watch this space.
What more do these templates offer? Well, you can structure data in the Excel output. Similar to RTF templates you can create sheets of data that have master-detail n relationships. Although the analyzer templates can do this, you have to get into macros whereas BIP will do this all for you. You can also use native XSL functions in your data to manipulate it prior to rendering. BP functions are not currently supported. The most impressive, for me at least, is the sheet 'bursting'. You can split your hierarchical data across multiple sheets and dynamically name those sheets. Finally, you of course, still get all the native Excel functionality.
Pre-reqs
You must be on 10.1.3.4.1 plus the latest rollup patch, 9546699. You can patch upa BIP instance running with OBIEE, no problem
You need Excel 2000 or above to build the templates
Some patience - there is no Excel template builder for these new templates. So its all going to have to be done by hand. Its not that tough but can get a little 'fiddly'. You can not test the template from Excel , it has to be deployed and then run.
Limitations
The new templates are definitely superior to the Analyzer templates but there are a few limitations.
Re-grouping is not supported. You can only follow a data hierarchy not bend it to your will unless you want to get into macros.
No support for BIP functions. The templates support native XSL functions only.
No template builder
Getting Started
The templates make the use of named cells and groups of cells to allow BIP to find the insertion point for data points. It also uses a hidden sheet to store calculation mappings from named cells to XML data elements. To start with, in the great BIP tradition, we need some sample XML data. Becasue I wanted to show the master-detail output we need some hierarchical data. If you have not yet gotten into the data templates, now is a good time, I wrote a post a while back starting from the simple to more complex. They generate ideal data sets for these templates. Im working with the following data set:
<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>
Simple enough to follow and bread and butter stuff for an RTF template.
Building the Template
For an Excel template we need to start by thinking about how we want to render the data. Come up with a sample output in Excel.
Its all dummy data, nothing marked up yet with one row of data for each level. I have the department name and then a repeating row for the employees. You can apply Excel formatting to the layout. The total is going to be derived from a data element. We'll get to Excel functions later.
Marking Up Cells
Next we need to start marking up the cells with custom names to map them to data elements. The cell names need to follow a specific format:
For data grouping, XDO_GROUP_?group_name?
For data elements, XDO_?element_name?
Notice the question mark delimter, the group_name and element_name are case sensitive.
The next step is to find how to name cells; the easiest method is to highlight the cell and then type in the name.
You can also find the Name Manager dialog. I use 2007 and its available on the ribbon under the Formulas section
Go thorugh the process of naming all the cells for the element values you have. Using my data set from above.You should end up with something like this in your 'Name Manager' dialog.
You can update any mistakes you might have made through this dialog.
Creating Groups
In the image above you can see there are a couple of named group cells. To create these its a simple case of highlighting the cells that make up the group and then naming them.
For the EMP group, highlight the employee row and then type in the name, XDO_GROUP?G_EMP?
Notice the 10,000 total is outside of the G_EMP group. Its actually named, XDO_?TOTAL_SALARY?, a query calculated value.
For the department group, we need to include the department name cell and the sub EMP grouping and name it, XDO_GROUP?G_DEPT?
Notice, the 10,000 total is included in the G_DEPT group. This will ensure it repeats at the department level.
Lastly, we do need to include a special sheet in the workbook. We will not have anything meaningful in there for now, but it needs to be present.
Create a new sheet and name it XDO_METADATA. The name is important as the BIP rendering engine will looking for it. For our current example we do not need anything other than the required stuff in our XDO_METADATA sheet but, it must be present. Easy enough to hide it. Here's what I have:
The only cell that is important is the 'Data Constraints:' cell. The rest is optional. To save curious users getting distracted, hide the metadata sheet.
Deploying & Running Templates
We should now have a usable Excel template. Loading it into a report is easy enough using the browser UI, just like an RTF template.
Set the template type to Excel. You will now be able to run the report and hopefully get something like this.
You will not get the red highlighting, thats just some conditional formatting I added to the template using Excel functionality. Your dates are probably going to look raw too. I got around this for now using an Excel function on the cell:
=--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
Google to the rescue on that one. Try some other stuff out.
To avoid constantly loading the template through the UI. If you have BIP running locally or you can access the reports repository, once you have loaded the template the first time. Just save the template directly into the report folder.
I have put together a sample report using a sample data set, available here. Just drop the xml data file, EmpbyDeptExcelData.xml into 'demo files' folder and you should be good to go.
Thats the basics, next we'll start using some XSL functions in the template and move onto the 'bursting' across sheets.
