Microsoft Excel Test Script Templatedownload Free Apps



  1. Free Microsoft Excel App Download
  2. Microsoft Excel Test Script Template Download Free Apps Download
  3. Microsoft Excel Test Script Template Download Free Apps Downloads
  4. Microsoft Excel App Download Pc
  5. Excel Templates Free Download Office 2010
  6. Free Excel App Download
  7. Microsoft Excel 2010 Free Templates

Software Testing Templates – 50 MS Word + 40 Excel spreadsheets

Software Testing Templates (MS Word & Excel)

This Software Testing Template pack includes 50 MS Word and 40 MS Excel templates. You can use these templates to save time when creating test forms, logs and checklists so your Test Department has a standardized approach to testing.

Microsoft Excel is the same spreadsheet app you’ve been using for years but now designed to run on your phone. The Microsoft Excel Android app gives you a full suite of data analysis tools, as well as the ability to view, backup, and share files from the cloud. Each test case record represents a single test case, with fields for each input in the Excel workbook being regression-tested. When the button is pushed, a macro opens the Excel workbook, fills in all the inputs for the test case, and publishes the outputs to tblResults along with a date and time stamp.

This Software Testing Template pack includes 50 MS Word and 40 MS Excel templates. You can use these templates to save time when creating test forms, logs and checklists so your Test Department has a standardized approach to testing. Software Testing Templates: Contents

When you download the template pack, it will include four folders: MS Excel spreadsheets, MS Word forms, User Acceptance Test Plan, and a Test Plan template.

  • 88 Checklists, Forms and Templates including Action Items, Change Control, Change Requests, Contract Review, Data Access, Enhancement Request, Error Log, Final Release, Test Environment, Installation Completion, Issue Log, Outsourced Project, Quality Log, Risk Log, Status Report, Test Case, Test Plan, Test Record, Test Script, Unit Test Plan, User Acceptance Test Plan, User Interface Checklist, Web Usability Test Report…
  • Test Plan Template (29 pages) defines the scope, strategy, environment, controls, execution, release criteria, and budgets.
  • User Acceptance Test Plan (15 pages) gains customer acceptance and verifies that deliverable meets requirements.
Software Testing: 50 Microsoft Word Templates

The Software Testing template toolkit includes the following 50 MS Word templates, forms, and checklists:

  1. Acceptance Checklist
  2. Acceptance Form
  3. Acceptance Test Plan
  4. Action Item Log
  5. Business Approval Checklist Form
  6. Business Approval Form
  7. Change Control Form
  8. Change Control Log
  9. Change Request Form
  10. Contract Review Form
  11. Data Access Control Form
  12. Enhancement Request Form
  13. Error Log
  14. Final Release Report
  15. Hardware Test Environment Specifications
  16. Installation Completion Form
  17. Issue Log
  18. Outsourced Project Checklist
  19. Quality Log
  20. Release Control Form
  21. Requirements Testing Report
  22. Requirements Traceability Matrix
  23. Risk Log
  24. Risk Management Plan Form
  25. Roles & Responsibility Matrix
  26. Status Report
  27. System Acceptance Form
  28. System Final Release Sign-off Form
  29. System Requirements Sign-off Form
  30. System Test Plan Sign-off Form
  31. Test Case Template
  32. Test Case Validation Log
  33. Test Cycle Signoff Document
  34. Test Evaluation Summary
  35. Test Manager Checklist
  36. Test Plan Template
  37. Test Plan Review Checklist
  38. Test Plan Task Preparation
  39. Test Problem Record
  40. Test Record
  41. Test Results Report
  42. Test Script
  43. Test Script Allocation Form
  44. Training Course Evaluation Form
  45. Training Requirements Form
  46. Unit Test Plan
  47. User Acceptance Test Plan
  48. User Acceptance Test Report
  49. User Interface Checklist
  50. Web Usability Test Report
Software Testing – MS Word Screenshots

Here are screenshots of MS Word templates, forms, and checklists

Software Testing Templates: 40 Microsoft Excel Spreadsheets

