By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Enterprise Software.
Tags: XML Publisher. Bilana July 25, 0 Comments. Hi Everybody, Can anyone help me regarding the following fact. Many thanks. Lakshminarayanan P Posted February 11, 0 Comments. Thanks, Narayanan.
Suresh Vaishya Posted February 13, 0 Comments. Subodh Deshpande Posted November 17, 0 Comments. I can't figure out exactly what is happening but with the section break, I end up with extra, repeated pages in my output.
I cannot find anything online about how to use section breaks in BI or if there are problems with their usage. Sample RTF. Sample XML. Then I removed the at the end of the loop. Now the section breaks seem to work as expected, and a section break is automatically added between each loop.
Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 1 year, 11 months ago. Active 1 year, 11 months ago. Viewed 2k times. Now use the Help Text box to enter the XML element name that holds the index for the drop-down field values.
Using the check box and drop-down list features, you can create a report to display population data with check boxes to demonstrate figures that reach a certain limit. An example is shown in Figure The template to create this report is shown in Figure and the fields have the values shown in Table Establishes the condition for the check box. If the value for the population element is greater than 1,,, the check box is displayed as checked. The drop-down form field for the continentIndex element.
See the preceding description for its contents. At runtime, the value of the XML element is replaced with the value it is cross-referenced to in the drop-down form field. If your report includes a row header that spans multiple rows, for example in a group-left construction as shown in Figure , you can specify that the contents in the initial cell repeats on the next page. In the preceding example, if the report breaks across the Dec group, you would most likely prefer that the cell contents "Dec" repeat on the next page.
To specify that the cell contents repeat, insert the following code in a form field in the table data cell that is to repeat:. For information about using the Template Builder to insert conditional regions and conditional formatting, see Section 5. Conditional formatting occurs when a formatting element appears only when a certain condition is met. BI Publisher supports the usage of simple "if" statements, as well as more complex "choose" expressions.
For example, you can specify that if reported numbers reach a certain threshold, they are displayed shaded in red. Or, you can use this feature to hide table columns or rows depending on the incoming XML data. Use an if statement to define a simple condition; for example, if a data field is a specific value. This example is displayed in Figure Note that you can insert the syntax in form fields, or directly into the template. To achieve this requirement, use the BI Publisher context command to place the if statement into the inline sequence rather than into the block the default placement.
For more information on context commands, see Section 4. To specify that the if statement should be inserted into the inline sequence, enter the following:.
If you use inlines with if syntax, any other if syntax inside the statement must use the context command inline. BI Publisher supports the common programming construct "if-then-else". This is extremely useful when you must test a condition and conditionally show a result. If the value is greater than , show the word "Higher"; if it is less than , show the word "Lower"; if it is equal to , show "Equal":.
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 are rendered. This is a very powerful feature of the RTF template. In the template, however, you can actually use visual widgets in the conditional flow in the following example, a table.
Otherwise, the row of data renders with no shading. In Figure , the form field default text is displayed.
The form field help text entries are shown in Table You can conditionally show and hide columns of data in the 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. Notice the "type" attribute associated with the items element.
For the "public" version of the list, the quantity column should not be shown in the output, but you want to develop only one template for both versions based on the list type. Figure contains a simple template that conditionally shows or hides the quantity column.
Table shows the entries made in the template that is shown in Figure Note that this syntax uses an XPath expression to navigate back to the "items" level of the XML to test the attribute. For more information about using XPath in templates, see Section 4. The conditional column syntax is the "if" statement syntax with the addition of the column clause. It is the column clause that instructs BI Publisher to hide or show the column based on the outcome of the if statement.
If you did not include the column the data would not display in the report as a result of the if statement, but the column still would because you had drawn it in the template. The column clause is an example of a context command. BI Publisher allows you to specify formatting conditions as the row-level of a table. Examples of row-level formatting are:. These tags are demonstrated in the sample template that is shown in Figure Table describes the fields from the template in Figure This example demonstrates how to set a background color on every other row.
The template to create this effect is shown in Figure Table shows values of the form fields from the template in Figure In Table , note the "format;" field. It contains an if statement with a "row" context row. This sets the context of the if statement to apply to the current row. This setting results in the output that is shown in Figure For more information about context commands, see Section 4. The following example demonstrates how to conditionally highlight a cell based on a value in the XML file.
The template lists the accounts and their credit and debit values. The final report will highlight in red any cell whose value is greater than The template for this is shown in figure Figure :. The placeholder for the debit element. The "if" statement is testing if the debit value is greater than If it is, then the next lines are invoked.
Notice that the example embeds native XSL code inside the "if" statement. The xdofo:ctx component is an BI Publisher feature that allows you to adjust XSL attributes at any level in the template.
In this case, the background color attribute is changed to red. To change the color attribute, you can use either the standard HTML names for example, red, white, green or you can use the hexadecimal color definition for example, FFFFF. 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. Page totaling is performed in the PDF-formatting layer.
This page totaling function works only if the source XML code 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:. Oracle-number-format is the format you want to use to for the display, using the Oracle format mask for example: 'C9GD00'.
For the list of Oracle format mask symbols, see Section 4. 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. Figure shows the table to insert in the template to hold the values. Table shows the form field entries made in the template whose table is shown in Figure 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. This field is the placeholder for the credit element from the XML file. The variable defined to hold the total for the credit element is ct. 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:. Therefore to complete the example, place the following at the bottom of the template page, or in the footer:. 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. The totaling for the brought forward and carried forward fields is performed in the PDF-formatting layer. 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. The sample template that is shown in Figure creates the invoice table and declares a placeholder that holds the page total.
Table shows the fields in the template that is shown in Figure To display the brought forward total at the top of each page except the first , use the following syntax:. This is an optional property that takes one of the following values:. 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.
It has the following properties:. In this case, "InvAmt". This property is mandatory. This property is optional, but if you want to supply a format mask, you must use the Oracle format mask. 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. 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. 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:.
The display-condition is set to exceptlast so that the carried forward total is displayed on every page except the last page.
The show-carry-forward element is used to show the carried forward value. It has the same properties as brought-carried-forward, described above. The display-condition logic computation depends on these commands to function correctly. The variable functionality see Section 4. This example assumes the following XML structure:. You can use this XML code to create a report that contains running totals as shown in Figure To create the Running Total field, define a variable to track the total and initialize it to 0.
The template is shown in Figure The values for the form fields in the template that is shown in Figure are described in Table Retrieves the RTotalVar value for display. You can sort a group by any element within the group. Insert the following syntax within the group tags:. If the order is not specified, by default, the sort order is ascending.
If the data type is not specified, the type is assumed to be text. For example, to sort a data set by an element named SALARY so that the highest salaries appear first, enter the following:.
When you are sorting within a for-each group, enter the sort statement after the for-each statement. To sort a group by multiple fields, just enter additional sort statements in the appropriate order.
For example, to sort by Supplier and then by Invoice Number, enter the following. In the report layout, you may want to specify a different behavior depending on the presence of the element and its value.
The following examples show how to check for each of these conditions using an "if" statement. The syntax can also be used in other conditional formatting constructs. With this feature, the template does not have to follow the hierarchy of the source XML file.
You are therefore no longer limited by the structure of the data source. To demonstrate the for-each-group standard, the following XML data sample of a CD catalog listing is regrouped in a template:.
Using the regrouping syntax, you can create a report of this data that groups the CDs by country and then by year. You are not limited by the data structure presented. You can then refer to the elements of the group to display the values desired. At runtime, BI Publisher loops through the occurrences of the new groupings, displaying the fields that you defined in the template. This syntax is a simplification of the XSL for-each-group syntax.
If you choose not to use the simplified syntax above, you can use the XSL syntax as shown below. The XSL syntax can only be used within a form field of the template.
Table shows the BI Publisher syntax entries made in the form fields of the template that is shown in Figure This template produces the report that is shown in Figure when merged with the XML file. Regrouping by an expression allows you to apply a function or command to a data element, and then group the data by the returned result. To demonstrate this feature, an XML data sample that simply contains average temperatures per month is used as input to a template that calculates the number of months having an average temperature within a certain range.
You want to display this data in a format showing temperature ranges and a count of the months that have an average temperature to satisfy those ranges, as shown in Figure You can then display a count of the members of each grouping, which is the number of months having an average temperature that falls within each range. Figure shows the template to create the report that is shown in Figure Table shows the form field entries made in the template that is shown in Figure The floor function is an XSL function that returns the highest integer that is not greater than the argument for example, 1.
This generates the following values from the XML data: 1, 1, 1, 2, 3, 3, 3, 3, 2, 2, 1, and 0. The expression concatenates the value of the current group times 10 with the value of the current group times 10 plus This allows you to create many new features in the templates that require updatable variables.
In a multi-threaded environment there may be many transformations occurring at the same time, therefore the variable must be assigned to a single transformation. You can pass runtime parameter values into the template. These can then be referenced throughout the template to support many functions.
For example, you can filter data in the template, use a value in a conditional formatting block, or pass property values such as security settings into the final document. For BI Publisher Enterprise users, all name-value parameter pairs are passed to the template.
You must register the parameters that you want to utilize in the template using the syntax described below. The syntax must be declared in the Help Text field of a form field. The form field can be placed anywhere in the template. This example illustrates how to declare a parameter in the template that filters the data based on the value of the parameter. Figure shows a template that accepts a parameter value to limit the invoices displayed in the final document based on the parameter value.
Table describes the fields for defining parameters as shown in the template in Figure If you pass in a parameter value of 1,, then the report that is shown in Figure is produced. The properties set in the template are resolved at runtime by the BI Publisher engine. You can either hard code the values in the template or embed the values in the incoming XML data.
Embedding the properties in the template avoids the use of the configuration file. For example, if you use a nonstandard font in the template, then rather than specify the font location in the configuration file, you can embed the font property inside the template.
To add a BI Publisher property to a template, use the Microsoft Word Properties dialog available from the File menu , and enter the following information:. Name - Enter the BI Publisher property name prefixed with "xdo-". Value - Enter the property value. This font is not available on the server; therefore you must tell BI Publisher where to find the font at runtime. You tell BI Publisher where to find the font by setting the "font" property.
Note that if the template is deployed in multiple locations, then you must ensure that the path is valid for each location. The XML data is as follows:. In the Properties dialog set two properties: pdf-security to set the security feature as enabled or not, and pdf-open-password to set the password. Enter the following in the Properties dialog:. To avoid this potential security risk, you can use a template parameter value that is generated and passed into the template at runtime.
For more information about template parameters, see Section 4. 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:. Figure shows a sample template.
Table shows the values of the form fields from the template in Figure The page numbers restart, and if header or footer information is derived from the data, it is reset as well. To prevent this from happening, edit the RTF template. 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. From this XML code, a report is generated that shows each industry and totals the sales by year as shown in Figure Figure shows the template to generate the report that is shown in Figure The form fields in the template that is shown in Figure have the values that are described in Table Defines the first column as a header that should repeat if the table breaks across pages.
For more information about this syntax, see Section 4. Uses the regrouping syntax see Section 4. 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.
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:. 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. 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 width calculation is illustrated in Table 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:. A template is required to display test score ranges for school exams. Logically, you want the report to be arranged as shown in Table 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. In this case there are five columns: , , , , and Using the dynamic column tags in form fields, set up the table in two columns as shown in Figure The first column, "Test Score" is static. The second column, "Column Header and Splitting" is the dynamic column. At runtime this column is split according to the data, and the header for each column is appropriately populated.
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. Wrapping of the data occurs if required. 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.
This section provides details for formatting numbers, dates, and currencies. It contains the following topics:. You can also use the native XSL format-number function to format numbers. For information, see Section 4. Use only one of these methods. If the number format mask is specified using both methods, then the data is formatted twice, causing unexpected behavior.
The group separator and the number separator are set at runtime based on the template locale. If you are working in a locale other than en-US, or the templates require translation, use the Oracle format masks. To use the Oracle format mask or the Microsoft format mask, the numbers in the data source must be in a raw format, with no formatting applied for example: If the number has been formatted for European countries for example: 1.
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: If you are working in a locale other than en-US, or the templates require translation, then use the Oracle format masks. The Microsoft format masks can generate unexpected results in templates run in different locale settings.
To format numeric values, use Microsoft Word's field formatting features available from the Text Form Field Options dialog. Determines the position of the decimal separator. The decimal separator symbol used is determined at runtime based on template locale.
Determines the placement of the grouping separator. The grouping separator symbol used is determined at runtime based on template locale. Subpattern boundary: A pattern contains a positive and negative subpattern, for example, " , 0.
Each subpattern has a prefix, numeric part, and suffix. The negative subpattern is optional. If absent, the positive subpattern prefixed with the localized minus sign "-" in most locales is used as the negative subpattern. That is, "0. If there is an explicit negative subpattern, it serves only to specify the negative prefix and suffix. The number of digits, minimal digits, and other characteristics are all the same as the positive pattern.
That means that " , 0. Figure shows an example Form Field Help Text dialog entry for the data element "empno". Table lists the supported Oracle number format mask symbols and their definitions. Each explicitly set 0 appears, if no other number occupies the position. Example: Format mask: 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. Determines the placement of the decimal separator. For example: Format mask: 9GD99 Data: Determines the placement of the grouping thousands separator. 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 is formatted twice, which causes unexpected behavior.
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:. The data after the "T" is optional, therefore the following date: can be formatted using either date formatting option. The year without the century. If the year without the century is less than 10, the year is displayed with a leading zero. The period or era. This pattern is ignored if the date to be formatted does not have an associated period or era string.
Displays the time zone offset for the system's current time zone in whole hours only. This element can be used for formatting only. Select the Add Help Text The detailed usage of format mask, calendar and time zone is described below.
Excel output. If you use these in an RTF template and generate Excel output, the date with any of these formats will not be viewable. Some combinations of these format masks may result in an error when the Excel Fractional seconds.
Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then simply enter the 4-digit year. Daylight savings information.
The TZD value is an abbreviated time zone string with daylight savings information. It must correspond to the region specified in TZR. Time zone region information. The value must be one of the time zone regions supported in the database.
Week of year where week 1 starts on the first day of the year and continues to the seventh day of the year. If you do not want to specify a format mask with either the MS method or the Oracle method, you can omit the mask definition and use the default format mask.
To use the default option using the Microsoft method, set the Type to Date, but leave the Date format field blank in the Text Form Field Options dialog. To use the default option using the Oracle method, do not supply a mask definition to the "format-date" function call, for example:.
When you use one of these masks, the output generated depends on the locale that is associated with the report. Table lists the abstract format masks and the sample output that would be generated for the US locale. BI Publisher enables you to define specific currency format masks to apply to the published data at runtime. Set up the currency formats in BI Publisher's runtime configuration properties. The currency formats can be defined at the system level or at the report level.
When you set up the currency format property, you define the format to be used for a specified currency, using the International Standards Organization ISO currency code. A sample is shown in Figure See Section 2. Enter the format-currency command in the RTF template to apply the format to the field at runtime, as described in Section 4. CurrencyCode can either be set to a static value or it can be set dynamically. If the value is static for the report, then enter the ISO three-letter currency code in single-quotes, for example, 'USD'.
Note that an element that contains the currency code must be present in the data. When set to 'true', the currency symbol is displayed in the report based on the value for CurrencyCode. If you do not want the currency symbol to be displayed, then you can either enter 'false' or simply do not specify the parameter.
Table provides an example that assumes you have set up the various currency formats in the report properties. In this example, you need not set the currency code dynamically. You have the following elements in the XML data:.
0コメント