Excel Template Report:
Most of the people knows that XML Publisher Report can only be build using RTF File. However, latest version of BI Publisher 11.1.15 onward has flexibility to create XML Publisher Report in Excel too.
Advantages: a) If one of your client ask you to develop a XML Publisher report in multi-tab excel format, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
b) If one of your client ask you to prepare a XML Publisher report where formulas should be visible as like in Excel, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
c) Splitting of Data into multiple tabs based on conditions
d) Split hierarchical data across multiple sheets and dynamically name the sheets
e) Create sheets of data that have master-detail relationships
f) Use native Excel functionality
Disadvantages: a) RTF Report is easy to design in comparison with Excel Template Report.
b) RTF Report is easy to debug in comparison with Excel Template Report.
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.
Limitations:
Most of the people knows that XML Publisher Report can only be build using RTF File. However, latest version of BI Publisher 11.1.15 onward has flexibility to create XML Publisher Report in Excel too.
Advantages: a) If one of your client ask you to develop a XML Publisher report in multi-tab excel format, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
b) If one of your client ask you to prepare a XML Publisher report where formulas should be visible as like in Excel, we generally say that it is not possible using XML Publisher. But now, it is possible using Excel Template Report.
c) Splitting of Data into multiple tabs based on conditions
d) Split hierarchical data across multiple sheets and dynamically name the sheets
e) Create sheets of data that have master-detail relationships
f) Use native Excel functionality
Disadvantages: a) RTF Report is easy to design in comparison with Excel Template Report.
b) RTF Report is easy to debug in comparison with Excel Template Report.
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.
Limitations:
- The template must be saved as Excel 97-2003 as *.xls.
- All tags must be manually coded, there is no tool available.
- Can’t burst the xls template.
No comments:
Post a Comment