Skip Headers
Oracle® Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher
11g Release 1 (11.1.1)

Part Number E22254-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Creating BI Publisher Layout Templates

This chapter describes creating BI Publisher (.xpt) layout templates using the layout editor.

This chapter includes the following topics:

3.1 Overview of BI Publisher Layouts

Release 11g of Oracle BI Publisher introduces a new type of layout template. The BI Publisher Layout template enables end users to:

BI Publisher Layout Templates are created using the BI Publisher Layout Editor - a design tool that provides a WYSIWIG, drag and drop interface for creating pixel perfect reports in PDF, RTF, Excel, PowerPoint, and HTML. It also provides dynamic HTML output that supports lightweight interaction through a browser. This interactive output is featured in Figure 3-1.

Figure 3-1 Example of Interactive Output

Surrounding text describes Figure 3-1 .
Description of Figure 3-1 follows
Description of "Figure 3-1 Example of Interactive Output"

Notice the following features:

3.1.1 When to Use a BI Publisher Layout

BI Publisher layouts are best suited for reports of simple to medium complexity that do not require custom coding. Because the dynamic HTML view is only available for BI Publisher layouts, BI Publisher layouts must be used when there is a requirement to enable a report consumer to interact with the report (change sorting, apply filters, and so on).

3.1.2 Prerequisites, Recommendations, and Limitations

  • To use the layout editor, your account must be granted a role that includes the appropriate permissions for accessing report layout tools.

  • You must attach sample data to the data model before you create a new layout. For information on adding sample data to the data model, see the "Testing Data Models and Generating Sample Data" section in Oracle Fusion Middleware Data Modeling Guide for Oracle Business Intelligence Publisher.

  • For optimum viewing, set your display resolution to 1024 x 768 or higher.

  • For enabling interactive sorting and filtering, the complete data set that is passed to the layout should be limited to several thousand rows to achieve a responsive report. BI Publisher layouts have a comparable performance to RTF layouts for generating static output such as PDF or RTF documents.

  • The layout editor does not support namespaces or attributes in the XML data.

3.2 Launching the Layout Editor

Launch the layout editor in one of the following ways:

3.2.1 When Creating a New Report

To launch the Layout Editor when creating a new report:

  1. Selecting the data model for the new report.

    The Report Editor displays the Add Layout page.

  2. From the Create Layout region, click a predefined template to launch the Layout Editor.

3.2.2 When Editing a Report

To launch the Layout Editor when editing a report:

  1. In the Report Editor:

    From the Thumbnail view, click Add New Layout.

    or

    From the List view, click the Create button on the layouts table toolbar.

  2. From the Create Layout region, click a predefined template to use to launch the Layout Editor.

3.2.3 When Viewing a Report

To launch the Layout Editor when viewing a report:

  1. Click Actions and then click Edit Layout.

    The layout must have been created in the layout editor.

3.2.4 Selecting a Predefined Layout

When you create a new layout, you are given the option of selecting a predefined layout to help you get started. Figure 3-2 shows the predefined layouts offered by the Basic and Shared Templates.

Figure 3-2 Predefined Layouts

Description of Figure 3-2 follows
Description of "Figure 3-2 Predefined Layouts"

The Basic and Shared Templates offer common layout structures with specific components already added. Choosing one of the predefined layouts is optional, but can facilitate layout design. If your enterprise utilizes a common design that is not available here, then you can add predefined layouts for your own use, or your Administrator can add more for all users.

3.2.4.1 Adding Shared Templates for All Users

To add predefined layout files to the shared directory for all users to access:

  1. Log in with Administrator privileges and navigate to the Catalog.

  2. In the Shared Folders directory, open the Components folder.

  3. Locate the Boilerplates report and click Edit.

  4. Click Add New Layout.

  5. Design or upload the layout.

    To design the layout: Click an existing boilerplate (or blank) to launch the layout editor. Insert the components to the layout. When finished, click Save and give the boilerplate a name. This layout is now displayed to all users in the Shared Templates region.

    To upload a layout: Click Upload to upload a predefined BI Publisher Template (.xpt file).

  6. Save the report.

Any BI Publisher Templates (.xpt) added to this report are displayed to all users as a Shared Template.

3.2.4.2 Adding Personal Predefined Layouts

To add predefined layouts that are available to your account user only:

  1. Navigate to My Folders.

  2. Create a new report called "Boilerplates". This report does not have a data model.

  3. Click Add New Layout.

  4. Design or upload the layout.

    To design the layout: Click an existing boilerplate (or blank) to launch the layout editor. Insert the components to the layout. When finished, click Save and give the boilerplate a name.

    To upload a layout: Click Upload to upload a predefined BI Publisher Template (.xpt file).

These layouts are presented in the My Templates region when you create a new layout.

3.3 About the Layout Editor Interface

Figure 3-3 shows the Layout Editor.

Figure 3-3 The Layout Editor

Description of Figure 3-3 follows
Description of "Figure 3-3 The Layout Editor"

The Layout Editor interface comprises the following:

3.3.1 About the Data Source Pane

The Data Source pane displays the structure of the data model and the data elements that are available to insert into the layout.

To insert a data element, select and drag it from the Data Source pane to the component in the layout.

The data type for each field is represented by an appropriate icon: number, date, or text.

Figure 3-4 shows the data source pane. The icon beside each element indicates the data type.

Figure 3-4 The Data Source Pane

Description of Figure 3-4 follows
Description of "Figure 3-4 The Data Source Pane"

The JOB_TITLE element is shown as text, the SALARY element is shown as a number, and the HIRE_DATE element is shown as a date data type.

Note:

When you enter dates in the Layout Editor (such as a data comparison for a filter or for conditional formatting), use one of the following XSL date or time formats: YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS.

3.3.2 About the Components Pane

The Components pane contains the layout components that you can insert into a report. These components include charts, pivot tables, and images. To insert a component, simply drag and drop it to the layout.

You can also use the Insert menu to add components to the layout.

Figure 3-5 shows the Components pane.

Figure 3-5 The Components Pane

Description of Figure 3-5 follows
Description of "Figure 3-5 The Components Pane"

3.3.3 About the Properties Pane

The Properties pane displays the properties for the selected component. The properties displayed are determined by the selected component. Some of the properties available in the Properties pane are also editable in the dynamic tab for the component.

Click a property value to edit it. The change is applied to the component when you move the cursor out of the field. Collapse or expand a property group by clicking the plus or minus signs beside the group name.

The properties available for each component are discussed in detail in the corresponding section for that component in this chapter. If a property field is blank, then the default is used.

Figure 3-6 shows a sample Properties pane for a table column header.

Figure 3-6 Sample Properties Pane for a Table Column Header

Description of Figure 3-6 follows
Description of "Figure 3-6 Sample Properties Pane for a Table Column Header"

3.3.4 About the Static Toolbar

The Static toolbar extends on either side of the tabbed toolbar and is shown in Figure 3-7.

Figure 3-7 The Static Toolbar

Description of Figure 3-7 follows
Description of "Figure 3-7 The Static Toolbar"

Use it to perform the following functions:

  • Undo and redo operations.

  • Cut, copy and paste items.

  • Preview as Interactive, HTML, PDF, Microsoft Word (RTF), Microsoft Excel, Microsoft PowerPoint, or Microsoft PowerPoint 2007.

  • Return to the previous page.

  • Create a new layout.

  • Open a layout.

  • Save the layout.

  • Save the layout as a new copy under a different name.

3.3.5 About the Tabbed Toolbar

The Tabbed toolbar contains the following tabs:

  • The Insert tab provides the components and page elements that can be placed on a layout. See Section 3.5, "Inserting Layout Components."

  • The Page Layout tab provides common page-level tools and commands. See Section 3.4, "Page Layout Tab."

  • The component-specific tab provides the most commonly used commands and properties for the component that is selected in the layout. For example, when you select a chart, the Chart tab displays. See the section on a specific component for details on the commands.

    To set or control more properties for the selected component, open the Properties pane in the accordion pane, as described in Section 3.3.3, "About the Properties Pane"

3.3.5.1 Selecting and Deleting Layout Objects