The software testing template kit also includes these 40 Microsoft Excel templates.

  1. Acceptance Criteria Log
  2. Action Item Log
  3. Business Approval Form
  4. Business Approval Checklist
  5. Change Control Log
  6. Change History Log
  7. Change Management Tracking Log
  8. Change Register
  9. Change Request Form
  10. Data Access Control
  11. Deviation Control Form
  12. Document Control Form
  13. Failed Scripts
  14. Hardware Inventory
  15. Issue Log
  16. Issue Submission Form
  17. Log Status
  18. Open Issues
  19. Project Costs
  20. Quality Log
  21. Recovery Tests
  22. Report – Test Summary
  23. Reporting Requirements
  24. Roles and Responsibilities Descriptions
  25. Roles and Responsibilities Matrix
  26. Risk Log
  27. Risk Register
  28. Risk Response Plan
  29. Status Report
  30. Task Preparation
  31. Test Case
  32. Test Case Validation Log
  33. Test Script
  34. Test Script List
  35. Test Tracking Report
  36. Testing Budget
  37. To Do List
  38. Unit Test
  39. Version Control Log
  40. Web Usability Report
Software Testing – MS Excel Screenshots

Here are screenshots of the MS Excel spreadsheets:

[Download these software testing templates here]

Free MS Office 97-2003 Templates

For those of you who use MS Office 97-2003, we’ve included the 27 MS Excel and 50 MS Word templates in .doc and .xls format.

These templates are the exact same as the MS Office 2016 templates shown above. The color scheme is slightly different, that’s all.

Here are some screenshots so you can see what they are like. You will find these in the MS Office Templates 97-2003 folder.

Software Testing: Project Costs MS Excel template

Status Report

Software Testing: Open Issues MS Excel template

Software Testing: Issue Submission MS Excel template

Software Testing: Action Item Log – MS Excel template

Software Testing: Release Control Form

Software Testing Change Request Form

Software Testing: Test Script – MS Word

Software Testing: Test Case Template


Software Testing: Test Manager Checklist

Software Testing: Issue Log – MS Word


Software Testing: User Acceptance Test Report

Software Testing: Status Reports

Test Plan Template

Software Testing: Environment Specifications

Software Testing Templates: Getting Started
  • Acceptance Criteria Log – Use this template to capture the acceptance criteria when testing each functional area.
  • Action Item Log – Allocate an action item number, description, status (Low/Medium/High), date reported, resource it was assigned to, its due date, and other additional comments.
  • Change Control Log – Identify the basis for the change; confirm whether it is disapproved or approved. Include the Software Change Request Number (SCR) #, Requirements #, date submitted, and whether it is approved/not approved, on hold, in progress, or cancelled.
  • Change History Log – Describe the date, author and history in this log file.
  • Data Access Control – For each Person or Group, identify the individuals who have access to the test cases and their status, e.g. Development has access to the Test Cases for Web Project.
  • Deviation Control Form – Use this form to record minor adjustments (i.e. deviations) that must be made to the system in order for testing to proceed.
  • Document Control Form – Use this form to identify, track and monitor test scripts.
  • Failed Scripts – Use this script to identify where test scripts have failed and provide details of the error and its severity, e.g. minor error, major error etc.
  • Issue Submission Form – Use this to describe errors/issues that must be addressed for testing to proceed. Usually sent by users or testers to the Line Manager.
  • Log Status – For each log, identify its Log ID, the nature of the risk/issue, and whether it is Open or Closed.
  • Open Issues – Use this to identify all open issues by number, date created; tester, with descriptions of Target Date, Category, Status, Resolution and Date.
  • Project Costs – Use this spreadsheet to determine the cost of running all test-related activities throughout the software development lifecycle.
  • Quality Log – Use this to capture the module under test, method of checking, tester, Planned Date, Date Completed, Results, Action Items and Sign-off Date.
  • Risk Log – Identify the Risk Number, its Date, Type (e.g. Business/Project/Stage) a brief description, Likelihood %, Severity (e.g. Low or Medium) Impact, Action Required, who is was Assigned To and its Status.
  • Roles and Responsibilities Descriptions & Matrix – Identify all the roles on the project, with details of their responsibilities. Include contact names and email addresses.
  • Status Report – Identify the function that is under test, enter its Business value on a scale of 1-5 with 1 the lowest value and 5 the highest (or whichever numbering system you wish to use); details of the problem severity broken out by a factor of 1 to 5.
  • Test Preparation – Use this to identify all activities that must be performed before testing commences, such as reviewing specifications and prioritizing test items.
  • Test Case – Use this template to test a specific part of the system as per the Test Plan.
  • Test Validation Log – Use this log file to track the results of the test cases.
  • Test Script – Enter the Area under test, its Set, whether it has Passed or Failed, with a Description of the Error and its Severity, e.g. L/M/H.
  • Test Script List – Enter the Area under test, its Test Case ID, Bug ID, Bug Fixed Date, Bug Fixed By and Fix verified By details.
  • Task Preparation – Use this checklist to prepare for the Test Plan: Review Software Requirements Specifications, Identify functions/modules for testing, Perform Risk Analysis.
  • Test Case & Validation Log – Use this Test Case template to capture the name of the Test Case; Description; Start Conditions; Pass Criteria; Tester Name; Build Number; Test Data; Steps, Actions and Expected Result.
  • Test Tracking Report – Use this report to track the progress of the software tests each Week, capture which are Planned, were Attempted and numbers that are Successful.
  • Testing Budget – Use this Excel spreadsheet to plan your quarterly and annual testing budgets. Identify, quantify and cost the resources required by your team to complete projects successfully.
  • Unit Test – Use this to describe the item under test, expected results and whether it passed or failed.
  • Version Control Log – Use this to track the Product’s Version No., its Date, and Approvals.
  • Web Usability Report – Use this to analyze the usability of a web project, such as the performance of its Navigation, Graphics, Error Messages, and the quality of its micro-content.
