Did You Know about Excel Template for BI Publisher?

It looks like most BI Publisher report developers in the community are using MS Word to build their templates. Did you know that Excel is also an option?

 

Main Features of Excel Templates

  • Formatting output in Excel (xls)
  • Splitting data across multiple worksheets
  • Using XSL functions to manipulate data
  • Creating sheets with a Header/Detail relationship
  • Mapping between the template and the data
  • Using Excel Template Builder to design template in either connected (by logging into BI Publisher Server) or disconnected mode (by downloading a sample data file to a local client).

 

Steps to create Excel-based template for BI report

Here are the basic steps to creating your first Excel-based template for seamless formatting and output manipulation in Excel workbooks:

Step 1: Make sure to install the BI Publisher Desktop plug-in. If you have already installed BI Publisher and are working with it in MS Word, then you simply need to go into Excel Options, select Add-ins, click on BI Publisher Template Builder for Excel. Then select COM Add-ins and click on Go. Select BI Publisher Template for Builder for Excel and click OK.

steps1.jpg

steps2.jpg

Step 2: Find the BlankExcelTemplate.xls. This file comes with the desktop version of BI Publisher. It is located in the drive that the application was installed. For example: C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\samples\Excel templates

 

Make a copy of this file. Note that there is a sheet named XDO_METADATA. This sheet needs to be inside your file in order for the BIP to process the template, however, you may choose to hide it. XDO_METADATA is the sheet onto which the mapping of the data to the template is written. It is also where you can enter directly the XSL functions you need (for calculations or advanced functions).

2019-08-27_11-11-08.jpg

Step 3: Download the XML data file from your source (this can be PS Query) and identify the data that you need to display.

Step 4: Upload the XML data. Go to the BI Publisher tab in Excel > Load Data > Sample XML.

Step 5: Design the template the way that you desire.

Step 6: Map the XML element (one-by-one). You will need to enter the prefix XDO_?  - for each column - followed by the element name inside the name box. Here is an example: XDO_?EMP_NAME? You can also insert the fields using the Field Dialog on the BI Publisher tab. In the Insert group, click on Field. You will see a dialog box that shows the data structure in the XML. Choose your field(s) and click Insert. You will notice sample data inserted in the template.

2019-08-27_11-58-17.jpg

Step 7: Create grouping in the template. This is similar to the 'for-each' loop statement in the RTF tempate version in Word. Select the cell you want to repeat and enter the prefix XDO_GROUP right before the element name, like so: XDO_GROUP_?DEPARTMENT_NAME? You can also go the BI Publisher menu and click on Repeating Group in the Insert group. A dialog box will appear.

Step 8: Test the Template. Click on Excel in the Preview group on the BI Publisher tab.

Note: every time you add a data element, the Template Builder maps that data and the cell by creating a unique name. This mapping is then written to the hidden XDO_METADATA sheet.

 

If you are already developing BI data templates in Excel, we would love to hear about your solutions! Comment on this blog or email our Communications Team to discuss ways that you can share your knowledge with our community! #shareyourknowledge @heug_trag

Dede Young: dyoung@niu.edu

Anna Kourouniotis: anna.kourouniotis@duke.edu

 

2 Comments
1 Like

Fascinating, didn't know that

September 5, 2019 02:21 PM by Paul Rhinehart

I'd be curious what business use cases have you seen where BI Publisher Excel becomes the chosen solution?

Also if we create a Report Definition and upload the Excel template back to PS, and end user runs output online, will PS render it as PDF or what format?

thanks again!

Looking for business cases

October 1, 2019 10:36 AM by Anna Kourouniotis, MA

Paul, thanks for your comment! We are actually looking for business cases. Any school that has implemented this is welcome (if not urged) to reach out to the TRAG and share their knowledge via webinar or presentation at Allliance or a regional conference. Regarding your second question, the output format is controlled on the Report definition side of things. You can set multiple output formats to display at runtime.

Recent Stories
TRAG Quarterly Newsletter - Volume 3 - October 2019 Fall Edition

Did You Know about all the options in Report Properties for BI Publisher?

Pop Select: What works, what you need to know, in the end...