Real Excel Templates I

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 standalone release and are willing to share their Excel goodness. You get everything you have with the 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 up a BIP instance running with OBIEE, no problem
  • You need Excel 2000 or above to build the templates. The outputs will be of the binary 97-2003 format. Not the new fangled xlsx format.
  • 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. Because 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.

ExcelTemplates1.jpg

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.

ExcelTemplates2.jpg

You can also find the Name Manager dialog. I use 2007 and its available on the ribbon under the Formulas section

ExcelTemplates3.jpg

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.

ExcelTemplates4.jpg

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?

ExcelTemplates5.jpg

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?

ExcelTemplates6.jpg

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:

ExcelTemplates7.jpg

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.

ExcelTemplates8.jpg

Set the template type to Excel. You will now be able to run the report and hopefully get something like this.

ExcelTemplates9.jpg

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.

Update! Take a look at Real Excel Templates 1.5 for some fresh updates on coming capabilities!
Comments:

Tim, Thank you! We are going to put this to work. We look forward to reading more about this.

Posted by Matt B on May 20, 2010 at 03:30 PM MDT #

Hello, Tim! I can't use autofilter functionality in my Excel-template. As i think - it's a bug.

Posted by Jack Carver on May 22, 2010 at 09:50 PM MDT #

My 4th grouping(grand total) seems to clobber the line it is on instead of printing at bottom of group. the hierarchy is 4 levels...detail, position, department and grand totals. in the template Grand Total line shows on line 8 and is not included in xdo_group_?g_main? so why doesn't it print at the bottom???

Posted by Natalie K on June 01, 2010 at 04:00 AM MDT #

When will this be available for 5.6.3 in 11i?

Posted by RD on June 07, 2010 at 02:52 AM MDT #

@RD I dont think they ever will. They are only on controlled release for R12 customers at the moment so the chances of getting them back ported is pretty slim. Sorry. @Natalie. Make sure you check your cell is in the correct named cell group @Jack. I need to check that out. Do you put the autofilter directly into the template ? Tim

Posted by Tim Dexter on June 07, 2010 at 03:09 AM MDT #

Tim, We have developed the excel templates for one of my clients but we have faced one issue while opening the output from notification url. When we approached Oracle support, they have confirmed that the excel templates are not yet supported by oracle. the actual issue is that : "When we tried to view the report output from the notification url, some of the report throwing junk data and not displaying the actual data. The same report output we could able to see it from 'View Output' on SRS window. This is happening with the reports having Excel templates." Do you have any solution for this? Thanks, Vijay

Posted by Vijay on June 07, 2010 at 06:41 PM MDT #

Tim, "Jack. I need to check that out. Do you put the autofilter directly into the template ?" Yes, exactly. As i know, Apache POI team still has't resolved a similar problem - https://issues.apache.org/bugzilla/show_bug.cgi?id=35125 Also, Tim, could you give an example of "XDO_CROSSTAB_??" usage?

Posted by Jack Carver on June 07, 2010 at 07:42 PM MDT #

@Vijay If the report is valid from the SRS window then I would suspect it might be something to do with the link in the notification? Can you view other reports using the link? What are the junk chars? Does Excel open on the client ? Sorry, not the best medium to exchange messages. Drop me a mail with all the details or even the SR # @Jack It does not work for me. I will log an enhancement request for it. The dev team are putting quite a lot of effort into the templates so hopefully it will be supported soon. Hang tight thou, they are trying to get a new release out the door at the moment so it will not be for a while Tim

Posted by Tim Dexter on June 08, 2010 at 03:49 AM MDT #

Tim, If you could provide me your email id I'll send the details. OR you can find more details in the SR# 3-1747954091 --Vijay

Posted by Vijay on June 09, 2010 at 08:00 PM MDT #

Hi, I'm looking into this XML templates, however, I have the following issues: 1) It is not possible to map a value to more than 1 cell? Since cell names need to be unique? 2) I'm getting alot of ArrayIndexOutOfBound errors when adding mappings: [062210_130828960][][EXCEPTION] java.lang.ArrayIndexOutOfBoundsException: 1 at oracle.apps.xdo.template.excel.object.SheetObject.addComponent(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.collectAllNameObjects(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.process(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.parseExcelTemplate(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.generateXSL(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.process(Unknown Source) at oracle.apps.xdo.template.ExcelProcessor.process(Unknown Source) at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedDataForExcelTemplate(ExcelServiceTemplate.java:718) at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedServiceTemplateData(ExcelServiceTemplate.java:737) at oracle.apps.xdo.servlet.ExcelCoreProcessor.transform(ExcelCoreProcessor.java:49) at oracle.apps.xdo.servlet.CoreProcessor.process(CoreProcessor.java:293) at oracle.apps.xdo.servlet.CoreProcessor.generateDocument(CoreProcessor.java:80) at oracle.apps.xdo.servlet.ReportImpl.renderBodyHTTP(ReportImpl.java:562) at oracle.apps.xdo.servlet.ReportImpl.renderReportBodyHTTP(ReportImpl.java:265) at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:264) at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:244) at oracle.apps.xdo.servlet.XDOServlet.doGet(XDOServlet.java:172) at oracle.apps.xdo.servlet.XDOServlet.doPost(XDOServlet.java:195) at javax.servlet.http.HttpServlet.service(HttpServlet.java:763) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.xdo.servlet.security.SecurityFilter.doFilter(SecurityFilter.java:97) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:368) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:866) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:448) at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:216) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:117) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:110) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:595) Thanks, Wannes

Posted by Wannes Schols on June 21, 2010 at 10:35 PM MDT #