Free Test Plan Template (29 Pages)

When you download the Software Testing Template kit, you get this 29 page MS Word Test Plan template for free. You can buy it separately here.

Free Acceptance Test Plan (21 Pages)

And as part of the Software Testing Template kit, you also get this free 21 page MS Word Acceptance Test Plan template.

This 21 page MS Word template outlines the steps required to prepare an Acceptance Test Plan, ensuring that all components of the system are tested.

Endorsements

We’ve been selling online for almost 20 years. Here are some of the kind words from our customers.

Ivan,

Thank you for going the extra mile and making it work in my “outdated” Excel program!

If you need a testimonial for your website, I’ll gladly write one up. I so appreciate your kindness, persistence and patience.

Suzanne, Virginia

~~~

Excellent materials here Ivan.

I have now purchased 3 different documents. They are very simple to understand, well laid out and very well written. Thank you for saving me weeks, if not months of frustration.

Dan Faubion

Advnt Biotechnologies

Phoenix, Arizona 85027

~~~

Thank you for your prompt response.

Free Microsoft Excel App Download

If you would like to have a reference. you can quote me.

‘Top professional templates and excellent customer service‘

Regards

Frederick Tsang

~~~

Dear Ivan,

I received your test template and like it very much!

Thank you.

Best regards / Mit freundlichen Grüßen,

Folkert, Germany

~~~

Thanks so much for your help Ivan!

I look forward to doing business with you in the future.

Holly

~~~

Microsoft Excel Test Script Template Download Free Apps Download

Hi Ivan, thank you so much.

I am blown away with the promptness of your response. I didn’t expect to hear from you so soon. Thanks again.

Kathy

~~~

Thanks so much Ivan. I really appreciate it. I will definitly keep you in mind for future purchases.

Thanks again,

Jamie

~~~

yes, i did receive them, thanks much! you have great docs, i’m sure i’ll be back to your website.

Jennifer

~~~

Thanks Ivan. Rare to find such service online. Really appreciate it.

Regards,

Prema

~~~

Hi Ivan,

Thanks for the quick response. The documents meet my business need beautifully.

Regards,

Leonard

~~~

I’d just like to compliment, how much I really like the quality of your work (the frameworks are very structured and clear) and it is very convenient to use as well.

Once again, thank you so much.

Regards,

Cherry, Australia

~~~

Hi Ivan,

Kudos to you for going the extra steps for me read your files and be able to utilize them.

Sincerely A Happy Customer!

Jacqueline

~~~

Wow. I did receive the templates. I went ahead a purchased again. Great work on the templates. These are great products.

Nikolas

~~~

Thanks so much for your quick reply. They do work for me. You’re the best!

Carol

~~~

Hi Ivan,

Your documents are excellent. I have bought a few of them myself. Truly professional.

With best regards,

Ajit,

Kuwait

~~~

Dear Ivan,

Marvelous job you’ve done by posting this small tip on your website.

