6.1.2 Limitations of Excel TemplatesThe 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.BI Publisher provides an add-in to Microsoft Excel to facilitate the insertion of fields and repeating groups. More complex designs require manual coding. Some features require the use of XSL and XSL Transformation (XSLT) specifications. Important:If you are using a version later than Excel 2003 to create your template and then save as Excel 97-2003, ensure that you do not use any features of the later version that are not supported in Excel 97-2003. For example, Excel 2003 allows only three conditional formatting rules per cell, but Excel 2007 allows more. If you apply more than three conditional formatting rules to a cell, only three are applied.
![]()
13+ Free Employee Review Templates Employee reviews are made in every organization in order to monitor, manage, and improve their performance. When Employee reviews are made appropriately and periodically, it ushers myriad advantages to the organization. Download free Microsoft Excel templates and spreadsheets, including Budget Templates, Calendar Templates, Schedule Templates, and Financial Calculators.
Excel 2007 also provides color support not provided in Excel 2003.To use some of the advanced features, you must have knowledge of XSL.The data model must be created in BI Publisher with sample data available. 6.2 Understanding Mappings Between the Template and the DataWhen you design Excel templates use the Excel Template Builder for inserting fields and repeating groups to your template.
When the Template Builder inserts a field or repeating group it creates a mapping between the data and the spreadsheet and writes the mapping to a hidden sheet called the XDOMETADATA sheet. The Template Builder creates the hidden XDOMETADATA sheet the first time you insert a field or repeating group.To view or update the XDOMETADATA sheet unhide the sheet. To add calculations or more advanced functions, enter the XSL functions directly in the XDOMETADATA sheet using the named mappings created by the Template Builder.
For more information about template-data mappings, see. Figure 6-1 The BI Publisher Tab in ExcelYou can use the Template Builder in connected mode or disconnected mode. In connected mode, log in to the BI Publisher server from Excel. The connection enables you to browse the BI Publisher catalog and load sample data from an existing report or data model. When your template is complete, you can upload it directly to the report definition in the BI Publisher catalog.
In disconnected mode, you must download a sample data file from the data model to your local client.This section includes the following topics about using the Template Builder for Excel:. 6.3.1 Working in Connected ModeIn connected mode you can interact directly with the BI Publisher server.
6.3.1.1 Logging In Through the Template BuilderThe Excel Template Builder enables a direct connection to the BI Publisher server from your desktop Excel session. By logging in directly to BI Publisher you can browse the catalog to choose the report to which to add the Excel template; or, if no report has been created, you can select the data model and create the report in the catalog from your Excel session.To log on to the BI Publisher server:.In Excel, on the BI Publisher tab in the Online group, click Log On.In the Login dialog, enter your BI Publisher username and password and the URL to the BI Publisher server.
The URL to the BI Publisher server takes the form www.:/xmlpserver. The Login dialog is shown in. Figure 6-4 Open Template DialogNavigate the catalog folders to locate the report, data model, or existing layout template. From this dialog you can initiate one of the following actions:.Modify an existing Excel template.When you select a report in the Reports region, any existing Excel templates or Excel Analyzer templates are displayed in the lower Layout Templates region. To modify an existing template, select the template name and click Open Layout Template. The Template Builder loads the sample data from the report's data model and opens the existing template in Excel.Create a new template for an existing report.Select the report name in the Reports region and click Open Report. The Template Builder loads the sample data for this report's data model.Select a data model to create a new report.When you select a data model from the catalog, the Create Report button is enabled.
![]()
Click Create Report and you are prompted to enter a report name and select the location in the catalog to save the new report. 6.3.1.3 Uploading Templates from the Template BuilderIf you have maintained the connection during the design process, click one of the following to upload your completed template to the BI Publisher server:.Upload Template uploads your edited template and replaces the existing template in the catalog. Upload Template is enabled only when you have opened an existing template from the Open Template dialog using the Open Layout Template button.Upload Template As prompts you to assign a Template Name and Locale to the template then uploads the file to the report in the BI Publisher catalog. 6.3.2 Working in Disconnected ModeWhen direct connection to the BI Publisher server is not possible or not desirable, you can use the Template Builder to design and preview templates in disconnected mode. In disconnected mode the commands in the Online group are not enabled. The process flow for working in disconnected mode is:.Log in to the BI Publisher server and download sample data from the data model for which you want to design a template.Open Excel with the BI Publisher Template Builder for Excel Add-in installed.Load the sample data to the Template Builder.Design your template in Excel.Preview your template using the Preview command.Log in to the BI Publisher server and use the report editor to upload your template.The following sections provide more detail on the Template Builder features when working in disconnected mode:. 6.3.2.1 Obtaining Sample DataThe Template Builder requires sample data to insert the data field mappings to your template.
For information on saving sample data from the report data model, see ' 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. 6.3.3 Inserting FieldsThe Field command in the Insert group maps data elements from the loaded sample data to the desired location in the spreadsheet.To insert a field:.In Excel, select the cell to which to map the data element.On the BI Publisher tab, in the Insert group, click Field. The Field dialog launches, displaying the data elements from your sample data.On the Field dialog select the element to insert to the cell. Notice that as you select items in the data structure, sample data is displayed in the Example region as shown in.
6.3.3.1 More Features of the Field DialogThe Field dialog provides the following features:FindFor an XML document with a large and complicated structure, use the find functionality to find a specific field. Enter all or part of the field name into the Find field and click Find Next.Business View or XML Tag Name ViewWhen working in connected mode, you can choose whether to view the data structure using the Business View names or the XML Tag Names as defined in the data model. Business View names are user-friendly names defined for the data elements in the data model editor. This option is not available when sample data has been loaded from a locally stored file or when the data model does not include Business View names. Figure 6-7 Inserting a Repeating GroupFor EachSelect 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.Note the following about creating repeating groups:.For loops and groupings not inside another group (that is, outer groups) you must select the repeating data element to be used. For example if the data set is flat, the only repeatable element is /DATA/ROWSET/ROW. In cases with multiple data sources or hierarchical XML you can choose the data set.If you are creating nested groups (inserting a loop or group inside of another loop in the template), the On Grouping checkbox is selected and the For Each field is not editable 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.Absolute PathSelect this check box to use the Absolute Path to the element in the XML structure. This is important if the data contains the same element name grouped under different parent elements.Group BySelect a field from the list by which you want to group the data. To create a simple loop, do not select a group by element. Selecting a group by element regroups the data into a new hierarchy based on the group by element.On GroupingWhen creating a nested for-each loop, select On Grouping to repeat the for-each loop only when the element appears within the parent loop.
Deselect On Grouping to repeat global data (outside the parent loop) within the nested loop.When you have completed the dialog options, click OK to insert the BI Publisher code to define the groupings. An Excel defined name is assigned to the cell range using the BI Publisher syntax XDOGROUP? And the code is written to the XDOMETADATA sheet as shown in. 6.3.5 Using the Field Browser to View, Edit, and Delete FieldsThe Field Browser enables you to view and edit the code inserted by the Template Builder and the code you inserted manually into the XDOMETADATA sheet.
When you select a line of code in the Field Browser, the corresponding cells in the template are highlighted, so you know which field you are editing, deleting, or viewing.To edit or delete a field using the Field Browser:.On the BI Publisher menu, in the Tools group, click Field Browser.The Field Browser displays the BI Publisher commands that are present in the template. Select the field or command to view. The code for the selected command displays in the lower Edit region. Notice that if the code has opening and ending tags (such as the opening and ending tags of a repeating group) the opening tag display in the upper code box and the closing tag displays in the lower code box.When you select a command, the area of the template that corresponds to the code is highlighted. In the repeating group is selected in the Field Browser and the corresponding fields are highlighted in the template. Important:The Analyzer for Excel plug-in and the Template Builder for Excel cannot both be installed. You must uninstall the Analyzer for Excel to use the Template Builder.To import an Excel Analyzer template:.Open the Excel Analyzer template.
If you are working in connected mode, navigate to the report that contains the template you wish to convert. When you select the report in the Open Template dialog, the Excel Analyzer template displays in the Layout Templates region as type 'excel'. Click Open Layout Template to open the Excel Analyzer template in Excel.Click Import.
A message notifies you: 'This feature will overwrite your template.' .Click OK.The Template Builder converts the Excel Analyzer template to an Excel template. 6.4.1 Step 1: Load Sample Data to the Template BuilderThe method you choose for loading sample data depends on whether you are working in connected or disconnected mode.To load data when working in connected mode, see.To load data when working in disconnected mode, seeThe sample data for this example is a list of employees by department. Note that employees are grouped and listed under the department.
- Generated by Oracle BI Publisher 11.1.1.4.0 - 20Marketing201Michael HartsteinMHARTSTE515.16-02-17T00:00:00.0000202Pat FayPFAY603.17-08-17T00:00:00.000.To build the template described in this tutorial, use the sample data available in the Samples folder installed with BI Publisher Desktop. A very similar data set can be found in BI Publisher DesktopTemplate Builder for WordsamplesExcel templatesEmployee By DepartmentsEmpByDept Single SheetsEmpbyDeptExcelData.xml. Field in LayoutElement in DataDepartmentDEPARTMENTNAMEEmployee NameEMPNAMEEmployee IDEMPLOYEEIDEmailEMAILTelephonePHONENUMBERSalarySALARYTo insert field mappings using the Template Builder:.Select the cell in the spreadsheet where the data field is to display. For example, to map the DEPARTMENTNAME element, select cell B5.On the BI Publisher tab, in the Insert group click Field. (Because this is the first field you are inserting, a message displays stating that the metadata sheet will be created.) The Field dialog displays showing the data structure, shown in.
6.4.4 Step 4: Use the Template Builder to Insert Repeating GroupsA 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 element, the employee's data (name, e-mail, telephone, salary) is displayed in the worksheet.For each occurrence of the 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, insert a repeating group 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 exampleestablishes the grouping desired for the report.To insert the repeating group for Employees:.Select the cells that make up the group. In this example, the first group is the Employee data that makes up a row in the table, the cells are A8 - E8.On the BI Publisher tab, in the Insert group, click Repeating Group.In the BI Publisher Properties dialog, select the following:.From the For Each list, select EMPS.From the Group By list, select EMPLOYEEID.shows BI Publisher Properties definitions for the Employees group. Notice that just the row of employee data is highlighted. Do not highlight the headers.
Notice also that the total cell is not highlighted. 6.4.5 Step 5: Insert the Calculated Salary FieldFinally, insert the second Salary field that is to be an aggregated sum for each department.To insert the calculated field:.Select the cell in the spreadsheet where the calculated salary is to display. In this example, the cell is E9.On the BI Publisher tab, in the Insert group, click Field to display the Field dialog.Select the SALARY element and click Insert to insert the mapping in the template.Open the XDOMETADATA sheet.The Template Builder created a hidden XDOMETADATA sheet when you inserted the first field. Unhide the sheet in your workbook by right-clicking Sheet1 and selecting Unhide from the menu.shows the XDOMETADATA sheet for the sample template. 6.5 Formatting DatesExcel 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 ExcelUsing the Employee by Department template and data from the first example, this procedure adds the HIREDATE element to the layout and displays the date as shown in Column E of. 6.6 Understanding Excel Template ConceptsSimilar 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 support more complex formatting instructions by defining the cell ranges and the commands in a separate worksheet designated to contain these commands. This sheet is called the XDOMETADATA sheet. 6.6.1 Mapping Data Fields and GroupsExcel 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 XDOMETADATA 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. 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 Template Builder for Excel automatically creates the defined names when you use it to insert fields and repeating groups. You can also insert the defined names manually. The defined names used in the Excel template must use the syntax described in this chapter and 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.
6.6.4 Using Native Excel Functions with the 'XDO' Defined NamesYou 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.
6.6.5 About the XDOMETADATA SheetEach Excel template requires a sheet within the template workbook called 'XDOMETADATA'. BI Publisher uses this sheet in the template in the following ways:.To identify the template as an Excel template.To insert the code for the field and group mappings you create with the Template Builder.As the template designer, you also use this sheet to specify more advanced 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. Figure 6-24 Format of the XDOMETADATA SheetThe 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 entries in Column A are:.Version.ARU-dbdrv.Extractor Version.Template Code.Template Type.Preprocess XSLT File.Last Modified Date.Last Modified ByThe Data Constraints section is used to specify the data field mappings and other processing instructions. Details are provided in the following sections. 6.7 Using Advanced BI Publisher FunctionsBI Publisher provides a set of functions to achieve additional reporting functionality.
Table 6-2 Column Entries Column A EntryColumn B EntryColumn C EntryXDOSHEET?Example:n/aXDOSHEETNAME?Example:(Optional)Example:XDOSHEET? Must refer to an existing high-level node in the XML data. The example creates a new sheet for each occurrence of 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, seeUse XDOSHEETNAME?
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:names each sheet using the value of DEPARTMENTNAME 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 in Column C.Example: Splitting the data into multiple sheetsUsing 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. Table 6-4 Command for Declaring Parameters Column A EntryColumn B EntryXDOPARAM? N?where n is unique identifier for the parameterwhere parametername is the name of the parameter from the data model and parametervalue is the optional default value.For example:To use the value of the parameter directly in a cell, refer to the parameter as $ parametername in the definition for the XDO defined name, as described in.
Table 6-5 Using a Parameter Directly Column A EntryColumn B EntryXDOPARAM?parametername?For example:XDOPARAM?Country?For example:You can also refer to the parameter in other logic or calculations in the XDOMETADATA sheet using $parametername.Example: Defining and passing a parameterTo 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? Table 6-6 Defining a Link Column A EntryColumn B EntryXDOLINK? Cell object name?For example:XDOLINK?INVOICENO?For example: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 (INVOICENO).To generate a report that creates a dynamic link to each invoice:.In the template sheet, assign the cell that is to display the INVOICENO the XDO defined name: XDO?INVOICENO?, as shown in. Table 6-8 Importing a Subtemplate Column A EntryColumn B EntryXDOSUBTEMPLATE? Where n is a unique identifier.
For example: XDOSUBTEMPLATE?1? For example: 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 Calling a Subtemplate Column A EntryColumn B EntryXDO? Cell object name? For more information on XSL subtemplates and creating the subtemplate object in the catalog, seeExample: Importing and Calling a SubtemplateAssume 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:UNDEFINEDPREPAIDINVOICECREDIT CARDDIRECT DEBITCASHCHECKALLIn 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. Table 6-10 Commands for Data Constraints Region Column A EntryColumn B EntryXDOSUBTEMPLATE?1?XDO?TYPE? The XDOSUBTEMPLATE?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. Table 6-11 Referencing Java Extension Libraries Column A EntryColumn B EntryXDOEXT?
Where n is a unique identifier. Example: XDOEXT?1? Example: You can have multiple extension libraries defined in a single template file.Example: Calling a Java Extension LibraryAssume the extension library includes the following two methods that you want to call in the template:.bipext:infTimeToStr.bipext:infStrToTimetAfter 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.
Shows example commands. Table 6-15 Excel Style Types and Values Style TypeSupported Values (Must be in returned by evaluation in Column B)Supported Types (Enter in Column C)NormalBORDERNONEBORDERTHINBORDERMEDIUMBORDERDASHEDBORDERDOTTEDBORDERTHICKBORDERDOUBLEBORDERHAIRBORDERMEDIUMDASHEDBORDERDASHDOTBORDERMEDIUMDASHDOTBORDERDASHDOTDOTBORDERMEDIUMDASHDOTDOTBORDERSLANTEDDASHDOTBottomBorderStyleTopBorderStyleLeftBorderStyleRightBorderStyleDiagonalLineStyleYou can also set a color using one of the types shown in. Table 6-17 Underline Types Style TypeSupported Values (Must be in returned by evaluation in Column B)Supported Type (Enter in Column C)UnderlineUNDERLINENONEUNDERLINESINGLEUNDERLINEDOUBLEUNDERLINESINGLEACCOUNTINGUNDERLINEDOUBLEACCOUNTINGUnderlineStyleYou can have multiple underline styles defined for a single cell.Example: Defining StylesTo 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 DEPTTOTALSALARY field shown in the Excel template.For this example, the following data is used. Note that the DEPTTOTALSALARY element in the data has these attributes defined:.borderStyle.underLineStyle.borderColorThe value of each of these attributes is used to apply the defined style based on logic defined in the template.10Administration200Jennifer WhalenJWHALEN515.17-09-17T00:00:00.000440020Marketing201Michael HartsteinMHARTSTE515.16-02-17T00:00:00.0000202Pat FayPFAY603.17-08-17T00:00:00.00019000.To define a style:.In the Excel template, assign the defined name XDO?DEPTTOTALSALARY? To the field that is to display the DEPTTOTALSALARY from the data, as shown in.
Table 6-18 Top Border Style Column A EntryColumn B EntryColumn C EntryXDOSTYLE1?DEPTTOTALSALARY?TopBorderStyleThe entry in Column A maps this style command to the cell assigned the name XDO?DEPTTOTALSALARY?The entry in Column B retrieves the style value from the attribute borderStyle of the DEPTTOTALSALARY element. Note from the sample data that the value for borderStyle is 'BORDERDOUBLE'.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 Top Border Color Column A EntryColumn B EntryColumn C EntryXDOSTYLE2?DEPTTOTALSALARY?TopBorderColorThe entry in Column A maps this style command to the cell assigned the name XDO?DEPTTOTALSALARY?The entry in Column B retrieves the style value from the attribute borderColor of the DEPTTOTALSALARY 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 Underline Style Column A EntryColumn B EntryColumn C EntryXDOSTYLE3?DEPTTOTALSALARY?UnderlineStyleThe entry in Column A maps this style command to the cell assigned the name XDO?DEPTTOTALSALARY?The entry in Column B retrieves the style value from the attribute underLineStyle of the DEPTTOTALSALARY element. Note from the sample data that the value for underLineStyle is 'UNDERLINEDOUBLEACCOUNTING'.The entry in Column C tells BI Publisher to apply the UnderLineStyle to the cell.shows the three entries in the Data Constraints region.
Table 6-21 Skipping a Row Column A EntryColumn B EntryXDOSKIPROW? Cellobjectname?For example:XDOSKIPROW?EMPLOYEEID?For example: Example: Skipping a Row Based on Data Element AttributeIn this example, the Excel template suppresses the display of the row of employee data when the EMPLOYEEID element includes a 'MANAGER' attribute with the value 'True'.Assume data as shown below. Note that the EMPLOYEEID element for employee Michael Hartstein has the MANAGER attribute with the value 'True'.
The other EMPLOYEEID elements in this set do not have the attribute.20Marketing201Michael HartsteinMHARTSTE515.16-02-17T00:00:00.0000202Pat FayPFAY603.17-08-17T00:00:00.000652William MorganWMORGAN219.14-10-17T00:00:00.000.To suppress the display of the row of the employee data when the MANAGER attribute is set to 'True', enter the entries shown in in the Data Constraints section. Table 6-24 Grouping the Data Column A EntryColumn B EntryColumn C EntryXDOGROUP? Group element?For example:XDOGROUP?STATEGROUP?For example: For example: 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.
Table 6-25 Using XDOMARKUP? Column A EntryColumn B EntryXDOMARKUP?' False' or '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 XDOMARKUP? 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 XDOGROUP?
Table 6-26 Regrouping the Data Column A EntryColumn B EntryXDOREGROUP?XDOREGROUP?UniqueGroupID?levelName?groupByName?sortByName?sortByName?sortByName?where.UniqueGroupID is the ID of the group. It can be the same as the levelName or you can assign it unique name.levelName is the XML level tag name in the XML data file or XDOCURRGRP that represents the current-group in the context of nested grouping. XDOCURRGRP should be used for all inner groups when more than one nesting group exists in your template.groupByName is the field name that you want to use for the GroupBy operation for the current group. This name can be empty if the XDOREGROUP? Command is used for the most inner group.sortByName is the field name that you want to sort the group. You can have multiple sortBy fields. If no sortByName is declared, then the data from the XML file is not sorted., and show an example of how to create three nested groupings.
6.8 Preprocessing the Data Using an XSL Transformation (XSLT) FileFor 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.
6.8.1 XSLT Preprocessing Examples: Splitting Flat Data into Multiple SheetsThis 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 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:COATINGSEnterpriseCENTRAL REGIONCHICAGO DISTRICT8.0COATINGSEnterpriseEASTERN REGIONNEW YORK DISTRICT8.0. 6.8.1.1 Splitting the Data by a Specific FieldThis 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 and creates a high level element for each of those groups.When applied to the data sample, this XSLT file generates intermediate data as follows:COATINGSEnterpriseCENTRAL REGIONCHICAGO DISTRICT8.0.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 XDOMETADATA sheet, enter the following:.For the Preprocess XSLT File parameter, enter 'splitByBrand.xsl'.In the Data Constraints region, make the entries shown in to split the data into multiple sheets based on the element created by the results of the XSLT preprocessing.
6.8.1.2 Splitting the Data by Count of RowsThis 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 $varsize and creates a high level element for each of those groups. When applied to the data sample, this XSLT file generates intermediate data as follows:COATINGSEnterpriseCENTRAL REGIONCHICAGO DISTRICT8.0.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 XDOMETADATA sheet, enter the following:.For the Preprocess XSLT File parameter, enter 'splitByCount.xsl'.In the Data Constraints region, make the entries shown in. 6.9 Using the Template Viewer to Debug a TemplateIf 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 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.
File Format. MS Excel. MS Word. Numbers. PagesSize: A4, USBlank Spreadsheet template comes in an easy Word file format to allow quick customization. All you have to do is download, edit and print, to keep track of student, employee attendances, percentages etc.
You may also see.Blank Spreadsheet TemplatesAre you in need of a Blank Spreadsheet Template? You can find and download the Blank Spreadsheet template from the internet. You can use this for any purpose. You can use a Blank Spreadsheet Template to keep track of your budget and for accounting purposes. Having a Blank Spreadsheet Template will help you save time, energy, and focus on your business efficiently. You can keep a record of all your business dealings using a Blank Spreadsheet Template. If you are wondering about creating a spreadsheet on your own, you can do so by using ms excel.Inventory Spreadsheet MS Excel Template.
File Format. MS Word. PagesSize: A4, USYou need to log on to your computer and you must have Microsoft Excel installed which is one of the best programs for the creation of spreadsheets. Not only is it simple to handle but you can modify the blank budget spreadsheet template as per your requirements.You have to change the Spreadsheet Templates that appears as per your specifications by deleting or adding further rows and columns.You may also see.
This can be done in a simple manner without much effort through the use of the “Insert” tab located right at the top of the screen.You then need to click the “File” tab on the top left of the screen and choose “Print” option. You will now find a blank spreadsheet for use. You may also see.Free Weekly Employee Blank Spreadsheet Template. If you’re unaware of how a spreadsheet works, well it is an electronic where one can arrange data in accordance to rows and columns. But creating them at times becomes a bit too tedious job. Blank spreadsheet templates are available now on internet to help you arranging your data within a spreadsheet by downloading the pre designed one.
They are freely available all over the internet and easily printable.These templates fit perfectly according to your need and different types are available to fulfill your exact requirement. Availability of these in apposite formats makes them user friendly. These templates are usually available in blank or sample format which helps one to customize it. There are several types of blank spreadsheet available online.Time saviorSpreadsheets are widely used to handle data. A lot of calculations are needed to make an appropriate one to fulfill your requirement.
It is easy to create but time taking. However, the availability of spreadsheet templates saves a lot of time and effort and allows the user to work on a pre defined one. You may also check out here.The Exclusive Usage of Blank SpreadsheetSpreadsheet templates are used by a wide spectrum of people because of its time effectiveness.
It helps to control your data. Organizing your data in a matrix format always helps you to customize it. Moreover the templates are office friendly and will help you to work according to your professional ethics. One of the main reasons why a large number of professionals prefer using blank spreadsheet templates is because they are easily downloadable. You can see also.Used by Broad SpectrumBlank downloadable spreadsheet templates are the key to solve half of your problems you face while collecting your data in an organized way.
At times creating a takes a lot of time, thus the templates which are also available in word format helps to stack your data in an efficient manner. These printable spreadsheet templates are widely used by the business holders, organizations, institutional work. The spreadsheet templates can also be used for personal data handling and manipulating.Billing is Hassle Free NowTemplates such as for spreadsheets, billing invoice blank spreadsheet are readily available online and give a pre defined structure of a bill so that everything becomes well organized. Moreover these templates are available in PDF or excel formats therefore customizing them becomes easy.
You can also see free spreadsheet template.Personal Documentation in an Efficient MannerThere are different types of template which allows you to handle your personal expenditure details or tax details just by downloading the apt format. Monthly income statement to monthly, all types of spreadsheet templates are available online for free.The Perfect LookAt times even after creating a spreadsheet we fail to add details which decreases the efficiency. The blank spreadsheet template gives the basic details where you can always customize it according to your need.
You may also see.Downloading an Appropriate Template is One Click Away NowThere are a huge number of blank spreadsheet templates available online of different formats. Downloading them and using them thereafter is effortless. If you have any issues on this post, please contact us!
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |