Templates

Report Templates Overview

To publish a report, Report Manager requires a layout template for the report. Templates are stored in the XML Publisher Template Manager as extensible stylesheet language formatting objects (XSL-FO) documents. The style information from the XSL-FO template is applied to the data in the report and controls the format of the information.

Templates are provided for some Oracle E-Business Suite standard, variable, and financial reports. You can modify the provided templates, or you can create new templates. For reports that do not provide a template, you must create a new template using one of the methods described in this chapter.

Report Types and Templates

For Financial reports, Report Manager provides the Financial Report Template Editor. Use this tool to create and modify templates in Microsoft Excel and then upload them to the Template Manager. At runtime, when you select your template, the report data is published in Excel, in the format you defined. For more information, see Using the Financial Report Template Editor.

If you prefer a Microsoft Word-based template, you can follow XML Publisher's standards for Rich Text Format (RTF) templates. Upload these templates directly through XML Publisher's Template Manager.

Standard and variable reports require an RTF template based on the XML Publisher standards. For more information, see Creating RTF Report Templates.

Generic Templates

Oracle Report Manager provides three generic templates that you can use to publish any report. These templates are called "Generic FSG Excel Template", "Generic FSG Template", and "Generic FSG Drill Template".

You cannot edit the standard generic templates provided by Oracle Report Manager. However, you can create custom generic templates that you can use to publish any report.

Tip: The standard generic templates include a default image. If you do not want to include this image in your reports, delete the financial_collage.gif file from the OA_MEDIA virtual directory on your Web server. For information on the OA_MEDIA directory in your installation, see your Web server administrator.

For a custom report that you define, you can either use a standard or custom generic template or create a new custom template for that specific report. Note that a custom report-specific template can only be used with the report for which it was created; it cannot be used for other reports.

Using the Financial Report Template Editor

Prerequisites and Setup

See Setup Overview for specific setup steps required for the Financial Report Template Editor.

Overview of Financial Report Templates in Excel

You can create or edit Financial Statement Generator (FSG) templates using Microsoft Excel.

When you choose to create a new custom generic template, the template editor provides default formatting based on the standard generic templates. When you choose to create a new custom template for a specific report, the template editor retrieves the structure of the chosen report and provides standard static data at the top of the report template in Excel as well as row and column details.

The template that is generated in Excel is fully functional. It can be uploaded immediately to the Template Manager using the Oracle ribbon tab and used to publish your report.

To build a template, choose Financial Report Template Editor from the top level menu in the Oracle Report Manager pages, or choose the Financial Report Template Editor menu item from a responsibility provided by your system administrator. A new template is fully formatted and ready to work with.

For a custom generic template, you can make the following changes:

For a custom report-specific template, you can make the following changes:

The Oracle ribbon tab in Microsoft Excel provides the option to upload your completed template. For generic templates, the Oracle ribbon tab also contains options to change the formatting of the main regions of an FSG report. For report-specific templates, the Oracle ribbon tab also contains options for adding components, moving items, and deleting items.

Oracle Ribbon Tab in Microsoft Excel for Editing a Generic Template

the picture is described in the document text

Oracle Ribbon Tab in Microsoft Excel for Editing a Report-Specific Template

the picture is described in the document text

When you create a custom report-specific template, you can select the language for the template. When you upload the template, you specify the language for which the Financial Report Template Editor registers the template.

Generic templates do not allow for any language-specific customizations. Consequently, custom generic templates are always created for the English language irrespective of your session language. However, similarly to the standard generic templates, your custom generic templates can be used to publish and render reports in any language session.