Microsoft Excel Test Script Template Download Free Apps Downloads

It worked after all possible software I used to repair my word file.

I feel very much grateful for your help.

Thank you very much.

Regards,

Sintha,

India

~~~

Thank you so much Ivan! Two days in and you fix my problem in twenty minutes. Will look to your site for ALL future help, you just don’t know how much this helped. Safe travels, and thanks again.

Matthew

~~~

Thanks so much!! You’re article on recovering a word document saved my life!

You’re invaluable!

Thanks!

Yaso

~~~

Ivan

Thanks for the quick response, I look forward to using the templates.

Best Regards

Chris

Ansell

~~~

The templates have been a huge help in my current project. I appreciate your quick response.

Thank you & have a wonderful day!

Crystal

~~~

Thanks again. Great templates by the way!

Daniel

~~~

Software Testing Templates: Contents

After you make the purchase, you will be sent an email with a link to download the files. This 7 MBs file includes four folders for the Excel spreadsheets, MS Word forms, the Acceptance Test Plan and the Test Plan. It also includes another Zip file with the MS Word/Excel files in MS Office 97-2203 format.

50 Software Testing templates in MS WordMS Word .doc and .docx40 Software Testing templates in MS ExcelMS Excel .xls and .xlsxTest Plan – 29 pagesMS Word .doc and .docxAcceptance Test Plan – 21 pagesMS Word .doc and .docxProduct Specifications

File Format: MS Word (.docx / .doc), MS Excel (.xlsx / xls)

File Size – The download file is 7 MBs.

It may take a few minutes to fully download depending on your connection speed. Please wait until the entire file downloads before opening it, otherwise, you may get an error message. If you do, simply click the Download link in your email.

Opening the Files – You don’t need any special software to unzip the files. To unzip the files, right click on it, then select Extract, and save it to your computer

Getting Started – Depending on your MS Office settings. the files may say Read Only when you open them. If this occurs, simple click File, Save As and save the files. There are no security settings on any of the files.

Images – All of the images in the templates are copyright free.

FAQs

How does the order process work?

Who are your Customers?

Here is a sample list of our customers. Since 1998, Klariti provided products and services to Accenture, AIG, Bearing Point, Cap Gemini, Cisco, Disney, Ernst & Young, GE, HBO, IBM, J.P. Morgan, KLM, KPMG, Lehman Brothers, LEVI’S, MacDonalds, Macy’s, Mayo Clinic, McCormick, McGrawHill, MEGA, Mesirow , Mitre, NHL, NTL, OmniVision, Oracle, OshKosh, PennWest Energy, Pepico, Performance Technologies, Philips, PriceWaterhouseCoopers, PV Cycle, Raytheon, Reuters, Roche, Rogers, SAAB, Sabre Holding, Safeway, Sagem Morpho, Sara Lee, Shaw, Shell, Siemens, Sikorsky, Sobeys, SpeakEasy, SunPower, Sydney Opera House, Symantec, TATA, Tetra, ThyssenKrupp, TNT, Toyota, Travellers, TYCO, UC Davis, Verizon, Visteon, Vodafone, Wachovia Securities, Wal Mart, Whirlpool, WIPRO, Zurich

Technical Support

I am here to help you with any questions. You can contact me directly if you need any helping using these templates.

What file formats are the templates in?

The files work on Word 97, 2003, 2007, Office 2010, Office 2016, Windows 7, XP, Vista, and Apple iWork (if you have Word for Mac).

How do I get my templates?

After you make the payment, you are sent an email. This has a link to a Download Page from where you can save the templates,

What is the refund policy?

We offer a 60 days refund policy.

I have not received my product. Who do I contact?

Please email ivan at klariti.com or info at klariti.com or ivanawalsh at gmail.com. Please include your order number

Help! I’ve accidentally deleted my templates!

Just drop us a line with the order number. We’ll send it out.

Why does my file say it’s corrupt?

If you open a zip file during the download, it may display an error message that the file is corrupt. What this means is that not all of the file have downloaded and therefore the system thinks its contents are corrupt. Please try to download the file again or contact us for assistance. Email us the receipt number you received from eJunkie and we’ll send you another copy.

Contact me at:

  • Email: ivan@klariti.com / info@klariti.com
  • Tel: 00.353.860.886.349
  • WhatsApp: IvanAWalsh

