XML Publisher Report (BI Publisher)

XML Publisher Report

Index

1. Introduction to XML Publisher
2. Process Overview

•    Design Time 
•    Runtime

3. Creating a RTF Template
4. Using the XML Publisher Template Builder:
5. Associating the XML Data to the Template Layout

•     How to Identify Placeholders and Groups Placeholders
•     Identifying the Groups of Repeating Elements

6. Basic RTF Method

7. Form Field Method

8. How to the initial page number be set at a specified number

9. Conditional Formatting and If  statement

10. If-then-Else Statements

11. Choose Statements

12. Column Formatting

13. Row Formatting

14. How to set a background color

15. How to Displaying Page Totals

16. How to do Running Totals

17. How to create  Sub-template

•       Register the Template in the Template Manager
•       Import the Subtemplate to the Main Template
•       Call the Subtemplate in Your Main Template
•       Register the Template in the Template Manager
• Import the Subtemplate to the Main Template
•      Call the Sub-template in Your Main Template

18. Defining Headers and Footers

•       Native Support
•       Multiple Headers and Footers

19. How to design the XML Publisher report

=================================================================================================

Introduction to XML Publisher

Oracle XML Publisher was originally developed to solve the reporting problems faced by Oracle Applications. It was first released with Oracle E-Business Suite 11.5.10 in 2003. In the last 2 years XML Publisher has matured and now satisfies all reporting requirements for Oracle Applications. Impressed by its capabilities, the J.D. Edwards Enterprise One application has just integrated with XML Publisher and Peoplesoft Enterprise will uptake XML Publisher in the next release.

Many customers outside of Oracle Applications face the same reporting issues. On the Oracle World 2004 customers asked for a standalone version of XML Publisher. XML Publisher Enterprise was developed for these customers with no dependency on Oracle Applications.

It is a complete web-based reporting application that allows user to:

Ø      Manage reports in a folder structure.

Ø      Create reports combining multiple databases as well as web services.

Ø      Define the report layout in Microsoft Word or Adobe Acrobat.

Ø      Connect from Microsoft EXCEL to XML Publisher reports.

Ø      Extract and translate the boilerplate text in reports.

Ø      Schedule and deliver reports via a variety of delivery channels such as email, fax, or print.

Merits of xml publisher

Oracle XML Publisher is a new, innovative reporting and output management solution that is designed to lower the total cost of ownership of reporting and document output management:

Ø      Deploy reports faster and lower your dependence on consulting services

Ø      Consolidate your reporting and document output management solution

Ø      Consolidate reporting and output management internationally.

Ø      Reduce the associated maintenance cost.

Ø      Protect investment in existing IT infrastructure.

XML Publisher Enterprise: Repository, Report Viewer and Scheduler

All XML Publisher solutions – the ones embedded into Oracle applications as well as XML Publisher Enterprise – offer multiple innovations that will be covered in the following sections.

COMPLETE SOLUTION

A typical enterprise has to create such diverse documents as invoices, purchase orders, sales orders, financial and management reports, government forms, checks, labels and electronic documents. These documents need to be delivered to customer, supplier and partners by email, fax, or print and stored in an archive or document management system. XML Publisher provides a complete solution for creating, managing and delivering all your business documents.

Solutions that can be consolidated with Oracle XML Publisher Enterprise

Before XML Publisher, no single solution was able to satisfy all these requirements. As a result many businesses – including Oracle – were required to maintain several reporting solutions: invoice server, a label print server, a report server, a check print sever, a fax server, etc…

XML Publisher allowed Oracle’s IT department to consolidate their printing solution and retire multiple third party solutions. Since then, many of Oracle’s customers were able to consolidate their printing and document management solution by replacing multiple systems with XML Publisher.

Oracle

Oracle XML Publisher Enterprise is Oracle’s revolutionary new reporting and document output management solution. XML Publisher offers multiple innovations that substantially lower the total cost of ownership for reporting solutions, such as:

End user desktop applications — Microsoft Word, EXCEL and Adobe Acrobat Professional — allow functional users to define and customize the layout of documents. This results in a more efficient cooperation between IT and functional departments and reduces the need for external consultants.

 Financial Reports

Ø A single web-based solution for producing and delivering a broad range of business documents such as: sales orders, invoices, purchase orders, management and financial reports, government forms, checks, labels, and electronic documents (EDI/EFT). Since Oracle XML Publisher can replace multiple systems, it results in less complex and easier to maintain solutions.

Ø XML Publisher facilitates the global consolidation of your reporting solutions. A single instance supports multiple user interface and document languages. The same user can create documents in English, German, Chinese or Arabic (bi-directional) and print them to a local or remote printer.

Ø XML Publisher reduces report customization and maintenance cost – due to the separation of data extract, layout, and translation. For examples changes to a report query require you to change only a single report file and none of the layouts or translations of this report.

Oracle XML Publisher is “hot pluggable” - meaning it is completely based on open standards. As such it can run on any J2EE compliant application server and can access data from practically any database. This makes XML Publisher fit into any IT environment and protects your investment into legacy systems.

Oracle’s XML Publisher is a template-based publishing solution delivered with the Oracle E-Business Suite. It provides a new approach to report design and publishing by integrating familiar desktop word processing tools with existing E-Business Suite data reporting. XML Publisher leverages standard, well-known technologies and tools, so you can rapidly develop and maintain custom report formats.

The flexibility of XML Publisher is a result of the separation of the presentation of the report from its data structure. The collection of the data is still handled by the E-Business Suite, but now you can design and control how the report outputs will be presented in separate template files. At runtime, XML Publisher merges your designed template files with the report data to create a variety of outputs to meet a variety of business needs, including: 

