Template builder word bi publisher

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.

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". Click Finish to create the table and insert it to the Microsoft Word document.

Customize the table by changing fonts, colors, column sizing, borders, shading, and so on, using Microsoft Word formatting commands. It allows you to perform the following tasks:. 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.

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. First select the data fields to insert in the template and then define how to format them. If the XML element has children, you see a pop-up menu with the following options:.

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

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:. 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. 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. The order in which the data elements are shown reflects the order of the columns in the table. 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:.

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

If you have selected an element for Sort By you can select if the data should be sorted either ascending or descending. If you have selected an element for Sort By the data is by default sorted as text. That means that 12 is shown after If the data is numeric, select Number as the sort data type. 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. To insert a page break before the first occurrence of an element, use Microsoft Word's page break command. If you select New Section per Element, then a section break is created for each data group.

A section break has the following effects: it inserts page break, it resets the page numbers and new data can be displayed in the header and footer. You typically use this option if you want to print multiple documents for example invoices or purchase orders to a single PDF file. 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.

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. 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. The grouping criterion Quarter now behaves like any other Data Group Node with children. 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. BI Publisher supports a large variety of chart types. Expand the Type list to select the chart type for this template. You can select multiple Value elements measures.

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

If you deselect the Group Data check box, the each occurrence of the value element is charted and aggregation functions are not available. 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. Select the element that for each occurrence, you want the loop to repeat. When you select the For Each data field you are telling BI Publisher that for each occurrence of the selected field in the data you want the elements and processing instructions contained within the loop to be repeated. For example, assume that the data contains invoice data for customers and you want to create a table with each customer's invoices.

In this case, for each customer number you want the table to repeat. You would therefore select the customer number in the For Each field to create a new loop or group for each customer. For loops and groupings not inside another group that is, outer groups or loops you must select the repeating XML element to be used.

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

To sort the grouped data, select the Sorting tab. You can select up to four sort-by fields. For each sort by field, select the following:. It is important that you select the correct data type to achieve the expected sort order. If you are sorting by four criteria and the XML data element names are long, then you might exceed the character length limitation characters of the Microsoft Word form field.

The Advanced tab enables you to edit the code directly and to enter Text to Display for the field. The Code region displays the code and processing instructions that the Template Builder has inserted for the field.

You can edit this if you want to change the processing instructions for this field. The Text to Display field shows how this field displays in the template. You can choose to enter descriptive text to enable you to understand each field better when reading the template, or you can enter abbreviated text entries that are less intrusive to the look and feel of the template. When you have completed the dialog options, click OK. This inserts the form fields in the template.

By default, the beginning for-each form field displays the text "F" and is inserted at the beginning of the selected template section. At the end of the selection, an "E" form field is inserted to denote the end of the repeating group. To create a group around an existing block of text or elements in a template:. 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. In the BI Publisher Properties dialog, enter the fields to define the group as described in Section 5. 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.

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 shows the Pivot Table dialog. Figure The Pivot Table Dialog. Drag and drop the elements from the Data pane to the Layout pane to build the pivot table structure. Use the Properties pane to select Aggregation. You can choose Sum, Count, or Average. Then choose a number Format , as shown in Figure By default subtotals for rows and columns are displayed.

You can choose not to display the subtotals by setting the properties to False. Figure Properties for Setting Totals and Subtotals. Click Preview to see how the pivot table is displayed before you insert it into the template. Click OK to insert the pivot table into the template. Figure shows how the pivot table is displayed in the template. Figure Example of a Pivot Table in a Template.

Figure A Pivot Table at Runtime. This section describes the code inserted by the pivot table builder. When the Template Builder inserts the pivot table, it inserts a BI Publisher command of the following structure:. Table Pivot Table Command Parameters. This is the XML data element that contains the data elements to include in the pivot table. If the pivot table is inside a repeating group, this field must be manually edited to achieve the expected results.

