- The report data model has been created and runs successfully. For information on creating Data Models, see the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher .
- Supported versions of Microsoft Word and Microsoft Windows are installed on the client.
Note: See System Requirements and Certification for the most up-to-date information on supported hardware and software.
- The Template Builder does not support bidirectional display of text in the user interface.
5.2 Getting Started
This section describes how to get started. It contains the following topics:
- Section 5.2.1, "Features of the Oracle BI Publisher Template Builder for Word"
- Section 5.2.2, "Building and Uploading a Template"
- Section 5.2.2.1, "Working in Connected Mode"
5.2.1 Features of the Oracle BI Publisher Template Builder for Word
When you open Microsoft Word after installing the Template Builder, you see the Oracle BI Publisher menu.
For versions of Microsoft Word prior to 2007, the menu and toolbar are displayed as shown in Figure 5-1.
Figure 5-1 Display of the Menu and Toolbar in Versions of Microsoft Word Prior to 2007
For Microsoft Word 2007 users, the BI Publisher commands are displayed in the ribbon format, as shown in Figure 5-2.
Figure 5-2 BI Publisher Commands Displayed in the Ribbon Format
Use the menu (or toolbar) to perform the following:
- Insert data fields into age RTF templates
- Insert tables, forms, charts, and pivot tables
- Preview the template in multiple outputs
- Browse and update the content of form fields
- Validate the template
- Perform calculations on fields within the template
- Connect to the Oracle BI Publisher catalog to retrieve data to build the template
- Upload the template to the Oracle BI Publisher server
- Extract boilerplate text into an XLIFF translation file and test translations
5.2.2 Building and Uploading a Template
You can build and upload the template using a direct connection with the BI Publisher server, or you can build and upload the template in disconnected mode.
5.2.2.1 Working in Connected Mode
To work in connected mode:
- Open Microsoft Word.
- From the Oracle BI Publisher menu, select Log On .
- Enter your BI Publisher credentials and the URL for the BI Publisher server, for example: http://www.example.com:7001/xmlpserver. (Contact your system administrator if you do not know the URL.)
- The Open Template dialog presents the same folder structure as the BI Publisher catalog. Select the report or data model for which you want to build a template.
- If you selected a data model : Click Create Report to create a report for this data model in the BI Publisher catalog. This is the report that you upload the template to. Enter a Report Name and select the folder in which to save it. Click Save . The sample data from the data model is loaded to the Template Builder. If you selected a report : Click Open Report to load the data to the Template Builder; or double-click in the Layout Templates pane, as shown in Figure 5-3. Any existing templates are listed in the Layout Templates pane.
5.2.2.2 Working in Disconnected Mode
To work in disconnected mode, you must have a sample data file available in the local work environment.
To work in disconnected mode:
- Save a sample data file to your local computer. See Section 5.3, "Accessing Data for Building Templates."
- Open Microsoft Word with the Template Builder installed.
- On the Oracle BI Publisher menu in the Load Data group select Sample XML . Locate the sample data file in the local directory and click Open .
Note: The Template Builder also supports using XML Schema to design an RTF template. However, because the schema contains no data, the preview of the report also contains no data.
5.3 Accessing Data for Building Templates
The data model defines the XML format that is merged with the RTF template. The Template Builder requires sample data to build the template. You must load sample data to use most of the template builder functionality.
If you are not connected to BI Publisher, then use the procedure in Section 5.3.1, "Loading XML Data from a Local File." If you are connected, then use the procedure in Section 5.3.2, "Loading Data from the BI Publisher Catalog."
5.3.1 Loading XML Data from a Local File
One method of loading data to the Template Builder is to save a sample of the report data to a local directory.
If you do not have access to the report data model, but you can access the report, then you can alternatively save sample data from the report viewer.
To save data from the report viewer:
- In the BI Publisher catalog, navigate to the report.
- Click Open to run the report in the report viewer.
- Click the Actions icon, then click Export , then click Data . You are prompted to save the XML file.
- Save the file to a local directory.
- Use the Load Sample XML feature below to load the saved XML file to the Template Builder.
The Load Data group from the Oracle BI Publisher menu enables you to select and load the saved XML file to the Template Builder.
- Sample XML - Enables you to load a sample XML file that contains all fields that you want to insert into the template as a data source. If you are not connected to the BI Publisher server, then use this method to load the data.
- XML Schema - Enables you to load an XML Schema file (.xsd) that contains the fields available in the report XML data. The XML schema has the advantage of being complete (a sample XML file might not contain all the fields from the data source). For the preview, the Template Builder can generate dummy sample data for an XML Schema. However, the preview works better if you also upload real sample data.
5.3.2 Loading Data from the BI Publisher Catalog
You can connect directly to the BI Publisher Server to load the BI Publisher report data to the Template Builder to use as sample data for designing layouts. You can also download an existing template to modify it.
To connect to BI Publisher and load a data source:
- Log on to the BI Publisher Server: From the Oracle BI Publisher menu, select Log On . For more information on logging in to the BI Publisher server, see Section 5.2.2.1, "Working in Connected Mode."
- After you area logged on, you can select Open . The Open Template dialog launches.
- Navigate to the folder that contains the report or data model for which you want to create a template. When you select a report, you can either select from the Layout Templates to open an existing template, select Open Report to load just the XML sample data to create a new layout, or double-click to load the data to the Template Builder to build a new layout. When you select a data model, you are prompted to create a report in the catalog.
5.4 Inserting Components to the Template
This section includes the following topics:
- Section 5.4.1, "Inserting a Field"
- Section 5.4.2, "Inserting a Table Using the Table Wizard"
- Section 5.4.3, "Inserting a Table or Form Using the Insert Table/Form Dialog"
- Section 5.4.4, "Inserting a Chart"
- Section 5.4.5, "Inserting a Repeating Group"
- Section 5.4.6, "Creating Grouping Fields Around an Existing Block"
- Section 5.4.7, "Inserting a Pivot Table"
- Section 5.4.8, "Manually Editing a Pivot Table"
- Section 5.4.9, "Inserting and Editing Conditional Regions"
- Section 5.4.10, "Inserting Conditional Formatting"
5.4.1 Inserting a Field
This dialog enables you to select data elements from the data source and insert them into the template.
In the Insert group select Field to open the Field dialog. The dialog shows the structure of the loaded data source in a tree view, as shown in Figure 5-4.
Figure 5-4 The Field Dialog
Select a field that represents a single data field (a leaf node of the tree) and select Insert (you can also insert the field by dragging and dropping it into the document, or by double-clicking the field). A text form field with hidden BI Publisher commands is inserted at the cursor position in the template. You may either select and insert additional data fields or close the dialog by clicking the Close button.
5.4.1.1 About the Insert Field Dialog
The Insert Field dialog fields are described in the following sections:
5.4.1.2 Find
For an XML document with a large and complicated structure, use the find functionality to find a specific field. Enter a partial string of the field name you are searching into the Find field and click Find Next . The next occurrence of a data element that includes the search expression is selected. Click the Find Next button again to see the next occurrence.
5.4.1.3 Example
When you select a field name in the tree view, an example value for this field is shown.
5.4.1.4 Force LTR (Left-to-Right) Direction
This check box is only needed if you are using the template in a language that prints the characters from right to left, such as Arabic or Hebrew. Use this feature to force left-to-right printing for fields such as phone numbers, addresses, postal codes, or bank account numbers.
5.4.1.5 Calculation
This feature enables you to perform aggregation functions on data fields, such as sum, average, count, minimum, and maximum.
For example, if you select sum for a data field, then the field shows the sum of all occurring values for this data field, depending on the grouping.
It is important to understand the grouping context (marked by G and E form fields) to know exactly which fields are accumulated. If you insert a data field with an accumulation function into a repeating section (marked by G and E processing instruction form fields), you must select On Grouping to accumulate the data for the occurrences within the group. If you do not want the accumulation to be restricted to the group, you must place the accumulation field outside the group.
Figure 5-5 shows an example.
Figure 5-5 Grouping Context Example
Also note that the data field must be a valid XSL number for the accumulation functions to work. Formatted numbers cannot be processed by BI Publisher (for example a number using a thousands separator: 10,000,000.00 cannot be processed).
For more information on groups in s template using the Template Builder, see Section 5.4.5, "Inserting a Repeating Group" and Section 4.4, "Defining Groups."
5.4.2 Inserting a Table Using the Table Wizard
The Insert Table Wizard enables you to create standard reports. On the Insert menu select Table Wizard and complete the following steps:
- Step 1: Select Report Format
- Step 2: Select Table Data
- Step 3: Select Data Fields
- Step 4: Group the Table
- Step 5: Insert a Break for the Group
- Step 6: Sort the Table
- Step 7: Click Finish
- Step 8: Customize the Table Using Microsoft Word Functionality
5.4.2.1 Step 1: Select Report Format
Start by selecting the basic report format. Choose from Table , Form , or Free Form . Figure 5-6 shows examples of each format.
Figure 5-6 Examples of Table, Form, and Free Form Formats
5.4.2.2 Step 2: Select Table Data
An XML document can include multiple grouped data sets. For example, a purchase order XML document may contain header level information, lines, shipments and contacts.
In this step, select the data group that contains the data that is required for the table.
For example, in the Balance Letter sample RTF template (found in the Template Builder installed files under Oracle\BI Publisher\BI Publisher Desktop\samples\RTF Templates), the sample XML file contains three data groups as follows:
- ARXCOBLX/G_CUSTOMER
- ARXCOBLX/G_CUSTOMER/G_CURRENCY
- ARXCOBLX/G_CUSTOMER/G_CURRENCY/G_INVOICES
The Table Wizard presents a list of the available data groups in the XML data file. Select the group that contains the data fields for the table.
Figure 5-7 shows the Table Wizard Step 2: Selecting Table Data.
Figure 5-7 The Table Wizard Step 2: Selecting Table Data
To build a table to list the invoices contained in the data, select:
as the data set.
5.4.2.3 Step 3: Select Data Fields
The Table Wizard presents the data fields from the selected data set.
Figure 5-8 shows the Table Wizard Step 3: Selecting Data Fields.
Figure 5-8 The Table Wizard Step 3: Selecting Data Fields
Use the shuttle buttons to select the data fields to show in the table. Use the up and down arrows to reorder the fields after selecting them.
5.4.2.4 Step 4: Group the Table
This step enables you to regroup the data by a particular field. This is optional.
For example, if you are building a table of invoices, you may want to group all invoices of a particular type or date to be grouped together in the report.
Figure 5-9 shows the Table Wizard Step 4: Grouping the Table.
Figure 5-9 The Table Wizard Step 4: Grouping the Table
There are two options for grouping: Group Left or Group Above. Group Left creates a nested table. The Group By field displays to the left in the outer table. Group Above creates a new table for each new value of the group by field, displaying the value of the group by field as a table title.
Group Left groups the group by element occurrences together, as shown in Figure 5-10.
Figure 5-10 Results of the Group Left Option
Group Above shows the result as a table with a header, as shown in Figure 5-11.
Figure 5-11 Results of the Group Above Option
When you select an element to group by, BI Publisher sorts the data by the grouping element. If the data is already sorted by the grouping element, then select the Data already sorted check box. This selection improves performance.
5.4.2.5 Step 5: Insert a Break for the Group
Use the Break option to insert either a Page break or Section break after each occurrence of this group. Note that a Section break can only be created on the top-level group. The subsequent grouping options only display the Page break option.
A page break starts the next group on a new page; a section break starts the next group on a new page, reset page numbering, reset headers and footers, and reset any running calculations for each occurrence of the group.
5.4.2.6 Step 6: Sort the Table
You can sort the data in the table by up to four different fields. Select a field and then define the sorting order (ascending or descending), and select the correct data type for the field. For example, if text is selected, "12" comes before "2" (alphanumerical order). If number is selected, "2" comes before "12".
Figure 5-12 shows the Table Wizard Step 6: Sorting the Table.
Figure 5-12 The Table Wizard Step 6: Sorting the Table
5.4.2.7 Step 7: Click Finish
Click Finish to create the table and insert it to the Microsoft Word document.
5.4.2.8 Step 8: Customize the Table Using Microsoft Word Functionality
Customize the table by changing fonts, colors, column sizing, borders, shading, and so on, using Microsoft Word formatting commands.
5.4.3 Inserting a Table or Form Using the Insert Table/Form Dialog
The Insert Table/Form dialog is the most flexible tool of the template builder. It allows you to perform the following tasks:
- Create a simple or nested table with a variable number of rows.
- Associate a group of data elements, such as a complete invoice or a purchase order line, with a form in the document that is repeated for each occurrence of the data element.
- Select and define a layout for all the data fields in the template.
- Group or re-group the data.
The Insert Table/Form dialog shows you two tree view panes. The left pane shows the data source structure, while the right pane shows the elements that are copied to the template when you click the Insert button.
5.4.3.1 Selecting Data Fields
First select the data fields to insert in the template and then define how to format them. Drag an XML element from the left Data Source pane to the right Template pane. If the XML element has children, you see a pop-up menu with the following options:
- Drop Single Node
- Drop All Nodes
- Cancel
Select Drop Single Node if you want to move only the selected node or Drop All Nodes if you want to move the node and all its children.
If you drag an additional data field from the left Data Source pane to the right Template pane, it is either inserted at the same level (Same Level) or below the node (Child) where you release the node. The Insert Position box defines where the node is inserted.
If you use the left mouse button for drag and drop, then the node and all children are copied. However, if you use the right mouse button for dragging, a dialog is displayed when you release the mouse button. The dialog gives you the option to copy either only the selected node or the selected node and all children.
5.4.3.2 Defining the Layout
When you select an element in the right Template pane, you see its properties as well as a preview of how the node is rendered. There are two kinds of nodes:
- Data Fields
- Data Groups
Data Field nodes (leaf nodes) do not have any child nodes. They represent simple attributes such as the total amount for an invoice or the subtotal for a purchase order line.
Data Group nodes (parent nodes) are nodes that do have child nodes. Typically, they do not represent data attributes, but groups of data - such as an invoice, a purchase order, a purchase order line or a shipment.
5.4.3.3 Data Field Properties
If a Data Field node is selected, its properties are shown in the Properties pane. You have the following options to describe how the Template Builder should show the field:
- Calculation You can select one of the aggregation functions for the data fields. These functions (besides count) only have an effect when there is more than one of the data fields in the context where you use the function.
- Force LTR (Left-to-Right) Direction This option is only needed if you are using the template in a language that displays characters from right to left, such as Arabic or Hebrew. Use this option to force left-to-right printing for fields such as phone numbers, addresses, postal codes, or bank account numbers.
5.4.3.4 Data Group Properties
The order in which the data elements are shown reflects the order of the columns in the table. If you want to reorder the columns, change the Insert Position box from Child to Same Level. Then drag the elements into the correct order.
If a Data Group node is selected, its properties are shown in the Properties pane. You have the following options to describe how the Template Builder should render the group:
- Style To display the data as a horizontal table with a header, select Table. To display the fields below each other with labels in a table, use Form. If you want to insert the fields into a free-form text section that should to repeated for this element select Free Form.
- Grouping Grouping is an advanced operation that allows you to group the data by a specific element in the data. For example, you might want to group all invoices by customer. You can select a child element of the selected element as a grouping criterion. For more information see Section 5.4.3.6, "Grouping."
- Show Grouping Value This property is shown only if you have selected a node created by the Grouping functionality. By default, the field you have selected to group the data by is displayed in the report. If you do not want the grouping data field displayed, then select No.
- Sort By Select an element by which the data groups are sorted.
- Sort Order If you have selected an element for Sort By you can select if the data should be sorted either ascending or descending.
- Sort Data Type If you have selected an element for Sort By the data is by default sorted as text. That means that 12 is shown after 111. If the data is numeric, select Number as the sort data type.
- Break This property allows you to insert a page break or a section break between every data group. If you select New Page per Element, then a page break is inserted between each element after the first occurrence.
Tip: To insert a page break before the first occurrence of an element, use Microsoft Word's page break command.
5.4.3.5 Inserting Tables and Forms
Once you have dragged all data fields over and defined the layout, select the Insert button to place the tables and forms at the cursor position in the document.
5.4.3.6 Grouping
You can group any Data Group node, by any of its child Data Field Nodes. For example if you have sales data for multiple quarters, you may want to show the sales data organized by quarter. In this case you would group the sales data rows by the quarter element.
Assume the following structure:
Sales Transaction Quarter Customer Amount
To group the child nodes of a node (Sales Transaction), you select one of the child nodes (Quarter) as the grouping property of the parent node (Sales Transaction). The Template Builder makes this node (e.g. quarter) the parent of the other child nodes (Customer and Amount).
The new structure looks like the following:
Sales Transaction Quarter Customer Amount
The grouping criterion (Quarter) now behaves like any other Data Group Node with children. That means that you can define the layout of its children using the Create As Table, Style, Label, Grouping, and Show Grouping Value properties.
5.4.3.7 Understanding Fields Inserted to the Template
The Insert Table/Form Dialog creates two kinds of form fields:
- Form fields representing data elements
- Form fields with processing instructions for repeating table rows or document sections
Form fields representing data elements are replaced with the data when the template is processed. Form fields indicating repeating sections are shown as for-each and end for-each in the document.
If you have selected the Abbreviated form field display option, then the for-each and end for-each form fields are displayed as F and E. The section of the document encapsulated by these two elements is repeated, if the associated data element is repeated in the data.
5.4.4 Inserting a Chart
Use the Chart dialog to insert a chart into a template.
Figure 5-13 shows the Chart dialog.
Figure 5-13 The Chart Dialog
5.4.4.1 Chart Type
BI Publisher supports a large variety of chart types. Expand the Type list to select the chart type for this template.
5.4.4.2 Values
Drag and drop the data value you want to measure to the Values field (for example, SALES). You can select multiple Value elements (measures).
The Values field changes depending on the Chart Type that you select:
- Combination Graph - Enables three fields for the Value selections.
- Scatter Graph - Compares pairs of values. Drag and drop the X and Y data elements to compare.
- Bubble Graph - Compares sets of three values. Similar to the scatter graph, the third value is displayed as the size of the bubble.
- Stock Graph - Drag and drop the elements that represent the Open, High, Low, Close, and Volume values for the stock graph.
5.4.4.3 Aggregation
You can choose to aggregate the Values data as a sum, a count, or an average.
5.4.4.4 Labels
Drag and drop the data element for which you want to see the Value charted (for example, Year). Select Group Data to group the occurrences of the label element before rendering it in the chart. For example, if you are charting Sales by Year, then selecting Group Data accumulates the values for Year, so that only one occurrence of each year is displayed in the chart. If you do not select Group Data, then the value for every occurrence of Year in the data is plotted separately.
5.4.4.5 Color
If you want to add a series element to the chart, then drag and drop the element to display as a series. Each value is displayed as a new color in the graph.
5.4.4.6 Chart is Inside Group
Select this box if the chart is inside a grouping and you want the chart to display data only for the occurrences of the data elements within the group.
5.4.4.7 Style
Select a color scheme and style for the chart.
5.4.4.8 Properties
The properties region enables you to change value and label display names, select color, font, and other display options for the chart. The properties list changes depending on the chart selection.
5.4.4.9 Preview
Click Preview to display the chart with the sample data.
5.4.4.10 Group Data
By default the data is grouped by the Value element and aggregated by sum.
If you deselect the Group Data check box, the each occurrence of the value element is charted and aggregation functions are not available.
5.4.4.11 Editing an Inserted Chart
To edit a chart that you have already inserted into the template, right-click the chart and select BI Publisher Chart from the menu. This invokes the chart dialog to enable you to edit the chart.
5.4.5 Inserting a Repeating Group
To insert a repeating group:
- Select the section of the template that contains the elements you want repeated.
- On the Oracle BI Publisher menu, in the Insert group, click Repeating Group .
- Enter the appropriate fields in the BI Publisher Properties dialog, as shown in Figure 5-14.
- For loops and groupings not inside another group (that is, outer groups or loops) you must select the repeating XML element to be used. For example if the data set is flat, the only repeatable element is /DATA/ROWSET/ROW. In cases with multiple data sources or hierarchical XML you can choose the data set.
- If you are creating nested groups (inserting a loop or group inside of another loop in the template), the For Each field is not updatable because it is already defined by the preexisting outer loop. The For Each field is displayed as "Group Item" to inform you that an outer group is already defined.
Select this check box to use the Absolute Path to the element in the XML structure. This is important if the data contains the same element name grouped under different parent elements.
Select a field from the list by which you want to group the data. If you just want to create a simple loop, do not select a group by element. Selecting a group by element actually regroups the data into a new hierarchy based on the group by element.
Use this option to create either a Page break or Section break if you want to insert a break after each occurrence of this group.
A Section break can only be created on outer groups that surround the whole document. If the selected field is not an outer group, the Section break option is not available.
Note also that when you insert a section break, the page numbering is reset, headers and footers are reset, and any running calculations are reset for each occurrence of the group.
Note: You can set the default display text as Descriptive or Abbreviated using the Options tab. Figure 5-15 shows the Advanced tab of the BI Publisher Properties dialog.5.4.6 Creating Grouping Fields Around an Existing Block
To create a group around an existing block of text or elements in a template:
- Select the block of text. For example, a table row. If any preexisting BI Publisher tags are included in the block, then you must include the beginning and ending tags. For example, if the block contains any opening for-each, if, or for-each-group tags, then you must include the end for-each, end-if, and end for-each-group tags in the selection.
- On the Oracle BI Publisher menu, on the Insert group, click Repeating Group .
- In the BI Publisher Properties dialog, enter the fields to define the group as described in Section 5.4.5, "Inserting a Repeating Group."
- Click OK to insert the grouping fields around the block. For example, if the block is a table row, then the begin field is inserted at the beginning of the first cell and the end field is inserted at the end of the last field.
5.4.7 Inserting a Pivot Table
To insert a pivot table:
-
On the BI Publisher menu on the Insert group, click Pivot Table . The Pivot Table dialog presents the data in the left pane with empty Layout panes on the right for you to drag and drop data elements. Figure 5-16 shows the Pivot Table dialog.