Ø Customer-ready PDF documents, such as financial statements, marketing materials, contracts, invoices, and purchase orders utilizing colors, images, font styles, headers and footers, and many other formatting and design options.

Ø  HTML output for optimum online viewing.

Ø  Excel output to create a spreadsheet of your report data.

Ø "Filled-out" third-party provided PDF documents. You can download a PDF document, such as a government form, to use as a template for your report. At runtime, the data and template produce a "filled-out" form.

Ø  Flat text files to exchange with business partners for EDI and EFT transmission.

Process Overview

Creating customized reports using XML Publisher can be divided into two phases:

Design Time and Runtime.

Design Time

1. Register the E-Business Suite report as a Data Definition in the Template Manager.

Create a Data Definition in the Template Manager for E-Business Suite reports that you wish to customize using XML Publisher. When you create the Data Definition, the Data Definition Code must match the E-Business Suite report Short name.

2. Design your template.

Your template files can be either in Rich Text Format (RTF) or Portable Document     Format (PDF).RTF is a specification used by many word processing applications,     such as Microsoft Word. You design the template using your desktop word processing application and save the file as an RTF file type (.rtf extension). Insert basic markup tags to the document to prepare it for merging with the XML data. XML Publisher recognizes the formatting features that you apply and converts them to XSL-FO. Use Adobe Acrobat to apply markup tags to your custom-designed or downloaded PDF template.

3. Register your Template in the Template Manager.

When you create the template in the Template Manager, you register and upload your RTF or PDF template files. The Template must be assigned to the Data Definition Code of the E-Business Suite report with which it will be merged.

4. Add desired translations of your RTF template.

XML Publisher’s translation utility allows you to extract the translatable strings from your template into an XLIFF file. Translate the strings in this file and re upload to the Template Manager to make the translation available at runtime.

Runtime

1. Set the concurrent program to generate XML.

2. Run the concurrent program using standard request submission to obtain the XML output.

Note: The Application Object Library, Publishing Concurrent Requests with XML, fully integrates XML Publisher with the concurrent manager’s standard request submission both in Oracle Forms and HTML-based applications. Simply run the request and select your template from the Submit Request user interface and the concurrent manager calls XML Publisher to merge the template and data all in a single step. You are not required to run the XML Publisher Concurrent Request.

3. Run the XML Publisher Concurrent Request.

The XML Publisher Concurrent Request will prompt you to enter the Request ID from the previous step, and to select a template, template locale, and output type. Available templates are those associated to the report Data Definition in the Template Manager. XML Publisher merges your design template with the XML data to generate your customized output.

Creating a RTF Template

Rich Text Format (RTF) is a specification used by common word processing applications, such as Microsoft Word. When you save a document, RTF is a file type option that you select.XML Publisher’s RTF Template Parser converts documents saved as the RTF file type to XSL-FO. You can therefore create report designs using many of your standard word processing application’s design features and XML Publisher will recognize and maintain the design.

During design time, you add data fields and other markup to your template using XML Publisher’s simplified tags for XSL expressions. These tags associate the XML report data to your report layout. If you are familiar with XSL and prefer not to use the simplified tags, XML Publisher also supports the use of pure XSL elements in the template.

In addition to your word processing application’s formatting features, XML Publisher supports other advanced reporting features such as conditional formatting, dynamic data columns, running totals, and charts.

If you wish to include code directly in your template, you can include any XSL element, many FO elements, and a set of SQL expressions extended by XML Publisher

Using the XML Publisher Template Builder:

The Template Builder is an extension to Microsoft Word that simplifies the development of RTF templates. It automates many of the manual steps that are covered in this chapter. Use it in conjunction with this manual to increase your productivity.

The Template Builder is tightly integrated with Microsoft Word and allows you to perform the following functions:

Ø      Insert data fields

Ø      Insert data-driven tables

Ø      Insert data-driven forms

Ø      Insert data-driven charts

Ø      Preview your template with sample XML data

Ø      Browse and update the content of form fields

Ø      Extract boilerplate text into an XLIFF translation file and test translations

Manual steps for performing these functions are covered in this chapter. Instructions and tutorials for using the Template Builder are available from the readme and help files delivered with the plugin.

Associating the XML Data to the Template Layout

1.The data fields that are defined on the template

2.The elements of the template that will repeat when the report is run.

a) How to Identify Placeholders and Groups

Each data field in your template must map to an element in the XML file. Each group of repeating elements in your template must correspond to a parent-child relationship in the XML file.

To map the data fields you define placeholders. To designate the repeating elements, you define groups

Placeholders

Each data field in your report template must correspond to an element in the XML file. When you mark up your template design, you define placeholders for the XML  elements. The placeholder maps the template report field to the XML element. At runtime the placeholder is replaced by the value of the element of the same name in the XML data file

b) Identifying the Groups of Repeating Elements

The placeholder maps the template field to the XML element data field. At runtime the placeholder is replaced by the value of the element of the same name in the XML data file.

Enter placeholders in your document using the following syntax:

<?XML element tag name?>

Note: The placeholder must match the XML element tag name exactly. It is case sensitive.

There are two ways to insert placeholders in your document:

1. Basic RTF Method: Insert the placeholder syntax directly into your template

document.

2. Form Field Method: (Requires Microsoft Word) Insert the placeholder syntax in

Microsoft Word’s Text Form Field Options window. This method allows you to

maintain the appearance of your template.

Basic RTF Method

Enter the placeholder syntax in your document where you want the XML data value to appear.

Enter the element’s XML tag name using the syntax:

<?XML element tag name?>

Form Field Method

1.Use Microsoft Word’s Text Form Field Options window to insert the placeholder tags:

2.Enable the Forms toolbar in your Microsoft Word application.

3.Position your cursor in the place you want to create a placeholder.

4.Select the Text Form Field toolbar icon. This action inserts a form field area in your 
   document.  

5.Double-click the form field area to invoke the Text Form Field Options dialog box.

6.(Optional) Enter a description of the field in the Default text field. The entry in this
    field will populate the placeholder’s position on the template.

7.For the example, enter "Supplier 1".

   Select the Add Help Text button.

   In the help text entry field, enter the XML element’s tag name using the syntax:

   <?XML element tag name?>

You can enter multiple element tag names in the text entry field.

 In the example, the report field "Supplier" maps to the XML element VENDOR_NAME.

 In the Form Field Help Text field enter: <?VENDOR_NAME?>

The following figure shows the Text Form Field Options dialog box and the Form Field Help Text dialog box with the appropriate entries for the Supplier field.

  8. Select OK to apply.

Page Breaks

To create a page break after the occurrence of a specific element use the "split-by-page-break" alias. This will cause the report output to insert a hard page break between every instance of a specific element. To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the <?end for-each?> tag that closes the group. In the Help Text of this form field enter the syntax:

<?split-by-page-break:?>

Example

For the following XML, assume you want to create a page break for each new supplier:

<SUPPLIER>

<NAME>My Supplier</NAME>

<INVOICES>

<INVOICE>

<INVNUM>10001-1</INVNUM>

<INVDATE>1-Jan-2005</INVDATE>

<INVAMT>100</INVOICEAMT>

</INVOICE>

<INVOICE>

<INVNUM>10001-2</INVNUM>

<INVDATE>10-Jan-2005</INVDATE>

<INVAMT>200</INVOICEAMT>

</INVOICE>

</INVOICES>

</SUPPLIER>

<SUPPLIER>

<NAME>My Second Supplier</NAME>

<INVOICES>

<INVOICE>

<INVNUM>10001-1</INVNUM>

<INVDATE>11-Jan-2005</INVDATE>

<INVAMT>150</INVOICEAMT>

</INVOICE>

…

In the template sample shown in the following figure, the field called PageBreak contains the split-by-page-break syntax:

Place the PageBreak field with the <?split-by-page-break:?> syntax immediately before the <?end for-each?> field. The PageBreak field sits inside the end of the SUPPLIER loop. This will ensure a page break is inserted before the occurrence of each

How to the initial page number be set at a specified number

Initial Page Number

Some reports require that the initial page number be set at a specified number. For example, monthly reports may be required to continue numbering from month to month. XML Publisher allows you to set the page number in the template to support this requirement.

Use the following syntax in your template to set the initial page number:

<?initial-page-number:pagenumber?>

where pagenumber is the XML element or parameter that holds the numeric value.

Set page number from XML data element

If your XML data contains an element to carry the initial page number, for example:

Creating an RTF

<REPORT>

<PAGESTART>200<\PAGESTART>

</REPORT>

Enter the following in your template:

<?initial-page-number:PAGESTART?>

Your initial page number will be the value of the PAGESTART element, which in this case is 200.

Example 2 - Set page number by passing a parameter value

If you define a parameter called PAGESTART, you can pass the initial value by calling the parameter.

Enter the following in your template: <?initial-page-number:$PAGESTART?>

Note: You must first declare the parameter in your template.

Last Page Only Content

XML Publisher supports the Microsoft Word functionality to specify a different page layout for the first page, odd pages, and even pages. To implement these options, simply select Page Setup from the File menu, then select the Layout tab. XML Publisher will recognize the settings you make in this dialog. However, Microsoft Word does not provide settings for a different last page only. This is useful for documents such as checks, invoices, or purchase orders on which you may want the content such as the check or the summary in a specific place only on the last page.XML Publisher provides this ability.

 To utilize this feature, you must:

1. Create a section break in your template to ensure the content of the final page is
    separated from the rest of the report.

2. Insert the following syntax on the final page:

    <?start@last-page:body?>

    <?end body?>

Any content on the page that occurs above or below these two tags will appear only on the last page of the report. Also, note that because this command explicitly specifies the content of the final page, any desired headers or footers previously defined for the report must be reinserted on the last page.

Example

This example uses the last page only feature for a report that generates an invoice listing with a summary to appear at the bottom of the last page.

<?xml version="1.0" encoding="WINDOWS-1252"?>

<INVOICELIST>

<VENDOR>

<VENDOR_NAME>Nuts and Bolts Limited</VENDOR_NAME>

<ADDRESS>1 El Camino Real, Redwood City, CA 94065</ADDRESS>

<INVOICE>

<INV_TYPE>Standard</INV_TYPE>

<INVOICE_NUM>981110</INVOICE_NUM>

<INVOICE_DATE>10-NOV-04</INVOICE_DATE>

<INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>

<ENT_AMT>122</ENT_AMT>

<ACCTD_AMT>122</ACCTD_AMT>

<VAT_CODE>VAT22%</VAT_CODE>

</INVOICE>

<INVOICE>

<INV_TYPE>Standard</INV_TYPE>

<INVOICE_NUM>100000</INVOICE_NUM>

<INVOICE_DATE>28-MAY-04</INVOICE_DATE>

<INVOICE_CURRENCY_CODE>FIM</INVOICE_CURRENCY_CODE>

<ENT_AMT>122</ENT_AMT>

<ACCTD_AMT>20.33</ACCTD_AMT>

<VAT_CODE>VAT22%</VAT_CODE>

</INVOICE>

</VENDOR>

<VENDOR>

...

<INVOICE>

...

</INVOICE>

</VENDOR>

