| Oracle® Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1) Part Number E22254-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1) Part Number E22254-02 |
|
|
PDF · Mobi · ePub |
This chapter describes creating report templates for BI Publisher in Microsoft Excel.
This chapter includes the following sections:
Section 6.6, "Preprocessing the Data Using an XSL Transformation (XSLT) File"
Section 6.7, "Using the Template Viewer to Debug a Template"
An Excel template is a report layout designed in Microsoft Excel for retrieving and formatting enterprise reporting data in Excel. Excel templates provide a set of special features for mapping data to worksheets and for performing additional processing to control how the data is output to Excel workbooks.
With Excel templates you can:
Define the structure for the data in Excel output
Split hierarchical data across multiple sheets and dynamically name the sheets
Create sheets of data that have master-detail relationships
Use native XSL functions in the data to manipulate it prior to rendering
Use native Excel functionality
The following are limitations of Excel templates:
For reports that split the data into multiple sheets, images are not supported. If the template sheet includes images, when the data is split into multiple sheets, the images are displayed only on the first sheet.
There is no tool to facilitate the markup of the template with BI Publisher tags; all tags must be manually coded. Some features require the use of XSL and XSL Transformation (XSLT) specifications
Following are prerequisites for designing Excel templates:
Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
To use some of the advanced features, the report designer requires knowledge of XSL and XSLT.
The report data model has been created.
BI Publisher provides a downloadable add-in to Excel that enables you to preview a template with sample data. This facilitates design by enabling you to test and edit a template without having to upload it to the BI Publisher catalog first.
The Template Builder for Excel is installed automatically when you install the Template Builder for Word. The tools can be downloaded from the Home page of Oracle Business Intelligence Publisher or Oracle Business Intelligence Enterprise Edition, as follows:
Under the Get Started region, click Download BI Publisher Tools.
Similar to RTF template design, Excel template design follows the paradigm of mapping fields from the XML data to positions in the Excel worksheet. Excel templates make use of features of Excel in conjunction with special BI Publisher syntax to achieve this mapping. In addition to direct mapping of data elements, Excel templates also utilize a special sheet (the XDO_METADATA sheet) to specify and map more complex formatting instructions.
Excel templates use named cells and groups of cells to enable BI Publisher to insert data elements. Cells are named using BI Publisher syntax to establish the mapping back to the XML data. The cell names are also used to establish a mapping within the template between the named cell and calculations and formatting instructions that are defined on the XDO_METADATA sheet.
The template content and layout must correspond to the content and hierarchy of the XML data file used as input to the report. Each group of repeating elements in the template must correspond to a parent-child relationship in the XML file. If the data is not structured to match the desired layout in Excel it is possible to regroup the data using XSLT preprocessing or the grouping functions. However, for the best performance and least complexity it is recommended that the data model be designed with the report layout in mind.
Note:
See Section 6.6, "Preprocessing the Data Using an XSL Transformation (XSLT) File" and Section 6.5.3, "Grouping Functions" for more information about these options.
The Excel defined names feature is used to identify data fields and repeating elements. A defined name in Excel is a name that represents a cell, range of cells, formula, or constant value.
Tip:
To learn more about defined names and their usage in Microsoft Excel 2007, see the Microsoft help topic: "Define and use names in formulas."
The defined names used in the Excel template must use the syntax described in this chapter, as well as follow the Microsoft guidelines described in the Microsoft Excel help document. Note that BI Publisher defined names are within the scope of the template sheet.
The BI Publisher defined names are Excel defined names identified by the prefix "XDO_". Marking up the placeholders in the template files creates the connection between the position of the placeholders in the template and the XML data elements, and also maintains the ability to dynamically grow data ranges in the output reports, so that these data ranges can be referenced by other formula calculations, charts, and macros.
You can use the XDO_ defined names in Excel native formulas as long as the defined names are used in a simple table. When a report is generated, BI Publisher automatically adjusts the region ranges for those named regions so that the formulas calculate correctly.
However, if you create nested groups in the template, then the cells generated in the final report within the grouping can no longer be properly associated to the correct name. In this case, the use of XDO_ defined names with native Excel functions cannot be supported.
Each Excel template requires a sheet within the template workbook called "XDO_METADATA". Use this sheet to identify the template to BI Publisher as an Excel template. This sheet is also used to specify calculations and processing instructions to perform on fields or groups in the template. BI Publisher provides a set of functions to provide specific report features. Other formatting and calculations can be expressed in XSLT.
It is recommended that you hide the XDO_METADATA sheet before you upload the completed template to the BI Publisher catalog. This specification prevents report consumers from seeing it in the final report output.
Note:
For more information see Section 6.3.5.1, "Format of the XDO_METADATA Sheet" and Section 6.5, "Defining BI Publisher Functions."
This section demonstrates the concepts of Excel templates by describing the steps to create a simple Excel template and testing it with the Excel Template Builder. This procedure follows these steps:
You need sample data in order to know the field names and the hierarchical relationships to properly mark up the template. For information on saving sample data from the report data model, see the "Testing Data Models and Generating Sample Data" section in Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.
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 menu, then click Export, then click Data. You are prompted to save the XML file.
Save the file to a local directory.
The sample data for this example is a list of employees by department. Note that employees are grouped and listed under the department.
<?xml version="1.0" encoding="UTF-8"?>
<! - Generated by Oracle BI Publisher 11.1.1.4.0 - >
<DATA>
<DEPT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<EMPS>
<EMPLOYEE_ID>201</EMPLOYEE_ID>
<EMP_NAME>Michael Hartstein</EMP_NAME>
<EMAIL>MHARTSTE</EMAIL>
<PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
<HIRE_DATE>1996-02-17T00:00:00.000+00:00</HIRE_DATE>
<SALARY>13000</SALARY>
</EMPS>
<EMPS>
<EMPLOYEE_ID>202</EMPLOYEE_ID>
<EMP_NAME>Pat Fay</EMP_NAME>
<EMAIL>PFAY</EMAIL>
<PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
<HIRE_DATE>1997-08-17T00:00:00.000+00:00</HIRE_DATE>
<SALARY>6000</SALARY>
</EMPS>
</DEPT>
<DEPT>
...
...
</DEPT>
</DATA>
Note:
It is recommended that you install the Template Builder for Excel. For information on downloading the tool, see Section 6.1.5, "Desktop Tools for Excel Templates."
The Template Builder installation includes a set of sample Excel templates, including a sample blank Excel template called BlankExcelTemplate.xls. This template file contains a blank Sheet1 and the XDO_METADATA sheet. It is recommended that you either start with this provided template or copy the XDO_METADATA sheet into the Excel workbook.
Tip:
If you are building a new template from an existing template, then be sure to clear any existing defined names in the template sheet. See Section 6.3.4.4, "Deleting Defined Names and Groups."
To open the BlankExcelTemplate.xls file:
From a Windows desktop, click Start, then Programs, then Oracle BI Publisher Desktop, then Samples, then Excel.
In the Excel Templates sample folder, double-click BlankExcelTemplate.xls to open it.
Save the file as the selected name in the Microsoft Excel 97-2003 Workbook format (*.xls).
In Excel, determine how you want to render the data and create a sample design, as shown in Figure 6-1.
The design shows a department name and a row for each employee within the department. You can apply Excel formatting to the design, such as font style, shading, and alignment. Note that this layout includes a total field. The value for this field is not available in the data and requires a calculation.
To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:
Data elements: XDO_?element_name?
where
XDO_ is the required prefix and
?element_name? is either:
the XML tag name from the data delimited by "?"
a unique name that you use to map a derived value to the cell
For example: XDO_?EMPLOYEE_ID?
Data groups: XDO_GROUP_?group_name?
where
XDO_GROUP_ is the required prefix and
?group_name? is the XML tag name for the parent element in the XML data delimited by "?".
a unique name that you use to define a derived grouping logic
For example: XDO_GROUP_?DEPT?
Note that the question mark delimiter, the group_name, and the element_name are case sensitive.
To apply a defined name to a cell:
Click the cell in the Excel worksheet.
Click the Name box at the left end of the formula bar. The default name is displayed in the Name box. By default, all cells are named according to position, for example: A8.
In the Name box, enter the name using the XDO_ prefix and the tag name from the data. For example: XDO_?EMP_NAME?
Press Enter.
Figure 6-2 shows the defined name for the Employee Name field entered in the Name box.
Figure 6-2 Defined Name for the Employee Name Field Entered in the Name Box
Repeat for each of the following data fields: DEPARTMENT_NAME, EMPLOYEE_ID, EMAIL, PHONE_NUMBER, and SALARY.
Tip:
If you navigate out of the Name box without pressing Enter, then the name that you entered is not maintained.
You cannot edit the Name box while you are editing the cell contents.
The name cannot be more than 255 characters in length.
For the total salary field, a calculation is mapped to that cell. For now, name that cell XDO_?TOTAL_SALARY?. The calculation is added later.
After you have entered all the fields, you can review the names and make any corrections or edits using the Name Manager feature of Excel. Access the Name Manager from the Formulas tab in Excel, as shown in Figure 6-3.
After you have named all the cells for this example, the Name Manager dialog contains the entries as shown in Figure 6-4.
You can review all your entries and update any errors through this dialog.
A group is a set of data that repeats for each occurrence of a particular element. In the sample template design, there are two groups:
For each occurrence of the <EMPS> element, the employee's data (name, e-mail, telephone, salary) is displayed in the worksheet.
For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department are displayed.
In other words, the employees are "grouped" by department and each employee's data is "grouped" by the employee element. To achieve this in the final report, add grouping tags around the cells that are to repeat for each grouping element.
Note that the data must be structured according to the groups that you want to create in the template. The structure of the data for this example
<DATA>
<DEPT>
<EMPS>
establishes the grouping desired for the report.
To create groups in the template:
Highlight the cells that make up the group. In this example the cells are A8 - E8.
Click the Name box at the left end of the formula bar and enter the name using the XDO_GROUP_ prefix and the tag name for the group from the data. For example: XDO_GROUP_?EMPS?
Press Enter.
Figure 6-5 shows the XDO_GROUP_ defined name entered for the Employees group. Note that just the row of employee data is highlighted. Do not highlight the headers. Note also that the total cell XDO_?TOTAL_SALARY? is not highlighted.
Figure 6-5 The XDO_GROUP_ Defined Name Entered for the Employees Group
To define the department group, include the department name cell and all the employee fields beneath it (A5-E9) as shown in Figure 6-6.
Figure 6-6 The Department Name Cell and All Employee Fields
Enter the name for this group as: XDO_GROUP_?DEPT? to match the group in the data. Note that the XDO_?TOTAL_SALARY? cell is included in the department group to ensure it repeats at the department level.
To delete defined names or groups from your template, use the Name Manager. Access the Name Manager dialog from the Formulas tab in Excel. Select the item to delete and click Delete.
BI Publisher requires the presence of a sheet called "XDO_METADATA" to process the template. This sheet must follow the specifications defined here.
The XDO_METADATA sheet must have the format shown in Figure 6-7.
Figure 6-7 Format of the XDO_METADATA Sheet
The format consists of two sections: the header section and the data constraints section. Both sections are required. In the header section, all the entries in column A must be listed, but a value is required for only one: Template Type, as shown. The Data Constraints section does not require any content, but also must be present as shown.
This procedure describes how to set up the sheet for this sample Excel template to run. For the detailed description of the functionality provided by the XDO_METADATA sheet see Section 6.5, "Defining BI Publisher Functions"
If you copied the XDO_METADATA sheet in Step 2, then skip this section and proceed to Section 6.3.5.3, "Adding the Calculation for the XDO_?TOTAL_SALARY? Field."
To set up the hidden sheet:
Create a new sheet in the Excel workbook and name it "XDO_METADATA".
Create the header section by entering the following variable names in column A, one per row, starting with row 1:
Version
ARU-dbdrv
Extractor Version
Template Code
Template Type
Preprocess XSLT File
Last Modified Date
Last Modified By
Skip a row and enter "Data Constraints" in column A of row 10.
In the header region, for the variable "Template Type" enter the value: TYPE_EXCEL_TEMPLATE
Earlier in this procedure, you assigned the defined name XDO_?TOTAL_SALARY? to the cell that is to display the total salaries listed in the SALARY column. In this step, you add the calculation to the Data Constraints section of the XDO_METADATA sheet and map the calculation to the XDO_?TOTAL_SALARY? field.
To add the calculation to the field:
In the Data Constraints section, in Column A, enter the defined name of the cell: XDO_?TOTAL_SALARY?
In Column B enter the calculation as an XPATH function. To calculate the sum of the SALARY element for all employees in the group, enter the following: <?sum(.//SALARY)?>
The completed XDO_METADATA sheet is shown in Figure 6-8.
Figure 6-8 A Completed XDO_METADATA Sheet
If you have installed the Template Builder for Excel, then the BI Publisher tab is displayed on the ribbon menu as shown in Figure 6-9.
To preview the report using sample data:
Click Sample XML. You are prompted to select the sample data file.
Click Preview.
The sample data is applied to the template and the output document is opened in a new workbook. Figure 6-10 shows the preview of the template with the sample data.
Figure 6-10 A Preview of a Template with Sample Data
Excel cannot recognize canonical date format. If the date format in the XML data is in canonical format, that is, YYYY-MM-DDThh:mm:ss+HH:MM, you must apply a function to display it properly.
One option to display a date is to use the Excel REPLACE and SUBSTITUTE functions. This option retains the full date and timestamp. If you only require the date portion in the data (YYY-MM-DD), then another option is to use the DATEVALUE function. The following example shows how to use both options.
Example: Formatting a Canonical Date in Excel
Using the Employee by Department template and data from the first example, assume you want to add the HIRE_DATE element to the layout to and display the date as shown in Column E of Figure 6-11.
Figure 6-11 The Employee by Department Template Showing the Hire Date
To format the date:
Copy and paste a sample value for HIRE_DATE from the XML data into the cell that is to display the HIRE_DATE field. For example:
Copy and paste
1996-02-03T00:00:00.000-07:00
into the E8 cell.
Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data, as shown in Figure 6-12.
Figure 6-12 Assigning the Defined Name XDO_?HIRE_DATE?
If you do nothing else, the HIRE_DATE value is displayed as shown. To format the date as "3-Feb-96", you must apply a function to that field and display the results in a new field.
Insert a new Hire Date column. This is now column F, as shown in Figure 6-13.
Figure 6-13 The New Hire Date Column in Column F
In the new Hire Date cell (F8), enter one of the following Excel functions:
To retain the full date and timestamp, enter:
=--REPLACE(SUBSTITUTE(E8,"T"," "),LEN(E8)-6,6,"")
To retain only the date portion (YYY-MM-DD), enter:
DATEVALUE(LEFT(E8,10))
After you enter the function, it populates the F8 cell as shown in Figure 6-14.
Figure 6-14 Hire Date Cell (F8) Populated
Apply formatting to the cell.
Right-click the F8 cell. From the menu, select Format Cells. In the Format Cells dialog, select Date and the desired format, as shown in Figure 6-15.
Figure 6-15 Applying the Format for the Date in the Format Cells Dialog
The sample data in the F8 cell now displays as 3-Feb-96.
Hide the E column, so that report consumers do not see the canonical date that is converted.
Figure 6-16 shows column E hidden.
BI Publisher provides a set of functions to achieve additional reporting functionality. You define these functions in the Data Constraints region of the XDO_METADATA sheet.
The functions make use of Columns A, B, and C in the XDO_METADATA sheet as follows:
Use Column A to declare the function or to specify the defined name of the object to which to map the results of a calculation or XSL evaluation.
Use Column B to enter the special XDO-XSL syntax to describe how to control the data constraints for the XDO function, or the XSL syntax that describes the special constraint to apply to the XDO_ named elements.
Use Column C to specify additional instructions for a few functions.
The functions are described in the following three sections:
Table 6-1 lists functions that you can add to a template using the commands shown and a combination of BI Publisher syntax and XSL. A summary list of the commands is shown in Table 6-1. See the corresponding section for details on usage.
Table 6-1 Reporting Functions
| Function | Commands |
|---|---|
|
Section 6.5.1.1, "Splitting the Report into Multiple Sheets" |
XDO_SHEET_? with XDO_SHEET_NAME_? |
|
XDO_PARAM_?n? |
|
|
XDO_LINK_?link object name? |
|
|
XDO_SUBTEMPLATE_?n? |
|
|
XDO_EXT_?n? |
Note:
Images are not supported across multiple sheets. If the template sheet includes images, when the data is split into multiple sheets, the images are displayed only on the first sheet.
Use the set of commands to define the logic to split the report data into multiple sheets, as described in the following list:
Use XDO_SHEET_? to define the logic by which to split the data onto a new sheet.
Use XDO_SHEET_NAME_? to specify the naming convention for each sheet.
Table 6-2 describes the column entries.
Table 6-2 Column Entries
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_SHEET_? |
<?xsl_evaluation to split the data?> Example: <?.//DEPT?> |
n/a |
|
XDO_SHEET_NAME_? |
<?xsl_expression to name the sheet?> Example: <?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?> |
(Optional) <?original sheet name?> Example: <?Sheet3?> |
XDO_SHEET_? must refer to an existing high-level node in the XML data. The example <?.//DEPT?> creates a new sheet for each occurrence of <DEPT> in the data.
If the data is flat, then you cannot use this command unless you first preprocess the data to create the desired hierarchy. To preprocess the data, define the transformation in an XSLT file, then specify this file in the Preprocess XSLT File field of the header section of the XDO _METADATA sheet. For more information, see Section 6.6, "Preprocessing the Data Using an XSL Transformation (XSLT) File."
Use XDO_SHEET_NAME_? to define the name to apply to the sheets. In Column B enter the XSL expression to derive the new sheet name. The expression can reference a value for an element or attribute in the XML data, or you can use the string operation on those elements to define the final sheet name. This example:
<?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>
names each sheet using the value of DEPARTMENT_NAME concatenated with "-" and the count of employees in the DEPT group.
The original sheet name entry in Column C tells BI Publisher on which sheet to begin the specified sheet naming. If this parameter is not entered, BI Publisher applies the naming to the first sheet in the workbook that contains XDO_ names. You must enter this parameter if, for example, you have a report that contains summary data in the first two worksheets and the burst data should begin on Sheet3. In this case, you enter <?SHEET3?> in Column C.
Example: Splitting the data into multiple sheets
Using the employee data shown in the previous example. This example:
Creates a new worksheet for each department
Names each worksheet the name of the department with the number of employees in the department, for example: Sales-21.
To split the data into sheets:
Enter the defined names for each cell of employee data and create the group for the repeating employee data, as shown in Figure 6-17.
Figure 6-17 Defining Employee Data and the Group for Repeating Employee Data
Note:
Do not create the grouping around the department because the data is split by department.
Enter the values that are described in Table 6-3 in the Data Constraints section of the XDO_METADATA sheet.
The entries are shown in Figure 6-18.
Figure 6-19 shows the generated report. Each department data now displays on its own sheet, which shows the naming convention specified.
Figure 6-19 Example of a Generated Report
To define a parameter, use the XDO_PARAM_?n? function to declare the parameter, then use the $parameter_name syntax to pass a value to the parameter. A parameter must be defined in the data model.
To declare the parameter, use the command that is described in Table 6-4.
Table 6-4 Command for Declaring Parameters
| Column A Entry | Column B Entry |
|---|---|
|
XDO_PARAM_?n? where n is unique identifier for the parameter |
<?param@begin:parameter_name;parameter_value?> where parameter_name is the name of the parameter from the data model and parameter_value is the optional default value. For example: <?param@begin:Country;US?> |
To use the value of the parameter directly in a cell, refer to the parameter as $parameter_name in the definition for the XDO_ defined name, as described in Table 6-5.
Table 6-5 Using a Parameter Directly
| Column A Entry | Column B Entry |
|---|---|
|
XDO_PARAM_?parameter_name? For example: XDO_PARAM_?Country? |
<?$parameter_name?>. For example: <?$Country?> |
You can also refer to the parameter in other logic or calculations in the XDO_METADATA sheet using $parameter_name.
Example: Defining and passing a parameter
To declare and reference a parameter named Country:
In the template sheet, mark the cell with a defined name. In the figure below, the cell has been marked with the defined name XDO_?Country?
Figure 6-20 Cell Marked with the Defined Name XDO_?Country?
In the hidden sheet assign that cell the parameter value, as shown in Figure 6-21.
Figure 6-21 Assigning a Parameter Value to the XDO_?Country?Cell
Use the XDO_LINK_? command to define a hyperlink for any data cell, as described in Table 6-6.
Example: Defining a Link
Table 6-6 Defining a Link
| Column A Entry | Column B Entry |
|---|---|
|
XDO_LINK_?cell object name? For example: XDO_LINK_?INVOICE_NO? |
<xsl statement to build the dynamic URL> For example: <xsl:value-of select="concat('https://server.company.com/documents/invoice_print.show?c_rptno=',./INVOICE_NO)"/> |
Assume your company generates customer invoices. The invoices are stored in a central location accessible by a Web server and can be identified by the invoice number (INVOICE_NO).
To generate a report that creates a dynamic link to each invoice:
In the template sheet, assign the cell that is to display the INVOICE_NO the XDO defined name: XDO_?INVOICE_NO?, as shown in Figure 6-22.
Figure 6-22 Assigning the Defined Name XDO_?INVOICE_NO?
In the XDO_METADATA sheet, enter the appropriate values, as described in Table 6-7:
Table 6-7 Generating a Report with a Link
| Column A Entry | Column B Entry |
|---|---|
|
XDO_LINK_?INVOICE_NO? |
<xsl:value-of select="concat('https://server.company.com/documents/invoice_print.show?c_rptno=',./INVOICE_NO)"/> |
The entries in Excel are shown inFigure 6-23.
Figure 6-23 XDO_LINK?_?INVOICE_NO? Entries in Excel
The report output is displayed as shown in Figure 6-24. The logic that is defined in the XDO_METADATA sheet is applied to create a hyperlink for each INVOICE_NO entry.
Use these commands to declare XSL subtemplates that you can then call and reference in any of the XDO_ commands.
Note:
The Template Builder for Excel does not support preview for templates that import subtemplates.
To import the subtemplate, enter the command shown in Table 6-8.
Table 6-8 Importing a Subtemplate
| Column A Entry | Column B Entry |
|---|---|
|
XDO_SUBTEMPLATE_?n? where n is a unique identifier. For example: XDO_SUBTEMPLATE_?1? . |
<xsl:import href="xdoxsl:///path to subtemplate.xsb?"/> For example: <xsl:import href="xdoxsl:///Shared Folders/Financial Reports/SubTemplates/MySubTemplate.xsb?"/> |
To call the subtemplate, declare the cell name for which the results should be returned in Column A, then enter the call-template syntax with any other XSL processing to be performed. The commands are shown in Table 6-9.
Table 6-9 Calling a Subtemplate
| Column A Entry | Column B Entry |
|---|---|
|
XDO_?cell object name? |
<xsl:call-template name="template_name"> </xsl:call-template> |
For more information on XSL subtemplates and creating the subtemplate object in the catalog, see Chapter 14, "Designing XSL Subtemplates."
Example: Importing and Calling a Subtemplate
Assume you have the following subtemplate uploaded to the BI Publisher catalog as PaymentsSummary-SubTemplate.xsb. This subtemplate evaluates the value of a parameter named pPayType and based on the value, return a string that indicates the payment type:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
</xsl:template>
<xsl:template name="BRM_PAY_TYPES">
<xsl:param name="pPayType" select="string('ALL')"/>
<xsl:choose>
<xsl:when test="$pPayType = '0'">UNDEFINED</xsl:when>
<xsl:when test="$pPayType=string('10000')">PREPAID</xsl:when>
<xsl:when test="$pPayType=string('10001')">INVOICE</xsl:when>
<xsl:when test="$pPayType=string('10003')">CREDIT CARD</xsl:when>
<xsl:when test="$pPayType=string('10005')">DIRECT DEBIT</xsl:when>
<xsl:when test="$pPayType=string('10011')">CASH</xsl:when>
<xsl:when test="$pPayType=string('10012')">CHECK</xsl:when>
<xsl:when test="$pPayType=string('ALL')">ALL</xsl:when>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>
In the Excel template, you have defined a field with the XDO Defined Name XDO_?TYPE?, which is populated based on the string returned from code performed in the subtemplate, as shown in Figure 6-25.
Enter the commands shown in Table 6-10 in the Data Constraints region.
Table 6-10 Commands for Data Constraints Region
| Column A Entry | Column B Entry |
|---|---|
|
XDO_SUBTEMPLATE_?1? |
<xsl:import href="xdoxsl:///Shared Folders/Financial Reports/SubTemplates/PaymentsSummary-SubTemplate.xsb?"/> |
|
XDO_?TYPE? |
<xsl:call-template name="BRM_PAY_TYPES"> <xsl:with-param name="pPayType" select="string('10000')"/> </xsl:call-template> |
The XDO_SUBTEMPLATE_?1? function imports the subtemplate from the BI Publisher catalog.
The XDO_?TYPE? cell entry maps the results of the subtemplate processing entered in Column B.
You can include the reference to a Java extension library in the template and then call methods from this library to perform processing in the template. Use the command shown in Table 6-11 to reference the Java extension libraries.
Table 6-11 Referencing Java Extension Libraries
| Column A Entry | Column B Entry |
|---|---|
|
XDO_EXT_?n? where n is a unique identifier. Example: XDO_EXT?1? |
<?namespace:xmlns:bipext="exension library"?> Example: <?namespace:xmlns:bipext="http://www.example.com/XSL/Transform/java/ example.com.xmlpublisher.reports.BIPExtension"?> |
You can have multiple extension libraries defined in a single template file.
Example: Calling a Java Extension Library
Assume the extension library includes the following two methods that you want to call in the template:
bipext:infTimeToStr()
bipext:infStrToTimet()
After you have declared the library as shown above, specify the cell to which you want to apply the method by entering the XDO defined name in Column A and calling the function in Column B. Table 6-12 shows example commands.
Table 6-12 Example: Calling a Java Extension Library
| Column A Entry | Column B Entry |
|---|---|
|
XDO_?PARAM_START_DATE? |
<xsl:value-of select="bipext:infTimeToStr(bipext:infStrToTimet((.//PARAM_START_DATE)[1],2),3)" |
The entries in the XDO_METADATA sheet to declare and call the Java extension libraries are shown in Figure 6-26.
Figure 6-26 Entries in the XDO_METADATA Sheet to Declare and Call the Java Extension Libraries
The following commands require that specific formatting attributes be present in the XML data file. A summary list of the commands is shown in Table 6-13. See the corresponding section for details on usage.
Table 6-13 Commands for Specific Formatting Attributes
| Function | Command |
|---|---|
|
XDO_STYLE_n_?cell object name? |
|
|
XDO_SKIPROW_?cell object name? |
While you can define a consistent style in the template using Excel formatting, the XDO_STYLE command enables you to define a different style for any data cell dynamically based on the XML data.
With the XDO_STYLE command you specify the cell to which to apply the style, the logic to determine when to apply the style, and the style type to apply. The style value must be present in the XML data. Table 6-14 provides examples.
Table 6-14 Defining Border and Underline Styles
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_STYLE_n_?cell_object_name? For example: XDO_STYLE_1_?TOTAL_SALARY? |
<xsl evaluation that returns a supported value> For example: <xsl:value-of select=".//TOTAL_SALARY/@borderStyle"/> |
Style type For example: BottomBorderStyle |
BI Publisher supports the normal Excel style types and values as shown in Table 6-15.
Table 6-15 Excel Style Types and Values
| Style Type | Supported Values (Must be in returned by evaluation in Column B) | Supported Types (Enter in Column C) |
|---|---|---|
|
Normal |
BORDER_NONE BORDER_THIN BORDER_MEDIUM BORDER_DASHED BORDER_DOTTED BORDER_THICK BORDER_DOUBLE BORDER_HAIR BORDER_MEDIUM_DASHED BORDER_DASH_DOT BORDER_MEDIUM_DASH_DOT BORDER_DASH_DOT_DOT BORDER_MEDIUM_DASH_DOT_DOT BORDER_SLANTED_DASH_DOT |
BottomBorderStyle TopBorderStyle LeftBorderStyle RightBorderStyle DiagonalLineStyle |
You can also set a color using one of the types shown in Table 6-16.
Table 6-16 Color Types
| Style Type | Supported Value (Must be in returned by evaluation in Column B) | Supported Types (Enter in Column C) |
|---|---|---|
|
Normal |
When you set Color Style, give the value in RRBBGG hex format, for example: borderColor="0000FF" |
BottomBorderColor TopBorderColor LeftBorderColor RightBorderColor DiagonalLineColor |
BI Publisher also supports the underline type with the values shown in Table 6-17.
Table 6-17 Underline Types
| Style Type | Supported Values (Must be in returned by evaluation in Column B) | Supported Type (Enter in Column C) |
|---|---|---|
|
Underline |
UNDERLINE_NONE UNDERLINE_SINGLE UNDERLINE_DOUBLE UNDERLINE_SINGLE_ACCOUNTING UNDERLINE_DOUBLE_ACCOUNTING |
UnderlineStyle |
You can have multiple underline styles defined for a single cell.
Example: Defining Styles
To apply a style in a template, the style value must be present in the data. In this example, a border style and an underline style are applied to the DEPT_TOTAL_SALARY field shown in the Excel template.
For this example, the following data is used. Note that the DEPT_TOTAL_SALARY element in the data has these attributes defined:
borderStyle
underLineStyle
borderColor
The value of each of these attributes is used to apply the defined style based on logic defined in the template.
<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEES>
<G_DEPT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<EMP_NAME>Jennifer Whalen</EMP_NAME>
<EMAIL>JWHALEN</EMAIL>
<PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
<HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>4400</SALARY>
</G_EMP>
</LIST_G_EMP>
<DEPT_TOTAL_SALARY borderStyle="BORDER_DOUBLE" underLineStyle="UNDERLINE_DOUBLE_ACCOUNTING" borderColor="0000FF">4400</DEPT_TOTAL_SALARY>
</G_DEPT>
<G_DEPT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID>201</EMPLOYEE_ID>
<EMP_NAME>Michael Hartstein</EMP_NAME>
<EMAIL>MHARTSTE</EMAIL>
<PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
<HIRE_DATE>1996-02-17T00:00:00.000-07:00</HIRE_DATE>
<SALARY>13000</SALARY>
</G_EMP>
<G_EMP>
<EMPLOYEE_ID>202</EMPLOYEE_ID>
<EMP_NAME>Pat Fay</EMP_NAME>
<EMAIL>PFAY</EMAIL>
<PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
<HIRE_DATE>1997-08-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>6000</SALARY>
</G_EMP>
</LIST_G_EMP>
<DEPT_TOTAL_SALARY borderStyle="BORDER_DOUBLE" underLineStyle="UNDERLINE_DOUBLE_ACCOUNTING" borderColor="0000FF">19000</DEPT_TOTAL_SALARY>
</G_DEPT>
...
</EMPLOYEES>
To define a style:
In the Excel template, assign the defined name XDO_?DEPT_TOTAL_SALARY? to the field that is to display the DEPT_TOTAL_SALARY from the data, as shown in Figure 6-27.
Figure 6-27 Assigning the Defined Name XDO_?DEPT_TOTAL_SALARY?
In the XDO_METADATA sheet, enter the following:
To define the top border style, use the entries shown in Table 6-18.
Table 6-18 Top Border Style
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_STYLE_1_?DEPT_TOTAL_SALARY? |
<xsl:value-of select=".//DEPT_TOTAL_SALARY/@borderStyle"/> |
TopBorderStyle |
The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?
The entry in Column B retrieves the style value from the attribute borderStyle of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for borderStyle is "BORDER_DOUBLE".
The entry in Column C tells BI Publisher to apply a TopBorderStyle to the cell.
To define the top border color, use the entries shown in Table 6-19.
Table 6-19 Top Border Color
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_STYLE_2_?DEPT_TOTAL_SALARY? |
<?.//DEPT_TOTAL_SALARY/@borderColor?> |
TopBorderColor |
The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?
The entry in Column B retrieves the style value from the attribute borderColor of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for borderColor is "0000FF" (blue).
The entry in Column C tells BI Publisher to apply a TopBorderColor to the cell.
To define the underline style, use the entries shown in Table 6-20.
Table 6-20 Underline Style
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_STYLE_3_?DEPT_TOTAL_SALARY? |
<?.//DEPT_TOTAL_SALARY/@underLineStyle?> |
UnderlineStyle |
The entry in Column A maps this style command to the cell assigned the name XDO_?DEPT_TOTAL_SALARY?
The entry in Column B retrieves the style value from the attribute underLineStyle of the DEPT_TOTAL_SALARY element. Note from the sample data that the value for underLineStyle is "UNDERLINE_DOUBLE_ACCOUNTING".
The entry in Column C tells BI Publisher to apply the UnderLineStyle to the cell.
Figure 6-28 shows the three entries in the Data Constraints region.
When you run the report, the style commands are applied to the XDO_?DEPT_TOTAL_SALARY? cell, as shown in Figure 6-29.
Figure 6-29 A Generated Report Showing Style Commands Applied to the XDO_?DEPT_TOTAL_SALARY? Cell
Use the XDO_SKIPROW command to suppress the display of a row of data in a table when the results of an evaluation defined in Column B return the case insensitive string "True". Example entries are shown in Table 6-21.
Table 6-21 Skipping a Row
| Column A Entry | Column B Entry |
|---|---|
|
XDO_SKIPROW_?cell_object_name? For example: XDO_SKIPROW_?EMPLOYEE_ID? |
<xsl evaluation that returns the string "True"/> For example: <xsl:if test="string-length(./EMPLOYEE_ID/@MANAGER) != 0"> <xsl:value-of select="./EMPLOYEE_ID/@MANAGER"/> </xsl:if> |
Example: Skipping a Row Based on Data Element Attribute
In this example, the Excel template suppresses the display of the row of employee data when the EMPLOYEE_ID element includes a "MANAGER" attribute with the value "True".
Assume data as shown below. Note that the EMPLOYEE_ID element for employee Michael Hartstein has the MANAGER attribute with the value "True". The other EMPLOYEE_ID elements in this set do not have the attribute.
<?xml version="1.0" encoding="UTF-8"?>
<EMPLOYEES>
<G_DEPT>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID MANAGER="TRUE">201</EMPLOYEE_ID>
<EMP_NAME>Michael Hartstein</EMP_NAME>
<EMAIL>MHARTSTE</EMAIL>
<PHONE_NUMBER>515.123.5555</PHONE_NUMBER>
<HIRE_DATE>1996-02-17T00:00:00.000-07:00</HIRE_DATE>
<SALARY>13000</SALARY>
</G_EMP>
<G_EMP>
<EMPLOYEE_ID>202</EMPLOYEE_ID>
<EMP_NAME>Pat Fay</EMP_NAME>
<EMAIL>PFAY</EMAIL>
<PHONE_NUMBER>603.123.6666</PHONE_NUMBER>
<HIRE_DATE>1997-08-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>6000</SALARY>
</G_EMP>
<G_EMP>
<EMPLOYEE_ID>652</EMPLOYEE_ID>
<EMP_NAME>William Morgan</EMP_NAME>
<EMAIL>WMORGAN</EMAIL>
<PHONE_NUMBER>219.123.7776</PHONE_NUMBER>
<HIRE_DATE>1994-10-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>8000</SALARY>
</G_EMP>
</LIST_G_EMP>
</G_DEPT>
...
</EMPLOYEES>
To suppress the display of the row of the employee data when the MANAGER attribute is set to "True", enter the entries shown in Table 6-22 in the Data Constraints section.
Table 6-22 Skipping a Row Based on Data Element Attribute
| Column A Entry | Column B Entry |
|---|---|
|
XDO_SKIPROW_?EMPLOYEE_ID? |
<xsl:if test="string-length(./EMPLOYEE_ID/@MANAGER) != 0"> <xsl:value-of select="./EMPLOYEE_ID/@MANAGER"/> </xsl:if> |
The output from this template is shown in Figure 6-30. Note that the employee Michael Hartstein is not included in the report.
Use the functions shown in Table 6-23 to create groupings of data in the template.
Use the XDO_GROUP command to group flat data when the layout requires a specific data grouping, for example, to split the data across multiple sheets. Example entries are shown in Table 6-24.
Table 6-24 Grouping the Data
| Column A Entry | Column B Entry | Column C Entry |
|---|---|---|
|
XDO_GROUP_?group element? For example: XDO_GROUP_?STATE_GROUP? |
<xsl beginning groupng logic/> For example: <xsl:for-each-group select="current-group()" group-by="./STATE"> <xsl:for-each-group select="current-group()" group-by="./RESOURCE_NAME"> <xsl:for-each select="current-group()"> |
<xsl ending groupng tags/> For example: </xsl:for-each> <xsl:for-each-group> <xsl:for-each-group> |
Define the XSL statements to be placed at the beginning and ending of the section of the group definition marked up by XDO_?cell object name?. You can mark multiple groups nested in the template, giving each the definition appropriate to the corresponding group.
When XDO_GROUP_? is used in a nested group, the ranges of XDO define names in the final report become meaningless. In this case, do not refer to the define names in formulas in the final report. You can disable the XDO markup activity in the final report using the command "XDO_MARKUP_?". Table 6-25 shows the usage of XDO_MARKUP_? in the XDO_METADATA sheet.
Table 6-25 Using XDO_MARKUP_?
| Column A Entry | Column B Entry |
|---|---|
|
XDO_MARKUP_? |
"false" (The cell must be formatted as Text in the Excel Format Cells dialog.) |
In addition, if your template includes a large number of defined names and these are used in multiple levels of nested groups, Excel may not be able to handle the number of generated defined names. In this case, use the XDO_MARKUP_? command to disable markup for the generated report.
When set to "false", BI Publisher does not produce any defined names for any result produced by XDO_GROUP_?
The XDO_REGROUP regroups the data by declaring the structure using the defined names. The XDO_REGROUP logic is a shortened form of the XDO_GROUP logic and does not require the XSLTcoding requirements in the XDO_METADATA sheet. The definition must therefore be directly on XDO_REGROUP_? define names, or on any other definition on the XDO_METADATA sheet. Entries are shown in Table 6-26.
Table 6-26 Regrouping the Data
| Column A Entry | Column B Entry |
|---|---|
|
XDO_REGROUP_? |
XDO_REGROUP_?UniqueGroupID?levelName?groupByName?sortByName?sortByName?sortByName? where
|
Table 6-27, Table 6-28, and Table 6-29 show an example of how to create three nested groupings.
Table 6-27 Creating Nested Groupings, Example 1
| Column A Entry | Column B Entry |
|---|---|
|
XDO_REGROUP_? |
XDO_REGROUP_?PAYMENTSUMMARY_Q1?PAYMENTSUMMARY_Q1?PAY_TYPE_NAME? |
In the definition shown in Table 6-27, the most outer group is defined as PAYMENTSUMMARY_Q1, and it is grouped by PAY_TYPE_NAME
Table 6-28 Creating Nested Groupings, Example 2
| Column A Entry | Column B Entry |
|---|---|
|
XDO_REGROUP_? |
XDO_REGROUP_?COUNTRYGRP?XDO_CURRGRP_?COUNTRY? |
The definition shown in Table 6-28 creates a second outer group. The group is assigned the name COUNTRY_GRP and it is grouped by the element COUNTRY.
Table 6-29 Creating Nested Groupings, Example 3
| Column A Entry | Column B Entry |
|---|---|
|
XDO_REGROUP_? |
XDO_REGROUP_?STATEGRP?XDO_CURRGRP_?STATE? |
The definition shown in Table 6-29 creates the inner group STATEGRP and it includes a sortByName parameter: STATE.
For the best performance, design the data model to perform as much of the data processing as possible. When it is not possible to get the required output from the data engine, you can preprocess the data using an XSLT file that contains the instructions to transform the data. Some sample use cases include:
To create groups to establish the necessary hierarchy to support the desired layout
To add style attributes to data elements
To perform complex data processing logic that may be impossible in the Excel Template or undesirable for performance reasons
Note:
The Template Builder for Excel does not support preview for templates that require XSLT preprocessing.
To use an XSLT preprocess file:
Create the file and save as .xsl.
Upload the file to the report definition in the BI Publisher catalog, as you would a template:
Navigate to the report in the catalog.
Click Edit.
Click Add New Layout.
Click Upload.
Complete the fields in the Upload dialog and select "XSL Stylesheet (HTML/XML/Text)" as the template Type.
After upload, click View a List. Deselect Active, so that users do not see this template as an option when they view the report.
Note:
For testing purposes, you might want to maintain the XSL template as active to enable you to view the intermediate data when the template is applied to the data. After testing is complete, set the template to inactive.
Save the report definition.
In the Excel template, on the XDO_METADATA sheet, in the Header section, enter the file name for the Preprocess XSLT File parameter. For example: splitByBrand.xsl
This section includes two examples of using an XSLT preprocess file to group flat data so that it can be split into multiple sheets in Excel. The examples are:
Splitting the Data by Count of Rows
Splitting the data by row count is an option when your report data exceeds the sheet row size of Excel 2003 (65,536 rows per sheet).
Both examples use the following XML data:
<ROWSET>
<ROW>
<Products.Type>COATINGS</Products.Type>
<Products.Brand>Enterprise</Products.Brand>
<Markets.Region>CENTRAL REGION</Markets.Region>
<Markets.District>CHICAGO DISTRICT</Markets.District>
<Periods.Year>2000</Periods.Year>
<Measures.Dollars>1555548.0</Measures.Dollars>
</ROW>
<ROW>
<Products.Type>COATINGS</Products.Type>
<Products.Brand>Enterprise</Products.Brand>
<Markets.Region>EASTERN REGION</Markets.Region>
<Markets.District>NEW YORK DISTRICT</Markets.District>
<Periods.Year>2000</Periods.Year>
<Measures.Dollars>1409228.0</Measures.Dollars>
</ROW>
...
</ROWSET>
This example demonstrates how to use an XSLT preprocess file to create a grouping in the data that will enable the splitting of the data across multiple Excel sheets based on the grouping. This example groups the sample data by the Products.Brand field.
Create an XSLT file to group the data.
The following sample XSLT file groups the data according to <Products.Brand> and creates a high level element <BrandGroup> for each of those groups.
<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<ROWSET>
<xsl:for-each-group select="/ROWSET/ROW" group-by="./Products.Brand">
<xsl:variable name="var_brand" select="current-grouping-key()" />
<BrandGroup>
<xsl:attribute name="name">
<xsl:value-of select="$var_brand" />
</xsl:attribute>
<xsl:copy-of select="current-group()" />
</BrandGroup>
</xsl:for-each-group>
</ROWSET>
</xsl:template></xsl:stylesheet>
When applied to the data sample, this XSLT file generates intermediate data as follows:
<ROWSET>
<BrandGroup name="Enterprise">
<ROW>
<Products.Type>COATINGS</Products.Type>
<Products.Brand>Enterprise</Products.Brand>
<Markets.Region>CENTRAL REGION</Markets.Region>
<Markets.District>CHICAGO DISTRICT</Markets.District>
<Periods.Year>2000</Periods.Year>
<Measures.Dollars>1555548.0</Measures.Dollars>
</ROW>
...
</BrandGroup>
... <ROWSET>
Save the XSLT file as splitByBrand.xsl and upload the file to the report definition in the BI Publisher catalog. Select "XSL Stylesheet (HTML/XML/Text)" as the template Type.
In the Excel template file, in the XDO_METADATA sheet, enter the following:
For the Preprocess XSLT File parameter, enter "splitByBrand.xsl"
In the Data Constraints region, make the entries shown in Table 6-30 to split the data into multiple sheets based on the <BrandGroup> element created by the results of the XSLT preprocessing.
Table 6-30 Entries in Data Constraints Region to Split by Brand
| Column A Entry | Column B Entry |
|---|---|
|
XDO_SHEET_? |
<?//BrandGroup?> |
|
XDO_SHEET_NAME_? |
<?./@name?> |
The sample entries in the XDO_METADATA sheet are shown in Figure 6-31.
Upload the Excel template file to the report definition in the BI Publisher catalog.
This example demonstrates how to use an XSLT preprocess file to group the sample XML data by the count of occurrences of /ROWSET/ROW and then configure the Excel template to create a new sheet for each occurrence of the newly created group.
Create an XSLT file to create groups in the data according to a size specified in a variable.
The following sample XSLT file groups the occurrences of /ROWSET/ROW according to the value of $var_size and creates a high level element <CountGroup> for each of those groups.
<?xml version="1.0" encoding="utf-8" ?> <xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<ROWSET>
<xsl:variable name="var_size" select="3" />
<xsl:for-each select="/ROWSET/ROW">
<xsl:variable name="var_pos" select="position()" />
<xsl:variable name="var_mod" select="$var_pos mod($var_size)" />
<xsl:if test="$var_mod = 1">
<xsl:variable name="var_groupNum" select="($var_pos - $var_mod) div number($var_size) + 1" />
<xsl:element name="CountGroup">
<xsl:attribute name="name">
<xsl:value-of select="concat('Group', $var_groupNum)" />
</xsl:attribute>
<xsl:for-each select="/ROWSET/ROW[position() > ($var_pos -1) and position() < ($var_pos + $var_size)]">
<xsl:copy-of select="." />
</xsl:for-each>
</xsl:element>
</xsl:if>
</xsl:for-each>
</ROWSET>
</xsl:template>
</xsl:stylesheet>
When applied to the data sample, this XSLT file generates intermediate data as follows:
<ROWSET>
<CountGroup name="Group1"> <ROW>
<Products.Type>COATINGS</Products.Type>
<Products.Brand>Enterprise</Products.Brand>
<Markets.Region>CENTRAL REGION</Markets.Region>
<Markets.District>CHICAGO DISTRICT</Markets.District>
<Periods.Year>2000</Periods.Year>
<Measures.Dollars>1555548.0</Measures.Dollars>
</ROW>
...
</CountGroup>
...
<ROWSET>
Save the XSLT file as splitByCount.xsl and upload the file to the report definition in the BI Publisher catalog. Select "XSL Stylesheet (HTML/XML/Text)" as the template Type.
In the Excel template file, in the XDO_METADATA sheet, enter the following:
For the Preprocess XSLT File parameter, enter "splitByCount.xsl"
In the Data Constraints region, make the entries shown in Table 6-31.
Upload the Excel template file to the report definition in the BI Publisher catalog.
If the template preview is not generating the results expected, then you can use the Template Viewer to enable trace settings to view debug messages. The Template Viewer also enables you to save and view the intermediate XSL file that is generated after the sample data and template are merged in the XSL-FO processor. If you are familiar with XSL, then this can be a very useful debugging tool.
The Template Viewer is installed when you install the Template Builder for Word; see Section 6.1.5, "Desktop Tools for Excel Templates" for more information.
To preview with the Template Viewer and view log messages:
Open the Template Viewer:
From the Windows desktop, click Start, then Programs, then Oracle BI Publisher Desktop, then Template Viewer.
Click Browse to locate the folder that contains the sample data file and template file. The data file and template file must reside in the same folder.
Select Excel Templates. The Data and Template regions display all .xml files and all .xls files present in the directory, as shown in Figure 6-32.
Figure 6-32 The Data and Template Regions Showing All .xml and .xls Files
Click the appropriate data and template files to select them.
Select the log level.
From the Output Format list, select Excel.
Click Start Processing.
The Template Viewer merges the selected data with the selected template and spawn the appropriate viewer. View any log messages in the message box, as shown in Figure 6-33.
To view the generated XSL:
In the Template Viewer, select the data and template files and choose Excel output.
On the Tools menu, select Generate XSL file from and then choose Excel Template, as shown in Figure 6-34.
At the prompt, save the generated XSL file.
Navigate to the saved location and open the XSL file in an appropriate viewer.
|
 Copyright © 2010, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