Each of the component-specific tabs include the Select region.

  • The Select tool enables you to control precisely which component on the layout has focus. This ability is particularly helpful when working with a complex layout where components overlap. For example, to select a table, it is sometimes difficult to click the correct spot to select the table and not a column, or header cell. To avoid unnecessary clicking, use the Select tool to precisely select the Table component from the list.

    Figure 3-8 shows the Select tool.

    Figure 3-8 The Select Tool

    Description of Figure 3-8 follows
    Description of "Figure 3-8 The Select Tool"

  • The Delete tool provides a similar function to the Select tool to enable you to precisely select the component to delete.

3.3.5.2 About the Insert Tab

Use the Insert tab to insert report components and page elements. Figure 3-9 shows the Insert tab.

Figure 3-9 The Insert Tab

Description of Figure 3-9 follows
Description of "Figure 3-9 The Insert Tab"

The Components group displays the report components that you can insert into the layout. To insert a component, select and drag the item to the desired location in the design area. For more information about each component, see its corresponding section in this chapter.

The Page Elements group contains page-level elements for the report. To insert a page break, the page number, or the total page number calculation, select and drag the component to the desired position in the layout.

Note:

Page elements are intended for paginated output types, such as PDF and RTF. Using them in interactive or HTML output may have unexpected results.

3.4 Page Layout Tab

Figure 3-10 shows the Page Layout tab.

Figure 3-10 The Page Layout Tab

Description of Figure 3-10 follows
Description of "Figure 3-10 The Page Layout Tab"

The Page Layout tab contains commands to set up the layout.

3.4.1 Paper Options

Table 3-1 describes paper options.

Table 3-1 Paper Options

Option Description

Orientation

Choose Portrait or Landscape.

Paper Size

Select from the following paper size options: Letter, Legal, A4, A3, Executive, B5, Com-10, Monarch DL, or C5. The paper size determines the dimensions of the layout area.


3.4.2 Header/Footer Options

Table 3-2 describes header and footer options.

Table 3-2 Header and Footer Options

Option Description

Page Header

Click to insert a page header in the layout. By default, the page header appears on every page of a printed report, but can be configured to skip the first page. To remove the page header, click Page Header again.

Page Footer

Click to insert a page footer in the layout. By default, the page footer appears on every page of a printed report, but can be configured to skip the last page. To remove the page footer, click Page Footer again.

Report Header

Click to insert a report header to the layout. The report header appears only once at the beginning of the report. To remove the report header, click Report Header again.

Report Footer

Click to insert a report footer to the layout. The report footer appears only once at the end of the report. To remove the report footer, click Report Footer again.


3.4.2.1 Setting Properties for Headers and Footers

The Properties pane enables you to set the following properties for headers and footers. To access the Properties pane, select the header or footer in the design region, then click Properties from the accordion pane on the left of the page.

For all report and page headers and footers:

  • Height - Set the height of the header region in pixels, points, centimeters, or inches

For headers:

For footers:

  • Show in the last page - Select True to show the footer in the last page. Select False to suppress the footer from the last page.

3.4.3 View Options

Table 3-3 describes view options.

Table 3-3 View Options

Option Description

Grid

Click to insert gridlines in the layout design area. The grid unit size depends on the Display Unit selected. To remove the gridlines, click Grid again.

Ruler

Click to insert a display ruler across the top of the layout design area. The ruler units depend on the Display Unit. To remove the ruler, click Ruler again.


3.4.4 Display Unit

Select the unit of measure to display. This unit is used for the ruler and grid view options, as well as for any other function that displays a measurement, such as setting border widths and sizing grid cells. Options are: inch, px (pixel), cm (centimeter), and point (pt).

3.4.5 Interactivity: Event Configuration

The Configure Events feature enables you to configure how components of the layout respond to events triggered by a user when viewing the report in interactive mode.

The two types of events are:

  • Filter - If you click an element in a list, chart, or pivot table, that element is used to dynamically filter other components defined as targets in the report. The component being clicked does not change.

  • Show Selection Only - If you click an element of a list, chart, or pivot table, the chart or pivot table (being clicked) shows the results for the selected element only. This action does not affect other components of the report.

3.4.5.1 Example of Filter Event Configuration

Figure 3-12 shows an example of filter event configuration. The layout contains two charts and a table. The first chart shows salary totals by department in a pie chart. The second chart shows salary totals by manager in a bar chart. The table displays a list of employees and their salaries.

Figure 3-12 Example of Filter Event Configuration

Description of Figure 3-12 follows
Description of "Figure 3-12 Example of Filter Event Configuration"

In this report, if a user clicks on a value in the Salary by Department chart, you want the Salary by Manager chart and the Employees table to automatically filter to show only the managers and employees in the selected department.

Figure 3-13 shows the automatic filtering that occurs when a user clicks the Sales department section of the Salary by Department pie chart. The Salary by Manager chart automatically filters to display only the managers belonging to the sales department. The Employee table automatically filters to display only the employees in the sales department.

Figure 3-13 Example of Automatic Filtering

Description of Figure 3-13 follows
Description of "Figure 3-13 Example of Automatic Filtering"

3.4.5.2 Configuring Automatic Filtering

To configure automatic filtering:

  1. On the Page Layout tab, click Event Configuration to display the Configure Events dialog.

    Figure 3-14 shows the Configure Events dialog.

    Figure 3-14 The Configure Events Dialog

    Description of Figure 3-14 follows
    Description of "Figure 3-14 The Configure Events Dialog"

  2. In the Components column, click the layout component (lists, charts, and pivot tables are available to configure).

  3. Select Filter to enable automatic filtering in other report components.

  4. Select the report components in the Targets column to enable the automatic filtering based on interactive events in the selected component. To disable the automatic filtering for a target component, clear the box.

    The preceding figure shows that the Filter event is enabled for Chart 1 in the layout. Chart 2 and Table 3 are selected as targets to enable automatic filtering when a selection event occurs in Chart 1.

    The Show Selection Only option is not enabled for Chart 1. That means that Chart 1 continues to display all values when one its elements is selected.

3.4.5.3 Example: Show Selection Only

The Show Selection Only event displays only the value of the selected element within the chart or pivot table (being acted on).

In the example in Figure 3-15, Chart 2 is configured with Show Selection Only enabled and Filter enabled with Table 3 as the Target.

Figure 3-15 Example of Show Selection Only

Description of Figure 3-15 follows
Description of "Figure 3-15 Example of Show Selection Only"

This configuration results in the output shown in Figure 3-16. When the user clicks on Chart 2, only the selected value is shown in Chart 2. Because the Filter event is enabled for Table 3, the selection is applied as a filter to Table 3.

Figure 3-16 Example of Output from the Show Selection Only Configuration

Description of Figure 3-16 follows
Description of "Figure 3-16 Example of Output from the Show Selection Only Configuration"

3.4.6 Setting Page Margins

To set the page margins for the report:

  1. Click anywhere in the design area outside of an inserted component.

  2. Click the Properties pane in the lower left of the Layout Editor. Figure 3-17 shows the Properties for the page.

    Figure 3-17 The Properties for the Page

    Description of Figure 3-17 follows
    Description of "Figure 3-17 The Properties for the Page"

  3. Click the value shown for Margin to launch the Margin dialog.

    Figure 3-18 shows the Margin dialog.

    Figure 3-18 The Margin Dialog

    Description of Figure 3-18 follows
    Description of "Figure 3-18 The Margin Dialog"

  4. Select the desired size for the margin. Enter the value for the Top, Left, Right, and Bottom margins.

    To automatically set the same value for all sides, select the box: Use same value for all sides. This action disables all but the Top margin entry. Enter the value in the Top to apply to all sides.

3.4.7 Setting Maximum Connections for an Interactive Report

You can limit the connections from the browser to the server for the interactive viewer. More connections are faster but increase server load The default is six connections. Reduce the number to reduce the load on the server for large reports.

