Excel Template for BI Publisher
I recently received a requirement to produce XLS output for one of the custom reports at a client.Initially I had difficulty in designing the report, but realized it was actually easy after a bit of research and trial and error. I want to share my experience, tips and tricks in designing simple XLS templates and help techies who are curious to know how easy it is.
The steps
In order to design the template, we need a template builder for Excel which is installed automatically when you install the BI Publisher Desktop plug-in. Here are the 10 simple steps to create an XLS template.
1.Get the ‘BlankExcelTemplate’ from the sample Excel template which comes along with your desktop tool and save it with the name you prefer. XDO_METADATA sheet is required for BIP to process the template and add calculations. Hide this sheet before you attach the template.
2.Obtain the XML data file and identify the columns you want to display on your excel report.
3.To map the XML element in excel, we should enter the prefix XDO_? then followed by element name in the name box. For example, ‘ XDO_?Company_Name_ID12?’
4.Select the cell and enter the XML element in the name box with XDO_ prefix and enter the name of the cell in the formula bar to display text on the template then press enter.
5.Repeat the above step for remaining columns. After entering all columns, all the names will appear as shown below in Name Manger dialog box.
6.To add calculations for example, total the gross amount for each payment date, we have to define the name in the name box by selecting the cell. Total_Gross is the name defined here.
7.Once the name is defined for the calculation, add the calculation in the XDO_METADATA sheet as shown below.
8.To create group in the template like ‘for each’ in the word template, select the cells you want to repeat and enter the XML element name with the prefix XDO_GROUP_ in name box. In this example, On_Payment_ID_Internal is the level break header where we display one record for each payment.
9.In order to repeat the section for each payment date, select the rows 1, 2, 3 and 4 then enter the element name with prefix XDO_GROUP_. In the example, On_Check_Item_Date_S11 is the level break header.
10.XML file produces the date in YYYY-MM-DD format by default. In order to display the date in MM-DD-YYYY format, use the DATEVALUE function after mapping the original date element. In this example, date is mapped to column A (hidden) and used function to display the date based on column A.
References
https://docs.oracle.com/cd/E28280_01/bi.1111/e22254/create_excel_tmpl.htm
https://docs.oracle.com/cd/E23943_01/bi.1111/e22254/create_excel_tmpl.htm#BIPRD2744
No Comments