Using the FSG Template Editor

  1. Choose Financial Report Template Editor from the top level menu in the Oracle Report Manager pages, or choose the Financial Report Template Editor menu item from a responsibility provided by your system administrator.

  2. Select the Template Type, either Custom for a report-specific template or Generic for a generic template.

  3. For a report-specific template only, enter the Report Name, or select the report from the list.

  4. Specify whether you want to create a new template or modify an existing template. To modify an existing template, select it from the list.

    Note: You can only edit custom generic or report-specific templates that you created. You cannot edit the standard generic templates.

  5. For a report-specific template only, select the Language for the template.

  6. Select Excel Template Editor from the Editor Selection list.

  7. Select Create. Oracle Report Manager initially creates the Microsoft Excel file in the binary Microsoft Excel format as an .xls file. Your browser prompts you to download the file "ReportManager.xls". Select Open, and if necessary, enable the Oracle Report Manager macros when prompted by Microsoft Excel. The Microsoft Excel spreadsheet opens and a small window opens that initiates the formatting of the spreadsheet. Do not close the window or use Microsoft Excel until this window indicates that formatting is complete.

    Note: If you save the report spreadsheet and open it again later, and the Oracle E-Business Suite session used to create the spreadsheet is no longer active, then the Oracle E-Business Suite login page appears, and you must log in and select a responsibility to continue working with the spreadsheet.

  8. Accept the default layout, or edit the Excel template as desired. See Template Editing Features for information on layout options for your template.

  9. When you are ready to upload, select the Oracle ribbon tab and then select Upload.

    You will be prompted for Upload Parameters including Template Name, Description, and, for report-specific templates only, Language.

    • For a custom generic template, the maximum length of the template name is 64 characters. For a custom report-specific template, the maximum length of the template name is 75 characters.

    • The list of values for the Language field displays the installed and base languages for your Oracle E-Business Suite instance.

      If you select an installed language that is associated with more than one territory, the Financial Report Template Editor registers the template for each language and territory combination.

      If you create a template in multiple languages, you must specify a different template name to store the template in each language. You cannot reuse the same template name.

    • For reports that include Oracle General Ledger data, you can optionally select the Enable Drill option to include drilldown links that allow users to drill into report values using the Account Analysis and Drilldown feature. For more information, see the Oracle General Ledger User's Guide.

    • If you specify a template name that already exists, you can choose to Overwrite Existing Template.

    Oracle Report Manager stores the template definition in the XSL-FO format, in the Microsoft Excel Office Open XML (OOXML) format as an .xlsx file, and in the Microsoft Excel binary format (non-OOXML) as an .xls file.

Template Editing Features

This section describes the layout features that are available to customize the report presentation in a template.

Template Editing Features for Generic Templates and Report-Specific Templates

The following features can be customized in both generic and report-specific templates.

Changing Font Style, Size, Color, and Alignment

Use Excel standard functionality to customize font style, font size, colors, and alignment. These changes can be applied to the five areas of the FSG report: Report Title, Report Heading, Column Heading, Line Items, and Amounts.

Inserting Images

For a generic template, you can insert a different image to replace the standard logo image. You cannot insert additional images. For a report-specific template, you can insert images at any location in the template.

Note: For a generic template, the size of the custom logo image must match the size of the logo image used in the standard generic templates.

Use standard Excel functionality to insert images in your template. The following image types are supported: PNG, GIF, JPEG, and BMP (Microsoft converted).

Before inserting images, you must select the Cut, copy, and sort inserted objects with their parent cells option in Microsoft Excel to enable images in the template to be included in the reports published with that template. The steps to do so may vary depending on your version of Microsoft Excel. For example:

To select the required image setting in Microsoft Excel 2010, 2013, and 2016:

  1. Choose the File tab, and then choose Options. In the Excel Options dialog box, choose Advanced and then Cut, copy, and paste.

  2. Select the Cut, copy, and sort inserted objects with their parent cells option.

After selecting this setting, you can insert images into your template.

To insert an image:

  1. Click on the cell where you want to position the top left hand corner of your image.

  2. From the Insert tab, select Picture, and then select the image to insert into your template.

  3. For a report-specific template, size the image as desired.

Note: Due to a limitation in the handling of image resizing, it is recommended that you do not modify the default font of the cells underneath the image.

Note: Watermarks and background images are not supported.

Template Editing Features for Report-Specific Templates Only

The following features can be customized only in report-specific templates. All the report elements from the report definition are presented in the Excel spreadsheet for a report-specific template.

Adding, Deleting, and Moving Report Objects