As we’re in Europe, there may be a time difference when getting back to you.

Again, if there are any problems, just let me know.

Regards,

Ivan Walsh

Affiliates

Like this product?

Spread the word about it and earn 50.00% of the purchase price on sales you refer.

Join our affiliate program Skip Headers
Oracle Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher
Release 11g (11.1.1)
Part Number E13881-02

This chapter covers the following topics:

Introduction

An Excel template is a report layout that you design in Microsoft Excel for retrieving and formatting your 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 your data is output to Excel workbooks.

Features of Excel Templates

With Excel templates you can:

  • Define the structure for your 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 your data to manipulate it prior to rendering

  • Use native Excel functionality

Limitations of Excel Templates

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 will show 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

Prerequisites

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 will need knowledge of XSL and XSLT.

  • The report data model has been created.

Supported Output

Excel templates generate Excel binary (.xls) output only.

Desktop Tools

BI Publisher provides a downloadable add-in to Excel that enables you to preview your template with sample data. This facilitates design by enabling you to test and edit your 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.

Sample Excel Templates

The Template Builder includes sample Excel templates.

Microsoft Excel App Download Pc

To access the samples from a Windows desktop:

Click Start, then Programs, then Oracle BI Publisher Desktop, then Samples, then Excel.

This will launch the folder that contains the Excel sample templates.

Concepts

Similar to RTF template design, Excel template design follows the paradigm of mapping fields from your 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.

Identifying Data Field Placeholders and Groups

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.

Your template content and layout must correspond to the content and hierarchy of the XML data file used as input to your report. Each group of repeating elements in your template must correspond to a parent-child relationship in the XML file. If your 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 Preprocessing the Data Using an XSL Transformation (XSLT) File and Grouping Functions for more information about these options.

Use of Excel Defined Names

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

About the XDO_ Defined Names

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.

Using Native Excel Functions

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 will automatically adjust the region ranges for those named regions so that the formulas calculate correctly.

However, if you create nested groups in your template, 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.

About the XDO_METADATA Sheet

Each Excel template requires a sheet within the template workbook called 'XDO_METADATA'. Use this sheet to identify your 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 your completed template to the BI Publisher catalog. This will prevent report consumers from seeing it in the final report output.

Note: For more information see Format of the XDO_METADATA Sheet and Defining BI Publisher Functions.

Building a Simple Template

This section will demonstrate the concepts of Excel templates by walking through the steps to create a simple Excel template and testing it with the Excel Template Builder. This procedure will follow these steps:

  1. Obtain sample XML data from your data model.

  2. Open the BlankExcelTemplate.xls file and save as your template name.

  3. Design the layout in Excel.

  4. Assign the BI Publisher defined names.

  5. Prepare the XDO_METADATA sheet.

  6. Test the template using the desktop Excel Template Builder.

Step 1: Obtain sample XML data from your data model

You need sample data in order to know your field names and the hierarchical relationships to properly mark up the template. For information on saving sample data from your report data model, see the topic 'Testing Data Models and Generating Sample Data' in the Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

Microsoft

If you do not have access to the report data model, but you can access the report, you can alternatively save sample data from the report viewer. To save data from the report viewer:

  1. In the BI Publisher catalog, navigate to the report.

  2. Click Open to run the report in the report viewer.

  3. Click the Actions menu, then click Export, then click Data. You will be prompted to save the XML file.

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

Step 2: Open the BlankExcelTemplate.xls file and save as your template name

Note: It is recommended that you install the Template Builder for Excel. For information on downloading the tool, see Desktop Tools.

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 your own Excel workbook.

Tip: If you are building a new template from an existing template, be sure to clear any existing defined names in the template sheet.

To open the BlankExcelTemplate.xls:

  1. From a Windows desktop, click Start, then Programs, then Oracle BI Publisher Desktop, then Samples, then Excel.

  2. In the Excel Templates sample folder, double-click BlankExcelTemplate.xls to open it.

  3. Save the file as your selected name in the Microsoft Excel 97-2003 Workbook format (*.xls).

Step 3: Design the layout in Excel

In Excel, determine how you want to render the data and create a sample design, as shown in the following figure:

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 will require a calculation.

Step 4: Assign the BI Publisher defined names

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 your data delimited by '?'

    • a unique name that you will 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 your XML data delimited by '?'.

    • a unique name that you will 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.