See the table following this section. This parameter defines the XML elements for row headers. The first attribute is the sort element. If not specified, the row header element is used as the sort element. Note that the sort element can be any element in the data set, and does not have to be included in the pivot table. In the preceding example, emp-lastname and emp-firstname do not have to be elements included in the pivot table.

This parameter defines the XML elements for column headers. If not specified, the column header element is used as the sort element. In the example, the first column header is "ProductsBrand". It is sorted by "ProductsBrand"; the order is ascending, and type is text. The second column header is "PeriodYear".

It is sorted by "PeriodYear"; the order is ascending, and type is text. The full data set includes four values for ProductsBrand, four values for Region, and two values for PeriodYear to be displayed in the pivot table.

Each XPath in the "cttree. The information in the table is to help you understand how BI Publisher constructs the pivot table. The generated cttree. The first level column "ProductsBrand". There are four distinct values. They are shown in the label H element. The column header label.

The first level column "PeriodYear", which is the second group-by key. There are two distinct values "" and "". The first level row "Region". There are four distinct values, they are shown in the label H element. This is the row header label for "Region".

This contains elements from combining R1 and C1. There are 4 distinct values for "Region", and four distinct values for "ProductsBrand". This contains elements from combining R1, C1 and C2. There are 4 distinct values for "Region", and four distinct values for "ProductsBrand", and two distinct values of "PeriodYear". This contains elements from combining R1 "Region" and R2 "District". Because the list of values in R2 has dependency on R1, the number of entries is not just a simple multiplication.

The R2 position number within R1. This is used to check if it is the last row, and draw table border accordingly. This contains elements from combining R1, R2, C1 and C2, which gives the finest level of details. When you create a pivot table inside a repeating group you must manually edit the pivot table code so that the elements included in the pivot table respect the grouping context.

The edit to the code depends on how you grouped the data. In the crosstab command, update the "data-element" component to "current-group ". Select and right-click the inserted pivot table. Replace the data-element component with "current-group ". This applies the XDO crosstab command only across the current group to return the expected values in the pivot table. Procedure When the Data is Already Grouped.

If the data input to the Template Builder is already grouped, then you must insert the appropriate XPath for the data-element component to ensure that the pivot table only includes the elements in the current group.

When you insert the pivot table within the repeating group, the code appears as. The edited code is:. A conditional region is an area that is surrounded by a conditional statement. If the statement tests true, the area is displayed in the report; if the condition tests false, the area is suppressed from the report. For example, the data contains sales information. The report contains a table that displays sales by industry.

You want this table in the report to display information for industries with sales amounts lower than , Using the insert conditional region functionality, you can select the region that contains the sales table and insert the condition that the sales element must be less than , Select the region that you want to apply the condition to. For example, if you want to display a table only for a certain condition, then select the region that contains the table.

Note that the region must be inside a loop. A new spreadsheet should generate with the data from the sample XML file neatly formatted in columns. For a simple, single page spreadsheet without data groupings, we can sum the Tax column by just putting a SUM function in a cell. Notice the range for the SUM function contains only the one cell in the repeating row. This address is Named Space Template Builder uses to map data to the spreadsheet. Click on the Preview icon to see if your formula worked correctly.

In cell below the repeating row, add the field to be summed. Change the field reference to a sum. The data manipulation tools within the BI Publisher Excel template are not robust nor extensive. The basic functionality will take a flat XML file and group the data. However, the ability for meaningful analysis with Excel features such as aggregate functions is limited. For this example, we are using the same payroll tax data, but this time we want to group and sum the report by taxing state.

To create grouped data with aggregate functions, the XML data must be in a hierarchical structure within those State groups. Using the same data, we created a new XML file with a parent-child relationship.

