| Oracle® Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1) Part Number E22254-02 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher 11g Release 1 (11.1.1) Part Number E22254-02 |
|
|
PDF · Mobi · ePub |
This chapter describes creating BI Publisher (.xpt) layout templates using the layout editor.
This chapter includes the following topics:
Release 11g of Oracle BI Publisher introduces a new type of layout template. The BI Publisher Layout template enables end users to:
View Dynamic HTML output and perform lightweight interaction with their report data from within a browser
Generate high fidelity, pixel perfect reports to PDF, RTF, Excel, PowerPoint, and static HTML
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.
Notice the following features:
Pop-up chart details - Hover cursor over chart items to display details of data.
Group filtering - Grouped regions can be filtered by the grouping element.
Scrollable tables - Table data can be scrolled while maintaining display of the headers and totals.
Table column sorting - Table data can be sorted by different columns from within the viewer.
Table column filtering - Table data can be filtered by values in different columns from within the viewer.
Automatic table totaling - Table data totals are automatically added to the layout.
Propagated filtering - Filter other components by clicking on chart areas or by clicking on pivot table header, column, or elements.
Collapse and expand areas of the document.
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).
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.
Launch the layout editor in one of the following ways:
To launch the Layout Editor when creating a new report:
Selecting the data model for the new report.
The Report Editor displays the Add Layout page.
From the Create Layout region, click a predefined template to launch the Layout Editor.
To launch the Layout Editor when editing a report:
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.
From the Create Layout region, click a predefined template to use to launch the Layout Editor.
To launch the Layout Editor when viewing a report:
Click Actions and then click Edit Layout.
The layout must have been created in the layout editor.
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.
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.
To add predefined layout files to the shared directory for all users to access:
Log in with Administrator privileges and navigate to the Catalog.
In the Shared Folders directory, open the Components folder.
Locate the Boilerplates report and click Edit.
Click Add New Layout.
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).
Save the report.
Any BI Publisher Templates (.xpt) added to this report are displayed to all users as a Shared Template.
To add predefined layouts that are available to your account user only:
Navigate to My Folders.
Create a new report called "Boilerplates". This report does not have a data model.
Click Add New Layout.
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.
Figure 3-3 shows the Layout Editor.
The Layout Editor interface comprises the following:
The top of the Layout Editor contains two toolbars:
The Static toolbar is always available and contains common commands such as save and preview. See Section 3.3.4, "About the Static Toolbar."
The Tabbed toolbar includes the Insert tab, the Page Layout tab, and a dynamic tab that shows the most commonly used actions and commands for the selected layout component. You can collapse this toolbar to make more room to view the design area. See Section 3.3.5, "About the Tabbed Toolbar."
The accordion pane on the left contains the following:
Use the Data Source pane to select the data fields to drag to the layout components.
Use the Components pane to select layout components and drag them to the design area. You can also use the Insert tab to insert components when this pane is collapsed.
Use the Properties pane to modify properties for the selected layout component.
You can expand and display each control by clicking the title of the control or the plus sign next to the title of the control. You can collapse the entire accordion pane to allow more room to view the layout.
The lower right region is the design area for building the layout.
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.
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.
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.
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
The Static toolbar extends on either side of the tabbed toolbar and is shown in Figure 3-7.
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.
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"
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.
The Delete tool provides a similar function to the Select tool to enable you to precisely select the component to delete.
Use the Insert tab to insert report components and page elements. Figure 3-9 shows 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.
Figure 3-10 shows the Page Layout tab.
The Page Layout tab contains commands to set up the layout.
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. |
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:
Show in the first page - Select True to show the header in the first page. Select False to suppress the header from the first page.
Figure 3-11 shows the Properties for a report header.
Figure 3-11 The Properties for a Report Header
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.
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. |
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).
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.
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
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
To configure automatic filtering:
On the Page Layout tab, click Event Configuration to display the Configure Events dialog.
Figure 3-14 shows the Configure Events dialog.
In the Components column, click the layout component (lists, charts, and pivot tables are available to configure).
Select Filter to enable automatic filtering in other report components.
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.
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
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
To set the page margins for the report:
Click anywhere in the design area outside of an inserted component.
Click the Properties pane in the lower left of the Layout Editor. Figure 3-17 shows the Properties for the page.
Click the value shown for Margin to launch the Margin dialog.
Figure 3-18 shows the Margin dialog.
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.
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:
Click anywhere in the design area outside of an inserted component.
Click the Properties pane in the lower left of the Layout Editor. Figure 3-19 shows the Properties for the page.
Click the value shown for Max. Connections and select the desired value from the list, as shown in Figure 3-20.
The layout editor supports components that are typically used in reports and other business documents. The followings components are described in these sections:
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:
Select and drag the Layout Grid component to the design area.
Figure 3-21 shows the 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
Note the following about a layout grid:
The grid is created with equidistant columns, and the row size defaults to a minimum of one row of text.
Although Font properties are not enabled for a layout grid cell (set font properties using the individual component properties), the background color and border properties are enabled.
When you insert a component to a grid cell, it automatically resizes to accommodate the component.
Adjust the column width and height by either positioning the mouse pointer over the border and dragging the blue bar, or by changing the grid column properties in the Properties pane.
The grid supports merging of cells.
You can insert a grid inside a grid.
Similar to Microsoft Word, the grid uses a flow layout that is very convenient for designing business documents. Components that do not occupy a full paragraph or block are positioned top-down and left to right.
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:
Click the Background Color command button to launch the Color Picker.
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
To join cells horizontally or vertically:
Select multiple adjacent cells by holding down the Ctrl key and clicking each grid cell.
Click the Join command button.
To unjoin cells that have been joined:
Select the joined cell and click the Unjoin button.
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:
Select the layout grid.
Open the Properties pane.
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
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
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:
Drag and drop the repeating section component to the layout.
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
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:
Select the repeating section component.
Open the Properties pane.
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
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
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
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:
Select the repeating section component.
Open the Properties pane.
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
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
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:
Table
Table Column Header
Column
Total Cell
This section contains the following topics about working with tables:
To insert a data table:
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
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
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
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."
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
To set an alternating row color:
Select the table.
Open the Properties pane.
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
Choose a color and click OK.
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.
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.
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.
To set a filter:
Click the Filter toolbar button. This launches the Filter dialog, as shown in Figure 3-37.
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. |
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:
Click the Manage Filters toolbar button to launch the Manage Filters dialog, as shown in Figure 3-38.
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.
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.
To apply a conditional format:
Click the Highlight button. This launches the Highlight dialog, as shown in Figure 3-39.
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
After you have added conditional formats, use the Manage Formats command to edit or delete a format.
To manage formats:
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
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.
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.
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."
Figure 3-43 shows 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
"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
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
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.
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.
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.
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
The Column tab is enabled when you select a specific column in a table. Figure 3-50 shows 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
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.
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
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.
If the column contains dates, the following formatting options are available:
Format - Select one of the common date formats from the list. The format is applied immediately to the table column. The formats are categorized by Date and Time, as shown in Figure 3-52.
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."
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:
To apply the sort order to this table:
Select the Title column.
On the Column tab, under Sort, click the Ascending Order button.
From the Priority list, select 1.
Figure 3-54 shows the Priority list.
Next select the Annual Salary column.
On the Column tab, under Sort, click the Descending Order button.
From the Priority list, select 2.
The sorted table is shown in Figure 3-55.
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.
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
See Section 3.8.5.1, "About the Data Formatting Options for Columns."
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."
The layout editor supports dynamic hyperlinks in tables.
To insert a dynamic hyperlink:
Select the table column.
Click Properties. The column properties include an option for URL, as shown in Figure 3-57
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.
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
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:
Chart Effect
Chart Legend
Chart Plot Area
Chart Title
Chart Label
Note:
The following Chart Label properties apply to Scatter and Bubble chart types only: Title Font, Title Horizontal Align, Title Text, and Title Visible.
Chart Values
Note:
Some font effects such as underline, italic, and bold might not render in PDF output.
To insert a chart:
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.
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.
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
To resize the chart, drag and drop the resize handler on the lower right corner of the chart, as shown in Figure 3-63.
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
See Section 3.8.3.2, "About Filters" for information on how to apply and manage filters.
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:
Select the measure field in the chart. This displays the Chart Measure Field tab, as shown in Figure 3-64.
Select from the following options available from the Formula list:
Count
Sum
Running Total
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
To insert a gauge chart in the layout:
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.
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
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.
Use the Properties Pane to set detailed options for a gauge chart.
See Section 3.8.3.2, "About Filters" for information on how to apply and manage filters.
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.
To insert a pivot table:
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
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
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.
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.
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
Figure 3-73 shows the Pivot Table tab.
See Section 3.8.3.2, "About Filters" for a description of he Filter and Manage Filters features.
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.
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
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
The Pivot Table Header tab is shown in Figure 3-76.
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)
The Pivot Table Data tab is shown in Figure 3-77.
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.
Customize the fonts, colors, alignment and other display features of the data
Apply conditional formatting to the data for more information (see Section 3.8.3.5, "About Conditional Formats")
Apply data formatting (see Section 3.8.5.1, "About the Data Formatting Options for Columns")
Apply a formula (see Section 3.8.6.2, "Applying a Formula")
The text item component allows you to enter free-form text in the layout.
To create a text item component:
Drag and drop the text item component to the layout.
Double-click the text to enter text editor mode. Select parts of the text to apply different formatting to different parts.
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
To display the data field inline with the text item:
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
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
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
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
Drag and drop the page number component to the design area.
Figure 3-82 shows the Page # of N construction.
To create the Page # of N construction:
From the Insert tab drag and drop a Text Item to the design area where you want the page numbers to display.
Double-click the inserted text to select the text item for editing. Type "Page ".
From the Text dynamic tab, drag and drop the Page Number component.
Enter a space, and type "of ".
From the Text dynamic tab, drag and drop the Page Total component.
To insert the date and time in a report:
From the Insert tab drag and drop a Text Item to the design area where you want the date and time to display.
Double-click the inserted text to select the text item for editing.
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
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
To insert a hyperlink in a report:
From the Insert tab drag and drop a Text Item to the design area where you want the date and time to display.
Double-click the inserted text to select the text item for editing. Enter the text which you want to convert to a link.
Select the text, then click the Link button.
In the dialog enter the URL.
The image component enables you to include a graphic in the layout. BI Publisher supports the following methods for including an image:
Static image: Upload a static image that is saved in the report file. An uploaded image file must be in one of the following graphic file formats: GIF, JPEG, PNG, or BMP. The image file cannot be larger than 500 KB.
Static URL: Specify a static link to a URL where an image is stored.
Dynamic URL: Include the image URL in an element of the data. The value of the element is evaluated at runtime enabling dynamic insertion of images.
To insert an image:
Drag and drop the image component to the layout.
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.
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.
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
To insert a list:
From the Insert tab, select and drag the List component to the design area.
Figure 3-87 shows an inserted, empty list.
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
Customize the appearance of the list. See Section 3.14.2, "Customizing a List."
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"
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.
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:
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.
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-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
Figure 3-93 shows the Define Custom Formula icon.
The Formula group of commands is available from the following tabs:
Column tab
Total Cell tab
Chart Measure Field tab
Pivot Table Data tab
Note that not all options are applicable to each component type.
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
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.
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
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.
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
Using a custom formula, you can add a column to this table to calculate Profit (Revenue - Cost).
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
With the table column selected, click Define Custom Formula.
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.
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
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
Click OK to close the dialog.
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
Example 2: Nested Function
This example uses a nested function to create a column that shows Revenue less taxes.
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
With the table column selected, click Define Custom Formula.
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
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
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
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
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%)
To save the layout to the report definition:
Click the Save or Save As toolbar button
The Save Layout dialog displays the list of layouts defined for the report definition as shown in the following figure:
Enter a unique name for this layout.
Select a Locale.
Note:
When you have saved the layout, the Locale cannot be updated.
|
 Copyright © 2010, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