Applying a Defined Name to a Cell

  1. Click the cell in the Excel worksheet.

  2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: A8.

  3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?EMP_NAME?

  4. Press Enter.

    The following figure shows the defined name for the Employee Name field entered in the Name box:

  5. 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, the name you entered will not be maintained.

    You cannot edit the Name box while you are editing the cell contents.

    The name cannot be more than 255 characters in length.

  6. For the total salary field, a calculation will be mapped to that cell. For now, name that cell XDO_?TOTAL_SALARY?. The calculation will be 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:

After you have named all the cells for this example, the Name Manager dialog will appear as shown:

You can review all your entries and update any errors through this dialog.

Understanding Groups

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) will display in the worksheet.

  • For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department will display.

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 your data must be structured according to the groups you want to create in your template. The structure of the data for this example

establishes the grouping desired for the report.

To Create Groups in the Template

  1. Highlight the cells that make up the group. In this example the cells are A8 - E8.

  2. 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 your data. For example: XDO_GROUP_?EMPS?

  3. Press Enter.

The following figure shows the XDO_GROUP_ defined named 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.

To define the department group, include the department name cell and all the employee fields beneath it (A5-E9) as shown in the following figure:

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.

Step 5: Prepare the XDO_METADATA Sheet

BI Publisher requires the presence of a sheet called 'XDO_METADATA' to process the template. This sheet must follow the specifications defined here.

Format of the XDO_METADATA Sheet

The XDO_METADATA sheet must have the format shown in the following figure:

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 Defining BI Publisher Functions.

Creating the XDO_METADATA Sheet

If you copied the XDO_METADATA sheet in Step 2, skip this section and proceed to Adding the Calculation for the XDO_?TOTAL_SALARY? Field; otherwise, set up the hidden sheet as follows:

  1. Create a new sheet in your Excel Workbook and name it 'XDO_METADATA'.

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

  3. Skip a row and enter 'Data Constraints' in column A of row 10.

  4. In the header region, for the variable 'Template Type' enter the value: TYPE_EXCEL_TEMPLATE

Adding the Calculation for the XDO_?TOTAL_SALARY? Field

Earlier in this procedure you assigned the defined named XDO_?TOTAL_SALARY? to the cell that is to display the total salaries listed in the SALARY column. In this step, you will add the calculation to the Data Constraints section of the XDO_METADATA sheet and map the calculation to the XDO_?TOTAL_SALARY? field.

  1. In the Data Constraints section, in Column A, enter the defined name of the cell: XDO_?TOTAL_SALARY?

  2. 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 the following figure:

Step 6: Test the template

If you have installed the Template Builder for Excel, the BI Publisher tab will appear on the ribbon menu as shown in the following figure:

To preview your report using sample data:

  1. Click Sample XML. You will be prompted to select the sample data file.

  2. Click Preview.

    The sample data will be applied to your template and the output document will be opened in a new workbook. The following figure shows the preview of the template with the sample data:

Formatting Dates

Excel cannot recognize canonical date format. If the date format in your 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 your date is to use the Excel REPLACE and SUBSTITUTE functions. This option will retain the full date and timestamp. If you only require the date portion in your data (YYY-MM-DD), 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 the following figure:

To format the date as shown above, follow these steps:

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

  2. Assign the cell the defined name XDO_?HIRE_DATE? to map it to the HIRE_DATE element in the data, as shown in the following figure:

    If you do nothing else, the HIRE_DATE value will display 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.

  3. Insert a new Hire Date column. This will now be column F as shown in the following figure:

  4. In the new Hire Date cell (F8), enter one of the following Excel functions:

    • To retain the full date and timestamp, enter:

    • To retain only the date portion (YYY-MM-DD), enter:

    After you enter the function, it will populate the F8 cell as shown in the following figure:

  5. 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 the following figure:

    The sample data in the F8 cell now displays as 3-Feb-96.

  6. Finally, hide the E column, so that report consumers will not see the canonical date that is converted.

Defining BI Publisher Functions

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:

Reporting Functions

The following functions can be added to your template using the commands shown and a combination of BI Publisher syntax and XSL. A summary list of the commands is shown in the following table. See the corresponding section for details on usage.

