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.
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).
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.
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