Inserting Page-Level Calculations

Learn more about the supported page-level calculations for PDFs and PPTs.

BI Publisher supports the page-level calculations that are described in the following sections for PDF and PPT outputs only:

Displaying Page Totals

BI Publisher allows you to display calculated page totals in the 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. This page totaling function works only if the source XML co6de has raw numeric values. The numbers must not be preformatted.

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 totaled 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 the page total, insert the following syntax immediately following the placeholder for the element that is to be totaled:

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

where

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

'element' is the XML element field to be totaled.

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

Then when you want to display the total field, enter the following syntax:

<?show-page-total:TotalFieldName;'Oracle-number-format' number-separators="{$_XDONFSEPARATORS}"?>

where

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

Oracle-number-format is the format you want to use to for the display, using the Oracle format mask (for example: 'C9G999D00'). For the list of Oracle format mask symbols, see Oracle Abstract Format Masks.

number-separators="{$_XDONFSEPARATORS}" is a required attribute to apply the grouping separator and decimal separator for the format mask you defined.

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 code:

<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:

Description of GUID-822F565C-CFB6-4F1B-8BB7-CD7C88B7AF29-default.gif follows

Description of the illustration GUID-822F565C-CFB6-4F1B-8BB7-CD7C88B7AF29-default.gif

The following table shows the form field entries made in the template whose table is shown in the previous figure:

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. To total this field by page, the page total declaration syntax is added. The variable defined to hold the total for the debit element is dt.
90.00	`<?credit?> <?add-page-total:ct;'credit'?>`	This field is the placeholder for the credit element from the XML file. To total this field by page, the page total declaration syntax is added. The variable 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.

Note that on the variable defined as "net" you perform a calculation on the values of the credit and debit elements.

Now that you have declared the page total fields, you can insert a field in the template where you want the page totals to appear. Reference the calculated variables using the names you supplied (in the example, ct and dt). The syntax to display the page totals is as follows:

For example, to display the debit page total, enter the following:

<?show-page-total:dt;'C9G990D00';'(C9G990D00)' number-separators="{$_XDONFSEPARATORS}"?>

Therefore to complete the example, place the following at the bottom of the template page, or in the footer:

Page Total Debit: <?show-page-total:dt;'C9G990D00';'(C9G990D00)' number-separators="{$_XDONFSEPARATORS}"?>

Page Total Credit: <?show-page-total:ct;'C9G990D00';'(C9G990D00)' number-separators="{$_XDONFSEPARATORS}"?>

Page Total Balance: <?show-page-total:net;'C9G990D00';'(C9G990D00)' number-separators="{$_XDONFSEPARATORS}"?>

The output for this report is shown in the following figure:

Description of GUID-C34CC61F-EE34-4228-9573-FB771B24D324-default.gif follows

Description of the illustration GUID-C34CC61F-EE34-4228-9573-FB771B24D324-default.gif

Inserting Brought Forward and Carried Forward Totals

Many reports require that a page total be maintained throughout the report output and be displayed at the beginning and end of each page. These totals are known as "brought forward and carried forward" totals.

Note:

The totaling for the brought forward and carried forward fields is performed in the PDF-formatting layer. Therefore this feature is not available for other outputs types such as HTML, RTF, and Excel.

An example of a report with forward totals is displayed in the following figure:

Description of GUID-7351323A-53E5-477C-9AEE-243BCA3689B2-default.gif follows

Description of the illustration GUID-7351323A-53E5-477C-9AEE-243BCA3689B2-default.gif

At the end of the first page, the page total for the Amount element is displayed as the Carried Forward total. At the top of the second page, this value is displayed as the Brought Forward total from the previous page. At the bottom of the second page, the brought forward value plus the total for that page is calculated and displayed as the new Carried Forward value, and this continues throughout the report.

This functionality is an extension of the Displaying Page Totals feature. The following example walks through the syntax and setup required to display the brought forward and carried forward totals in the published report.

Assume that you have the following XML code:

<?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>

The sample template that is shown in the following figure creates the invoice table and declares a placeholder that holds the page total.

Description of GUID-4362EE8C-65FA-48A9-A167-A6DEC545C9C0-default.gif follows

Description of the illustration GUID-4362EE8C-65FA-48A9-A167-A6DEC545C9C0-default.gif

The following table shows the fields in the template that is shown in the above figure.

