In this article we will look into how to create Excel template for BI Publisher Reporting.
We can get the Excel (.xslx) output with BI Publisher by using an RTF template too, however the formatting would be different and if you have to split data into multiple worksheets it is not possible to do it dynamically.
Few links that might help:
We have a limitation with the excel template it generates output in .xls format and it can generate maximum of 65536 rows in a single worksheet. If the total row count exceeds this number, then another worksheet will be created within the same excel file and we need to look at both worksheets to check the complete output. However if we create an rtf template and use excel output option it generates output in .xlsx format which supports upto 1million rows. So depending on your requirement you have to chose which template to use wisely.
Now let’s get started with creating the Excel Template.
Pre-requisites: BI Publisher Add-in installed.
If you haven’t used BI add-in on excel yet, then you need to enable the add-in first. Navigate to File > Options > Add-ins > COM Addins
Once you enable the add-in you should see a BI Publisher in the banner section.
First we need to create a SQL Query, put it in the Data Model and download the sample XML to get us started.
SELECT PERSON_ID, EFFECTIVE_START_DATE, PERSON_NUMBER, PRIMARY_PHONE_ID FROM PER_ALL_PEOPLE_F
Here is the sample data:
We will export this to xml file by clicking the Export option.
Next we will open a new Excel file and navigate to the BI Publisher tab. It will look like below
Next we will import the sample XML file that we just download by using the Sample SQL button and browsing through the file.
Once the file is imported, the adjacent fields “Field” and “Repeating Group” will be enabled.
We will add the fields to the template by clicking on the Field button.
Once we click on Field button, a new popup will appear with a message “Meta data sheet will be created”. Click OK. It will create a new worksheet which by default will be hidden. We will cover that worksheet in the later part of this article.
We will leave the 1st row for writing the header names later on.. We will start from A2 and select one field at a time and click insert.. You can also view the sample value in the Example while inserting the fields.
A2 – PERSON_ID
B2 – PERSON_NUMBER
C2 – PRIMARY_PHONE_ID
D2 – I have intentionally left blank. Will explain later.
E2 – EFFECTIVE_START_DATE
After inserting these fields, write the headers on the 1st row manually.
As we can see the numbers are formatted incorrectly.. If there are any leading zeros they will be wiped off. so format the A2 and C2 columns to text format.
After formatting the result will look like..
Now we can see that the EFFECTIVE_START_DATE is in the canonical date YYYY-MM-DD format and we cannot use the date formatting in excel to modify it. We need to use DATEVALUE function to get it into DD-MM-YYYY format. In the cell D2 we will write a function as =DATEVALUE(LEFT(E2,10)).. It will return a number in D2. Now we have to apply date formatting to that number to get the date
Now after this, we will save the template and click preview.. it will show only one row of data as we haven’t done the for loop grouping.
We will have to select the contents of the row that we inserted.. from A2 to E2 and then click on Repeating Group button. It will show a popup, we need to select the For-Each group. As we have only one G_1 group in Data Model, we will select it from Drop Down and then click OK.
Now save the template and click Excel preview in BI Publisher tab, the output will appear like below:
As effective date in column E is being shown two times, we can hide that column from the template. And if you want to do some formatting changes to columns or to do coloring of field values based on some conditions you can use the excel options on the template.
Once the template is ready, you can upload it to BI Catalog with Excel Template option and run the report to get the excel output.
When we click preview, instead of showing the output if it throws an error, then there are chances that something is screwed up in the template and we need to debug the template.
In order to check the mapping of fields, we can click on Right click on Sheet1 and click Unhide and select the XDO_METATA sheet and click OK.
This sheet will have the mapping of XML tags to columns on the Sheet1. The final tag XDO_GROUP_?XDOG1? is the group tag for the for each group. Do not modify this sheet, else the template might not work. Only power users or who use advanced grouping on the excel templates can modify this and write custom grouping conditions.
If you want to see the column mapping of the fields, then you need to navigate to Formulas tab > Name Manager
If you want to modify any tags, you can do here by selecting row and clicking on Edit icon. If you want to delete any fields, you can do so from here.
Sample Files used:
Hope this gives you a better understanding on how to create excel templates.
If you have any questions, please feel free to reach out to me by posting in comments section.
If you are interested in learning Fusion Technical tools go through this post
If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.