FunctionCommands
Split the report data into multiple sheetsXDO_SHEET_?
with
XDO_SHEET_NAME_?
Define a parameterXDO_PARAM_?n?
Define a linkXDO_LINK_?link object name?
Import a subtemplateXDO_SUBTEMPLATE_?n?
Reference Java extension librariesXDO_EXT_?n?

Splitting the Report into Multiple Sheets

Note: Images are not supported across multiple sheets. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.

Use the this set of commands to define the logic to split the report data into multiple sheets:

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

Column A EntryColumn B EntryColumn 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 your XML data. The example <?.//DEPT?> will create a new sheet for each occurrence of <DEPT> in the data.

If your data is flat 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 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 your final sheet name. This example:

will name 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 will apply the naming to the first sheet in the workbook that contains XDO_ names. You would need to 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 would enter <?SHEET3?> in Column C.

Example: Splitting the data into multiple sheets

Using the employee data shown in the previous example. This example will:

  • Create a new worksheet for each department

  • Name each worksheet the name of the department with the number of employees in the department, for example: Sales-21.

  1. Enter the defined names for each cell of employee data and create the group for the repeating employee data:

    Note: Do not create the grouping around the department because the data will be split by department.

  2. Enter the following in the Data Constraints section of the XDO_METADATA sheet:

    Column A EntryColumn B Entry
    XDO_SHEET_? <?.//DEPT?>
    XDO_SHEET_NAME_? <?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>

The entries are shown in the following figure:

The following figure shows the generated report. Each department data now displays on its own sheet, which shows the naming convention specified:

Declaring and Passing Parameters

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 your data model.

Excel Templates Free Download Office 2010

To declare the parameter, use the following command:

Column A EntryColumn 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 follows:

Column A EntryColumn 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

In this example, declare and reference a parameter named Country.

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

  2. In the hidden sheet assign that cell the parameter value as follows:

Defining a Link

Use the XDO_LINK_? command to define a hyperlink for any data cell.

Column A EntryColumn 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)'/>

Example: Defining a Link

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). You can generate a report that creates a dynamic link to each invoice as follows:

  1. In your template sheet, assign the cell that is to display the INVOICE_NO the XDO defined name: XDO_?INVOICE_NO?.

  2. In the XDO_METADATA sheet, enter the following:

    Column A EntryColumn 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 in the following figure:

The report output will display as shown in the following figure. The logic defined in the XDO_METADATA sheet will be applied to create a hyperlink for each INVOICE_NO entry:

Importing and Calling a Subtemplate

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 following command:

Column A EntryColumn 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:

Column A EntryColumn 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 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 will evaluate the value of a parameter named pPayType and based on the value, return a string that indicates the payment type:

In your Excel template, you have defined a field with the XDO Defined Name XDO_?TYPE?, which will be populated based on the string returned from code performed in the subtemplate, as shown in the following figure:

Enter the following in the Data Constraints region:

Column A EntryColumn 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.

Referencing Java Extension Libraries

You can include the reference to a Java extension library in your template and then call methods from this library to perform processing in your template. Use this command to reference the Java extension libraries:

Free Excel App Download

Column A EntryColumn 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.oracle.com/XSL/Transform/java/ oracle.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 your 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. For example:

Column A EntryColumn 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 the following figure:

Formatting Functions That Rely on Specific Data Attribute Values

The following commands require that specific formatting attributes be present in the XML data file. A summary list of the commands is shown in the following table. See the corresponding section for details on usage.

FunctionCommand
Define border and underline stylesXDO_STYLE_n_?cell object name?
Skip a rowXDO_SKIPROW_?cell object name?

Defining Border and Underline Styles

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.

Column A EntryColumn B EntryColumn 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 following normal Excel style types and values:

Style TypeSupported 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 following types:

Style TypeSupported 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 following values:

Style TypeSupported 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 will be 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 will be used to apply the defined style based on logic defined in the template.

  1. In the Excel template assign the defined named XDO_?DEPT_TOTAL_SALARY? to the field that is to display the DEPT_TOTAL_SALARY from the data.

  2. In the XDO_METADATA sheet, enter the following:

    • To define the top border style, enter:

      Column A EntryColumn B EntryColumn 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, enter:

      Column A EntryColumn B EntryColumn 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, enter:

      Column A EntryColumn B EntryColumn 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.

The following figure shows the three entries in the Data Constraints region:

When you run the report, the style commands will be applied to the XDO_?DEPT_TOTAL_SALARY? cell, as shown in the following figure:

Skipping a Row

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

Column A EntryColumn 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 will suppress 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.

To suppress the display of the row of the employee data when the MANAGER attribute is set to 'True', enter the following in the Data Constraints section:

Column A EntryColumn 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 the following figure. Note that the employee Michael Hartstein is not included in the report:

Grouping Functions

Use these functions to create groupings of data in the template.

FunctionCommand
Group dataXDO_GROUP_?group element?
Regroup dataXDO_REGROUP_?

Grouping the data

Microsoft Excel 2010 Free Templates

Use the XDO_GROUP command to group flat data when your layout requires a specific data grouping, for example, to split the data across multiple sheets.

Column A EntryColumn B EntryColumn 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.

Regrouping the Data

The XDO_REGROUP regroups the data by declaring the structure using the defined names. It does not require the XSLT logic.

Column A EntryColumn B Entry
XDO_REGROUP_? XDO_REGROUP_?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 current-group() in the context of the XDO_ grouping structure.

  • 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 XDO_REGROUP_? command is used for the most inner group.

  • sortByName is the field name that you want to sort the group by. You can have multiple sortBy fields. If no sortByName is declared, the data from the XML file will not be sorted.

The following shows an example of how to create three nested groupings:

Column A EntryColumn B Entry
XDO_REGROUP_? XDO_REGROUP_?PAYMENTSUMMARY_Q1?PAYMENTSUMMARY_Q1?PAY_TYPE_NAME?

In the above definition, the most outer group is defined as PAYMENTSUMMARY_Q1, and it is grouped by PAY_TYPE_NAME

Column A EntryColumn B Entry
XDO_REGROUP_? XDO_REGROUP_?COUNTRYGRP?XDO_CURRGRP_?COUNTRY?

The above definition creates a second outer group. The group is assigned the name COUNTRY_GRP and it is grouped by the element COUNTRY.

Column A EntryColumn B Entry
XDO_REGROUP_? XDO_REGROUP_?STATEGRP?XDO_CURRGRP_?STATE?

This definition creates the inner group STATEGRP and it includes a sortByName parameter: STATE.

Preprocessing the Data Using an XSL Transformation (XSLT) File

For the best performance, you should design your data model to perform as much of the data processing as possible. When it is not possible to get the required output from data engine, you can preprocess the data using an XSLT file that contains the instructions to transform the data. Some sample use cases may be:

  • 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:

  1. Create the file and save as .xsl.

  2. Upload the file to the report definition in the BI Publisher catalog, as you would a template:

    1. Navigate to the report in the catalog.

    2. Click Edit.

    3. Click Add New Layout.

    4. Click Upload.

    5. Complete the fields in the Upload dialog and select 'XSL Stylesheet (HTML/XML/Text)' as the template Type.

    6. After upload, click View a List. Deselect Active, so that users will not see this template as an option when they view the report.

      Note: For testing purposes, you may 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.

    7. Save the report definition.

  3. In your 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

Using the Template Viewer to Debug a Template

If your template preview is not generating the results expected, 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 this can be a very useful debugging tool.

The Template Viewer is installed when you install the Template Builder for Word; see Desktop Tools for more information.

To preview with the Template Viewer and view log messages:

  1. Open the Template Viewer:

    From your Windows desktop, click Start, then Programs, then Oracle BI Publisher Desktop, then Template Viewer.

  2. Click Browse to locate the folder that contains your sample data file and template file. The data file and template file must reside in the same folder.

  3. Select Excel Templates. The Data and Template regions will display all .xml files and all .xls files present in the directory.

  4. Click the appropriate data and template files to select them.

  5. Select the log level.

  6. From the Output Format list, select Excel.

  7. Click Start Processing.

    The Template Viewer will merge the selected data with the selected template and spawn the appropriate viewer. View any log messages in the message box as shown in the following figure:

To view the generated XSL:

  1. In the Template Viewer, select the data and template files and choose Excel output.

  2. On the Tools menu, select Generate XSL file from and then choose Excel Template.

  3. At the prompt, save the generated XSL file.

  4. Navigate to the saved location and open the XSL file in an appropriate viewer.


Copyright © 2010, 2011, Oracle and/or its affiliates. All rights reserved.