6Modify Reports

This chapter contains the following:

Overview of Business Intelligence Publisher

Oracle Business Intelligence (BI) Publisher allows you to author, generate, and deliver all types of highly formatted documents and operational reports. You can use familiar office desktop tools to create rich text format (.rtf) documents and reports against any data source. You can view reports, schedule them to run, and distribute them to predefined destinations.

A BI Publisher report consists of one or more .rtf layouts, a reference to a data model, and a set of properties. A report may also include style templates and subtemplates, which allow reusability of functionality and maintain consistency of design. In the logical architecture depicted in the figure, the report includes a data model and a layout, and the layout references two style templates in the catalog which in turn reference subtemplates.

In the BI Publisher logical architecture, the data model accesses data sources and the layout and its optional style template and subtemplates determine the formatting of the report output, which can include email, print, fax, or files, and others. The layout is designed using common office applications, for example Microsoft Word and Excel and Adobe Acrobat and Flash, among others.

BI Publisher logical architecture

Style Templates

A style template is a .rtf template that contains style information that can be applied to report layouts.

A style template is a .rtf template that contains style information that can be applied to report layouts at runtime to achieve a consistent look and feel across your enterprise reports. You associate a style template to a report layout in the report definition. Using a style template has the following benefits:

  • Enables the same look and feel across your reports

  • Enables consistent header and footer content, such as company logos, headings, and page numbering

  • Simplifies changing elements and styles across all reports

Use style templates to define paragraph and heading styles, table styles, and header and footer content.

Paragraph and Heading Styles

When the named style is used in a report layout, the report layout inherits the following from the style template definition:

  • font family

  • font size

  • font weight (normal, bold)

  • font style (normal, italic)

  • font color

  • text decoration (underline or strike through)

Table Styles

Style elements inherited from the table style definition include:

  • font style

  • border style

  • line definition

  • shading

  • text alignment

Header and Footer Content

The header and footer regions of the style template are applied to the report layout. This includes images, dates, page numbers, and any other text-based content. If the report layout also includes header and footer content, then it is overwritten.

Subtemplates

A subtemplate is a piece of formatting functionality in .rtf or .xsl format that can be defined once and used multiple times within a single layout template or across multiple report layout template files. Rich Text Format (RTF) subtemplates are easy to design using native features in Microsoft Word, while subtemplates using Extensible Stylesheet Language (XSL) can be used for complex layout and data requirements. Both .rtf and .xsl subtemplates are stored in the business intelligence catalog as a subtemplate object, and both can be called from .rtf layout templates.

Some common uses for subtemplates include:

  • Reusing a common layout or component (such as a header, footer, or address block)

  • Handling parameterized layout

  • Handling dynamic or conditional layouts

  • Handling lengthy calculations or reusing formulas

RTF Subtemplates

An RTF subtemplate is an RTF file that consists of one or more <?template:?> definitions, each containing a block of formatting or commands, that when uploaded to Business Intelligence (BI) Publisher as a subtemplate object in the catalog can be called from within another RTF template.

XSL Subtemplates

An XSL subtemplate is an XSL file that contains formatting or processing commands in XSL for the Business Intelligence Publisher formatting engine to execute. Use an XSL template to include complex calculations or formatting instructions not supported by the RTF standard.

How You Create and Edit Reports

Use reports to generate and print documents for internal operations, external business transactions, or legal requirements. To meet specific requirements, you must create or edit reports to capture different data, or present data in another way.

Report Components

Each report has components that you can modify, as described in this table:

Report Component Description Tool for Modifying

Data model

Defines the data source, data structure, and parameters for the report. Multiple reports can use the same data model. Each report has one data model.

Data model editor in the application

Layout

Defines the presentation, formatting, and visualizations of the data. A report can have multiple layouts. Different types of layout templates are available, for example Excel and RTF.

Depending on the template file type:

  • XPT: Layout editor in the application

  • RTF: Microsoft Word

  • PDF: Adobe Acrobat Professional

  • Excel: Microsoft Excel

  • eText: Microsoft Word

Properties

Specifies formatting and other settings for the report.

Report editor in the application

What You Can Create or Edit

This table gives just a few examples of creating or editing reports.

Task Example

Edit the layout of a report.

Add your company logo to the report output.

Add a new layout to a report.

Design a new layout template that provides less detail than the existing template.

Edit a data model.

Add two fields to the data model used by a report so you can add those new fields to a layout for the report.

Create a new report based on a new data model.

Create a new data model based on data from an external system, and create reports using the data model.

How You Access and Modify Report Components

To create or edit reports, you must access the business intelligence (BI) catalog. In the catalog, objects of type Report represent the report definition, which includes report properties and layouts. Data models are separate objects in the catalog, usually stored in subfolders called Data Models.

Accessing the BI Catalog

You can access the BI catalog in any of the following ways:

  • In the Reports and Analytics work area, click Browse Catalog to open the BI catalog, and find your report or data model in the Folders pane.

  • In the Reports and Analytics work area, find your report and select More to go to the report directly in the catalog. The data model associated with the report should be in the Data Models subfolder within the same folder as the report.

  • Sign in to the application directly (for example: http://host:port/analytics/saw.dll) to open the catalog.

  • Sign in to the BI Publisher server directly (for example: http://hostname.com:7001/xmlpserver) to open the catalog.

    • Alternatively, once you are in the catalog using another method, for example, through the Reports and Analytics work area, change the final node of the URL. For example, change (http://host:port/analytics/saw.dll) to xmlpserver. So the URL you use would be: http://host:port/xmlpserver.

Predefined Reports

A special Customize option is available only:

  • For predefined reports, not data models.

  • Through direct access to the BI Publisher server using the /xmlpserver URL. When you find your report in the BI catalog, select Customize from the More menu.

The Customize option automatically creates a copy of a predefined report and stores it in the Shared Folders > Custom folder within the catalog. The new report is linked to the original, so that when users open or schedule the original, they are actually using the copied version.

If you don't have access to the Customize option or don't want the original version linked to the new report, make a copy of the predefined report and save it in the Custom folder.

Predefined Data Models

Don't edit predefined data models. Instead, copy the data model into the Custom folder and edit the copy. You can't create a new data model based on the transactional tables.

Using the Customize Option for Predefined Reports: Points to Consider

The Customize option automatically creates a copy of a predefined report and stores it in the Shared Folders > Custom within the business intelligence (BI) catalog. The copy includes the report definition, folder structure, and original report permissions, and is linked internally to the original report. You can edit the copy of the report, leaving the original report intact. When users open or schedule the original report, they are actually using the newer version.

Benefits of the Customize Option

In addition to conveniently copying a predefined report to the Custom folder, the Customize option:

  • Makes it unnecessary to update processes or applications that call the report. For example, if the original report is set up to run as a scheduled process, you don't need to change the setup. When users submit the same scheduled process, the newer report runs instead of the original.

  • Automatically copies the security settings of the original report.

  • Removes the risk of patches overwriting your edits. If a patch updates the original report, the newer report is not updated in any way.

    Note: The report still references the original data model. The data model is not copied. A patch that updates the data structure of the data model might affect your report.

Accessing the Customize Option

To access the Customize option:

  1. Sign in to the BI server (for example, http://hostname.com:7001/xmlpserver).

  2. In the Folders pane, select the predefined report.

  3. Select Customize from the More menu for the report.

  4. The copied report in the Custom folder opens, so proceed to edit this report.

To edit the copied report again later, you don't need to be in the BI server. Just go to the BI catalog and either:

  • Select the Customize or Edit option for the original report.

  • Find your report in the Custom folder and select Edit.

How Links Between Original and Modified Reports Are Managed

The Customize option for predefined reports creates a copy of the report that is linked to the original. Consider the following points when you work with both the original and modified versions.

Maintaining the Link Between Reports

The link between the predefined and modified report is based on the name of the modified report and its location within the Custom folder in the business intelligence (BI) catalog.

  • If you manually create a report with the same name as a predefined report, and give it the same folder path in the Custom folder, then the new report becomes a version of the original. It would be as if you had used the Customize option to create a copy of the predefined report.

  • You can edit the report so that it uses a different data model. But if the original data model is updated later, then your newer report doesn't benefit from the change.

Caution: The link to the original report breaks if you rename the modified or original report.

Tasks Performed on Original Reports

This table describes what happens when you use the original report and a corresponding copied report exists.

Task Performed on the Original Report Result When There Is a Copied Report

Open

Opens the copied report.

Schedule

Creates a report submission for the copied report.

Edit

Edits the copied report.

Delete

Deletes the original report only. If you delete the copied report, the original report is not deleted.

Copy

Copies the original report.

Cut and Paste

Cuts and pastes the original report.

Rename

Renames the original report. The copied report name is not changed.

Caution: This breaks the link between the original and copied reports.

Download

Downloads the copied report.

Customize

Edits the copied report.

History

Opens the job history of the copied report.

Layouts

Create and Edit Report Layouts

The layout determines what and how data is displayed on report output. Each report has at least one layout template. This topic describes the following aspects of report templates:

  • Layout templates

  • Layout template types

  • Overall process of managing layouts

  • Deleting layout templates

Layout Templates

To modify a layout, you edit the layout template, which:

  • Defines the presentation components, such as tables and labeled fields.

  • Maps columns from the data model to these components so that the data is displayed in the correct place.

  • Defines font sizes, styles, borders, shading, and other formatting, including images such as a company logo.

Layout Template Types

There are a few types of template files to support different report layout requirements.

  • RTF: Rich text format (RTF) templates created using Microsoft Word.

  • XPT: Created using the application's layout editor, these templates are for interactive and more visually appealing layouts.

  • eText: These templates are specifically for Electronic Data Interchange (EDI) and electronic funds transfer (EFT) information.

You can also create and edit other types of templates using Adobe PDF, Microsoft Excel, Adobe Flash, and XSL-FO.

Overall Process to Create or Edit Layouts

Editing or creating report layout, for example using Microsoft Word or the layout editor, involves making the actual changes to the template file. But that task is just one part of the entire process for modifying layouts.

  1. Copy the original report and save the new version in Shared Folders > Custom in the business intelligence (BI) catalog. You create or edit templates for the new copy of the report.

    Tip: You can use the Customize option if the original is a predefined report.
  2. Review report settings for online viewing.

  3. Generate sample data for the report.

  4. Edit or create the layout template file.

  5. Upload the template file to the report definition. Skip this step if you're using the layout editor.

  6. Configure the layout settings.

Deleting Layout Templates

To remove a layout template for a report:

  1. Select your report in the BI catalog and click Edit.

  2. In the report editor, click View a list.

  3. Select the layout template and click Delete.

Making Reports Available for Online Viewing: Procedure

Some reports are set up so that you can only view them through another application or submit them as scheduled processes. To view your report online while you're editing it, you must define a few settings. When you're done editing your report, make sure that you reset these settings as needed.

Updating Report Properties

  1. Select your report in the business intelligence catalog and click Edit.

  2. In the report editor, click Properties.

  3. In the Report Properties dialog box, select Run Report Online and deselect Report is Controlled by External Application.

Updating Layout Settings

  1. Back in the report editor, click View a list.

  2. Make sure that the View Online check box is selected.

Generating Sample Report Data: Procedure

Depending on the type of report layout changes you're making, sample data can be required or optional. You generate sample data, and then load it for use with your layout so that you can map data fields to layout components. For example, for the Start Date table column in your layout, you can set it so that the data displayed in that column comes from the Start Date field in the sample data.

You can generate sample data from the:

  • Report data model

  • Report viewer

  • Scheduler

Generating Sample Data from the Data Model

Follow these steps:

  1. Select your data model in the business intelligence (BI) catalog and click Edit. Alternatively:

    1. In the catalog, find the report to generate sample data for and click Edit.

    2. Click the data model name in the report editor.

  2. In the data model editor, click View Data.

  3. Enter values for any required parameters, select the number of rows to return, and click View.

  4. To save the sample data to the data model, click Save As Sample Data.

    If you're designing a .rtf template, click Export to save the file locally.

  5. Save the data model.

Saving Sample Data from the Report Viewer

For reports that are enabled for online viewing, you can save sample data from the report viewer:

  1. Select the report in the BI catalog.

  2. Click Open to run the report in the report viewer with the default parameters.

  3. On the Actions menu, click Export, then click Data.

  4. Save the data file.

Saving Sample Data from the Scheduler

For reports that are enabled for scheduling (not necessarily as a scheduled process), you can save sample data from the scheduler:

  1. Select the report in the BI catalog.

  2. Click Schedule.

  3. On the General tab, enter values for any report parameters.

  4. On the Output tab, ensure that Save Data for Republishing is selected.

  5. Click Submit.

  6. Open the Report Job History page.

  7. On the global header, click Open, then click Report Job History.

  8. Select your report job name in the Job Histories table.

  9. On the details page, under Output and Delivery, click the XML Data Download icon button.

Layout Templates

Create and Edit Report Layout Templates Using the Layout Editor

The layout editor in the application provides an intuitive, drag-and-drop interface for creating pixel-perfect reports with PDF, RTF, Excel, PowerPoint, and HTML output. The layout template files you create with this tool have an .xpt extension. The layout editor tool is the only editing tool that provides dynamic HTML output. Users can interact with this output in a browser, for example by sorting, applying filters, and so on.

Prerequisite

Make sure that sample data is generated from the data model that your report is using.

Using the Layout Editor

To create or edit XPT templates:

  1. Select the report in the business intelligence (BI) catalog and click Edit.

  2. In the report editor, click Edit to update a template.

    Or, click Add New Layout and select a template type in the Create Layout section.

  3. Create or edit the layout.

  4. Click Save to save the layout to the report definition.

Set Up for RTF and Excel Report Layout Templates

You can use Microsoft Word or Microsoft Excel to create or edit RTF and Excel layout templates, in addition to the layout editor in the application. If you use Word or Excel directly, you must download and install the appropriate add-in so that the Microsoft application has the features you need to design report layouts.

Note: If you're designing a new layout for your report, consider using the layout editor instead unless you are an experienced layout designer.
Installing the Add-In

  1. Open the Reports and Analytics work area.

  2. Click the Browse Catalog button.

  3. Click Home.

  4. In the Get Started pane, click Download BI Desktop Tools.

  5. Select the add-in for the type of template you're working with.

    • Template Builder for Word: RTF templates

    • Analyzer for Excel: Excel templates

  6. Save and then run the installer.

Protecting Reports by Using Passwords

You can assign a password to your report and protect it from unwanted access. You can do this by setting the runtime properties at the server level using the Runtime Configuration page. The same properties can also be set at the report level, from the report editor's Properties dialog. If different values are set for a property at each level, then report level takes precedence.

The following table describes the properties you can use to set passwords for reports in different formats.

Report Format Property Name Description Default Configuration Name

DOCX output

Open password

Use this property to specify the password that report users must provide to open any DOCX report.

NA

docx-open-password

PPTX output

Open password

Use this property to specify the password that report users must provide to open any PPTX report.

NA

pptx-open-password

XLSX output

Open password

Use this property to specify the password that report users must provide to open any XLSX output file.

NA

xlsx-open-password

Creating and Editing RTF Report Layout Templates: Procedure

An RTF template is a rich text format file that contains the layout instructions to use when generating the report output. Use Microsoft Word with the Template Builder for Word add-in to design RTF templates.

Prerequisites

Install the Template Builder for Word add-in, and generate sample data.

Using Template Builder for Word

To modify an RTF template:

  1. If you are editing an existing layout:

    1. Select your report in the business intelligence catalog and click Edit.

    2. In the report editor, click the Edit link of the layout to download the RTF file.

    If you are creating a new layout, skip this step.

  2. Open the downloaded RTF template file in Microsoft Word. Or, if you're creating a new template, just open Microsoft Word.

  3. Load the sample data that you generated.

  4. Edit or create the layout template.

  5. Save the file as Rich Text Format (RTF).

eText Report Layout Templates: Explained

An eText template is an RTF-based report template that is used for Electronic Funds Transfer (EFT) and Electronic Data Interchange (EDI). The template is applied to an input XML data file to create a flat text file that you transmit to a bank or other organizations. Use Microsoft Word to create or edit eText templates.

File Format

Because the output is for electronic communication, not printing, you must follow specific format instructions for exact placement of data on the template. You design eText templates using tables.

  • Each record is represented by a table.

  • Each row in a table corresponds to a field in a record.

  • The columns of the table specify the position, length, and value of the field.

Special Commands

You must set up special handling of the data from the input XML file. This table describes the two levels of handling and where you declare the corresponding commands.

Level Example Setup

Global

Character replacement

Sequencing

Declare global commands in separate setup tables.

Record

Sorting

Declare functions in command rows, in the same table as the data.

Upload the Layout Template File to the Report Definition

If you're creating or editing a report layout using the layout editor, the layout is automatically saved to the report definition, so you can skip this step. For all other layout types, for example RTF, upload the template file to the report definition after you're done making layout changes.

Uploading the Template File

  1. Select your report in the business intelligence catalog and click Edit.

  2. In the report editor, click View a list.

  3. In the table that lists the layouts, click Create.

  4. Click Upload in Upload or Generate Layout.

  5. In the Upload Template File dialog box:

    1. Enter a layout name.

    2. Browse for and select the layout template file that you created or edited.

    3. Select the template file type.

    4. Select the locale, which you can't change once the template file is saved to the report definition.

    5. Click Upload.

  6. Save the report definition.

Configuring Layout Settings for Reports: Procedure

As part of creating or editing layout, you can set report properties related to layout. These settings determine, for example, which layouts users can choose from when viewing or scheduling the report. The settings apply only to your report.

Setting Layout Properties

  1. Select your report in the business intelligence catalog and click Edit.

  2. In the report editor, click View a list.

  3. Set layout properties, some of which are described in this table.

    Setting Usage

    Output Formats

    Depending on the requirements for a report, you may want to limit the output file formats (for example. PDF or HTML) that users can choose. The available output formats vary depending on the template file type.

    Default Format

    When multiple output formats are available for the report, the default output format is generated when users open the report in the report viewer.

    Default Layout

    When multiple layouts are available for the report, you must select a default layout to present it first in the report viewer.

    Active

    Active layouts are available for users to choose from when they view or schedule the report.

    View Online

    Select this check box so that layouts are available to users when they view the report. Otherwise, the layout is available only for scheduling the report.

  4. Click Save Report.

Data Models

Modify Data Models

A data model defines where data for a report comes from and how that data is retrieved. If a data model can't give you all the data that you need in your report, then you can either copy and edit an existing data model or create a new one. You must be a BI Administrator to create new data models.

Creating a Data Model

  1. In the business intelligence (BI) catalog, click the New button and select Data Model in Published Reporting.

  2. Optionally click the Data Model node in the Data Model pane to set properties for the data model.

  3. Click the Data Set node in the Data Model pane to create or edit data sets, which determine where and how to retrieve data.

  4. Click the New Data Set button and select a data set type. It's best practice to use the BI repository as a data source, so you should select either:

    • Oracle BI Analysis: To use columns from a selected analysis.

    • SQL Query: To use a Query Builder tool to define what to use from the repository. Select Oracle BI EE as the data source.

  5. Optionally, to limit the data included in the report output, click the Parameters node in the Data Model pane to define variables that users can set when they use the report.

    Note: The order of parameters is important if there are job definitions defined for reports that use your data model. If you change the order in the data model, you must also update the job definitions.
  6. Optionally, define other components of the data model.

  7. Click Validate to validate your data model. Errors and warnings pertaining to query performance are displayed. In case of errors in validation, the model is usable in development, but will not be available in production until errors are resolved.

  8. Save your data model.

Editing a Data Model

  1. To edit a predefined data model:

    1. Find the data model in the BI catalog and click Copy.

    2. Paste within Shared Folders > Custom in a subfolder that has a folder path similar to the folder that stores the original data model.

    3. For the data model you pasted, click More, and select Edit.

  2. Optionally click the Data Model node in the Data Model pane to set properties for the data model.

  3. Click the Data Set node in the Data Model pane to create or edit data sets.

    Most predefined data models are of type SQL Query, and are set up to get application data from the following tables:

    • ApplicationDB_FSCM: Financials, Supply Chain Management, Project Management, Procurement, and Incentive Compensation

    • ApplicationDB_CRM: Sales

    • ApplicationDB_HCM: Human Capital Management

  4. Perform steps 5 through 8 from the preceding Creating a Data Model task, as needed.

Validating Data Models: Explained

Data model validation errors and warnings help you correct data models, optimize queries, reduce stuck threads, and enhance the reporting performance.

When you create or edit a data model that's created in the current or previous releases, if you click Validate, BI Publisher:

  1. Checks the queries used for data sets, LOVs, and bursting definitions.

  2. Generates the execution plan for SQL queries.

  3. Displays a list of errors and warnings.

Take the required action based on the data model validation messages. See Data Model Validation Messages.

Note that when you upgrade BI Publisher from a previous release, the existing data models are marked as not validated.

Data Model Validation Messages

The data model validation messages can be of the following types:

  • Error - You must resolve the data model errors if you want to use the data model to run a report.

  • Warning - Make the correction suggested in the warning message. Reporting performance might get affected if you choose to run the report ignoring the warning.

The data model validation messages and their details are as follows:

Validation Code Validation Type Message Type Message Limit

DM01_USE_OF_SELECT_STAR

Query

Warning

SQL query contains SELECT *. Use of '*' is restricted. Select the specific columns.

Not applicable

DM02_BIJDBC_NESTED_QUERY

Query

Warning

Data model contains nested BI JDBC queries. Linking logical queries is restricted. Use OTBI instead of BIP reports or remove the link between OBIEE data sets.

Not applicable

DM03_MERGE_CARTESIAN_JOINS

Query

Warning

SQL query execution plan contains merge cartesian joins. Generate the explain plan for the SQL query and identify the merge cartesian joins. Add the required filters in the SQL query.

Not applicable

DM04_NUM_BIND_VALUES_PER_PARAM

Runtime

Warning

Number of bind values per parameter more than the limit of {0} results in poor performance. Reduce the number of bind values.

100

DM05_NUM_COLUMNS_WITHOUT_PRUNING

Query

Warning

Number of columns in SELECT exceeds the limit of {0}. Select only the required columns and enable pruning.

30

DM06_NUM_COLUMNS_WITH_PRUNING

Query

Warning

Number of columns in SELECT exceeds the limit of {0}. Select only the required columns.

100

DM07_WHERE_CLAUSE_NOTIN_NOTEQUAL

Query

Warning

SQL query contains non-equal joins. Intermediate row spawning can cause performance issues. Replace non-equal joins with equal join or outer join.

Not applicable

DM08_COLUMN_ALIAS_LENGTH

Query

Warning

Selected column length name exceeds the limit of {0}. Length of the column name must not be more than 15 chars. Use short alias for column names.

15

DM09_NUMBER_OF_INLINE_QUERY

Query

Warning

Number of inline or subquery exceeds the limit of {0}. Remove the additional in-line select queries.

10

DM10_USE_OF_DUAL

Query

Warning

SQL query contains the FROM DUAL clause. SQL query contains too many DUAL tables. Avoid the usage of FROM DUAL clause.

Not applicable

DM12_NUM_OF_CLOB_COLUMNS

Query

Warning

Number of LOB columns in SELECT exceeds the limit of {0}. Select only the required columns.

2

DM14_DML_DDL_KEYWORD

Query

Error

Query contains DDL or DML keywords. Remove the DDL and DML keywords from the SQL query.

Not applicable

DM15_NUM_GROUP_BREAKS

Structure

Warning

Number of group breaks on single data set exceeds the limit of {0}. Remove multiple groups from the data set.

2

DM16_GROUP_FILTERS_CHECK

Structure

Warning

Data model contains group filters. Replace the group filters with the WHERE clause in the SQL query.

Not applicable

DM17_DM_PROPERTY_CHECK

Runtime

Error

Data model property is invalid or contains invalid values. Specify the correct data model property and check the property value.

Not applicable

DM18_FULL_TABLE_SCAN

Query

Warning

SQL query execution plan contains full table scans. Provide the required filters on indexed columns in the SQL query.

Not applicable

DM19_HIGH_BUFFER_READS

Query

Warning

SQL query execution plan contains high buffer reads. Buffer reads exceed the limit of 1 GB. Add filters in the SQL query to reduce the data fetch volume.

Not applicable

DM20_HIGH_CPU_COST

Query

Warning

SQL query execution plan contains high CPU cycles. Add the required filters in the SQL query to reduce the data fetch volume.

Not applicable

DM22_FUNC_USAGE_ON_FILTERS

Query

Warning

SQL query execution plan contains function calls on filter columns. Use of SQL function calls on index columns results in poor performance. Remove function calls on filter columns.

Not applicable

Define the Number of Rows in Tables

The data model used for a report can determine the number of rows to display in specific tables on the report. To change that number, you can edit a copy of the predefined data model.

Copying the Data Model

To copy the predefined data model used for the report:

  1. Click Navigator > Reports and Analytics.

  2. Click the Browse Catalog icon.

  3. In the BI catalog (the Folders pane), find the predefined data model used for the report.

  4. Click Copy in the toolbar.

  5. In the BI catalog, expand Shared Folders > Custom and follow the same file path as the predefined data model outside the Custom folder. Create the corresponding folder structure in Custom if it doesn't exist.

    Note: All reports using the predefined data model are automatically redirected to point to your copied data model in the Custom folder. This applies:
    • To all reports, predefined or not

    • Only if the copied data model has the same name and relative file path in Custom as the predefined data model

  6. Click Paste in the toolbar.

Modifying the Data Model

To edit the copied data model:

  1. In the BI catalog, find your copied data model.

  2. Click Edit.

  3. In the Data Model pane, in Data Sets, select the data set that provides data for the table in the report.

  4. On the Diagram tab, click the Edit Selected Data Model icon.

  5. In the Edit Data Set dialog box, see if the last line in the SQL Query field starts with FETCH FIRST, for example FETCH FIRST 500 ROWS ONLY. Not all data sets have this row limit that you can edit.

  6. Change the number in that line to the number of rows you want displayed in the table, for example, FETCH FIRST 50 ROWS ONLY.

  7. Click OK.

  8. Click the Save icon.

New Reports

Create Reports

Create a report when the predefined reports don't provide the data you need. Or, if you want to use a predefined data model, and also want to change other aspects of the report other than layout. Save your report to Shared Folders > Custom in the business intelligence catalog. Saving content in the Custom folder is the only way to ensure that the content is maintained upon upgrade.

Creating a Report

  1. Open the Reports and Analytics work area, and .

  2. Click click New and select Report.

  3. Select the data model to use as the data source for your report.

    Note: Be sure that the data model you select has been validated. If you select a data model that has not been validated or has errors, when the report is run it will error indicating that the data model has not been validated or has an invalid status. To override this warning for a report, select the Ignore Data Model Validation Error option in the report properties.
  4. Continue with the wizard to create the report layout, or choose to use the layout editor and close the wizard.

  5. Define the layout for the report.

  6. Click the Properties button in the report editor to set specific formatting, caching, and processing options for your report, including overriding data model validation errors.

Setting Up Access

You or your administrator can:

  • Create a job definition so that users can run your report as a scheduled process.

  • Secure general access to your report and its job definition, if any.

Configurable Workflow Notifications

Best Practices for Layouts in Workflow Notifications

Predefined workflow notifications based on report layout templates all follow a general format. When you edit a copy of these layout templates in Microsoft Word, follow the predefined layout as closely as possible for consistency. Also keep in mind shared components and mobile considerations.

General Structure

In general, the workflow notifications contain a set of components that are displayed in a certain order.

The callouts in this figure identify the email notification components listed in the following table.

Example of a workflow email notification with callouts
to identify the various components

The callouts in this figure identify the in-app notification components listed in the following table. In addition to describing each component, the table also indicates if the component appears in the email notification, in-app notification, or both.

Example of a workflow in-app notification with
callouts to identify the various components
Callout Component Notification Type

1

Buttons with the primary actions to take on the task, such as Approve and Reject. These buttons aren't part of the configurable, report-based notification content.

In-app

2

Notification header listing key attributes of the workflow task and the associated transaction.

Both

3

Buttons for the primary actions to take on the task, such as Approve and Reject.

Email

4

Notification body that usually includes transaction and line level details, displayed in tables or sets of attributes with corresponding values. The data model for the report restricts the total number of rows displayed in some of the tables. If the limit is exceeded, the table footer provides a link to the transaction details page, where users can view all the rows. To change this limit, you can edit a copy of the data model.

Both

5

Approval history, including any attachments that users in the history uploaded for the task. You can't edit the approval history component, which usually appears in the body of only email notifications. For in-app notifications, you can usually view the history by clicking the Actions button and selecting History.

Email (or both, in rare cases)

6

Buttons for the primary actions again.

Email

7

A link to the corresponding transaction page, and another link to the in-app notification.

Email

When you modify notifications, try to keep to this general structure and don't remove essential elements such as the action buttons. Likewise, don't change the styles in your layout template. The predefined style template should still apply to your notification; don't edit a copy of the style template and apply that to your notification.

To add components to your notification, for example another table, consider first downloading another style template from My Oracle Support. This template contains Quick Parts content that you can use in Word when you do more advanced work on layout templates. For example, from the Quick Parts gallery, you can select and add the table that is consistent in format with predefined tables already on your notification.

By default, the components that you add in the layout template appear in both email and in-app notifications, where available. You can add conditions to explicitly make a particular element, for example a field, appear only in one type of notification and not the other.

Shared Components

A predefined subtemplate in the business intelligence (BI) catalog applies to all predefined layout templates for workflow notifications. The subtemplate contains components that are shared among the notifications, for example:

  • Branding logo, if you add one to the subtemplate, which would appear as the first component in the email body. The logo appears in email notifications only.

  • Action buttons in email notifications.

  • Links at the end of the email notification, one to the corresponding transaction page, and another to the in-app notification.

When you make a copy of a predefined layout template to edit, the copy automatically inherits the same predefined subtemplate. To edit these shared components, make a copy of the predefined subtemplate, edit the copied version, and apply it to your own layout templates.

Mobile Considerations

Because users can view the workflow notifications on mobile devices, always consider mobile first and keep the notifications as simple as possible. For example:

  • Don't put too much content horizontally, such as too many columns in tables.

  • Keep all text, including attributes and column headings, as short as possible.

  • Center align lists of attributes and their values, if they appear outside tables.

Make sure to test your email notifications on mobile devices.

Add a Branding Logo and Change Other Shared Components in Workflow Notifications

A predefined subtemplate contains common components for all workflow notifications based on predefined report layouts. For example, the subtemplate has a place for you to add a branding logo, which would appear at the beginning of email notifications. You can modify other shared components so that the same changes apply to your notifications. For example, for email notifications, you can also change the text on action buttons, or the text of the links that appear at the end of emails.

Note:
  • You must edit a copy of the subtemplate in the Custom folder of the business intelligence (BI) catalog. Don't directly update the predefined subtemplate.

  • The exact steps can vary depending on your version of Microsoft Word.

Modifying Shared Components in the Subtemplate

To edit a copy of the predefined subtemplate that contains the shared components:

  1. Click Navigator > Reports and Analytics.

  2. Click the Browse Catalog icon.

  3. In the BI catalog (the Folders pane), expand Shared Folders > Common Content > Templates.

  4. For Workflow Notification Subtemplate, click More and select Customize.

    If you're not using the Customize option:

    1. Click Copy in the toolbar with Workflow Notification Subtemplate selected.

    2. In the BI catalog, expand Shared Folders > Custom > Common Content > Templates. Create a Templates folder in this location if it doesn't exist.

    3. Click Paste in the toolbar.

    4. Click the Edit link for the copied subtemplate.

    All reports using the predefined subtemplate are automatically redirected to point to your subtemplate in the Custom folder. This applies:

    • To all reports, predefined or not

    • No matter if you copy and paste the subtemplate or use the Customize option

    • Only if your subtemplate has the same name and relative file path within Custom as the predefined subtemplate

  5. In the Templates section, click the link in the Locale column.

  6. Save the subtemplate .rtf file to your computer.

  7. Open the .rtf file with Microsoft Word.

    • To add a logo, insert your own image in the subtemplate.

    • To change button or link text, edit the text accordingly. Make the same edits wherever that button or link text appears in the subtemplate.

      Caution: To ensure that your layout templates reflect these changes without additional rework, don't edit any other text in the subtemplate .rtf file.
  8. Update Word options to ensure that existing links remain intact in the subtemplate.

    1. Click File > Options > Advanced.

    2. In the Word Options dialog box, click Web Options in the General section.

    3. In the Web Options dialog box, open the Files tab.

    4. Deselect the Update links on save check box.

  9. Save your changes in Word.

Uploading the Modified Subtemplate

To upload your subtemplate to the BI catalog:

  1. In the BI catalog, expand Shared Folders > Custom > Common Content > Templates.

  2. Click Edit for Workflow Notification Subtemplate.

  3. In the Templates section, click the Upload icon.

  4. Select your modified .rtf subtemplate and a locale, and click OK to overwrite the original subtemplate.

Use Quick Parts for Workflow Notifications

Use the Quick Parts feature in Microsoft Word to easily insert reusable pieces of formatted content. When you edit copies of predefined report layout templates for workflow notifications in Word, you can add predefined Quick Parts content to your .rtf file. For example, you can insert a table in a format that's consistent with predefined notifications. The predefined Quick Parts content is available in a style template .dotx file on My Oracle Support.

Note: The exact steps can vary depending on your version of Microsoft Word.

Prerequisites

To get the predefined Quick Parts content into your Quick Parts gallery:

  1. Open Configurable Workflow Notifications: Implementation Considerations (2215570.1) on My Oracle Support at https://support.oracle.com.

  2. Download the .dotx file and save it to your Microsoft Word template folder, for example C:\Users\<user name>\AppData\Roaming\Microsoft\Templates.

Also, to preview your layout template changes before uploading the .rtf file back to the business intelligence (BI) catalog:

  • Generate sample report data from the data model for the report that you're editing.

  • Download a local copy of the subtemplate that applies to the layout template.

Adding Quick Parts Content to Workflow Notifications

To insert content from the Quick Parts gallery into a layout template:

  1. In the BI catalog, find the predefined report with the layout template that you want to modify.

  2. For the report, click More and select Customize.

    If you're not using the Customize option:

    1. Copy the predefined report and paste it in an appropriate subfolder within the Custom folder.

    2. Click the Edit link for the copied report.

  3. Click Edit for the layout template to insert Quick Parts content into, and save the .rtf file to your computer with a new file name.

  4. Open the .rtf file with Microsoft Word.

  5. Put your cursor where you want to insert new content.

  6. From the Insert tab on the ribbon, click Quick Parts within the Text group, and select the component to insert.

  7. Edit the inserted component as needed and add any other components.

  8. Save your changes in Word.

Previewing the Layout Template Changes

To preview your edits before uploading your layout template to the BI catalog:

  1. On the ribbon, open the BI Publisher tab and click Sample XML within the Load Data group to import sample data from the data model. Skip this step if you already loaded sample data.

  2. At the beginning of the document, replace the path with the location of the downloaded subtemplate file on your computer. For example, change <?import:xdoxsl:///Common Content/Templates/Workflow Notification Subtemplate.xsb?> to <?import:file:///C:/Template_Directory/FinFunWorkflowNotificationSub.rtf?>.

  3. From the BI Publisher tab on the ribbon, click HTML in the Preview group.

  4. If the preview reflects your changes as expected, then change the path back to the original location.

  5. Save your changes in Word.

Uploading the Modified Layout Template

To upload your layout template to the BI catalog after previewing the changes:

  1. Back in the BI catalog, click Edit for the report within the Custom folder, if that page isn't still open.

  2. Click the View a list link.

  3. Click the Create icon on the table toolbar.

  4. In the Upload or Generate Layout section, click Upload.

  5. Upload your edited .rtf file with a unique layout name.

  6. Back on the page for editing the report, click Delete for the layout template that you downloaded earlier.

  7. Click the Save Report icon.

Set Up Content to Appear in Only Email or In-App Workflow Notifications

For workflow tasks that have configurable email and in-app notifications, the same .rtf report layout template is used for both types of notifications. When you edit a copy of predefined templates in Microsoft Word to modify the notifications, you can make content conditional. For example, you can add an attribute from the data models used for the report, and set the attribute to appear only in in-app notifications.

The logo, action buttons, and links at the end of email notifications are predefined to appear only in emails, based on the subtemplate. The approval history is usually predefined to also appear in the body of only email notifications. Any conditional setting you apply to these components in the .rtf template won't override the predefined setup.

Prerequisites

Generate sample report data from the data model used for the report, and save the .xml file to your computer.

Defining Conditional Regions

To define a conditional region of content that appears only in email or in-app notifications:

  1. Open your .rtf report layout template in Microsoft Word.

  2. On the ribbon, open the BI Publisher tab and click Sample XML within the Load Data group.

  3. Select the .xml file you downloaded to import sample data from the data model.

  4. In your .rtf document, select the content you want to make conditional.

  5. On the ribbon, click Conditional Region within the Insert group.

  6. In the Conditional Region dialog box, on the Properties tab, select BINDISONLINENOTIF from the Data field list in the General section. The values in this list come from the sample data you imported from the data model.

  7. Select Date/Text from the next list.

  8. In the Condition 1 section, select Equal to from the Data field list.

  9. In the corresponding text field, enter true for content to appear only in in-app notifications, or false for content to appear only in emails.

  10. Make sure that form fields containing the conditional logic are inserted around your selected content. The beginning form field, C, should be immediately before the conditional content, and the closing form field, EC, should be immediately after. Move the form fields as needed.

    Tip: To make sure you're looking at the correct form fields, double-click the C form field to open the Conditional Region dialog box and see the BINDISONLINENOTIF setting.
  11. Save your changes in Word.

Entering Conditional Code

If the data model for your report doesn't have the BINDISONLINENOTIF attribute, then:

  1. In your .rtf report layout template, put your cursor immediately before the content you want to make conditional.

  2. Enter the following code, which functions the same as the C form field:

    • <?if:BINDISONLINENOTIF='true'?> for in-app only

    • <?if:BINDISONLINENOTIF='false'?> for email only

  3. Put your cursor immediately after your conditional content.

  4. Enter <?end if?>, which functions the same as the EC form field.

  5. Save your changes in Word.

Preview Changes to Layout Templates for Workflow Notifications

To modify workflow notifications, you edit a local copy of the .rtf report layout templates in Microsoft Word. Before uploading the .rtf files to the business intelligence (BI) catalog, you should preview the output with the changes you made. You can avoid uploading a broken report that displays an error in the notifications sent to users.

Note: The exact steps can vary depending on your version of Microsoft Word.

Prerequisites

  • Generate sample report data from the data model used for the report, and save the .xml file to your computer.

  • Download a local copy of the subtemplate that applies to your own report layout template:

    1. In the BI catalog, expand Shared Folders > Custom > Common Content > Templates if you're using a modified subtemplate, or Shared Folders > Common Content > Templates for the predefined subtemplate.

    2. Click Edit for Workflow Notification Subtemplate.

    3. In the Templates section, click the link in the Locale column.

    4. Save the subtemplate .rtf file to your computer.

Previewing Output

To generate sample output from a local layout template:

  1. Open your .rtf report layout template in Microsoft Word and make your edits.

  2. On the ribbon, open the BI Publisher tab and click Sample XML within the Load Data group.

  3. Select the .xml file you downloaded to import sample data from the data model.

  4. At the beginning of your .rtf document, replace the path with the location of the downloaded subtemplate file on your computer. For example, change <?import:xdoxsl:///Common Content/Templates/Workflow Notification Subtemplate.xsb?> to <?import:file:///C:/Template_Directory/FinFunWorkflowNotificationSub.rtf?>.

  5. From the BI Publisher tab on the ribbon, click HTML in the Preview group.

  6. If the preview reflects your changes as expected, then change the path back to the original location.

  7. From the BI Publisher tab on the ribbon, click Validate Template in the Tools group.

  8. Also in the Tools group, click Check Accessibility.

  9. Save your changes in Word.