<SUMMARY>

<SUM_ENT_AMT>61435</SUM_ENT_AMT>

<SUM_ACCTD_AMT>58264.68</SUM_ACCTD_AMT>

<TAX_CODE>EU22%</TAX_CODE>

</SUMMARY>

</INVOICELIST>

The report should show each VENDOR and their INVOICE data with a SUMMARY

section that appears only on the last page, placed at the bottom of the page. 

<<insert section break>>

Insert a Microsoft Word section break (type: next page) on the first page of the template. For the final page, insert new line characters to position the summary table at the bottom of the page. The summary table is shown in the following figure: Last Page Only Layout:

        Last Page Placeholder 

The F and E components contain the for-each grouping statements.

The grayed report fields are placeholders for the XML elements. The "Last Page Placeholder" field contains the syntax:

<?start@last-page:body?> <?end body?>

to declare the last page layout. Any content above or below this statement will appear on the last page only. The content above the statement is regarded as the header and the content below the statement is  regarded as the footer. If your reports contains headers and footers that you want to carry over onto the last page, you must reinsert them on the last page. For more information about headers and footers see Defining Headers and Footers.You must insert a section break (type: next page) into the document to specify the last page layout.

Template Builder for Word installation

It is important to note that if the report is only one page in length, the first page layout will be used. If your report requires that a single page report should default to the last page layout (such as in a check printing implementation) then you can use the following alternate syntax for the "Last Page Placeholder" on the last page:

<?start@last-page-first:body?> <?end body?>

Substituting this syntax will result in the last page layout for reports that are only one page long.  

Conditional Formatting and If  statement

Conditional formatting occurs when a formatting element appears only when a certain condition is met. XML Publisher supports the usage of simple "if" statements, as well as more complex "choose" expressions. The conditional formatting that you specify can be XSL or XSL:FO code, or you can specify actual RTF objects such as a table or data. For example, you can specify that if reported numbers reach a certain threshold, they will display shaded in red. Or, you can use this feature to hide table columns or rows depending on the incoming XML data. If Statements Use an if statement to define a simple condition; for example, if a data field is a specific value.

1. Insert the following syntax to designate the beginning of the conditional area. <?if:condition?>

2. Insert the following syntax at the end of the conditional area: <?end if?>.

For example, to set up the Payables Invoice Register to display invoices only when the Supplier name is "Company A", insert the syntax <?if:VENDOR_NAME=’COMPANYA’?> before the Supplier field on the template. Enter the <?end if?> tag after the invoices table. This example is displayed in the figure below. Note that you can insert the syntax in form fields, or directly into the template.

If-then-Else Statements

XML Publisher supports the common programming construct "if-then-else". This is extremely useful when you need to test a condition and conditionally show a result. For example:

      IF X=0 THEN

           Y=2

      ELSE

           Y=3

     END IF

You can also nest these statements as follows:

    IF X=0 THEN

        Y=2

    ELSE

   IF X=1 THEN

     Y=10

  ELSE Y=100

  END IF

Use the following syntax to construct an if-then-else statement in your RTF template:

<?xdofx:if element_condition then result1 else result2 end if?>

For example, the following statement tests the AMOUNT element value. If the value

is greater than 1000, show the word "Higher"; if it is less than 1000, show the word

"Lower"; if it is equal to 1000, show "Equal":

<?xdofx:if AMOUNT > 1000 then ’Higher’

else

if AMOUNT < 1000 then ’Lower’

else

’Equal’

end if?>

Choose Statements

Use the choose, when, and otherwise elements to express multiple conditional tests. If certain conditions are met in the incoming XML data then specific sections of the template will be rendered. This is a very powerful feature of the RTF template. In regular XSL programming, if a condition is met in the choose command then further XSL code is executed. In the template, however, you can actually use visual widgets in the conditional flow (in the following example, a table). Use the following syntax for these elements:

<?choose:?>

<?when:expression?>

<?otherwise?>

"Choose" Conditional Formatting Example

This example shows a choose expression in which the display of a row of data depends on the value of the fields EXEMPT_FLAG and POSTED_FLAG. When the EXEMPT_FLAG equals "^", the row of data will render light gray. When POSTED_FLAG equals "*" the row of data will render shaded dark gray. Otherwise, the row of data will render with no shading.

Column Formatting

You can conditionally show and hide columns of data in your document output. The following example demonstrates how to set up a table so that a column is only displayed based on the value of an element attribute. This example will show a report of a price list, represented by the following XML:

<items type="PUBLIC"> <! - can be marked ‘PRIVATE’ - >

<item>

<name>Plasma TV</name>

<quantity>10</quantity>

<price>4000</price>

</item>

<item>

<name>DVD Player</name>

<quantity>3</quantity>

<price>300</price>

</item>

<item>

<name>VCR</name>

<quantity>20</quantity>

<price>200</price>

</item>

<item>

<name>Receiver</name>

<quantity>22</quantity>

<price>350</price>

</item>

</items>

Notice the type attribute associated with the items element. In this XML it is marked

as "PUBLIC" meaning the list is a public list rather than a "PRIVATE" list. For the "public"

version of the list we do not want to show the quantity column in the output, but we

want to develop only one template for both versions based on the list type.

The following figure is a simple template that will conditionally show or hide the quantity column:

Row Formatting

XML Publisher allows you to specify formatting conditions as the row-level of a table. Examples of row-level formatting are: • Highlighting a row when the data meets a certain threshold. • Alternating background colors of rows to ease readability of reports. • Showing only rows that meet a specific condition. Conditionally Displaying a Row To display only rows that meet a certain condition, insert the <?if:condition?> <?end if?>tags at the beginning and end of the row, within the for-each tags for the group. This is demonstrated in the following sample template.