Hi, I'm looking into this XML templates, however, I have the following issues: 1) It is not possible to map a value to more than 1 cell? Since cell names need to be unique? 2) I'm getting alot of ArrayIndexOutOfBound errors when adding mappings: [062210_130828960][][EXCEPTION] java.lang.ArrayIndexOutOfBoundsException: 1 at oracle.apps.xdo.template.excel.object.SheetObject.addComponent(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.collectAllNameObjects(Unknown Source) at oracle.apps.xdo.template.excel.object.BookManager.process(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.parseExcelTemplate(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.generateXSL(Unknown Source) at oracle.apps.xdo.template.excel.ExcelController.process(Unknown Source) at oracle.apps.xdo.template.ExcelProcessor.process(Unknown Source) at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedDataForExcelTemplate(ExcelServiceTemplate.java:718) at oracle.apps.xdo.service.excel.ExcelServiceTemplate.embedServiceTemplateData(ExcelServiceTemplate.java:737) at oracle.apps.xdo.servlet.ExcelCoreProcessor.transform(ExcelCoreProcessor.java:49) at oracle.apps.xdo.servlet.CoreProcessor.process(CoreProcessor.java:293) at oracle.apps.xdo.servlet.CoreProcessor.generateDocument(CoreProcessor.java:80) at oracle.apps.xdo.servlet.ReportImpl.renderBodyHTTP(ReportImpl.java:562) at oracle.apps.xdo.servlet.ReportImpl.renderReportBodyHTTP(ReportImpl.java:265) at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:264) at oracle.apps.xdo.servlet.XDOServlet.writeReport(XDOServlet.java:244) at oracle.apps.xdo.servlet.XDOServlet.doGet(XDOServlet.java:172) at oracle.apps.xdo.servlet.XDOServlet.doPost(XDOServlet.java:195) at javax.servlet.http.HttpServlet.service(HttpServlet.java:763) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.xdo.servlet.security.SecurityFilter.doFilter(SecurityFilter.java:97) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:621) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:368) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:866) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:448) at com.evermind.server.http.HttpRequestHandler.serveOneRequest(HttpRequestHandler.java:216) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:117) at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:110) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:595) Thanks, Wannes

Posted by Wannes Schols on June 21, 2010 at 10:35 PM MDT #

Tim: Just got a simple data table to populate based on your example, but I'd like to produce a chart with this data. I was thinking of leveraging VBA macro language and using named data ranges from the report output but I notice that as the data table is filled out it doesn't populate the XDO_?? as it builds new rows. Any other ideas on how to construct a chart using this functionality? Thanks!

Posted by Mac Rinehart on June 22, 2010 at 07:44 AM MDT #

Hi Tim, Is this functionality usable from BIP Enterprise edition standalone edition, or can this be used with R12 EBS also? If it can be used with R12 EBS, is there any patch/add on required for the same? Thanks, Arun

Posted by Arun on June 24, 2010 at 05:37 AM MDT #

You without doubt have a style all your own when it comes to creating these nice blog posts.

Posted by Claudie Fodera on July 25, 2010 at 05:38 AM MDT #

The article is very helpful! Thank you for the template help. Personally, I had some problems converting excel files over to PDF and making it look nice. Then I found this program at my work that quickly and painlessly converts my excel documents into pdf so I didn't have to do it manually (which would end up taking aggravating hours upon hours). It provides my business a simple way to make individualized statements for our business associates, taking our invoices and seamlessly placing the information in awesome templates. Check it out <a href=“http://www.nirvaha.com/free-spreadsheet-templates.html”>here</a>.

Posted by Joseph Becket on July 28, 2011 at 06:07 AM MDT #

These are beautiful, and promise to be a worthy replacement for our current .RTF to Excel templates. Thanks for sharing the info.

There is one thing, however, that they don't appear to address; conditional regions. In the .RTF templates you can insert conditional regions that disappear based on certain criteria. We use those to tailor the column list in our Excel extracts based on user-entered parameters.

Is such functionality available using these true Excel templates? I've experimented with a couple of ways of incorporating the .RTF syntax, but Excel doesn't seem to like any of the ways I've tried.

Posted by guest on August 11, 2011 at 01:56 AM MDT #

"It is not possible to map a value to more than 1 cell" - FYI this can be done...

When you enter the name into the Top Right (as shown above) then you create a Workbook wide cell name (hense only allowed one), however you can restrict the scope of the Cell Names to Worksheet only by right mouse clicking on the Cell (or range of cells by highlighting them first), selecting "Name a Range", enter the name and in the "Scope" pick list choose the Worksheet to associate the Cell Name to.

Hope this helps!
Gav

Posted by guest on September 29, 2011 at 07:07 PM MDT #

can you create charts using this template? i was trying but not able to..

any help?

thanks
Prakhar

Posted by Prakhar on November 23, 2011 at 07:54 PM MST #

Is it possible to do Subtemplates using Excel template?

Posted by guest on November 29, 2011 at 05:00 AM MST #

Which is the best way to create excel outputs?
Using rtf template builder or excel template builder?
Currently excel template builder supports only excel 2003 format (.xls) which has a row limit of 65536 records. Conditional regions is another issue.
Is this going to get better?
Could you please help me on this?

Posted by DVeneros on November 02, 2013 at 03:32 AM MDT #

Which is the best way to create excel outputs?
Using rtf template builder or excel template builder?
Currently excel template builder supports only excel 2003 format (.xls) which has a row limit of 65536 records. Conditional regions is another issue.
Is this going to get better?
Could you please help me on this?

Posted by guest on November 02, 2013 at 03:33 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today