Using Advanced Report Layouts
Learn more about Advanced Report Layouts.
This section describes the following tasks for advanced report layouts:
Creating Batch Reports
It is a common requirement to print a batch of documents, such as invoices or purchase orders in a single PDF file. Because these documents are intended for different customers, each document requires that the page numbering be reset and that page totals are specific to the document. If the header and footer display fields from the data (such as customer name), then these must be reset as well.
BI Publisher supports this requirement through the use of a context command. This command allows you to define elements of the report to a specific section. When the section changes, these elements are reset.
The following example demonstrates how to reset the header and footer and page numbering within an output file:
The following XML code is a report that contains multiple invoices:
...
<LIST_G_INVOICE>
<G_INVOICE>
<BILL_CUST_NAME>Vision, Inc. </BILL_CUST_NAME>
<TRX_NUMBER>2345678</TRX_NUMBER>
...
</G_INVOICE>
<G_INVOICE>
<BILL_CUST_NAME>Oracle, Inc. </BILL_CUST_NAME>
<TRX_NUMBER>2345685</TRX_NUMBER>
...
</G_INVOICE>
...
</LIST_G_INVOICE>
...
Each G_INVOICE element contains an invoice for a potentially different customer. To instruct BI Publisher to start a new section for each occurrence of the G_INVOICE element, add the @section command to the opening for-each statement for the group, using the following syntax:
<?for-each@section:group name?>
where group_name is the name of the element for which you want to begin a new section.
For example, the for-each grouping statement for this example is as follows:
<?for-each@section:G_INVOICE?>
The closing <?end for-each?> tag is not changed.
The following figure shows a sample template for batch reports:
Note:
The G_INVOICE group for-each declaration is still within the body of the report, even though the headers are reset by the command.
The following table describes the values of the form fields from the template in the previous figure (that shows a sample template for batch reports):
| Default Text Entry | Form Field Help Text | Description |
|---|---|---|
|
for-each G_INVOICE |
|
Begins the G_INVOICE group, and defines the element as a Section. For each occurrence of G_INVOICE, a new section is started. |
|
|
N/A |
Microsoft Word does not support form fields in the header, therefore the placeholder syntax for the TRX_NUMBER element is placed directly in the template. |
|
end G_INVOICE |
|
Closes the G_INVOICE group. |
Now for each new occurrence of the G_INVOICE element, a new section begins. The page numbers restart, and if header or footer information is derived from the data, it is reset as well.
Handling No Data Found Conditions
When you use @section with the BI Publisher commands for-each or for-each-group (for example: <?for-each@section:ELEMENT_NAME?>), and the input data file has no data, then an empty or invalid PDF output document may be generated for that for-each loop. To prevent this from happening, edit the RTF template.
Inserting Pivot Tables
The columns of a pivot table are data dependent.
At design time you do not know how many columns are reported, or what the appropriate column headings are. Moreover, if the columns should break onto a second page, you must be able to define the row label columns to repeat onto subsequent pages. The following example shows how to design a simple pivot tale report that supports these features. See Inserting a Pivot Table.
This example uses the following XML sample:
<ROWSET>
<RESULTS>
<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>
<YEAR>2005</YEAR>
<QUARTER>Q1</QUARTER>
<SALES>1000</SALES>
</RESULTS>
<RESULTS>
<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>
<YEAR>2005</YEAR>
<QUARTER>Q2</QUARTER>
<SALES>2000</SALES>
</RESULTS>
<RESULTS>
<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>
<YEAR>2004</YEAR>
<QUARTER>Q1</QUARTER>
<SALES>3000</SALES>
</RESULTS>
<RESULTS>
<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>
<YEAR>2004</YEAR>
<QUARTER>Q2</QUARTER>
<SALES>3000</SALES>
</RESULTS>
<RESULTS>
<INDUSTRY>Motor Vehicle Dealers</INDUSTRY>
<YEAR>2003</YEAR>
...
</RRESULTS>
<RESULTS>
<INDUSTRY>Home Furnishings</INDUSTRY>
...
</RESULTS>
<RESULTS>
<INDUSTRY>Electronics</INDUSTRY>
...
</RESULTS>
<RESULTS>
<INDUSTRY>Food and Beverage</INDUSTRY>
...
</RESULTS>
</ROWSET>
From this XML code, a report is generated that shows each industry and totals the sales by year as shown in the following illustration.
The following illustration shows the template to generate the report that is shown in the previous illustration.
The form fields in the template that is shown in the previous illustration have the values that are described in the following table.
| Default Text Entry | Form Field Help Text | Description |
|---|---|---|
|
header column |
|
Defines the first column as a header that should repeat if the table breaks across pages. See Defining Columns to Repeat Across Pages. |
|
for: |
|
Uses the regrouping syntax (see Regrouping the XML Data) to group the data by YEAR; and the @column context command to create a table column for each group (YEAR). See Controlling the Placement of Instructions Using the Context Commands. |
|
YEAR |
|
Placeholder for the YEAR element. |
|
end |
|
Closes the for-each-group loop. |
|
for: |
|
Begins the group to create a table row for each INDUSTRY. |
|
INDUSTRY |
|
Placeholder for the INDUSTRY element. |
|
for: |
|
Uses the regrouping syntax (see Regrouping the XML Data) to group the data by YEAR; and the @cell context command to create a table cell for each group (YEAR). |
|
sum(Sales) |
|
Sums the sales for the current group (YEAR). |
|
end |
|
Closes the for-each-group statement. |
|
end |
|
Closes the for-each-group statement. |
Note that only the first row uses the @column context to determine the number of columns for the table. All remaining rows must use the @cell context to create the table cells for the column. See Controlling the Placement of Instructions Using the Context Commands.
Constructing Dynamic Data Columns
The ability to construct dynamic data columns is a very powerful feature of the RTF template. Using this feature you can design a template that correctly renders a table when the number of columns that is required by the data is variable.
For example, you are designing a template to display columns of test scores within specific ranges. However, you do not how many ranges have data to report. You can define a dynamic data column to split into the correct number of columns at runtime.
Use the following tags to accommodate the dynamic formatting required to render the data correctly:
-
Dynamic Column Header
<?split-column-header:group element name?>Use this tag to define which group to split for the column headers of a table.
-
Dynamic Column
<?split-column-data:group element name?>Use this tag to define which group to split for the column data of a table.
-
Dynamic Column Width
<?split-column-width:name?>or<?split-column-width:@width?>Use one of these tags to define the width of the column when the width is described in the XML data. The width can be described in two ways:
-
An XML element stores the value of the width. In this case, use the syntax
<?split-column-width:name?>, where name is the XML element tag name that contains the value for the width. -
If the element defined in the split-column-header tag, contains a width attribute, use the syntax
<?split-column-width:@width?>to use the value of that attribute.
-
-
Dynamic Column Width's unit value (in points)
<?split-column-width-unit:value?>Use this tag to define a multiplier for the column width. If the column widths are defined in character cells, then you must use the appropriate multiplier value to render the columns to the correct width in points. For example, if you are using 10 point courier font in the table, you would use a multiplier of 6, which is the approximate width of a character displayed in 10 point courier font. If the multiplier is not defined, then the widths of the columns are calculated as a percentage of the total width of the table. The column width calculations are illustrated in the following table:
Width Definition Column 1 (Width = 10) Column 2 (Width = 12) Column 3 (Width = 14) Multiplier not present -% width
10/10+12+14*100 28%
%Width = 33%
%Width =39%
Multiplier = 6 - width
60 pts
72 pts
84 pts
Defining Columns to Repeat Across Pages
If the table columns expand horizontally across more than one page, you can define how many row heading columns you want to repeat on every page.
Use the following syntax to specify the number of columns to repeat:
<?horizontal-break-table:number?>
where number is the number of columns (starting from the left) to repeat.
Note:
This functionality is supported for PDF output only.Example of Dynamic Data Columns
A template is required to display test score ranges for school exams. Logically, you want the report to be arranged as shown in the table here.
| Test Score | Test Score Range 1 | Test Score Range 2 | Test Score Range 3 | ...Test Score Range n |
|---|---|---|---|---|
|
Test Category |
# students in Range 1 |
# students in Range 2 |
# students in Range 3 |
# of students in Range n |
However, you do not know how many Test Score Ranges are reported. The number of Test Score Range columns is dynamic, depending on the data.
The following XML data describes these test scores. The number of occurrences of the element <TestScoreRange> determine how many columns are required. In this case there are five columns: 0-20, 21-40, 41-60, 61-80, and 81-100. For each column there is an amount element (<NumOfStudents>) and a column width attribute (<TestScore width="15">).
<?xml version="1.0" encoding="utf-8"?>
<TestScoreTable>
<TestScores>
<TestCategory>Mathematics</TestCategory>
<TestScore width ="15">
<TestScoreRange>0-20</TestScoreRange>
<NumofStudents>30</NumofStudents>
</TestScore>
<TestScore width ="20">
<TestScoreRange>21-40</TestScoreRange>
<NumofStudents>45</NumofStudents>
</TestScore>
<TestScore width ="15">
<TestScoreRange>41-60</TestScoreRange>
<NumofStudents>50</NumofStudents>
</TestScore>
<TestScore width ="20">
<TestScoreRange>61-80</TestScoreRange>
<NumofStudents>102</NumofStudents>
</TestScore>
<TestScore width ="15">
<TestScoreRange>81-100</TestScoreRange>
<NumofStudents>22</NumofStudents>
</TestScore>
</TestScores>
<TestScoreTable>
Using the dynamic column tags in form fields, set up the table in two columns as shown in the following figure. The first column, Test Score is static. The second column, Column Header and Splittingis the dynamic column. At runtime this column is split according to the data, and the header for each column is appropriately populated. The Default Text entry and Form Field Help entry for each field are listed in the following table. (See Using the Form Field Method for more information on using form fields).
| Default Text Entry | Form Field Help Text Entry |
|---|---|
|
Group:TestScores |
|
|
Test Category |
|
|
Column Header and Splitting |
|
|
Content and Splitting |
|
|
end:TestScores |
|
-
Test Score is the boilerplate column heading.
-
Test Category is the placeholder for the
<TestCategory>data element, that is, Mathematics, which is also the row heading. -
The second column is the one to be split dynamically. The width you specify is divided by the number of columns of data. In this case, there are 5 data columns.
-
The second column contains the dynamic range data. The width of the column is divided according to the split column width. Because this example does not contain the unit value tag (
<?split-column-width-unit:value?>), the column is split on a percentage basis. Wrapping of the data occurs if required.If the tag (
<?split-column-width-unit:value?>) were present, then the columns have a specific width in points. If the total column widths were wider than the allotted space on the page, then the table breaks onto another page.The horizontal-break-table tag could then be used to specify how many columns to repeat on the subsequent page. For example, a value of 1 would repeat the column Test Score on the subsequent page, with the continuation of the columns that did not fit on the first page.
The template renders the output that is shown in the following figure:
