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.
