You will follow these steps to complete the process:
1. Prepare XML data Create an XML file with a customer and its invoice listing using Notepad or an equivalent tool.
2. Load the XML file Load the XML data file. The file can be uploaded using the Oracle BI Publisher menu path Data | Load XML Data.
3. Develop the template Use the BI Publisher syntax to loop through the records and reference data from the XML file. You will also display running totals.
4. Save the file Save the file with an .rtf extension.
5. View the output Use the preview feature of BI Publisher plug-in to view the final output that BI Publisher report will produce.
In this example, the structure of the XML file used will be a list of customers, with the list of invoices nested within the customer group. The name of the repeating customer group is G_CUSTOM ER_LIST, and the name of the nested repeating invoice group is G_INVO ICE_LIST. Effectively, this is a master/detail relation between the customers and their invoices. Save the XML file as shown in the following code into a flat file named CustomerList.xml anywhere on your desktop.
Once the XML file has been saved, create a new RTF file using Microsoft Word. Then click the Oracle BI Publisher menu in MS Word, select Data | Load Sample Data, and browse to select the file CustomerList.xml. You will get a message: “Data Loaded Successfully.” Click OK to proceed.
To display the output as shown in Figure 8-1, we will require two “for loops.” The first loop is for iterating over all the customer entries and the second loop is to iterate through the list of invoices for each customer. The tabular region listing the invoices will be within the Customer Loop. The inner loop will begin inside the table to display each invoice record.
Figure 8-1: Output of sample BI Publisher report using data in CustomerList.xml
Table 8-1 indicates how this template is built step by step. Figure 8-2 shows how the template will appear after the steps of Table 8-1 have been implemented in the RTF, which will be stored as XXCUSINV.rtf on your desktop.
Implementation Step | |
Loop through customers | Create a loop using the syntax <?for-each: G_CUSTOMER_LIST?>. |
Display customer name | Value is displayed using the syntax <?CUSTOMER_NAME?>. |
Initialize variable RtotalVar | This variable tracks the running total of INV_AMT for each customer. The syntax is <?xdoxslt:set_variable($_X |
Create table in MS Word | Create a table with two rows. Enter column headings in first row. |
Loop through invoices | In the first column of the second row in the MS Word table, create a new loop for iterating over invoices within the customer loop. Use the syntax <?for-each: G_INVOICE_LIST?>. |
Display invoice description | Value is displayed using the syntax <?INV_DESCR?>. This value is displayed within the invoice loop, in the first column. |
Display invoice amount | Value is displayed using the syntax <?INV_AMT?> in the second column. |
Increment running total | Get the current value of the RtotalVar variable and add to it the INV_AMT of current record. The syntax is <?xdoxslt:set_variable($_XDOCTX, ‘RtotalVar’,xdoxslt: get_variable($_XDOCTX,‘RtotalVar’)+INV_AMT)?>. |
Declare variable to hold page total | Add this syntax to the field INV_AMT, which you need to print the page totals: <?add-page-total:xxPageTotalField;’INV_AMT’?>. |
Display running total | Use the syntax <?xdoxslt:get_variable($_XDOCTX,‘RtotalVar’)?> to display the running total. This retrieves the value of variable RtotalVar. |
Finish loop for invoices | After displaying the running total for the invoice, close the nested invoice loop using the syntax <?end for-each?>. |
Finish loop for customers | Close the outer loop for customers using the syntax <?end for-each?>. This is done outside the table. |
Show page total | Show the page total using the syntax <?show-page-total: xxPageTotalField?>. |
Create inline template | Instead of placing form fields in the header/footer, create a nested template anywhere in the RTF using the syntax <?template:xxCompanyHeader?>place contents here<?end template?>. Then reference that template by calling <?call: xxCompanyHeader?> from the header or the footer of MS Word. |
Figure 8-2: Template used in the example to display customers and their invoices
Once you build XXCUSINV.rtf, it will look exactly the same as Figure 8-2. Click the Oracle BI Publisher menu within MS Word and select Validate Template. Ensure that there are no errors reported when the template is validated. To see the output of this template, click Oracle BI Publisher and select Preview | PDF, as shown in Figure 8-3. This will display the output in PDF format, which will resemble the report output shown in Figure 8-1. We will use the very same RTF template when developing the BI Publisher report in E-Business Suite in the subsequent sections of this chapter.
Figure 8-3: View the output of the RTF layout in PDF format.
Note |
After you develop the RTF template as just shown, you will notice that even though the RTF layout produces the desired output, it is not very user-friendly, as it contains the syntax for referencing XML elements and embedding the XML tags into the RTF template can make the RTF template look cluttered. For this reason, Oracle also provides an option to create fields within the RTF template. These fields can then be attached to the code snippet using the Advanced property, as shown in Figure 8-4.
Figure 8-4: Field property where code snippets can be embedded
For example, the Oracle BI Publisher desktop wizard can automatically create a table layout in the RTF template that has directives for “begin for loop” and “end for loop.” To try this, take a copy of XXCUSINV.rtf and rename it XXCUSINV01.rtf. Move the cursor to just above the sentence “<?template:xxCompanyHeader?>”. Using the Oracle BI Publisher menu, click Insert | Table From and drag G_INVO ICE_LIST from data source to Template and select Drop All Nodes. Click OK. You will notice that a table has been created in the RTF template by the wizard. This table has two rows, and the second row contains a field titled F.
As shown in Figure 8-4, right-click the F field and select BI Publisher properties. In the Advanced tab, you will find the code “<?for-each:G_INVO ICE_LIST?>”. This is exactly the same code that you write when you build the RTF template manually. In other words, you can write code in BI Publisher either by typing the code snippet directly into MS Word Template or by writing the code in the Advanced Code property of the fields. The end result is the same whether you write the logic inside the field property or directly into the MS Word template.
In the Oracle BI Publisher desktop software, you can click Oracle BI Publisher | Tools | Options | Build and enable the Hidden check box. This hides any fields created using the wizard in the RTF template. Developers may enable the Hidden property for building some components in the layout. Therefore, the best way to browse all the fields in a template is to select Oracle BI Publisher | Tools | Field Browser. This will show all the fields within the template alongside the supporting code snippets.
After you understand these concepts, we recommend that you read the Oracle BI Publisher developer guide that comes along with the BI Publisher desktop software. In the subsequent sections of this chapter, we will carry forward the template XXCUSINV.rtf that we built and use it for integrating with Oracle E-Business Suite.
