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.