To set the maximum connections for this layout:

  1. Click anywhere in the design area outside of an inserted component.

  2. Click the Properties pane in the lower left of the Layout Editor. Figure 3-19 shows the Properties for the page.

    Figure 3-19 The Properties for the Page

    Description of Figure 3-19 follows
    Description of "Figure 3-19 The Properties for the Page"

  3. Click the value shown for Max. Connections and select the desired value from the list, as shown in Figure 3-20.

    Figure 3-20 Example of Max. Connections

    Description of Figure 3-20 follows
    Description of "Figure 3-20 Example of Max. Connections"

3.5 Inserting Layout Components

The layout editor supports components that are typically used in reports and other business documents. The followings components are described in these sections:

3.6 About Layout Grids

The layout grid provides a way to divide a layout into sections. It functions similarly to a table in HTML or Word documents to create forms or to provide sophisticated layouts. Use a layout grid to control the exact placement of all other components in the layout.

To create a layout grid:

  1. Select and drag the Layout Grid component to the design area.

Figure 3-21 shows the Create a Layout Grid dialog.

Figure 3-21 Create a Layout Grid Dialog

Description of Figure 3-21 follows
Description of "Figure 3-21 Create a Layout Grid Dialog"

In the dialog, enter the number of rows and columns for the grid and click OK to insert the grid to the design area, as shown in Figure 3-22.

Figure 3-22 Example of a Grid Inserted in the Design Area

Description of Figure 3-22 follows
Description of "Figure 3-22 Example of a Grid Inserted in the Design Area"

Note the following about a layout grid:

3.6.1 Adding a Border or Background Color

By default, the gridlines are displayed in the design area only and are not shown during runtime. If you want to display the gridlines in the finished report, then select the grid cell and click the Set Border command button to launch the Border dialog.

To add a background color to a cell:

  1. Click the Background Color command button to launch the Color Picker.

3.6.2 About the Insert Options

When you have inserted a layout grid, you can add additional rows or columns. Select the layout grid cell that is the focal point, then click the appropriate command button:

  • Add a Row above

  • Add a Column to the right

  • Add a Row below

  • Add a Column to the left

3.6.3 About the Join and Unjoin Options

To join cells horizontally or vertically:

  1. Select multiple adjacent cells by holding down the Ctrl key and clicking each grid cell.

  2. Click the Join command button.

To unjoin cells that have been joined:

  1. Select the joined cell and click the Unjoin button.

3.6.4 Adding an Expand and Collapse Option

When viewing a report in interactive mode, expand and collapse of a layout grid are supported. Expand and Collapse are supported at the grid level, (not the cell-level) therefore ensure to insert grids appropriately. For example, if the report contains a chart in the top portion of the layout and a table in the bottom and you want to be able to collapse the chart display, you must insert one layout grid to contain the chart and a second layout grid beneath the first to contain the table. Do not insert one grid with two rows.

To enable the expand and collapse option:

  1. Select the layout grid.

  2. Open the Properties pane.

  3. Set the Interactive: Expand/Collapse property to True. Figure 3-23 shows this option on the Properties pane.

    Figure 3-23 The Interactive: Expand/Collapse Property

    Description of Figure 3-23 follows
    Description of "Figure 3-23 The Interactive: Expand/Collapse Property"

Figure 3-24 demonstrates the expand and collapse behavior when the report is viewed in interactive mode. The top of the figure shows the collapse icon in the upper right area of the report. Click the icon to collapse the grid. The bottom of the figure shows the report with the region collapsed.

Figure 3-24 Example of the Expand and Collapse Behavior

Description of Figure 3-24 follows
Description of "Figure 3-24 Example of the Expand and Collapse Behavior"

3.7 About Repeating Sections

Repeating sections repeat the components within the section of the layout based on the occurrence of an element in the data. Repeating sections are used to create classic banded reports, as well as repeating pages or sections for different data elements (such as Group Above/Outline).

To create a repeating section:

  1. Drag and drop the repeating section component to the layout.

  2. In the Repeating Section dialog, select one of the following:

    • Element - Specify the element for which the section repeats. For example, if the dataset contains sales information for several countries. If you select COUNTRY as the repeat-by element, then the section of the layout repeats for each unique country occurring in the dataset.

    • Group Detail - If you have nested sections, then select this option. To continue the previous example, assuming there are unique data rows for each city and grouping by country, then this option creates a section that repeats for each city.

Figure 3-25 shows a layout that has a repeating section defined for the element Department. Within the repeating section are a chart that shows salaries by manager and a table that shows all employee salaries. So for each occurrence of department in the dataset, the chart and table are repeated.

Figure 3-25 An Example of a Layout that has a Repeating Section Defined for the Element Department

Description of Figure 3-25 follows
Description of "Figure 3-25 An Example of a Layout that has a Repeating Section Defined for the Element Department"

3.7.1 Setting Page Break Options for a Repeating Section

By default, for paginated output types, the page breaks automatically according to the amount of content that fits on a page. It is frequently desirable to have the report break after each occurrence of the repeated content.

Using the preceding example, it is desirable for the PDF output of this report to break after each department.

To create a break in the report after each occurrence of the repeating section:

  1. Select the repeating section component.

  2. Open the Properties pane.

  3. Set the Page Break property to Page.

    Figure 3-26 shows the Properties for a repeating section.

    Figure 3-26 The Properties for a Repeating Section

    Description of Figure 3-26 follows
    Description of "Figure 3-26 The Properties for a Repeating Section"

3.7.2 How Repeating Sections Display in Interactive Mode

In interactive mode, the values for the repeat by element are displayed as a list of values. This enables the enable the report consumer to dynamically select and view the results.

Figure 3-27 shows the repeat by element Department displayed in a list of values.

Figure 3-27 The Repeat by Element Department Displayed in a List of Values

Description of Figure 3-27 follows
Description of "Figure 3-27 The Repeat by Element Department Displayed in a List of Values"

By contrast, Figure 3-28 shows the same layout displayed in PDF. In this example the page break option is set so that each new department begins the repeating section on a new page.

Figure 3-28 Repeat by Element Department Layout Displayed in PDF

Description of Figure 3-28 follows
Description of "Figure 3-28 Repeat by Element Department Layout Displayed in PDF"

3.7.3 Showing All Values in a Repeating Section

In interactive mode, the values for the repeat by element are displayed as a list of values. By default, this list includes only the values present for the element in the data. Therefore, a report consumer can view results for only one item at a time.

To enable a report consumer to view the results in the repeating section for all values of the element, the Repeating Section component provides the property: Show All. When this property is set to true, the value "All" is added to the list to enable the display of results for all values.

To enable Show All:

  1. Select the repeating section component.

  2. Open the Properties pane.

  3. Set the Show All property to True.

    Figure 3-29 shows the Show All setting in the Properties pane.

    Figure 3-29 The Show All Setting in the Properties Pane

    Description of Figure 3-29 follows
    Description of "Figure 3-29 The Show All Setting in the Properties Pane"

When you view the report, the option All is added to the menu of values, as shown in Figure 3-30.

Figure 3-30 The All Option Added to the Menu of Values

Description of Figure 3-30 follows
Description of "Figure 3-30 The All Option Added to the Menu of Values"

3.8 About Data Tables

The data table is a standard table that is shown in many layouts. It contains a header, data columns, and a total row. The table supports "group left" functionality (outlines) that merges fields with the same values as well as subtotals, grand totals, custom calculations, and running totals.

Once inserted, you can edit the table properties using the dynamic tabs or the Properties pane. The following dynamic tabs are available for the table components:

This section contains the following topics about working with tables:

3.8.1 Inserting a Data Table

To insert a data table:

  1. From the Insert tab, select and drag the Data Table component to the design area.

    Figure 3-31 shows an inserted, empty data table. Notice that the Table tab is now displayed.

    Figure 3-31 Example of an Inserted, Empty Data Table

    Description of Figure 3-31 follows
    Description of "Figure 3-31 Example of an Inserted, Empty Data Table"

  2. To add data columns to the table, select an element from the Data Source pane and drag it to the table in the layout.

    Figure 3-32 shows the columns being added to the table. Notice that when you drop a column on the table the sample data is immediately displayed.

    Figure 3-32 Example of Columns Added to a Table

    Description of Figure 3-32 follows
    Description of "Figure 3-32 Example of Columns Added to a Table"

  3. Continue to drag the elements from the Data Source pane to form the columns of the table. If you must reposition a column that you have already added, then select it and drag it to the correct position.

    Figure 3-33 shows a completed data table.

    Figure 3-33 Example of a Completed Data Table

    Description of Figure 3-33 follows
    Description of "Figure 3-33 Example of a Completed Data Table"

    Notice the following default behavior:

    • A total row is automatically inserted. By default it calculates the sum of the items in the column. You can remove this row or edit the display and calculation applied. See About the Total Cell Tab.

    • Default date formatting is applied. To change the default formatting, see About the Column Tab.

    • Default number formatting and alignment is applied. To change the default formatting, see Section 3.8.5, "About the Column Tab."