Default Text Entry Form Field Help Text Description for-each SALE <?for-each:SALE?> Opens the for-each loop to repeat the data belonging to the SALE group. if big <?if:SALES>5000?> If statement to display the row only if the element SALES has a value greater than 5000. INDUSTRY

<?INDUSTRY?> Data field

YEAR <?YEAR?> Data field

MONTH <?MONTH?> Data field

SALES end if <?end if?> Closes the if statement.

end SALE <?end for-each?>

 Closes the SALE loop.

How to set a background color

Conditionally Highlighting a Row

This example demonstrates how to set a background color on every other row.

Default Text Entry Form Field Help Text Description for-each SALE <?for-each:SALE?>

Defines the opening of the for-each loop for the SALE group.format; <?if@row:position() mod 2=0?>

<xsl:attribute name="background-color" xdofo:ctx="incontext">lightgray</xsl:

attribute><?end if?> For each alternate row, the background color attribute is set to gray for the row.

INDUSTRY <?INDUSTRY?> Data field YEAR <?YEAR?> Data field

MONTH <?MONTH?> Data field

SALES <?SALES?> Data field

end SALE <?end for-each?>

Closes the SALE for-each loop.

How to Displaying Page Totals 

XML Publisher allows you to display calculated page totals in your report. Because the page is not created until publishing time, the totaling function must be executed by the formatting engine.

Note: Page totaling is performed in the PDF-formatting layer. Therefore this feature is not available for other outputs types: HTML, RTF, Excel. Because the page total field does not exist in the XML input data, you must define a variable to hold the value. When you define the variable, you associate it with the element from the XML file that is to be totalled for the page. Once you define total fields, you can also perform additional functions on the data in those fields. To declare the variable that is to hold your page total, insert the following syntax immediately following the placeholder for the element that is to be totalled:

<?add-page-total:TotalFieldName;’element’?>

where

TotalFieldName is the name you assign to your total (to reference later) and

’element’ is the XML element field to be totalled.

You can add this syntax to as many fields as you want to total.

<?show-page-total:TotalFieldName;’number-format’?>

where

TotalFieldName is the name you assigned to give the page total field above and

number-format is the format you wish to use to for the display.

The following example shows how to set up page total fields in a template to display

total credits and debits that have displayed on the page, and then calculate the net of

the two fields.

This example uses the following XML:

<balance_sheet>

<transaction>

<debit>100</debit>

<credit>90</credit>

</transaction>

<transaction>

<debit>110</debit>

<credit>80</credit>

</transaction>

…

<\balance_sheet>

The following figure shows the table to insert in the template to hold the values:

Default Text Entry	Form Field Help Text Entry	Description
FE	<?for-each:transaction?>	This field defines the opening "for-each" loop for the transaction group.
100.00	<?debit?><?add-page-total: dt;’debit’?>	This field is the placeholder for the debit element from the XML file. Because we want to total this field by page, the page total declaration syntax is added. The field defined to hold the total for the debit element is dt.
90.00	<?credit?> <?add-pagetotal: ct;’credit’?>	This field is the placeholder for the credit element from the XML file. Because we want to total this field by page, the page total declaration syntax is added. The field defined to hold the total for the credit element is ct
Net	<add-page-total:net;’debit - credit’?>	Creates a net page total by subtracting the credit values from the debit values.
EFE	<?end for-each?>	Closes the for-each loop.

How to do Running Totals

Example

The variable functionality (see Using Variables, page 2-79) can be used to add a running total to your invoice listing report. This example assumes the following XML structure: 

<?xml version="1.0" encoding="WINDOWS-1252"?>

<INVOICES>

<INVOICE>

<INVNUM>10001-1</INVNUM>

<INVDATE>1-Jan-2005</INVDATE>

<INVAMT>100</INVOICEAMT>

</INVOICE>

<INVOICE>

<INVNUM>10001-2</INVNUM>

<INVDATE>10-Jan-2005</INVDATE>

<INVAMT>200</INVOICEAMT>

</INVOICE>

<INVOICE>

<INVNUM>10001-1</INVNUM>

<INVDATE>11-Jan-2005</INVDATE>

<INVAMT>150</INVOICEAMT>

</INVOICE>

</INVOICES>

Form Field	Syntax	Description
RtotalVar	<?xdoxslt:set_variable($_ XDOCTX, ’RTotalVar’, 0)?>	Declares the "RTotalVar" variable and initializes it to 0.
FE	<?for-each:INVOICE?>	Starts the Invoice group.
10001-1	<?INVNUM?>	Invoice Number tag
1-Jan-2005	<?INVDATE?>	Invoice Date tag
100.00	<?xdoxslt:set_variable($_ XDOCTX, ’RTotalVar’, xdoxslt: get_variable($_XDOCTX, ’RTotalVar’) + INVAMT)?> xdoxslt:get_variable($_ XDOCTX, ’RTotalVar’)?>	Sets the value of RTotalVar to the current value plus the new Invoice Amount. Retrieves the RTotalVar value for display.
EFE	<?end for-each?>	Ends the INVOICE group.

How to create  Subtemplate

 There are four steps to create sub template such as

    Step1: Create the sub template.

    Step 2:Register the sub template in the Template Manager.

    Step 3 :Import the sub template to the main template.

   Step 4 :Call the sub template in your main template.

For create the subtemplate  the following tag to the beginning of the section to be included:

<?template:template_name?>

where template_name is whatever name you choose for the section.

Register the Template in the Template Manager

Register the subtemplate in the Template Manager. To define it as a subtemplate,
select Subtemplate = YES.,