Field	Form Field Help Text Entry	Description
Init PTs	`<?init-page-total: InvAmt?>`	Declares InvAmt as the placeholder that holds the page total.
FE	`<?for-each:INVOICE?>`	Begins the INVOICE group.
10001-1	`<?INVNUM?>`	Placeholder for the Invoice Number tag.
1-Jan-2005	`<?INVDATE?>`	Placeholder for the Invoice Date tag.
100.00	`<?INVAMT?>`	Placeholder for the Invoice Amount tag.
InvAmt	`<?add-page-total:InvAmt;INVAMT?>`	Assigns the "InvAmt" page total object to the INVAMT element in the data.
EFE	`<?end for-each?>`	Closes the INVOICE group.
End PTs	`<?end-page-total:InvAmt?>`	Closes the "InvAmt" page total.

To display the brought forward total at the top of each page (except the first), use the following syntax:

<xdofo:inline-total
  display-condition="exceptfirst"
  name="InvAmt">
   Brought Forward:
<xdofo:show-brought-forward
  name="InvAmt"
  format="99G999G999D00" number-separators="{$_XDONFSEPARATORS}"/>/>
</xdofo:inline-total>

The following list describes the elements that comprise the brought forward syntax:

inline-total - This element has two properties:
- name - Specifies the name of the variable you declared for the field.
- display-condition - Sets the display condition. This is an optional property that takes one of the following values:
  - first - Contents are displayed only on the first page.
  - last - Contents are displayed only on the last page.
  - exceptfirst - Contents are displayed on all pages except first.
  - exceptlast - Contents are displayed on all pages except last.
  - everytime - (Default) Contents are displayed on every page.
  In this example, display-condition is set to "exceptfirst" to prevent the value from appearing on the first page where the value would be zero.
Brought Forward: - This string is optional and is displayed as the field name on the report.
show-brought-forward - Shows the value on the page. It has the following properties:
- name - The name of the field to show. In this case, InvAmt. This property is mandatory.
- format - The Oracle number format to apply to the value at runtime. This property is optional, but if you want to supply a format mask, you must use the Oracle format mask.See Oracle Abstract Format Masks.
- number-separators="{$_XDONFSEPARATORS}" - This attribute is required to apply the grouping separator and number separator for the format mask you defined.

Insert the brought forward object at the top of the template where you want the brought forward total to display. If you place it in the body of the template, then you can insert the syntax in a form field.

If you want the brought forward total to display in the header, you must insert the full code string into the header because Microsoft Word does not support form fields in the header or footer regions. However, you can alternatively use the start body/end body syntax, which allows you to define what the body area of the report is. BI Publisher recognizes any content above the defined body area as header content, and any content below as the footer. This allows you to use form fields. See Creating Multiple or Complex Headers and Footers for details.

Place the carried forward object at the bottom of the template where you want the total to display. The carried forward object for our example is as follows:

<xdofo:inline-total
  display-condition="exceptlast"
  name="InvAmt">
   Carried Forward:
<xdofo:show-carry-forward
  name="InvAmt"
  format="99G999G999D00" number-separators="{$_XDONFSEPARATORS}"/>
</xdofo:inline-total>

Note the following differences with the brought-forward object:

The display-condition is set to exceptlast so that the carried forward total is displayed on every page except the last page.
The display string is "Carried Forward".
The show-carry-forward element is used to show the carried forward value. It has the same properties as brought-carried-forward, described above.

You are not limited to a single value in the template, you can create multiple brought forward/carried forward objects in the template pointing to various numeric elements in the data.

Note:

Ensure that you do not include the commands <?init-page-total:invAmnt?> and <?end-page-total:InvAmt?> as shown in the preceding example. The display-condition logic computation depends on these commands to function correctly.

Inserting Running Totals

The variable functionality can be used to add a running total to the invoice listing report.

See Setting Variables for more information. 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>

You can use this XML code to create a report that contains running totals as shown in the following illustration.

Description of GUID-038AAFB6-EFB3-4FA0-BFD8-81A79DFF5DCB-default.gif follows

Description of the illustration GUID-038AAFB6-EFB3-4FA0-BFD8-81A79DFF5DCB-default.gif

To create the Running Total field, define a variable to track the total and initialize it to 0. The template is shown in the following illustration.

Description of GUID-0B5C1607-9F34-43D0-9CBB-59F205784C90-default.gif follows

Description of the illustration GUID-0B5C1607-9F34-43D0-9CBB-59F205784C90-default.gif

The values for the form fields in the template that is shown in the previous illustration are described in the next table.

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.