3.8.2 Setting Alternating Row Colors

Some data tables are easier to read when the rows display alternating colors, as shown in Figure 3-34.

Figure 3-34 Example of Rows Displayed in Alternating Colors

Description of Figure 3-34 follows
Description of "Figure 3-34 Example of Rows Displayed in Alternating Colors"

To set an alternating row color:

  1. Select the table.

  2. Open the Properties pane.

  3. Click the value shown for Alternate Row Color to launch the color picker. Figure 3-35 shows the Alternate Row Color option.

    Figure 3-35 The Alternate Row Color Option

    Description of Figure 3-35 follows
    Description of "Figure 3-35 The Alternate Row Color Option"

  4. Choose a color and click OK.

3.8.3 About the Table Tab

The Table Tab enables you to perform the following:

  • Set the number of rows displayed

  • Define filters for the data displayed in the table

  • Define conditions and formats to apply to rows that meet the conditions

  • Show or hide the total row for the table

Figure 3-36 shows the Table tab.

Figure 3-36 The Table Tab

Description of Figure 3-36 follows
Description of "Figure 3-36 The Table Tab"

3.8.3.1 Setting the Rows to Display Option

The Rows to Display property controls the number of rows of data displayed as follows:

  • When designing the layout, this property sets the number of rows that are displayed for the table within the layout editor.

  • When viewing this layout in the report viewer in interactive mode, this property sets the size of the scrollable region for the table.

The default is 10 rows of data. You can select 10, 20, 30, 40, or All rows of data to be displayed. To set a custom value, open the Properties pane and enter the custom value for the Rows to Display property.

Note:

Displaying more rows of data can impact the performance of the Layout Editor.

3.8.3.2 About Filters

A filter refines the displayed items by a condition. This is a powerful feature that enables you to display only desired elements in the table without having to perform additional coding. For example, you could add a filter to meet some of the following report conditions:

  • Display only the top 10 salaries

  • Display only the bottom 25 store sales

  • Display only employees in the IT department

  • Display only sales that are between $10,000 and $20,000 and in the Southern region

You can add multiple filters and manage the order in which they are applied to the table data.

3.8.3.3 Setting Filters for a Table

To set a filter:

  1. Click the Filter toolbar button. This launches the Filter dialog, as shown in Figure 3-37.

    Figure 3-37 The Filter Dialog

    Description of Figure 3-37 follows
    Description of "Figure 3-37 The Filter Dialog"

  2. Enter the fields to define a filter, as described in Table 3-4.

    Table 3-4 Fields to Define Filters

    Field Description

    Data Field

    Select the data field to filter the table data by. All elements are available regardless of whether they are included as table columns.

    Operator

    Select from the following operators: is equal to is not equal to is less than is greater than is less than or equal to is greater than or equal to is between is in top is in bottom

    Value

    Enter the value or values appropriate for the operator selected. The value can be either a text entry, or an element from the data.


3.8.3.4 Managing Filters

After you have added filters, use the Manage Filters feature to edit, delete, or change the order that the filters are applied.

To manage filters:

  1. Click the Manage Filters toolbar button to launch the Manage Filters dialog, as shown in Figure 3-38.

    Figure 3-38 The Manage Filters Dialog

    Description of Figure 3-38 follows
    Description of "Figure 3-38 The Manage Filters Dialog"

  2. Hover the cursor over the filter to display the actions toolbar. Use the toolbar buttons to edit the filter, move the filter up or down in the order of application, delete, or add another filter.

3.8.3.5 About Conditional Formats

A conditional format changes the formatting of an element in the table based on a condition. This feature is extremely useful for highlighting target ranges of values in the table. For example, you could create a set of conditional formats for the table that display rows in different colors depending on threshold values.

3.8.3.6 Applying Conditional Formats to a Table

To apply a conditional format:

  1. Click the Highlight button. This launches the Highlight dialog, as shown in Figure 3-39.

    Figure 3-39 The Highlight Dialog

    Description of Figure 3-39 follows
    Description of "Figure 3-39 The Highlight Dialog"

  2. Enter the fields to define a condition and format to apply, as described in Table 3-5.

    Table 3-5 Fields to Define Conditions and Formats

    Field Description

    Data Field

    Select the data field to apply the condition to. All elements are available regardless of whether they are included as table columns. For example, you may want to highlight in red all employees with salaries greater than $10,000, but not actually include the salary element in the table.

    Operator

    Select from the following operators: is equal to is not equal to is less than is greater than is less than or equal to is greater than or equal to is between

    Value

    Enter the value or values appropriate for the operator selected. The value can be either a text entry, or an element from the data.

    Important: If entering a date value, use on of the following XSL date or time formats: YYYY-MM-DD or YYYY-MM-DDTHH:MM:SS.

    Font Family

    Select the font to apply to the row of data that meets the condition. You can also apply bold, italic, or underline emphasis.

    Size

    Select the size of the font to apply to the row of data that meets the condition.

    Color

    Click the color box to open the Color Picker. Choose one of the predefined colors or click Custom Color to define a color to apply to the font.

    Background Color

    Click the color box to open the Color Picker. Choose one of the predefined colors or click Custom Color to define the background color to apply to the row.


    Figure 3-40 shows the table in the layout with the condition applied.

    Figure 3-40 Example of Conditional Formatting

    Description of Figure 3-40 follows
    Description of "Figure 3-40 Example of Conditional Formatting"

3.8.3.7 Managing Formats

After you have added conditional formats, use the Manage Formats command to edit or delete a format.

To manage formats:

  1. Click the Manage Formats button to launch the Manage Conditional Formats dialog, as shown in Figure 3-41.

    Figure 3-41 The Manage Conditional Formats Dialog

    Description of Figure 3-41 follows
    Description of "Figure 3-41 The Manage Conditional Formats Dialog"

  2. Hover the cursor over an item to display the actions toolbar. Use the toolbar buttons to edit the format, move the format up or down in the order of application, delete, or add another format. The order of the conditions is important because only the first condition that is met is applied.

3.8.3.8 Controlling the Display of the Total Row

By default, the layout editor inserts a total row in a table that sums numeric columns. To remove the total row, click the Show menu and select the table view without the highlighted total row. Figure 3-42 shows the Show menu options.

Figure 3-42 The Show Menu Options

Description of Figure 3-42 follows
Description of "Figure 3-42 The Show Menu Options"

The total row can be further customized using the Total Cell tab and the Properties pane. For more information see Section 3.8.6, "About the Total Cell Tab."

3.8.4 About the Table Column Header Tab

Figure 3-43 shows the Table Column Header tab.

Figure 3-43 The Table Column Header Tab

Description of Figure 3-43 follows
Description of "Figure 3-43 The Table Column Header Tab"

The Table Column Header tab enables you to perform the following:

  • Edit the font properties of the table header column

  • Edit the cell properties of the table header including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the table header

  • Apply grouping

3.8.4.1 About Grouping

"Grouping" groups together elements in the data of the same value. In a table, applying grouping can make the table easier to read.

The Grouping option enables you to choose between "Group Left" or "Group Above". Group left maintains the "group by" element within the table. Figure 3-44 shows a table that has been grouped by Manager using Group Left.

Figure 3-44 Example of a Table Grouped by Manager Using Group Left

Description of Figure 3-44 follows
Description of "Figure 3-44 Example of a Table Grouped by Manager Using Group Left"

Group above inserts a Repeating Section component, and extracts the grouping element from the table. The grouping element is instead displayed above the table and a separate table is displayed for each occurrence of the grouping element. Figure 3-45 shows a table that has been grouped by Manager using Group Above.