Use the Oracle menu to perform the following edits to the generated template:

Note that the Insert Fields button group on the Oracle tab displays available report objects and data fields that you can insert into your template. If you are creating a new template, Report Manager builds the default template with all the available report objects and data fields. If you are modifying an existing template, then report objects and data fields may appear in the Insert Fields button group that are not present in the template. You can add these objects to your report.

Changing Row and Column Sizes

Use Excel standard functionality for changing row heights and column widths in your template.

Changing General Page Settings

Use Excel standard functionality to modify the following page settings:

Cell Formatting

You can apply many of the native Microsoft Excel cell formatting options to a particular cell or group of cells in the template, and at runtime the formatting will be applied to all cells that correspond to the data intersection.

Formatting Negative Numbers

To apply special formatting to the appearance of negative numbers in your report, you can use the Excel functionality as follows:

  1. Select the cells for which you want the negative number formatting applied.

  2. From the Excel Home tab, select Format and then select Format Cells.

  3. Choose the Number tab.

  4. From the Category list select Number, and then select the format desired in the Negative Numbers display box.

At runtime, any negative numbers appearing in the column will display in the format you selected.

Transferring Custom Financial Statement Generator Report Templates Between Instances

You can use a concurrent program named Report Manager - Transfer FSG Custom Templates to transfer custom Financial Statement Generator (FSG) report templates, including custom report-specific templates as well as custom generic templates, between Oracle E-Business Suite instances. For example, you can use this program to transfer templates from a test instance to a production instance.

Before you can run the Report Manager - Transfer FSG Custom Templates program, a system administrator must add the program to the request group for a responsibility assigned to you. See Add the Report Manager - Transfer FSG Custom Templates Program to a Request Group.

To prepare for a transfer, you must define a database link between the source instance and the target instance. Then identify the templates on the source instance that you want to transfer to the target instance.

You can transfer a single template, a group of templates whose names begin with the same value, or all custom templates in the source instance.

Note: When you create a template in multiple languages, you must specify a different template name for the version of the template in each language. Consequently, to transfer all the versions of the template, you must include all the relevant template names.

A template definition usually includes an XSL-FO template type as well as one or both of the following template types:

The program transfers all of these template types that exist in the source instance for the specified template. However, if the template definition includes a corporate logo image, then you must manually copy that image to the $OA_MEDIA/FrmFsgImages directory for the target instance.

Note: This program does not transfer Rich Text Format (RTF) templates. For information on uploading RTF templates, see the Oracle XML Publisher Administration and Developer's Guide, available from My Oracle Support Knowledge Document 1640073.1, Notes for Using Oracle Business Intelligence Publisher 10g in Oracle E-Business Suite Release 12.2.

Additionally, this program transfers only custom templates. To add or upgrade templates provided by Oracle, apply the patches containing those template definitions.

If the target instance already contains a template whose name matches a template being transferred from the source instance, you can choose whether to overwrite or preserve the existing template in the target instance. If you choose to overwrite an existing template in the target instance, then after performing the transfer you may need to resubmit reports that use this template to obtain updated output, depending on the output formats you need.

Note: If you transfer custom generic templates, ensure that the R12.ATG_PF.C.Delta.9 Release Update Pack or later has been applied on the target instance to support the use of those templates.

Defining a Database Link

On the target instance, define a database link to the source instance using the Define Database Links window in Oracle General Ledger.

Note: The source and target instances between which you transfer templates must be at the same Oracle E-Business Suite release level. For example, if the source instance is on Release 12.2.9, then the target instance should also be on Release 12.2.9.

  1. From your Oracle General Ledger responsibility, choose Setup > System > Database Links. In the Define Database Links window, choose the New Database Link button.

  2. In the Create Database Link window, enter the database name, description, TNS connect string, and domain name for the source instance, as well as the user name and password for the APPS schema for that instance. The TNS connect string must be entered as a single text string without any spaces, carriage returns, or other breaks. After entering the database link properties, choose OK.

Identifying Templates to Transfer