Import the Subtemplate to the Main Template

Call a template from within another template by using the import command as follows:

<?import:xdo://APPCODE.TEMPLATE_CODE.LANG.TERR?>

where APPCODE is the Application code for the template defined in the Template Manager.

TEMPLATE_CODE is the template code defined in the Template Manager.

LANG is the template language specified in the Template Manager.

TERR is the template territory specified in the Template Manager.

Call the Subtemplate in Your Main Template

Once you have imported the template, use the call template command to render its

contents in the position specified, as follows:

<?call template:template_name?>

where

template_name is the name assigned in the template declaration of the subtemplate.

Register the Template in the Template Manager

Register the subtemplate in the Template Manager. To define it as a subtemplate, select Subtemplate = YES.

Import the Subtemplate to the Main Template

Call a template from within another template by using the import command as follows:

<?import:xdo://APPCODE.TEMPLATE_CODE.LANG.TERR?>

where

APPCODE is the Application code for the template defined in the Template Manager.

TEMPLATE_CODE is the template code defined in the Template Manager.

LANG is the template language specified in the Template Manager.

TERR is the template territory specified in the Template Manager.

Call the Subtemplate in Your Main Template

Once you have imported the template, use the call template command to render its contents in the position specified, as follows:

<?call template:template_name?>

where template_name is the name assigned in the template declaration of the subtemplate.  For example the company and the address are appearing each every page that is  Static strings. so instead of  re produce address  in all  templates, we can place it in one template and reference it from all the others.

Step1:

<?template:MyAddress?>

ABC india pvt  ltd.

5, East Main Road,

Hyderabd,

<?end template?>

This defines the string in a function (or XSL template) called MyAddress.

 Save this template as RTF. 

Step2: 

Add it to the Template Manager using the following criteria:

• Application Code: CUSTOM

• Template Code: COMMONCOMPONENTS

• Language: English

• Territory: United States of America

• Select "Yes" for Subtemplate

Now this subtemplate and any functions therein can be referenced from any other template.

Step3:

To use this subtemplate in another template, first import it by inserting the following syntax at the top of the calling template:

<?import:xdo://CUSTOM.COMMONCOMPONENTS.en.US?>

At the position in the template where we you want to display the address, enter:

<?call template:MyAddress?>

At runtime the string will be fetched from the subtemplate and rendered in the layout of the calling template.

This functionality is not limited to just strings, you can insert any valid RTF template functionality in a subtemplate, and even pass parameters from one to the other.

Defining Headers and Footers

Native Support

XML Publisher supports the use of the native RTF header and footer feature. To create a  header or footer, use the word processing application’s header and footer insertion tools. As an alternative, you can define an internal template in the body of your template, and then call this sub template from the header or footer region. See Header and Footer Template Alternative

Multiple Headers and Footers

If your template requires multiple headers and footers, create them by using XML Publisher tags to define the body area of your report. When you define the body area, the elements occurring before the beginning of the body area will compose the header. The elements occurring after the body area will compose the footer.

Use the following tags to enclose the body area of your report:

<?start:body?>

<?end body?>

Use the tags either directly in the template, or in form fields.

The Payables Invoice Register contains a simple header and footer and therefore does not require the start body/end body tags. However, if you wanted to add another header to the template, define the body area as follows:

  1. Insert <?start:body?> before the Suppliers group tag: <?for-each:G_VENDOR_ NAME?>

  2. Insert <?end body?> after the Suppliers group closing tag: <?end for-each?>

To See version the xml publisher:

In the xml output if u right click we can see the version of the xml publisher.

Extended SQL and XSL Functions

Ø       XSL Equivalents

Ø       Using FO Elements

XML Publisher has extended a set of SQL and XSL functions for use in RTF

templates. The syntax for these extended functions is:

<?xdofx:expression?>

for extended SQL functions or

<?xdoxslt:expression?>

for extended XSL functions.

SQL Statement	Usage	Description
2+3 2-3 2*3 2/3 2**3	<?xdofx:2+3?> <?xdofx:2-3?> <?xdofx:2*3?> <?xdofx:2/3?>  <?xdofx:2**3?	Addition Subtraction Multiplication Division Exponential Concatenation

SQL Statement

Usage

Description

lpad(’aaa’,10,’.’)

<?xdofx:lpad(’aaa’,10,’.’)?>