Figure 3-45 Example of a Table Grouped by Manager Using Group Above

Description of Figure 3-45 follows
Description of "Figure 3-45 Example of a Table Grouped by Manager Using Group Above"

3.8.4.2 Example: Group Left

In Figure 3-46, the table data has been grouped by the elements of the first two columns, Manager and Title. Notice that there is only one entry per manager name and one entry for each job title under that manager name. This organizes the data rows more cleanly in the table.

Figure 3-46 Group Left Example

Description of Figure 3-46 follows
Description of "Figure 3-46 Group Left Example"

3.8.4.3 Applying Subtotals

To further enhance a table, you can add a subtotal row to display for each grouped occurrence of the element. Figure 3-47 shows the same table with the Subtotals box checked. Notice that for each manager a subtotal row has been inserted.

Figure 3-47 Example of Subtotals

Description of Figure 3-47 follows
Description of "Figure 3-47 Example of Subtotals"

3.8.4.4 Example: Group Above

In Figure 3-48, the table data has been grouped by Manager. Notice that in the design pane, the Data Table component has been replaced with a Repeating Element component that contains the data table. The Manager element is inserted above the table with a label.

Figure 3-48 Group Above Example

Description of Figure 3-48 follows
Description of "Figure 3-48 Group Above Example"

The label is a text item. Edit the text by double-clicking the item to select it, then single-clicking to edit.

When you run the report, a separate table is created for each occurrence of the grouping element. In Interactive output mode, the grouping element displayed at the top of the table is displayed as a filter. Select the value that you want to view from the list, as shown in Figure 3-49.

Figure 3-49 Grouping Element Displayed as a Filter

Description of Figure 3-49 follows
Description of "Figure 3-49 Grouping Element Displayed as a Filter"

3.8.5 About the Column Tab

The Column tab is enabled when you select a specific column in a table. Figure 3-50 shows the Column tab.

Figure 3-50 The Column Tab

Description of Figure 3-50 follows
Description of "Figure 3-50 The Column Tab"

It enables you to perform the following:

  • Edit the font properties of the column including style, size, and color

  • Edit the cell properties of the column including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the column contents

  • Apply formatting to the column data (options depend on the data type)

  • Apply grouping

  • Apply a running total (or other formula) to the data

  • Apply sorting and sort precedence

  • Apply conditional formatting to the column

3.8.5.1 About the Data Formatting Options for Columns

The options available from the Data Formatting region of the tab depend on the data type of the column selected. The tab provides common options to choose from.

3.8.5.2 Applying Formatting to Numeric Data Columns

If the column contains numeric data, the following formatting options are available:

  • Format - Select one of the common number formats from the list. The format is applied immediately to the table column. The formats are categorized by Number, Percent, and Currency, as shown in Figure 3-51.

    Figure 3-51 Number, Percent, and Currency Formats

    Description of Figure 3-51 follows
    Description of "Figure 3-51 Number, Percent, and Currency Formats"

  • Decimal position - Click the Move Left or Move Right to increase or decrease the decimal positions displayed.

  • Show/Hide Grouping Separator - Click this button to hide the grouping separator (for example, 1,234.00 displays as 1234.00). To show the grouping separator, click the button again.

3.8.5.3 Applying Formatting to Date Type Data Columns

If the column contains dates, the following formatting options are available:

3.8.5.4 About the Formula Option

The options available from the Formula region of the column tab depend on the data type of the column.

For more information about applying formulas, see Section 3.15, "Setting Predefined or Custom Formulas."

3.8.5.5 About the Sort Option

To sort the data in a column, select the column, then under the Sort group click Ascending Order or Descending Order.

To sort by more than one column, select the column, the sort order, and then assign a Priority to each column. The priority list is a list of values beneath the sort order commands.

For example, in the employee salary table shown in Figure 3-53, assume you want to sort ascending first by Title then sort descending by Annual Salary:

Figure 3-53 Employee Salary Table

Description of Figure 3-53 follows
Description of "Figure 3-53 Employee Salary Table"

To apply the sort order to this table:

  1. Select the Title column.

  2. On the Column tab, under Sort, click the Ascending Order button.

  3. From the Priority list, select 1.

    Figure 3-54 shows the Priority list.

    Figure 3-54 Priority List

    Description of Figure 3-54 follows
    Description of "Figure 3-54 Priority List"

  4. Next select the Annual Salary column.

  5. On the Column tab, under Sort, click the Descending Order button.

  6. From the Priority list, select 2.

The sorted table is shown in Figure 3-55.

Figure 3-55 Example of a Sorted Table

Description of Figure 3-55 follows
Description of "Figure 3-55 Example of a Sorted Table"

3.8.5.6 Removing a Sort Order

To remove a sort order applied to a column:

  1. Select the column.

  2. From the Sort region on the Column tab, click the appropriate button of the sort order that has been applied. For example, to deselect the ascending order, click the Ascending Order button to undo the sort.

3.8.6 About the Total Cell Tab

The Layout Editor automatically inserts a grand total row when you insert a data table to the layout. As shown in the section on grouping, you can also insert subtotal rows within the table based on a grouping element. To edit the attributes of the cells in a grand total or subtotal row, select the cell and use the options in the Total Cell tab shown in Figure 3-56.

Figure 3-56 The Total Cell Tab

Description of Figure 3-56 follows
Description of "Figure 3-56 The Total Cell Tab"

The Total Cell tab enables you to perform the following:

  • Edit the font properties of the total cell

  • Edit the cell properties of the total cell including border weight, style, and color and background fill color

  • Set the vertical and horizontal alignment of the table header

  • Apply formatting to the cell data

  • Apply a formula to the cell

  • Apply conditional formatting to the cell

3.8.6.1 Applying Data Formatting to a Total Cell

See Section 3.8.5.1, "About the Data Formatting Options for Columns."

3.8.6.2 Applying a Formula

By default, the formula applied to a Total Cell within a numeric column is a sum of the column items. The Formula option enables you to apply a different formula.

Not all options available from the Formula region of the column tab are applicable to a Total Cell.

For more information about applying formulas, see Section 3.15, "Setting Predefined or Custom Formulas."

3.8.7 Inserting Dynamic Hyperlinks

The layout editor supports dynamic hyperlinks in tables.

To insert a dynamic hyperlink:

  1. Select the table column.

  2. Click Properties. The column properties include an option for URL, as shown in Figure 3-57

    Figure 3-57 URL Property Option for the Selected Column

    Selecting column and displaying Properties pane
  3. In the URL field, enter the static portion of the URL and embed the absolute path to the element that provides the dynamic portion of the URL within curly braces {}. For example:

    http://example.com/show_page?id={/DATA/GROUP1/ELEMENT_NAME}

    where http://example.com/show_page?id= is the static portion of the URL and {/DATA/GROUP1/ELEMENT_NAME} is the absolute path to the element in the data that supplies the dynamic portion.

For example, in the employee salary report, suppose each employee name should render as a hyperlink to the employee's person record. Assume the static portion of the URL to each person record is

https://people.hrserver.com/records/show_page?id=

The dynamic portion comes from the data element EMPLOYEE_ID. For this example, append the full path to the EMPLOYEE_ID element within curly braces and enter this in the URL field as follows:

https://people.hrserver.com/records/show_page?id={/ROWSET/ROW/EMPLOYEE_ID}

If you are unsure of the correct element names in the absolute path, hover your mouse over the data element on the Data Source pane to display the path in the hover text, as shown in Figure 3-58.

Figure 3-58 Hover Text Displaying Absolute Path to Element

Hover Text Displaying Absolute Path to Element

3.9 About Charts

The layout editor supports a variety of chart types and styles to graphically present data in the layout. Figure 3-59 shows side-by-side vertical bar and pie charts in the layout editor.

Figure 3-59 Side-by-Side Vertical Bar and Pie Charts in the Layout Editor

Description of Figure 3-59 follows
Description of "Figure 3-59 Side-by-Side Vertical Bar and Pie Charts in the Layout Editor"