Highlight a block of cells high enough for the structure of each group. Test out this much by clicking on the Excel icon before continuing. Add column headings and formatting for numbers and dates. Ensure that the Child Row data structure is selected. We need to add the total field to sum within the group instead of sum for then entire data. Add the field to be summed below your detail data using the field control. This forces the template to sum only within that group instead for the entire data set.

A feature of the Excel template is to split groups into multiple worksheets: one group per sheet. Using the insert conditional region functionality, you can select the region that contains the sales table and insert the condition that the sales element must be less than , Select the region that you want to apply the condition to.

For example, if you want to display a table only for a certain condition, select the region that contains the table. Note that the region must be inside a loop. Data Field — Select the field to test for the condition. Select the value to meet the condition. Note that you may enter an integer, enter text, or select another data element to define a comparison based on the incoming values.

Click OK. The form fields containing the conditional logic are inserted around the region. The beginning form field will display the text "C" and the form field closing the region will display the text "EC". To edit the conditional region, double-click the inserted form field to launch the dialog for editing; or, right-click the form field and select BI Publisher, then Properties.

Using the Conditional Format feature you can insert simple conditional formats to apply to table rows or cells. The dialog provides several common options that you can select and the Template Builder inserts the code automatically. The Conditional Format dialog supports two conditions per field. Important: The Conditional Format dialog cannot be used inside of pivot tables.

You must insert the conditional formatting logic directly to the appropriate form fields. Place the cursor in the table cell of the data element for which you want to define the condition. Apply to Entire Table Row — If you want the format applied to the entire table row, not just the cell of the selected element, select this box. Click Format to define the format you want to apply when the condition is met. Options are background color, font color, and font style regular, bold, italic, bold italic.

Select the box and format of each option you want to apply. After you select the format, the Preview region will display the format chosen. Select OK. The conditional format field will be inserted as a form field with the display text "C". To edit the conditional format, double-click the inserted form field to launch the dialog for editing; or, right-click the form field and select BI Publisher, then Properties.

Note: If you have not already done so, you must load sample data to the Template Builder to preview the report. From the Preview group select the output format. If you have not yet saved your template as an RTF file, you will be prompted to do so. You must have Adobe Acrobat Reader version 5. To use this option, you must have Microsoft Excel or later. If you have Excel , this option generates the document in.

Embedded images such as charts and logos are not supported in this output type. If you do not have Microsoft Excel or later, use this option. This section describes additional tools provided with the Template Builder to help you validate and edit your template. This section includes:. Once you have inserted a data field see Inserting a Field you can view or edit the field properties in the BI Publisher Properties dialog.

Data Field — Select the data field from the list of available fields from the loaded data source. Text to Display — Enter the display text for the form field in the template.

This text will be replaced at runtime by the value in the data. Type — Select the type of data. The selection in this field will determine the format options. Format — For any data type except Regular Text, you can select from several number or date display formatting masks or enter your own.

Force LTR — Force Left-to-Right Use this check box when you are publishing the template in a language that prints the characters from right to left, such as Arabic or Hebrew. Function — This feature enables you to perform aggregation functions Sum, Average, Count, Minimum, Maximum on data fields.

For example, if you select sum for a data field, the field will show the sum of all occurring values for this data field depending on the scope see below. See also Inserting a Field for information on aggregation functions.

Group Item — Indicates that the data field is inside a group. If you choose to perform a function on the field, only the occurrences of the field within the current group will be included in the aggregation.

Normal — Indicates that the field is not inside a group. Aggregation functions will be performed on all occurrences of the field in the data. The Advanced tab displays the underlying code. If the code pattern within the form field is not recognized for example, because you added commands manually to the field , the BI Publisher Properties dialog will display this tab only.

Use this tab to edit or add code to the form field manually. Select OK to update the template. The following figure shows the Advanced tab:. You can also use this dialog to set the data type and number format. The Template Builder provides a validation tool to check the template for incorrect use of BI Publisher commands and unsupported elements in the RTF file.