On the source instance, identify the custom templates that you want to transfer to the target instance. You can use the Template Selection: Modify Existing field in the Financial Report Template Editor to search for existing templates. See Using the FSG Template Editor.

You can also use the following SQL command to obtain a list of custom templates in the source instance.

SELECT nvl(substr(template_name,1,regexp_instr(template_name,'_XLS$')
      + regexp_instr(template_name,'_XLSX$') - 1),
      template_name) unique_template_name,
   template_name,
   template_id,
   template_code,
   creation_date,
   last_update_date
FROM xdo_templates_vl
WHERE ds_app_short_name = 'SQLGL'
   AND data_source_code = 'RGRARG'
   AND start_date <= SYSDATE
   AND (end_date IS NULL OR end_date >= SYSDATE)
   AND created_by > 1000
   AND template_type_code IN ('XLS', 'XSL-FO')
ORDER BY 1;

Transferring Templates

  1. On the target instance, navigate to the responsibility whose request group includes the Report Manager - Transfer FSG Custom Templates program, and submit a concurrent request for this program. For example, if you are using the System Administrator responsibility, choose Requests > Run and select the Single Request option. Specify the following parameters for the program:

    • Source Template Name - Use one of the following options to specify the templates in the source instance that you want to transfer to the target instance.

      • Enter the full name of a template to transfer that specific template. For example: Summary Income Statement

      • Enter a partial value to transfer all templates in the source instance whose names begin with that value. For example: Summary%

      • Leave this field blank to transfer all custom templates in the source instance.

      Note: This field is case-sensitive.

    • Database Link - Select the database link that you defined to connect to the source instance.

    • Overwrite Template - Specify how to proceed when a template with the same name already exists in the target instance. Select Yes to transfer the template definition from the source instance and overwrite the previous template definition in the target instance. Select No to skip the transfer for this template and preserve the existing template definition in the target instance.

    • Logging Level - Select the level of detail to record in the concurrent request log file. The available logging levels, in order from the highest level recording the least detail to the lowest level recording the most detail, are Unexpected, Error, Exception, Event, Procedure, and Statement. For production instances, the recommended logging level is Error. You can use lower logging levels for testing and debugging purposes if needed. For more information on the logging levels, see AFLOG_LEVEL, Oracle E-Business Suite Developer's Guide.

  2. After the concurrent request completes, check the request output file to review the results of the transfer operation. If the output file includes error or warning messages, check the request log file for additional information.

  3. If a template you transferred includes a corporate logo image, copy that image from the $OA_MEDIA/FrmFsgImages directory of the source instance to the $OA_MEDIA/FrmFsgImages directory of the target instance. If the FrmFsgImages directory has not been created yet on the target instance, then you should create it now. See Creating FrmFsgImages Directory and Setting Permissions.

    Additionally, to enable the image to be rendered in HTML or PDF report output, you must also restart the application tier services using the adstpall.sh and adstrtal.sh scripts. See Commands to Manage Oracle E-Business Suite Service Processes, Oracle E-Business Suite Setup Guide.

Using Transferred Templates

After performing the transfer, you can submit reports in the target instance using the transferred templates.

If you chose to overwrite an existing template in the target instance with a different version of that template from the source instance, then you may need to perform an additional step for reports that use this template, depending on the output formats you need.

Creating RTF Report Templates

Oracle Report Manager utilizes Oracle XML Publisher's RTF templates for standard and variable reports.

Rich Text Format (RTF) templates are created in Microsoft Word. You can use many native Microsoft Word features to apply to your report template to achieve richly formatted reports directly out of Oracle E-Business Suite. Oracle XML Publisher provides a plug-in to Microsoft Word that enables you to quickly insert and manipulate report elements in your Word document.

For information on creating or modifying RTF templates, see the Oracle XML Publisher Report Designer's Guide. For information on uploading the templates to the Template Manager to make them available to the Report Manager, see the Oracle XML Publisher Administration and Developer's Guide. These guides are available from My Oracle Support Document 1640073.1, Notes for Using Oracle Business Intelligence Publisher 10g in Oracle E-Business Suite Release 12.2.