Once inserted, you can edit the chart properties using the dynamic toolbars or the Properties pane. The Properties pane extends the options from the Chart tab and enables you to enter very specific custom settings for the following:

Note:

Some font effects such as underline, italic, and bold might not render in PDF output.

3.9.1 Inserting a Chart

To insert a chart:

  1. From the Insert menu, select and drag the Chart component to the layout.

    By default an empty vertical bar chart is inserted and the Chart dynamic tab is displayed, as shown in Figure 3-60.

    Figure 3-60 Chart Dynamic Tab

    Description of Figure 3-60 follows
    Description of "Figure 3-60 Chart Dynamic Tab"

  2. To change the chart type, click the Chart Type list to select a different type. In Figure 3-61 the chart type is changed to Pie.

    Figure 3-61 The Pie Chart Type

    Description of Figure 3-61 follows
    Description of "Figure 3-61 The Pie Chart Type"

  3. Select and drag the data fields from the Data Source pane to the appropriate areas in the chart. The chart immediately updates with the preview data, as shown in Figure 3-62.

    Figure 3-62 Dragging and Dropping Data Fields to a Chart

    Description of Figure 3-62 follows
    Description of "Figure 3-62 Dragging and Dropping Data Fields to a Chart"

  4. To resize the chart, drag and drop the resize handler on the lower right corner of the chart, as shown in Figure 3-63.

    Figure 3-63 Chart Resizing

    Description of Figure 3-63 follows
    Description of "Figure 3-63 Chart Resizing"

3.9.1.1 About the Chart Tab

The Chart tab enables you to perform the following:

  • Select a different Chart Type

  • Apply a different Chart Style

  • Enable 3-D effects

  • Filter the data that is displayed in the chart

  • Manage multiple filters

  • Convert the chart to a pivot table or switch the series and dimensions values

3.9.1.2 Applying and Managing Filters

See Section 3.8.3.2, "About Filters" for information on how to apply and manage filters.

3.9.1.3 Converting a Chart to a Pivot Table

To convert a chart to a pivot table:

  1. Select the chart.

  2. In the Convert group, click Pivot Table.

The layout editor converts the label, series, and value elements of the chart into the appropriate rows, columns, and data elements of a pivot table.

3.9.2 Changing the Formula Applied to a Chart Measure Field

By default, the chart displays a sum of the values of the chart measure. You can change the formula applied to a chart measure field by selecting an option from the Chart Measure Field tab.

To change the formula:

  1. Select the measure field in the chart. This displays the Chart Measure Field tab, as shown in Figure 3-64.

    Figure 3-64 Chart Measure Field Tab

    Description of Figure 3-64 follows
    Description of "Figure 3-64 Chart Measure Field Tab"

  2. Select from the following options available from the Formula list:

    • Count

    • Sum

    • Running Total

3.9.3 Sorting a Chart Field

To sort a field in the chart:

  1. Select the field to display the Chart Field tab.

  2. On the Chart Field tab select Sort Ascending or Sort Descending.

  3. To sort by multiple fields, apply a Priority to each sort field to apply the sort in the desired order.

3.10 About Gauge Charts

A gauge chart is a useful way to illustrate progress or goals. For example, Figure 3-65 shows a report with three gauges to indicate the status of regional sales goals:

Figure 3-65 Gauges Showing the Status of Regional Sales Goals

Description of Figure 3-65 follows
Description of "Figure 3-65 Gauges Showing the Status of Regional Sales Goals"

3.10.1 Inserting a Gauge Chart

To insert a gauge chart in the layout:

  1. From the Insert menu, select and drag the Gauge component to the layout. This inserts an empty gauge chart, as shown in Figure 3-66.

    Figure 3-66 An Empty Gauge Chart

    Description of Figure 3-66 follows
    Description of "Figure 3-66 An Empty Gauge Chart"

  2. Select and drag the data fields from the Data Source pane to the Label, Value, and Series areas of the chart. The chart immediately updates with the preview data.

    Figure 3-67 shows REGION being dragged to the Label area and DOLLARS being dragged to the Value area:

    Figure 3-67 Dragging and Dropping REGION and DOLLARS to a Chart

    Description of Figure 3-67 follows
    Description of "Figure 3-67 Dragging and Dropping REGION and DOLLARS to a Chart"

    Note the following:

    • A separate gauge is created for each occurrence of the Label (that is, each REGION). One set of properties applies to each occurrence. To apply different properties to each gauge, see Creating Filtered Gauges.

    • By default, the Value field is a sum. You can change the expression applied to the value field. See Section 3.9.2, "Changing the Formula Applied to a Chart Measure Field."

    • You can apply a sort to the other gauge chart fields.

3.10.2 Setting the Properties for a Gauge Chart

Use the Properties Pane to set detailed options for a gauge chart.

3.10.3 Applying and Managing Filters

See Section 3.8.3.2, "About Filters" for information on how to apply and manage filters.

3.11 About Pivot Tables

The pivot table provides views of multidimensional data in tabular form. It supports multiple measures and dimensions and subtotals at all levels. Figure 3-68 shows a pivot table.

Figure 3-68 A Pivot Table

Description of Figure 3-68 follows
Description of "Figure 3-68 A Pivot Table"

3.11.1 Inserting a Pivot Table

To insert a pivot table:

  1. From the Insert tab, select and drag the Pivot Table component to the layout. Figure 3-69 shows the empty pivot table structure.

    Figure 3-69 The Empty Pivot Table Structure

    Description of Figure 3-69 follows
    Description of "Figure 3-69 The Empty Pivot Table Structure"

  2. Drag and drop data fields from the Data Source pane to the row, column, and data positions.

    Drag multiple fields to the pivot table and place them precisely to structure the pivot table, as shown in Figure 3-70.

    Figure 3-70 Dragging and Dropping Data Fields to a Pivot Table

    Description of Figure 3-70 follows
    Description of "Figure 3-70 Dragging and Dropping Data Fields to a Pivot Table"

  3. By default the pivot table is inserted with no data formatting applied. To apply a format to the data, click the first column of data to enable the Pivot Table Data toolbar. On the Data Formatting group, select the appropriate format as shown in Figure 3-71.

    Figure 3-71 Selecting a Format

    Description of Figure 3-71 follows
    Description of "Figure 3-71 Selecting a Format"

  4. Optionally resize the pivot table by clicking and dragging the handler in the lower right corner of the pivot table, as shown in Figure 3-72.

    Figure 3-72 Resizing a Pivot Table

    Description of Figure 3-72 follows
    Description of "Figure 3-72 Resizing a Pivot Table"

3.11.2 Customizing a Pivot Table Menu

After you insert a pivot table customize the appearance and layout using the following dynamic tabs:

  • Pivot Table tab

  • Pivot Table Header tab

  • Pivot Table Data tab

3.11.3 About the Pivot Table Tab

Figure 3-73 shows the Pivot Table tab.

Figure 3-73 The Pivot Table Tab

Description of Figure 3-73 follows
Description of "Figure 3-73 The Pivot Table Tab"

3.11.3.1 Applying Filters

See Section 3.8.3.2, "About Filters" for a description of he Filter and Manage Filters features.

3.11.3.2 Customizing the Display of Totals

The Pivot Table tab enables you to quickly customize the display of grand total and subtotal rows.

By default, the layout editor inserts the pivot table with the total and subtotal displays as shown in the tab:

  • Row Grand Total - Inserted at bottom of table

  • Row Subtotal - Inserted at top of each subgroup, with no row header

  • Column Grand Total - Inserted at the far right

  • Column Subtotal - Inserted to the left of each column subgroup, with no header

Change the positioning and display of totals and subtotals by clicking the appropriate group in the tab and selecting the desired layout pattern from the menu.

3.11.3.3 Converting a Pivot Table to a Chart

The Convert Pivot Table to a Chart command converts the pivot table to a default vertical bar chart. After conversion, customize the table as described in Section 3.9, "About Charts."

Figure 3-74 shows the pivot table created in the preceding step converted to a vertical bar chart.

Figure 3-74 A Pivot Table Converted to a Vertical Bar Chart

Description of Figure 3-74 follows
Description of "Figure 3-74 A Pivot Table Converted to a Vertical Bar Chart"