The lpad function pads the left side of a string with a specific set of characters. The syntax for the lpad function is: lpad(string1,padded_length,[pad_string]) string1 is the string to pad characters to (the left-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the left-hand side of string1 .

rpad(’aaa’,10,’.’)

<?xdofx:rpad(’aaa’,10,’.’)?>

The rpad function pads the right side of a string with a specific set of characters. The syntax for the rpad function is: rpad(string1,padded_length,[pad_ string]). string1 is the string to pad characters to (the right-hand side). padded_length is the number of characters to return. pad_string is the string that will be padded to the right-hand side of string1

decode(’xxx’,’bbb’, ’ccc’,’xxx’,’ddd’)

<?xdofx:decode(’xxx’,’bbb’,’ccc’, ’xxx’,’ddd’)?>

The decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode(expression, search, result [,search, result]...[, default]) expression is the value to compare. search is the value that is compared against expression. result is the value returned, if expression is equal to search. default is returned if no matches are found.

Instr(’abcabcabc’,’a’,2)

<?xdofx:Instr(’abcabcabc’,’a’,2)?>

The instr function returns the location of a substring in a string. The syntax for the instr function is: instr(string1,string2,[start_ position],[nth_appearance]) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1. nth appearance is the nth appearance of string2.

substr(’abcdefg’,2,3)

<?xdofx:substr(’abcdefg’,2,3)?>

The substr function allows you to extract a substring from a string. The syntax for the substr function is: substr(string, start_position, [length]) string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is the number of characters to extract.

replace(name,’John’ ,’Jon’)

<?xdofx:replace(name,’John’, ’Jon’)?>

The replace function replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is: replace(string1,string_to_replace, [replacement_string]) string1 is the string to replace a sequence of characters with another set of characters. string_to_replace is the string that will be searched for in string1. replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1.

to_number(’12345’)

<?xdofx:to_number(’12345’)?>

to_char(12345)

<?xdofx:to_char(12345)?>

sysdate()

<?xdofx:sysdate()?>

The following table shows supported combination functions:

SQL Statement	Usage
(2+3/4-6*7)/8	<?xdofx:(2+3/4-6*7)/8?>
lpad(substr(’1234567890’,5,3),10,’^’)	<?xdofx:lpad(substr(’1234567890’,5,3),10,’^’)?>
decode(’a’,’b’,’c’,’d’,’e’,’1’)||instr(’321’,1,1)	<?xdofx:decode(’a’,’b’,’c’,’d’,’e’,’1’)||instr(’321’, 1,1)?>

Number and Date Formatting

Number Formatting

XML Publisher supports two methods for specifying the number format:

• Microsoft Word’s Native number format mask

• Oracle’s format-number function

Note: You can also use the native XSL format-number function to format numbers. Use only one of these methods. If the number format mask is specified using both methods, the data will be formatted twice, causing unexpected behavior.The group separator and the number separator will be set at runtime based on the template locale. This is applicable for both the Oracle format mask and the MS format mask.

Data Source Requirements

To use the Oracle format mask or the Microsoft format mask, the numbers in your data source must be in a raw format, with no formatting applied (for example: 1000.00). If the number has been formatted for European countries (for example: 1.000,00) the format will not work.

Note: The XML Publisher parser requires the Java BigDecimal string representation. This consists of an optional sign ("-") followed by a sequence of zero or more decimal digits (the integer), optionally

followed by a fraction, and optionally followed by an exponent. For example: -123456.3455e-3.

Translation Considerations

If you are designing a template to be translatable, using currency in the Microsoft format mask is not recommended unless you want the data reported in the same currency for all translations. Using the MS format mask sets the currency in the template so that it cannot be updated at runtime.

Instead, use the Oracle format mask. For example, L999G999G999D99, where "L" will be replaced by the currency symbol based on the locale at runtime.

Do not include "%" in the format mask because this will fix the location of the percent sign in the number display, while the desired position could be at the beginning or the end of a number, depending on the locale.

Using the Microsoft Number Format Mask

To format numeric values, use Microsoft Word’s field formatting features available from the Text Form Field Options dialog box. The following graphic displays an example:

To apply a number format to a form field:

1. Open the Form Field Options dialog box for the placeholder field.

2. Set the Type to Number.

3. Select the appropriate Number format from the list of options.

Supported Microsoft Format Mask Denitions

The following table lists the supported Microsoft format mask definitions:

Symbol	Location	Meaning
0	Number	Digit. Each explicitly set 0 will appear, if no other number occupies the position. Example: Format mask: 00.0000 Data: 1.234 Display: 01.2340
#	Number	Digit. When set to #, only the incoming data is displayed. Example: Format mask: ##.#### Data: 1.234 Display: 1.234
.	Number	Determines the position of the decimal separator. The decimal separator symbol used will be determined at runtime based on template locale. For example: Format mask: #,##0.00 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
-	Number	Determines placement of minus sign for negative numbers.
,	Number	Determines the placement of the grouping separator. The grouping separator symbol used will be determined at runtime based on template locale. For example: Format mask: #,##0.00 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
E	Number	Separates mantissa and exponent in a scientific notation. Example: 0.###E+0 plus sign always shown for positive numbers 0.###E-0 plus sign not shown for positive numbers
;	Subpattern boundary	Separates positive and negative subpatterns. See Note below.
%	Prefix or Suffix	Multiply by 100 and show as percentage
‘	Prefix or Suffix	Used to quote special characters in a prefix or suffix

Using the Oracle Format Mask

To apply the Oracle format mask to a form field:

1. Open the Form Field Options dialog box for the placeholder field.

2. Set the Type to "Regular text".

3. In the Form Field Help Text field,

Enter the mask definition according to the

following example:

<?format-number:fieldname;’999G999D99’?>

where

fieldname is the XML tag name of the data element you are formatting and

999G999D99 is the mask definition.

The following graphic shows an example Form Field Help Text dialog entry for the

data element "empno":

The following table lists the supported Oracle number format mask symbols and their

definitions:

Symbol	Meaning
0	Digit. Each explicitly set 0 will appear, if no other number occupies the position. Example: Format mask: 00.0000 Data: 1.234 Display: 01.2340
9	Digit. Returns value with the specified number of digits with a leading space if positive or a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. Example: Format mask: 99.9999 Data: 1.234 Display: 1.234
C	Returns the ISO currency symbol in the specified position.
D	Determines the placement of the decimal separator. The decimal separator symbol used will be determined at runtime based on template locale. For example: Format mask: 9G999D99 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
EEEE	Returns a value in scientific notation.
G	Determines the placement of the grouping (thousands) separator. The grouping separator symbol used will be determined at runtime based on template locale. For example: Format mask: 9G999D99 Data: 1234.56 Display for English locale: 1,234.56 Display for German locale: 1.234,56
L	Returns the local currency symbol in the specified position
MI	Displays negative value with a trailing "-".
PR	Displays negative value enclosed by <>
PT	Displays negative value enclosed by ()
S (before number)	Displays positive value with a leading "+" and negative values with a leading "-"
S (after number)	Displays positive value with a trailing "+" and negative value with a trailing "-"

Date Formatting

XML Publisher supports three methods for specifying the date format:

• Specify an explicit date format mask using Microsoft Word’s native date format mask.

• Specify an explicit date format mask using Oracle’s format-date function.

• Specify an abstract date format mask using Oracle’s abstract date format

masks. (Recommended for multilingual templates.)

Only one method should be used. If both the Oracle and MS format masks are

specified, the data will be formatted twice causing unexpected behavior.

Data Source Requirements

To use the Microsoft format mask or the Oracle format mask, the date from the XML

data source must be in canonical format. This format is:

YYYY-MM-DDThh:mm:ss+HH:MM

where

• YYYY is the year

• MM is the month

• DD is the day

• T is the separator between the date and time component

• hh is the hour in 24-hour format

• mm is the minutes

• ss is the seconds

• +HH:MM is the time zone offset from Universal Time (UTC), or Greenwich Mean Time

An example of this construction is:

2005-01-01T09:30:10-07:00

The data after the "T" is optional, therefore the following date: 2005-01-01 can be formatted using either date formatting option. Note that if you do not include the time zone offset, the time will be formatted to the UTC time.

Translation Considerations

If you are designing a template to be translatable, explicitly setting a date format mask is not recommended. This is because the date format mask is part of the template, and all published reports based on this template will have the same date format regardless of locale. For translatable templates, it is recommended that you use the Oracle abstract date format. If it is necessary to explicitly specify a format mask, the Oracle format mask is recommended over the MS format mask to ensure future compatibility.

Using the Microsoft Date Format Mask

To apply a date format to a form field:

1. Open the Form Field Options dialog box for the placeholder field.

2. Set the Type to Date, Current Date, or Current Time.

3. Select the appropriate Date format from the list of options. If you do not specify the mask in the Date format field, the abstract format mask"MEDIUM" will be used as default.

The following figure shows the Text Form Field Options dialog box with a date format

applied:

The following table lists the supported Microsoft date format mask components:

Symbol	Meaning
d	The day of the month. Single-digit days will not have a leading zero.
dd	The day of the month. Single-digit days will have a leading zero.
ddd	The abbreviated name of the day of the week, as defined in AbbreviatedDayNames.
dddd	The full name of the day of the week, as defined in DayNames.
M	The numeric month. Single-digit months will not have a leading zero.
MM	The numeric month. Single-digit months will have a leading zero.
MMM	The abbreviated name of the month, as defined in AbbreviatedMonthNames.
MMMM	The full name of the month, as defined in MonthNames.
yy	The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero.
yyyy	The year in four digits.
gg	The period or era. This pattern is ignored if the date to be formatted does not have an associated period or era string.
h	The hour in a 12-hour clock. Single-digit hours will not have a leading zero.
hh	The hour in a 12-hour clock. Single-digit hours will have a leading zero
H	The hour in a 24-hour clock. Single-digit hours will not have a leading zero.
HH	The hour in a 24-hour clock. Single-digit hours will have a leading zero.
m	The minute. Single-digit minutes will not have a leading zero.
mm	The minute. Single-digit minutes will have a leading zero.
s	The second. Single-digit seconds will not have a leading zero.
ss	The second. Single-digit seconds will have a leading zero.
f	Displays seconds fractions represented in one digit.
ff	Displays seconds fractions represented in two digits.
fff	Displays seconds fractions represented in three digits.
ffff	Displays seconds fractions represented in four digits.
fffff	Displays seconds fractions represented in five digits.
ffffff	Displays seconds fractions represented in six digits.
fffffff	Displays seconds fractions represented in seven digits.
tt	The AM/PM designator defined in AMDesignator or PMDesignator, if any.
z	Displays the time zone offset for the system’s current time zone in whole hours only. (This element can be used for formatting only)
zz	Displays the time zone offset for the system’s current time zone in whole hours only. (This element can be used for formatting only)
zzz	Displays the time zone offset for the system’s current time zone in hours and minutes.
:	The default time separator defined in TimeSeparator.
/	The default date separator defined in DateSeparator.
‘	Quoted string. Displays the literal value of any string between two ‘ characters.
“	Quoted string. Displays the literal value of any string between two “ characters.

Base Tables:

The following base tables used to stores  details of  XML Publiser

• XDO_LOBS
• XDO_DS_DEFINITIONS_B
• XDO_DS_DEFINITIONS_TL
• XDO_TEMPLATES_B
• XDO_TEMPLATES_TL

How to design the XML Publisher Report

Step1:

Create the rdf file (layout is not required)

1.2 create executable file.

Step2: 

Create  Concurrent  Program

Step3:

Run the concurrent  the program.

Save as .xml   the file.

Step4:

Create Template

1. Open Ms Word. If Template Builder is already installed on the client machine, You can see the menu as shown in the below picture.
2. Load the xml file

 For example:

2.1 Load the xml file into ms word

2.3 Load into insert fields or individual fields, here we can design the layout.

2.4 see the report output in preview mode

2.5              save as the Template in rtf format.

Step5: Go to XML PUBLISHER ADMISTRATOR Resp. 

Navigation: Home>Data Definitions

5.1 Create data definitions.

It will looks like below after creation

5.2 Go to Templates

a.       create template name.

b.      create code.

c.       select the Application.

d.      set type as RTF

e.       Attach the Data definitions here.

f.       Active is yes

g.      press Create the Templates  

  

Note: Template Code is the Concurrent Program short name.

Attached the report to your request group and Run the report.

-- ************************ HOPE THIS WILL HELP ****************************