If there are no validation errors, No Error found will be returned. If an error is found, the error will be displayed. You can use the Field Browser to help locate the error. The field browser dialog provides a fast way to review and update the BI Publisher instructions hidden in the Microsoft Word form fields.

This dialog is particularly useful to understand and modify existing templates. The Field Browser dialog shows a table with the display text of the form field in the Text column and the underlying code instructions in the second Code column. When you select a specific row in the dialog, the matching form field will be selected in the Microsoft Word document. If you select some part of the text before opening the Field Browser, the dialog only shows the fields in your selection.

If no text is selected, the field browser will show all fields in the document. The Template Builder provides an accessibility checker to check the template for features to enhance the accessibility of the report for report consumers who may need assistive technologies to view the report. The tool will generate a report indicating areas of a template that do not include the following accessibility features:.

In some cases the accessibility checker will be unable to determine if the accessibility feature is present and will generate a warning. The report designer should then verify that the accessibility features are present. For information on how to add these features to your template, see Designing Accessible Reports. If you used the Open Template dialog to connect to BI Publisher, and load your data to the Template Builder, or if you downloaded an existing template from the BI Publisher catalog, you can upload the new or updated layout back to the report definition on the server.

See Working in Connected Mode. If you downloaded an existing template and wish to upload the modifications to the template, select Upload Template from the Oracle BI Publisher menu. If this is a new template for the report definition, use the Upload Template As option to upload the layout to the report definition on the server. Also use this option to upload modifications to an existing template under a different name.

Use the first option if the translated template requires a different layout from the original template.

For detailed information on translation concepts and support, see the section Translating Reports and Catalog Objects. To use the Template Builder translation tools to create your templates for translations, see the following topics in this section:. This menu item allows you to create a standard XLIFF translation file containing the boilerplate text from your template. XLIFF is a standard file format that is understood by many translation software packages.

A "translatable string" is any text in the template that is intended for display in the published report, such as table headers and field labels. Text supplied at runtime from the data is not translatable, nor is any text that you supply in the Microsoft Word form fields. You will be prompted to save the extract file as an XML file type. Enter a name for the extract file and save to the desired location.

If you wish to translate the template yourself, open the. For a detailed description of working with the BI Publisher generated. When done, you can Preview the translation. Then upload the file to the BI Publisher report definition. Because BI Publisher enables you to extract the boilerplate text strings from your template into an XLIFF file that can be translated and then applied at runtime, if your reports for additional languages only require the translation of these text strings, then you only need to supply translated XLIFF files to accompany your base template.

However, you would localize a template when the requirements for the report in the specific language go beyond the simple translation of the text in the layout. This will invoke a warning that Localizing your template will overwrite the template. Locate the appropriate file and click Open. Upload the template file to the appropriate report definition in the BI Publisher catalog. Select the appropriate locale in the upload dialog. The tree view showing the data source can show either the correct XML tag names of the data source or they can show a slightly modified version that is easier to read.

The Preview Options tab allows you to specify options that influence the Preview functionality of the Template Builder. Use the Build Options tab to specify options that influence how the Template Builder generates tables and forms.

The configuration file must be named xdoconfig. Alternatively, you can use the file name xdo. The configuration file allows you to:. The Online group of commands enable you to initiate interaction with the BI Publisher application. For more information about working with the online commands, see: Working in Connected Mode.

Note: You must log in directly to the BI Publisher server. The data group of commands enables you to load a saved sample data file or sample schema to the Template Builder. You must load data to use most of the Template Builder functionality. Use the Insert group to insert the layout components to your template.

For more information see Inserting Components to the Template. Note: As a beginner, you should use Insert Fields only for data fields that are unique — none repeating - in your document. See Inserting a Table for additional information on how to insert repetitive fields. Note: XML documents often contain a large number of fields in a deeply nested hierarchy. For example, an Oracle Purchasing purchase order contains purchase order lines, which contain shipments, which contain distributions.