3.11.3.4 Switching Rows and Columns

Use the Switch Rows and Columns command to see a different view of the same data. Figure 3-75 shows the pivot table created in the previous step with rows and columns switched.

Figure 3-75 A Pivot Table with Rows and Columns Switched

Description of Figure 3-75 follows
Description of "Figure 3-75 A Pivot Table with Rows and Columns Switched"

3.11.4 Customizing the Pivot Table Headers

The Pivot Table Header tab is shown in Figure 3-76.

Figure 3-76 The Pivot Table Header Tab

Description of Figure 3-76 follows
Description of "Figure 3-76 The Pivot Table Header Tab"

Select the column or row header of the pivot table and use the Pivot Table Header tab to perform the following:

  • Customize the fonts, colors, alignment and other display features of the header

  • Apply a sort order (for more information see Section 3.8.5.5, "About the Sort Option")

  • Apply data formatting (if the data type is number or date)

3.11.5 Customizing the Pivot Table Data

The Pivot Table Data tab is shown in Figure 3-77.

Figure 3-77 The Pivot Table Data Tab

Description of Figure 3-77 follows
Description of "Figure 3-77 The Pivot Table Data Tab"

Select the data area of the pivot table and use the Pivot Table Data tab to perform the following actions. The commands in the Pivot Table Data tab are the same as the corresponding commands in the table Column tab. See the references for more information on their use.

3.12 About Text Items

The text item component allows you to enter free-form text in the layout.

To create a text item component:

  1. Drag and drop the text item component to the layout.

  2. Double-click the text to enter text editor mode. Select parts of the text to apply different formatting to different parts.

3.12.1 Displaying a Data Field Side by Side with a Text Item

By default, the text item always spawns a complete paragraph. Inserting a data field next to the text field places the data field beneath the text field as shown in Figure 3-78.

Figure 3-78 A Data Field Beneath a Text Item

Description of Figure 3-78 follows
Description of "Figure 3-78 A Data Field Beneath a Text Item"

To display the data field inline with the text item:

  1. Set the Display property to Inline in the Properties pane, as shown in Figure 3-79.

    Figure 3-79 Setting the Display Property to Inline

    Description of Figure 3-79 follows
    Description of "Figure 3-79 Setting the Display Property to Inline"

    This setting enables the positioning of text items and data fields into a single line as shown in Figure 3-80.

    Figure 3-80 Text Items and Data Fields Positioned in a Single Line

    Description of Figure 3-80 follows
    Description of "Figure 3-80 Text Items and Data Fields Positioned in a Single Line"

3.12.2 About the Text Toolbar

The Text tab is shown in Figure 3-81.

The Text tab enables you to perform the following:

  • Set the font properties

  • Set alignment of the text in the grid cell

  • Insert predefined text items: page number, date, and time

  • Insert a hyperlink

3.12.2.1 Editing Font Properties

Use the Font group of commands to set the following:

  • Select a font style

  • Select a font size

  • Apply emphasis (bold, italic, or underline)

  • Insert a border around the text item

  • Apply a background color

  • Apply a font color

3.12.2.2 Inserting Page Numbers

Drag and drop the page number component to the design area.

Figure 3-82 shows the Page # of N construction.

Figure 3-82 Page # of N Construction

Description of Figure 3-82 follows
Description of "Figure 3-82 Page # of N Construction"

To create the Page # of N construction:

  1. From the Insert tab drag and drop a Text Item to the design area where you want the page numbers to display.

  2. Double-click the inserted text to select the text item for editing. Type "Page ".

  3. From the Text dynamic tab, drag and drop the Page Number component.

  4. Enter a space, and type "of ".

  5. From the Text dynamic tab, drag and drop the Page Total component.

3.12.2.3 Inserting the Date and Time

To insert the date and time in a report:

  1. From the Insert tab drag and drop a Text Item to the design area where you want the date and time to display.

  2. Double-click the inserted text to select the text item for editing.

  3. Click the Date icon to insert the date icon in the text item. Click the Time icon to insert the time icon in the text item.

    Note:

    To display the items side-by-side, set the Text Item property to "Inline".

    Figure 3-83 shows the insertion of the date and time icons.

    Figure 3-83 Inserting the Date and Time Icons

    Description of Figure 3-83 follows
    Description of "Figure 3-83 Inserting the Date and Time Icons"

When this report is viewed, the date and time are displayed according to the server time zone if viewed online, or for scheduled reports, the time zone selected for the schedule job. Figure 3-84 shows the date and time displayed in a report.

Figure 3-84 The Date and Time Displayed in a Report

Description of Figure 3-84 follows
Description of "Figure 3-84 The Date and Time Displayed in a Report"

3.12.2.4 Inserting a Hyperlink

To insert a hyperlink in a report:

  1. From the Insert tab drag and drop a Text Item to the design area where you want the date and time to display.

  2. Double-click the inserted text to select the text item for editing. Enter the text which you want to convert to a link.

  3. Select the text, then click the Link button.

  4. In the dialog enter the URL.

3.13 About Images

The image component enables you to include a graphic in the layout. BI Publisher supports the following methods for including an image:

To insert an image:

  1. Drag and drop the image component to the layout.

  2. In the Insert an Image dialog, specify one of the following sources for the image:

    • Location: Click Browse to specify the file name and directory of the image on a local or mapped drive to upload the image.

    • URL: Enter the URL where the image is stored.

    • Field:

      Image URL: Select the field from the data that contains a URL to an image.

      Alternative Text: If the data includes a field that contains alternative text for the image, then select that field to display alternative text when the report is viewed as HTML.

    Figure 3-85 shows the Insert an Image dialog set up to retrieve an image URL dynamically from the "Image" data element. The value of the "Name" element is used as alternative text.

    Figure 3-85 Insert an Image Dialog

    Description of Figure 3-85 follows
    Description of "Figure 3-85 Insert an Image Dialog"

  3. Optionally resize the image in one of these ways:

    • Drag the right bottom corner of the image. To preserve the aspect ratio when resizing an image, press and hold the Shift key before starting to drag the corner.

    • Modify the width and height in the Properties pane.

3.14 About Lists

The list component displays all values of a data element in a vertical or horizontal list. When viewed in interactive mode, clicking an item in the list updates the results shown in the linked components of the report. Figure 3-86 shows a report that displays multiple charts based on sales data. The list component displays each country for which there is sales data. The list enables the report consumer to quickly see results for each country in the list by clicking the entry in the list.

Figure 3-86 Using a List Component to Update Results

Description of Figure 3-86 follows
Description of "Figure 3-86 Using a List Component to Update Results"

3.14.1 Inserting a List

To insert a list:

  1. From the Insert tab, select and drag the List component to the design area.

    Figure 3-87 shows an inserted, empty list.

    Figure 3-87 An Inserted, Empty List

    Description of Figure 3-87 follows
    Description of "Figure 3-87 An Inserted, Empty List"

  2. To create the list, select an element from the Data Source pane and drag it to the empty list in the layout.

    Figure 3-88 shows the list component after dragging the element Country Name to it.

    Figure 3-88 A List Component Showing Country Names

    Description of Figure 3-88 follows
    Description of "Figure 3-88 A List Component Showing Country Names"

  3. Customize the appearance of the list. See Section 3.14.2, "Customizing a List."

  4. Configure linked components using the Configure Events command. By default, all other tables and charts in the layout are configured to filter their results based on the user selections in the list component. To change this default behavior, see Section 3.4.5, "Interactivity: Event Configuration"

3.14.2 Customizing a List

Use the List tab to:

  • Edit the font size, style, and color

  • Define borders for the list

  • Set the background color

  • Edit the font color and background color for the display of selected items

  • Set the orientation of the list

  • Specify the sort order

Figure 3-89 shows the List tab.

3.14.2.1 Customizing the Font Style and the Selected Font Style Commands

In Figure 3-90, the list on the left shows the default format of the list. The list on the right shows the Selected Font default format:

Figure 3-90 Default Formats

Description of Figure 3-90 follows
Description of "Figure 3-90 Default Formats"

Edit the font settings by selecting a font family from the list and adjusting the point size.

