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.