By default, the list displays with one point black gridlines. Click the Set Border to adjust the default borders of the list. Use the Background Color and Font Color commands to customize the colors.

The Selected Font commands edit the appearance of the item in a list when it is selected. By default, the selected element is moved to the top of the list, and the background is changed to light blue. You can edit the font weight, background color, and font color that are displayed for selected items.

3.14.2.2 Customizing Behavior of Selected Items

By default, the selected items move to the top of the list and the nonselected items are "hidden" by a gray fill. You also have the option of not applying this behavior by setting the property "Hide Excluded".

This property is available from the Properties pane when the List component is selected. The Hide Excluded property is highlighted in Figure 3-91.

Figure 3-91 The Hide Excluded Property

Description of Figure 3-91 follows
Description of "Figure 3-91 The Hide Excluded Property"

Figure 3-92 shows the difference in the display depending on the setting of the property.

Figure 3-92 Display Differences of the Hide Excluded Property Settings

Description of Figure 3-92 follows
Description of "Figure 3-92 Display Differences of the Hide Excluded Property Settings"

3.15 Setting Predefined or Custom Formulas

Figure 3-93 shows the Define Custom Formula icon.

Figure 3-93 Define Custom Formula Icon

Description of Figure 3-93 follows
Description of "Figure 3-93 Define Custom Formula Icon"

The Formula group of commands is available from the following tabs:

Note that not all options are applicable to each component type.

3.15.1 About the Predefined Formulas

The menu provides the predefined formulas that are described in Table 3-6.

Table 3-6 Predefined Formulas

Formula Description

No Formula

Removes any mathematical formula from a numeric column.

Blank Text

Removes all data and inserts blank text.

Count

Returns the count of the number of occurrences of the element in the current group.

Count Distinct

Returns a count of the distinct values of an element in the current group.

Summation

Sums the values of the element in the current group.

Average

Displays the average of the values in the current group.

Maximum

Displays the highest value of all occurrences in the current group.

Minimum

Displays the lowest value of all occurrences in the current group.


For non-numeric data, only the following formula options are supported:

  • Blank Text

  • Count

  • Count Distinct

3.15.2 Applying a Custom Formula

Click Define Custom Formula to define your own formula for a component. The Function dialog enables you to define Basic Math, Context, and Statistical functions in the layout.

Figure 3-94 shows the Function dialog.

Figure 3-94 The Function Dialog

Description of Figure 3-94 follows
Description of "Figure 3-94 The Function Dialog"

3.15.2.1 About the Basic Math Functions

When you click one of the basic math functions, you are prompted to define the appropriate parameters for the function. You can enter a constant value, select a field from the data, or create a nested function to supply the value.

In Figure 3-95, clicking the Multiplication function displays prompts to enter the multiplicand and the multiplier. The example shows that the multiplicand is the value of the Amount Sold field. The multiplier is the constant value.

Figure 3-95 Example of the Multiplication Function

Description of Figure 3-95 follows
Description of "Figure 3-95 Example of the Multiplication Function"

3.15.2.2 About the Statistical Math Functions

When you click one of the statistical math functions you are prompted to define the appropriate parameter for the function. You can select a field from the data, or create a nested function to supply the values. In Figure 3-96, clicking the Average function displays prompts for you to specify the source of the values for which to calculate the average.

Figure 3-96 The Average Function

Description of Figure 3-96 follows
Description of "Figure 3-96 The Average Function"

3.15.3 Applying a Custom Formula: Examples

Example 1: Subtraction

Figure 3-97 shows data for Revenue and Cost for each Office:

Figure 3-97 A Table Showing Revenue and Cost Data for Each Office

Description of Figure 3-97 follows
Description of "Figure 3-97 A Table Showing Revenue and Cost Data for Each Office"

Using a custom formula, you can add a column to this table to calculate Profit (Revenue - Cost).

  1. Add another numeric data column to the table. For example, drag another instance of Revenue to the table, as shown in Figure 3-98.

    Figure 3-98 A Table With Two Instances of Revenue

    Description of Figure 3-98 follows
    Description of "Figure 3-98 A Table With Two Instances of Revenue"

  2. With the table column selected, click Define Custom Formula.

  3. In the Function dialog select Subtraction from the list, as shown in Figure 3-99. Because the source data for the column is Revenue, by default the Minuend and the Subtrahend both show the Revenue element.

    Figure 3-99 The Subtraction Function

    Description of Figure 3-99 follows
    Description of "Figure 3-99 The Subtraction Function"

  4. Select Subtrahend, then in the Parameter region, select Field and choose the Cost element, as shown in Figure 3-100.

    Figure 3-100 Subtraction Function with the Cost Element Selected

    Description of Figure 3-100 follows
    Description of "Figure 3-100 Subtraction Function with the Cost Element Selected"

    The dialog is updated to show that the formula is now Revenue minus Cost, as shown in Figure 3-101.

    Figure 3-101 Updated Subtraction Function Showing a Formula of Revenue Minus Cost

    Description of Figure 3-101 follows
    Description of "Figure 3-101 Updated Subtraction Function Showing a Formula of Revenue Minus Cost"

  5. Click OK to close the dialog.

  6. The table column displays the custom formula. Edit the table column header title, and now the table has a Profit column, as shown in Figure 3-102.

    Figure 3-102 A Table Showing the Custom Formula Column Titled Profit

    Description of Figure 3-102 follows
    Description of "Figure 3-102 A Table Showing the Custom Formula Column Titled Profit"

Example 2: Nested Function

This example uses a nested function to create a column that shows Revenue less taxes.

  1. Add another numeric data column to the table. For example, drag another instance of Revenue to the table, as shown in Figure 3-103.

    Figure 3-103 A Table With Two Instances of Revenue

    Description of Figure 3-103 follows
    Description of "Figure 3-103 A Table With Two Instances of Revenue"

  2. With the table column selected, click Define Custom Formula.

  3. In the Function dialog select Subtraction from the list. Because the source data for the column is Revenue, by default the Minuend and the Subtrahend both show the Revenue element, as shown in Figure 3-104.

    Figure 3-104 Subtraction Function with Minuend and Subtrahend Showing the Revenue Element

    Description of Figure 3-104 follows
    Description of "Figure 3-104 Subtraction Function with Minuend and Subtrahend Showing the Revenue Element"

  4. Select Subtrahend, then in the Parameter region, select Nested Function and click Edit, as shown in Figure 3-105.

    Figure 3-105 Subtraction Function with the Nested Function Selected

    Description of Figure 3-105 follows
    Description of "Figure 3-105 Subtraction Function with the Nested Function Selected"

    A second Function dialog is displayed to enable you to define the nested function. In this case the nested function is Revenue times a constant value (tax rate of .23), as shown in Figure 3-106.

    Figure 3-106 The Function Dialog Showing the Nested Function Revenue Times a Constant Value

    Description of Figure 3-106 follows
    Description of "Figure 3-106 The Function Dialog Showing the Nested Function Revenue Times a Constant Value"

  5. Click OK to close the dialog. The primary Function dialog now shows the nested function as the source of the subtrahend, as shown in Figure 3-107.

    Figure 3-107 The Function Dialog Showing a Nested Function as the Source of the Subtrahend

    Description of Figure 3-107 follows
    Description of "Figure 3-107 The Function Dialog Showing a Nested Function as the Source of the Subtrahend"

  6. Click OK to close the Function dialog. The table column displays the custom formula. Edit the table column header label, and now the table displays the custom function, as shown in Figure 3-108.

    Figure 3-108 A Table Showing the Custom Function Revenue less tax (23%)

    Description of Figure 3-108 follows
    Description of "Figure 3-108 A Table Showing the Custom Function Revenue less tax (23%)"

3.16 Saving a Layout

To save the layout to the report definition:

  1. Click the Save or Save As toolbar button

  2. The Save Layout dialog displays the list of layouts defined for the report definition as shown in the following figure:

    Figure 3-109 The Save Layout Dialog

    Description of Figure 3-109 follows
    Description of "Figure 3-109 The Save Layout Dialog"

  3. Enter a unique name for this layout.

  4. Select a Locale.

    Note:

    When you have saved the layout, the Locale cannot be updated.