7 Adding Application Components

Understanding the Difference Between Interactive and Classic Reports

Oracle Application Express includes two main report types, an interactive report and a classic report. The main difference between these two report types is that interactive reports enable the user to customize the appearance of the data through a searching, filtering, sorting, column selection, highlighting, and other data manipulations.

The following is an example of an interactive report.

Notice that the interactive report includes a Search Bar, Column Heading Menu links, and Link Column icons in the first column of each row. For a complete description of these components, see "Using Interactive Reports".

In contrast, a classic report does not include any of these customization features. The following is an example of a classic report built on top of the same data.

Classic reports do not include a Search Bar and the links at the top of each column only support basic sorting.

About the Search Bar

A search bar displays at the top of every interactive report and includes the following features:

• Search columns icon - Resembles a magnifying glass. Click this icon to narrow your search to only specific columns. To search all columns, select All Columns.

• Text area - Enter case insensitive search criteria (wild card characters are implied) and then click Go.

• Rows - Selects the number of rows to display per page.

• Go button - Executes a search.

• Actions menu - Displays the Actions menu. See "About the Actions Menu".

About Link Column Icons

Link Column icons display on the far left of each row in an interactive report. A Link Column can link to a single row view, a custom target, or be excluded from the report. To learn more about customizing this interactive report attribute, see "Understanding Link Columns" and "Creating a Column Link in an Interactive Report".

About the Actions Menu

The Actions menu appears to the right of the Go button on the Search bar. Use this menu to customize an interactive report.

The Actions menu contains the following options:

• Select Columns specifies which columns to display and in what order. See "Selecting Columns to Display".

• Filter focuses the report by adding or modifying the WHERE clause on the query. See "Adding a Filter".

• Sort specifies which columns to sort on and the sort order (that is, ascending or descending). See "Selecting Columns and Sort Order".

• Control Break creates a break group on one or several columns. See "Creating a Control Break".

• Highlight defines a filter that highlights the rows that meet the filter criteria. See "Adding Highlighting".

• Compute enables users to add computed columns to a report. See "Computing Columns".

• Aggregate enables users to perform mathematical computations against a column. See "Aggregating a Column".

• Chart displays the report data as a chart. See "Creating a Chart".

• Flashback performs a flashback query enabling users to view the data as it existed at a previous point in time. See "Executing a Flashback Query".

• Save Report saves the current customized report settings so they can be used in the future. See "Saving a Report".

• Reset enables users to reset the report back to the default report settings. See "Resetting a Report".

• Help provides detailed descriptions of how to use the interactive report components to customize your reports.

• Download enables users to download a report. Available download formats depend upon your installation and report definition. See "Downloading a Report".

Selecting Columns to Display

To customize a report to include specific columns, select Select Columns on the Actions menu.

To use the Select Columns option:

1. Click the Actions menu and select Select Columns.

   The Select Columns region appears.

   
   Description of the illustration ir_select_col.gif
   
   

2. Select the columns you wish to move. Cclick the arrows in the center of region to move a column from the Display in Report box to the Do Not Display in Report box. To select multiple columns at once, press and hold the CTRL key.

3. To change the order of the columns, click the Top, Up, Down, and Bottom arrows on the right.

4. Click Apply.

   A revised report appears.

Adding a Filter

You can create a filter to focus a report by selecting Filter on the Actions menu. This option enables you to add or modify the WHERE clause in the query.

To add a filter:

1. Click the Actions menu and select Filter.

   The Filter region appears.

2. Select a column, an operator, and expression.

   
   Description of the illustration ir_filter.gif
   
   

   In the previous example, the filter will display all people who have the Person Role of Lead.

3. Click Apply.

   
   Description of the illustration ir_select_col_rpt.gif
   
   

   Notice the filter Person Role contains 'Lead' has been added to the Report Settings area above the report.

4. To revise the filter:

   1. Click the filter name (in this example, Person Role contains 'Lead').

   2. Edit your selections and click Apply.

5. To disable the filter, select the Enable/Disable Filter check box.

6. To delete the filter, click Remove Filter.

Selecting Columns and Sort Order

You can specify which columns to display and the sort order (ascending or descending) by selecting Sort on the Actions menu.

To sort by column:

1. Click the Actions menu and select Sort.

   The Sort region appears.

   
   Description of the illustration ir_sort.gif
   
   

2. Select a column, the sort direction (Ascending or Descending), and Null Sorting behavior (Default, Nulls Always Last, or Nulls Always First).

3. Click Apply.

Creating a Control Break

You can create a break group of one or several columns by selecting Control Break from the Actions menu. Creating a break group pulls the columns out of the interactive report and displays them as a master record.

To create a break group:

1. Click the Actions menu and select Control Break.

   The Control Break region appears.

   
   Description of the illustration ir_ctrl_break.gif
   
   

2. Select a column and then a status (Enable or Disable)

3. Click Apply.

   One or more break groups display in Report Settings area above the report.

4. To expand the break group, click the plus (+) sign.

5. To enable the Control Break filter, select the Enable/Disable Filter check box.

6. To delete the filter, click Remove Filter.

Adding Highlighting

You can customize the display to highlight specific rows in a report by selecting Highlight on the Actions menu.

To create highlighting:

1. Click the Actions menu and select Highlight.

   The Highlight region appears.

   
   Description of the illustration ir_highlight.gif
   
   

2. Enter the following information:

   1. Name - Enter a name to define this filter.

   2. Sequence - Enter numeric value to identify the sequence in which the rules are evaluated.

   3. Highlight Type - Select Cell or Row. If Cell is selected, the column referenced in the Highlight Condition is highlighted.

   4. Background Color - Select a new color for the background of the highlighted area.

   5. Text Color - Select a new color for the text in the highlighted area.

   6. Select a column, an operator, and expression.

3. Click Apply.

   
   Description of the illustration ir_highlight_rpt.gif
   
   

   Notice a new filter appears in the Report Settings area above the report.

4. To revise the filter:

   1. Click the filter name (in this example, Project Lead).

   2. Edit your selections and click Apply.

5. To disable the filter, select the Enable/Disable Filter check box.

6. To delete the filter, click Remove Filter.

Computing Columns

You can add computations to columns by selecting Compute from the Actions menu.

To create a computation:

1. Click the Actions menu and select Compute.

   The Compute region appears.

   
   Description of the illustration ir_compute.gif
   
   

2. From Computation, select a previously defined computation or select New Computation.

3. For Column Heading, enter a heading for the new column.

4. For Format Mask, select an Oracle format mask to be applied to the new column.

   Next, create the computation. Use the Columns, Keypad, and Functions sections to create a computation.

5. To create the computation:

   1. Columns - Select the column name or alias to be added to the computation.

   2. Keypad - Click the appropriate options. This section provides shortcuts for commonly used keys.

   3. Functions - Select the appropriate function.

6. Click Apply.

Aggregating a Column

You can define a mathematical computation against a column (or aggregate) by selecting Aggregate from the Actions menu. Aggregates are displayed after each control break and at the end of the report within the column in which they are defined.

To aggregate columns:

1. Click the Actions menu and select Aggregate.

   The Aggregate region appears.

   
   Description of the illustration ir_aggreg.gif
   
   

2. From Aggregation, select a previously defined aggregation or select New Aggregation.

3. For Function, select one of the following: Sum; Average, Count, Minimum, Maximum, or Median.

4. For column, select a column name or alias.

5. Click Apply.

   The computation appears at the bottom of the report.

   
   Description of the illustration ir_aggreg_rpt.gif
   
   

   In this example, the aggregate shows the sum of all amounts in the Order Total column.

6. To remove the aggregate:

   1. Select Aggregate from the Actions menu.

      The Aggregate region appears.

Creating a Chart

To create a chart, select Chart on the Actions menu. You can create one chart for each interactive report. Once defined, you can switch between the chart and report views using links on the Search bar.

To create a chart:

1. Click the Actions menu and select Chart.

   The Chart region appears.

   
   Description of the illustration ir_chart.gif
   
   

2. In the Chart region, specify the following:

   1. Chart Type - Select the type of chart you wish to create.

   2. Label - Select the column to be used as the label.

   3. Axis for Title for Label - Enter title to be displayed on the axis associated with the column selected for Label (not available for pie chart).

   4. Value - Select the column to be used as the Value. If your function is a COUNT, a Value does not need to be selected.

   5. Axis Title for Value - Enter the title to display on the axis associated with the column selected for Value (not available for pie chart).

   6. Function - (Optional) Select a function to be performed on the column selected for Value.

      Edit Chart and View Chart links appear beneath the Search bar.

3. To alter the chart, click Edit Chart.

4. To return to the report, click View Report.

5. To delete the chart, click Edit Chart and then Delete.

Executing a Flashback Query

You can execute a flashback query by selecting Flashback from the Actions menu. A flashback query enables you to view the data as it existed at a previous point in time. The default amount of time that you can flashback is 3 hours (or 180 minutes) but the actual amount will differ for each database.

To execute a flashback query:

1. Click the Actions menu and select Flashback.

2. In the Flashback region, enter the number of minutes.

3. Click Apply.

Saving a Report

You can save a customized report by selecting Save Report from the Actions menu. You can choose to save a Named Report or Default Report Settings.

Resetting a Report

You can reset a report back to the default settings by selecting Reset from the Actions menu. Resetting a report removes any customizations you have made.

To reset a report:

1. Click the Actions menu and select Reset.

2. Click Apply.

Downloading a Report

You can download an interactive report back by selecting Download from the Actions menu. Available download formats depend upon your installation and report definition but may include comma-delimited file (CSV) format, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF).

To download a report:

1. Click the Actions menu and select Download.

2. Select a report download format and follow the provided instructions.

Accessing the Interactive Report Attributes Page

You can access the Interactive Report Attributes page by clicking the Interactive Report link next to the report region you want to edit on the Page Definition. You can also navigate to the Report Attributes page by clicking the region name and then selecting the Report Attributes tab.

To access the Report Attributes page:

1. On the Workspace home page, click the Application Builder icon.

2. Select the application.

3. Select a page.

   The Page Definition appears.

4. Under Regions, click Interactive Report next to the name of the report region you want to edit.

   The Report Attributes page appears and is divided into the following sections:

   • Column Attributes control the report layout. See "Editing Interactive Report Column Attributes".

   • Column Groups attributes enable you to group columns together on the single row view. See "Creating Column Groups".

   • Default Report Settings displays the initial way a report displays to end users. See "Saving Default Report Settings".

   • Pagination attributes control report pagination. See "Controlling Interactive Report Pagination".

   • Sorting attributes enable you to define images and image attributes for images that display in report headings to sort values. See "Managing Interactive Report Column Sorting".

   • Search Bar contain attributes that enable you to define a search bar within an interactive report. See "Removing or Customizing a Search Bar in an Interactive Report".

   • Download enables you to add a download option to the Actions menu and export the report as CSV, XLS, PDF, or RTF file. See "Adding a Download Option to the Actions Menu".

   • Link Column attributes enable you to add a link to a column. See "Creating a Column Link in an Interactive Report".

   • Advanced Attributes enable you to define a report alias, send a user a saved view of a report, show saved reports as tabs, prevent execution of queries that take a specified amount of time to execute, and define items to set into session state. See "Configuring Advanced Attributes for Interactive Reports".

5. To learn more about a specific attribute, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

About Navigation Alternatives

The Report Attribute page is divided into these sections: Column Attributes, Groups, Default Report, Pagination, Sorting, Search Bar, Download, Link Column, Advanced, and Description.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page.

Description of the illustration int_rprt_nav.gif

When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Editing Interactive Report Column Attributes

You can use the Column Attributes section of the Report Attributes page to precisely control the report layout. For example, you can use these attributes to alter column heading text, change column positioning, or hide a column.

To access Column Attributes:

1. On the Workspace home page, click the Application Builder icon.

2. Select the application.

3. Select a page.

   The Page Definition appears.

4. Under Regions, click Interactive Report next to the name of the report region you want to edit.

   The Interactive Report Attributes page appears with the Column Attributes section at the top of the page.

   
   Description of the illustration int_rprt_col.gif
   
   

   Use the Column Attributes section to control report column appearance and functionality.

   Heading displays the heading text to be included in the report. Type indicates the type of query column. The Link column indicates if a link column is currently defined.

   Table 7-1 describes common report column edits.

   Table 7-1 Common Report Column Edits for Interactive Reports

   Description	Developer Action
   Alter column display sequence.	Click the up and down arrows to change the column display sequence.
   Change column group assignment.	If Column Groups are defined, individual columns can be assigned under Column Group.
   Change column heading text.	Under Heading, enter different heading text.
   Change how columns display.	Select an option from Display Text As.

   
   

   You can further refine the attributes of a specific column on the Column Attributes page.

5. To access the Column Attributes page, click the Edit icon next to the appropriate column Alias.

   You can control whether users can sort, filter, control breaks, aggregate, and create computations or chart, by selecting the appropriate check boxes under Allow Users To.

6. Under Column Definition, Allow Users To, select the appropriate check boxes.

   To learn more about a specific attribute, click the item label.

Creating Column Groups

Use column groups to group columns together on a single row view.

To create a column group:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Scroll down to Column Groups.

3. Click Add Group.

4. Under Column Group:

   1. Sequence - Enter the display sequence for this column group. This attribute determines the order in which the column groups display in the detail view.

   2. Group Name - Enter a column group name. This name displays in the region header in detailed view.

   3. Description - Enter an optional description for the column group. This description never displays to the end user.

5. Click Create.

Controlling Interactive Report Pagination

You control how pagination displays by making selections from Pagination attributes on the Interactive Report Attributes page.

To access the Pagination section of the Report Attributes page:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Scroll down to Pagination.

   You use the Pagination attributes to select a pagination style, determine where pagination occurs, specify the maximum number of rows to query, and messages to display when more than the maximum number of rows are returned or if no rows are returned. Table 7-2 describes the available Pagination attributes.

Managing Interactive Report Column Sorting

You manage column sorting on the Interactive Report Attributes page.

To manage column sorting:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Under Column Attributes, click the Edit icon for the column for which you want to enable sorting.

3. Under Column Definition, select Sort and click Apply Changes.

   The Interactive Report Attributes page appears.

4. Scroll down to Sorting.

5. Specify ascending and descending image attributes, or click set defaults.

Removing or Customizing a Search Bar in an Interactive Report

Search bars display above the report.

To remove a search bar in your report:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Scroll down to Search Bar.

3. For Include Search Bar, select Yes or No. Selecting No removes the following default components:

   • Search Field - Displays a text field in the search bar in which to enter search criteria.

   • Finder Drop Down - Enables users to search on a selected column or against all columns.

   • Rows Per Page Selector - Displays a select list to enable users to select the number of rows to display per page.

   • Actions Menu - Enables the user to customize the report based on selected Actions.

4. To customize the Actions Menu, select the Action functions to include.

5. For Button Template, select a template for buttons that display in the search region and in the dialogs.

6. For Finder Image, specify an image. If no image is defined, #IMAGE_PREFIX#/htmldb/builder/builder_find.png is used.

7. For Actions Menu Image, specify an image. If no image is defined, #IMAGE_PREFIX#/ws/action_dropdown.gif is used.

8. For Search Button Label, enter the text to be used as the search button label.

9. For Maximum Rows Per Page, enter the maximum number to display in the Rows Per Page Selector in the search bar.

10. Click Apply Changes.

Adding a Download Option to the Actions Menu

You can add a Download option to the Actions menu to enable users to export an interactive report as a comma-delimited file (CSV) format, Microsoft Excel (XLS) format, Adobe Portable Document Format (PDF), and Microsoft Word Rich Text Format (RTF).

To add a download link to a report you must enable the CSV output option.

To enable the Enable CSV output option:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Scroll down to Search Bar.

   1. For Include Search Bar, select Yes and Actions Menu.

   2. Under Include in Actions menu, select Download.

3. Scroll down to Download.

4. From Download Formats, select CSV - Comma-delimited file (CSV) file format.

   Tip:

   The Download option will not display in the Actions menu if no file formats are selected.

5. In the CSV Separator, define the column separator. If no value is entered, a comma or semicolon is used depending on your current NLS settings.

6. In the CSV Enclosed By, enter delimiter character. This character is used to delineate the starting and ending boundary of a data value. Default delimiter is double quotation marks (" ").

7. In Filename, enter a name for the downloaded document. Do not specify a file extension, the extension is added automatically. If you leave this blank, the region name is used as the file name.

8. Click Apply Changes.

Understanding Link Columns

A Link Column displays on the left side of an interactive report. It can link to a single row view, a custom target, or be excluded from the report. Note that a Link Column cannot be sorted, hidden, or moved by an end user and does not interact with the standard column link defined on the Column Attributes page.

Creating a Column Link in an Interactive Report

Use Column Attributes to create a link from a report to another page in your application or to a URL.

To create a column link to another page:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Under Column Attributes, locate the column to contain the link.

3. Click the Edit icon next to the column name.

   The Column Attributes page appears.

4. Scroll down to Column Link.

5. To create a column link to another page:

   1. From Target, select Page in this Application.

   2. (Optional) In Link Attributes, specify additional column link attributes to be included in the <a href= > tag (for example, a link target, classes, or styles).

   3. In Link Text, enter the text to be displayed as a link, specify an image tag, or pick from the list of default images.

   4. In Page, specify the target page number. To reset the pagination for this page, select Reset Pagination.

   5. In Request, specify the request to be used.

   6. In Clear Cache, specify the pages (that is, the page numbers) on which to clear cache. You can specify multiple pages by listing the page numbers in a comma-delimited list.

   7. Use the Name and Value fields to specify session state for a specific item.

   8. For Checksum - Select one of the following:

      • Use default

      • Application Level - Link reusable by any user

      • User Level - Link reusable by current user

      To learn more about each option, see item Help.

6. Click Apply Changes.

To create a column link to a URL:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Scroll down to Column Link.

4. Under Column Link, specify the following:

   1. From Target, select URL.

   2. In Link Text, enter the text to be displayed as a link and select a substitution string.

   3. (Optional) In Link Attributes, specify additional column link attributes to be included in the <a href= > tag (for example, a link target, classes, or styles).

   4. In URL, enter the appropriate address.

5. Click Apply Changes.

Defining a Column as a List of Values in an Interactive Report

In interactive reports you can use a list of values to improve the speed of built-in filter tools.

To define a report column as a list of values:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Scroll down to List of Values.

4. From Column Filter List of Values, specify how to create a list of values as a filter on the report column list. Available options include:

   • None - Disable filter on list of values.

   • Default Based on Column Type - STRING and NUMBER columns derive list of values from distinct column values. DATE columns derive list of values from predefined date ranges.

   • Use Defined List of Values - Derive list of values from defined list of values SQL Query.

   • List of values definition - If you select Use Defined List of Values, enter a SQL query that selects the distinct column values. Entering a SQL query can improve performance in some reports. This list of values will be displayed when a user clicks on a column header.

5. Click Apply Changes.

Configuring Advanced Attributes for Interactive Reports

Use the Advanced Attributes section to define a report alias, send a user a saved view of a report, show saved reports as tabs, prevent execution of queries that take a specified amount of time to execute, and define items to set into session state.

To configure Advanced Attributes:

1. Navigate to the Interactive Report Attributes page. See "Accessing the Interactive Report Attributes Page".

   The Interactive Report Attributes page appears.

2. Scroll down to Advanced Attributes.

   Table 7-3 describes the available Advanced Attributes.

Linking to Interactive Reports

Unlike classic reports which typically use page items as report parameters, the data in an interactive report is driven by declarative filters. However, the developer may desire to set these report parameters by modifying interactive report settings for an end user. These settings only affect the end user's working report and will not affect any saved report settings.

To reset an interactive report in a link, use the string "RIR" in the Clear-Cache section of a URL. This is equivalent to the end user choosing the Reset option from the interactive report actions menu on the target page. The report is returned to the default report settings specified by the developer or saved by the user.

To create a Row text contains filter on an interactive report, use the string IR_ROWFILTER in the Item Names section of the URL. Adding this string passes the filter value in the corresponding location in the Item Values section of the URL.

To create a filter on an interactive report in a link, use the string IR<operator>_<target column alias> in the Item Names section of the URL, and pass the filter value in the corresponding location in the Item Values section of the URL.

Valid operators include:

• EQ = Equals (this is the default)

• NEQ = Not Equals

• LT = Less than

• LTE = Less then or equal to

• GT = Greater Than

• GTE = Greater than or equal to

• LIKE = SQL Like operator

• N = Null

• NN = Not Null

• C = Contains

• NC = Not Contains

Consider the following examples:

f?p=...:IR_ENAME:KING

The previous example is equivalent to the end user creating a filter on the target page: ENAME = KING. Note that when an operator is not provided, EQ is used because it is the default.

f?p=...:IRGT_SAL:3000

The previous example is equivalent to the end user creating a filter on the target page: SAL > 3000.

Accessing Classic Report Attributes

You can access the classic Report Attributes page by clicking the Report link next to the report region you want to edit on the Page Definition. You can also navigate to the Report Attributes page by clicking the region name and then selecting the Report Attributes tab.

To access the Report Attributes page:

1. On the Workspace home page, click the Application Builder icon.

2. Select the application.

3. Select a page.

   The Page Definition appears.

4. Under Regions, click Report next to the name of the report region you want to edit.

   The Report Attributes page appears and is divided into the following sections:

   • Column Attributes control the report layout. See "Altering Classic Report Layout Using Column Attributes".

   • Layout and Pagination attributes control report pagination. See "Controlling Classic Report Pagination".

   • Sorting attributes enable you to define images and image attributes for images that display in report headings to sort values. See "Enabling Column Sorting in a Classic Report".

   • Messages contain attributes that enable you to define messages that display if no data is found or more data is found than can be displayed.

   • Report Export attributes enable you to add download link to a report or export a report as either an XML file or CSV file. See "Adding a Download Link to a Classic Report" and "Exporting a Classic Report as an XML File or a CSV File".

   • Break Formatting attributes enable you to control if a specific column repeats and how column breaks appear when printed. See "Controlling Column Breaks in a Classic Report".

5. To learn more about a specific attribute, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

6. Click Apply Changes.

About Navigation Alternatives

The Report Attribute page is divided into these sections: Column Attributes, Layout and Pagination, Sorting, Messages, Report Export, Break Formatting, and External Processing.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page.

Description of the illustration int_rprt_nav.gif

When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Altering Classic Report Layout Using Column Attributes

You can use the Column Attributes section of the Report Attributes page to precisely control the report layout. For example, you can use these attributes to alter column heading text, change column positioning, hide a column, create a sum of a column, or select a sort sequence.

To access the Column Attributes:

1. On the Workspace home page, click the Application Builder icon.

2. Select the application.

3. Select a page.

   The Page Definition appears.

4. Under Regions, click Report next to the name of the report region you want to edit.

   The Report Attributes page appears with the Column Attributes section at the top of the page.

   
   Description of the illustration rpt_att.gif
   
   

   Use the Column Attributes section to control report column appearance and functionality.

   Heading Type identifies how the heading is generated for the report. The Link column indicates if a column link is currently defined. The Edit column indicates whether a column is currently updatable.

   Table 7-4 describes common report column edits.

   Table 7-4 Common Report Column Edits for Classic Reports

   Description	Developer Action
   Alter column display sequence.	Click the up and down arrows to change the column display sequence.
   Alter heading alignment.	Under Column Alignment, select a column alignment.
   Change column heading text.	Under Heading, enter different heading text.
   Control which columns display.	Click Show to indicate a column should display.
   Enable a unique sort sequence.	Click Sort and select a sequence number from Sort Sequence. Any number of columns can be sort enabled. However, at least one column must have a Sort Sequence defined.
   Enable the sum of a column.	Click Sum to enable the sum of a column.

   
   

   You can further refine the attributes of a specific column on the Column Attributes page.

5. To access the Column Attributes page, click the Edit icon next to the appropriate column Alias.

   To learn more about a specific attribute, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

Controlling Classic Report Pagination

You control report pagination by:

• Including a pagination substitution string in the report template

• Making selections from the Layout and Pagination section on the Report Attributes page

You control how pagination displays by making selections from the Layout and Pagination attributes on the Report Attributes page.

Topics in this section include:

• Accessing and Understanding Layout and Pagination Attributes

• Including Pagination After the Rows in a Classic Report

• Including Pagination Before the Rows in a Classic Report

Enabling Column Sorting in a Classic Report

You enable column sorting on the Report Attributes page.

To enable column sorting:

1. Navigate to the Report Attributes page. See "Editing SQL and Wizard Reports".

2. Under Column Attributes, select the Sort check box next to the columns to be sorted.

3. From Sort Sequence, select a sequence number.

   Sort Sequence is optional. However, if there are one or more sort enabled columns, then at least one column needs a defined Sort Sequence.

4. Scroll down to Sorting.

5. Specify ascending and descending image attributes or click set defaults.

6. Click Apply Changes.

Adding a Download Link to a Classic Report

You can create a link within a report that enables users to export the report as a comma-delimited file (.csv) file. To add a CSV link to a report you must enable the CSV output option. When using the CSV output option, the report template is not important. You can include a CSV link with any report template that has the CSV export substitution string defined.

Enabling the CSV Output Option in a Classic Report

To use the enable the Enable CSV output option:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Scroll down to Report Export.

3. From Enable CSV output, select Yes.

4. (Optional) In the Separator and Enclosed By fields, define the separator and delimiter.

   The default Enclosed By by characters are a double quotation marks (" "). The default delimiter is either a comma or a semicolon depending upon your current NLS settings.

5. In the Link Label field, enter link text. This text will display in your report and enable users to invoke a download.

6. (Optional) To specify a default export file name, enter a name in the Filename field.

   By default, the Application Express engine creates an export file name by taking the region name and adding the appropriate file name extension (.csv or .xml).

7. Click Apply Changes.

Exporting a Classic Report as an XML File or a CSV File

You can export a report as an XML file by selecting a report template.

To export a report as a file:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Scroll down to Layout and Pagination.

3. From the Report Template list, select export: XML or export: CSV.

   Selecting export: XML prevents the Application Express engine from rendering the page and dumps the content to an XML file.

4. Click Apply Changes.

Creating a Column Link in a Classic Report

Use the Column Link attributes to create a link from a report to another page in your application or to a URL.

To create a column link to another page:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Under Column Attributes, locate the column to contain the link.

3. Click the Edit icon next to the column name.

   The Column Attributes page appears.

4. Scroll down to Column Link.

5. To create a column link to another page:

   1. From Target, select Page in this Application.

   2. (Optional) In Link Attributes, specify additional column link attributes to be included in the <a href= > tag (for example, a link target, classes, or styles).

   3. In Link Text, enter the text to be displayed as a link, specify an image tag, or pick from the list of default images.

   4. In Page, specify the target page number. To reset the pagination for this page, select Reset Pagination.

   5. In Request, specify the request to be used.

   6. In Clear Cache, specify the pages (that is, the page numbers) on which to clear cache. You can specify multiple pages by listing the page numbers in a comma-delimited list. See "Linking to Interactive Reports".

   7. Use the Name and Value fields to specify session state for a specific item.

6. Click Apply Changes.

To create a column link to a URL:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Scroll down to Column Link.

4. Under Column Link, specify the following:

   1. From Target, select URL.

   2. In Link Text, enter the text to be displayed as a link and select a substitution string.

   3. (Optional) In Link Attributes, specify additional column link attributes to be included in the <a href= > tag (for example, a link target, classes, or styles).

   4. In URL, enter the appropriate address.

5. Click Apply Changes.

Defining an Updatable Column in a Classic Report

You can make a column updatable by editing Tabular Form Element attributes on the Column Attributes page. Note that the Application Express engine can only perform updates if:

• A multirow update is defined

• A PL/SQL process is implemented to process updated data

• When using the built-in tabular form elements and display types, then the report has to be defined using the type SQL Query (updatable report)

To define updatable column attributes:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Scroll down to Tabular Form Element.

4. Under Tabular Form Element, specify the following:

   1. Display As - Select a type of updatable column.

      Use this option to make a column updatable. Updates can only be performed if a multirow update is defined, or a PL/SQL process is implemented to process updated data.

   2. Date Picker Format Mask - Make a selection if you selected the Display As type of Date Picker.

   3. Element Width - Specify the width of the form item.

   4. Number of Rows - Specify the height of a form item (applicable to text areas).

   5. Element Attributes - Define a style or standard form element attribute.

   6. Element Option Attributes - Specify form element attributes for items in a radio group or check box.

   7. Primary Key Source Type - Identify the default type.

   8. Primary Key Source - Identify the default source.

      If the current column is part of the primary key defined in an MRU process, only the primary key source type and source appear.

      Otherwise, Default and Default Type appear. Use Default and Default Type to establish a relationship between two master records in a master detail form, or to set the default values for new rows.

   9. Reference Table Owner - Identify the owner of the referenced table. Use this attribute to build User Interface Defaults for reports.

   10. Reference Table Name - Identify the table or view that contains the current report column.

   11. Reference Column Name - Identify the column name that this report column references.

5. Click Apply Changes.

Defining a Column as a List of Values in a Classic Report

Report columns can be rendered as lists of values. For example, a column can be rendered using a select list or a popup list of values. Or, a column can be rendered as read-only text based on a list of values.

This last approach is an effective strategy when creating display lookup values and is particularly useful in regular, nonupdatable reports. This approach enables you to display the value of a column without having to write a SQL JOIN statement.

To render a report column as a list of values:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Scroll down to List of Values.

4. From Named LOV, make a selection from the List of Values repository. See "Creating Lists of Values".

5. To include a null value in a list of values:

   1. In Display Null, select Yes.

   2. In Null Text, specify the value that displays.

   A column can also have a value that does not display in its list of values.

6. To define a value that does not display in the list of values:

   1. From Display Extra Value, select Yes.

      The extra value is used if the actual column value is not part of the LOV. In that situation, the actual value is shown. If you do not display extra values, you may end up with the wrong value and unintentionally update your data incorrectly.

   2. In Null Value, specify the value that displays.

   3. If you have not selected a Named LOV, enter the query used to display a select list in the LOV Query field.

7. If you have not selected a Named LOV, enter the query used to display a select list in LOV Query.

8. Click Apply Changes.

Controlling When Columns Display in a Classic Report

You can use the Authorization and Condition attributes to control when a column displays.

Authorization enables you to control access to resources (such as a report column) based on predefined user privileges. For example, you could create an authorization scheme in which only managers can view a specific report column. Before you can select an authorization scheme, you must first create it.

A condition is a small unit of logic that enables you to control the display of a column based on a predefined condition type. The condition evaluates to true or false based on the values you enter in the Expressions fields.

To specify Authorization and Condition attributes:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Access the Column Attributes page by clicking the Edit icon next to the appropriate column.

   The Column Attributes page appears.

3. Under Authorization, make a selection from the Authorization Scheme list.

4. Under Conditions, make a selection from the Condition Type list, and depending upon your selection, enter an expression or value in the appropriate Expression fields.

   If the authorization is successful and the condition type display evaluates to true, the column displays.

Controlling Column Breaks in a Classic Report

You can control if a specific column repeats and how column breaks appear when printed using Break Formatting attributes. For example, suppose your report displays employee information by department number. If multiple employees are members of the same department, you can increase the readability by specifying the department number only appears once.

To create this type of column break:

1. Navigate to the appropriate Report Attributes page. See "Editing SQL and Wizard Reports".

2. Scroll down to Break Formatting.

3. Make a selection from the Breaks list.

About Printing Reports to PDF

When printing to a PDF, the report data is transformed using an externally defined report server. When the application end user clicks a print link, a request is sent to the Application Express engine. The Application Express engine then generates the report data in XML format and report template in XSL-FO or RTF format. The external reporting engine then transforms the data and the template into a PDF which displays to the end user using the conversion servlet that ships with BI Publisher 10.1.3.2 (formerly known as Oracle XML Publisher). Fortunately, this architectural complexity is transparent to both end users and developers. End users just click a print link, and developers just declaratively set regions to support PDF printing. Output to other formats operates in the same manner using the necessary conversion servlet.

About Report Printing Configuration Options

Your report server can be Oracle BI Publisher, OC4J with Apache FOP, or another standard XSL-FO processing engine. Oracle BI Publisher provides a higher level of functionality and facilitates "high fidelity" output requirements including master-detail reports, charts, and so on. To accommodate the difference in functionality, Oracle Application Express provides two report printing configuration options:

• Standard Support. Enables you to print report regions and report queries using either the built-in templates (provided with a standard XSL-FO processing engine), or other XSL-FO compatible formats you provide. This setting does not support RTF.

  Standard Support provides declarative formatting of report regions and report queries with basic control over page attributes, including page orientation, page size, column heading formats, page header, and page footer.

• Advanced Support. Requires a valid license of Oracle BI Publisher (also known as Oracle XML Publisher). This setting, provides you with all the capabilities of the Standard configuration plus the ability to define RTF-based report layouts developed using the BI Publisher Word Template Builder.

  To learn more about installing and configuring Oracle BI Publisher, see PDF Printing in Application Express 3.1. Go to:

  http://www.oracle.com/technology/products/database/application_express/html/configure_printing.html
  

About Classic Report Printing Methods

There are two ways to print classic report regions:

• Configure Printing Attributes for a Report Region. You can print a report region by configuring the Printing Attributes for the region. See "Configuring Classic Report Region Print Attributes".

• Create a Report Query. You can print a report by defining a report query as a Shared Component. See "About Report Queries".

Both report regions and report queries can be downloaded in the following formats:

• PDF - Adobe Portable Document Format.

• RTF - Microsoft Word Rich Text Format.

• XLS - Microsoft Excel format. Note that this is not a true .xls file because the content is HTML-based.

• HTML

• XML - Extensible Markup Language.

To format either a report region or report query, you associate it with a report layout. To learn more, see "About Report Layouts".

About Report Queries

You can print a report region by defining a report query as a Shared Component. A report query identifies the data to be extracted. Unlike SQL statements contained in regions, report queries contain SQL statements that are validated when you save the query. Note that report queries must be SQL statements, not functions returning SQL statements.

You can associate a report query with a report layout and download it as a formatted document. If no report layout is selected, a generic layout is used. The generic layout is intended to be used to test and verify a report query. When using the generic layout option and multiple source queries are defined, only the first result set is included in the print document. The reports can include session state of the current application.

To make these reports available to end users, you then integrate them with an application. For example, you can associate a report query with a button, list item, branch, or other navigational component that enables you to use URLs as targets. Selecting that item then initiates the printing process.

Topics in this section include:

• Creating a Report Query

• Editing Report Queries

• Copying Report Queries

About Report Layouts

To format either a classic report region or report query, you associate it with a report layout. Using report layouts renders the data in a printer-friendly format. If you do not select a report layout, a default XSL-FO layout is used. The default XSL-FO layout is always used for rendering Interactive Report regions.

When creating and using report layouts, you can:

• Take advantage of the default layouts for report regions and generic layouts for report queries provided with Oracle Application Express.

• Utilize the built-in XSL-FO-based layouts for report regions by copying and customizing the code. You can edit a number of attributes for report regions that control page size, fonts, colors, and so on.

• Create RTF or XSL-FO report layouts to customize the report look and feel. To use RTF report layouts, your Oracle Application Express service administrator must select the Advanced setting for your site. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

Topics in this section include:

• About Report Layout Options

• Creating a Report Layout

• Editing Report Layouts

• Copying Report Layouts

Configuring Classic Report Region Print Attributes

One approach to printing a report region is to configure Print Attributes. Once configured, these attributes only apply only to current region and cannot be used outside the context of the region.

If the printing feature is set up for your instance, you can configure a report region to print in various formats.

To configure a classic report region for printing:

1. In your application, select the page containing the classic report region you want to print.

   The Page Definition appears.

2. Under Regions, click Report next to the region you want to print.

3. Click the Print Attributes tab.

4. Under Printing, specify the appropriate information:

   1. Enable Report Printing - Select Yes.

   2. Link Label - Enter the text for the link that starts the printing process.

   3. Response Header - Specify whether the response header generated by Oracle Application Express is based on your Report Settings or the Print Server. The response header is the information the server sends back to the Web browser in response to receiving an HTTP request. It contains the date, size and type of file that the server is sending back to the client and also data about the server itself. To specify content disposition (using the attribute View File As) and file name, the response header needs to be based on Report Settings.

   4. View File As - Select how the Web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

   5. (Optional) File Name - Enter a name for the downloaded file. If you leave this blank, the region name is used as the file name.

   6. Output Format - Select an output option.

   7. Item - Determines the output format at runtime. Select the application or page item that holds format value.

   8. Report Layout - Select Default Report Layout to use the built-in generic report layout, or an available report layout you or your administrator created. See item Help for a listing of the supported substitution strings available when building custom XSL-FO report layouts.

   9. Print Server Overwrite - Specify an alternate print server. A print server is typically configured centrally for all workspaces in Oracle Application Express Administration Services. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

      Configuring an alternative print server at the report level makes debugging and diagnosis of potential printing problems easier and supports the use of specific custom built end points to perform custom XSLT transformations.

   10. Print URL - Report regions can be printed using the standard print link or using a page button. To use a button, remove the Link Label, create a button on your page, and use the URL shown in this attribute as the button target.

5. In the remaining sections, define page size, paper orientation, page headers and footers, fonts, text color, and background color.

6. Under Report Columns, specify the width for each column.

   The default is to make each column the same width. The Total Width is a display only field that shows the sum of all column width values. When changing the width for an individual column the Total Width is automatically recalculated. If Column Width Units is set to Percentage, rather than Points, then the total column width must be less than or equal to 100.

   Use the Reset Column Width button to reset the Column Width Units to Percentage and change all column widths back to their default value. The Recalculate button maintains the current ratios between column widths but reduce all column widths such that the Total Width is less than or equal to 100%.

7. Click Apply Changes.

Configuring Interactive Report Region Print Attributes

Unlike classic reports, the Interactive Report Print Attributes can only utilize the default XSL-FO layout and is initiated from the Report Attributes, Download section rather than directly from this screen. Once configured, these attributes only apply only to the current region and cannot be used outside the context of the region.

If the printing feature is set up for your instance, you can configure a report region to print in various formats

To configure a interactive report region for printing:

1. In your application, select the page containing the interactive report region you want to print.

   The Page Definition appears.

2. Under Regions, click Interactive Report next to the region you want to print.

3. Click the Print Attributes tab.

4. Under Printing, specify the appropriate information:

   1. Response Header - Specify whether the response header generated by Oracle Application Express is based on your Report Settings or the Print Server. The response header is the information the server sends back to the Web browser in response to receiving an HTTP request. It contains the date, size and type of file that the server is sending back to the client and also data about the server itself. To specify content disposition (using the attribute View File As) and file name, the response header needs to be based on Report Settings.

   2. Content Disposition - Select how the Web browser opens the report query document. Options include:

      • Attachment - Displays a File Open/Save dialog box when the user clicks the report query download button.

      • Inline - Displays the report query document inside the Browser window.

   3. Print Server Overwrite - Specify an alternate print server. A print server is typically configured centrally for all workspaces in Oracle Application Express Administration Services. See "Configuring Report Printing" in Oracle Application Express Administration Guide.

      Configuring an alternative print server at the report level allows for easier debugging and diagnosis of potential printing problems and supports the use of specific custom built end points to perform custom XSLT transformations.

5. In the remaining sections, define page size, paper orientation, page headers and footers, fonts, text color, and background color.

6. Click Apply Changes.

Processing a Form

Once you create a form, the next step is to process the data a user types by inserting into or updating the underlying database tables or views. There are three ways to process a form:

• Creating an Automatic Row (DML) Processing Process

• Creating a Process that Contains One or More Insert Statements

• Using a PL/SQL API to Process Form Values

Creating an Automatic Row (DML) Processing Process

One common way to implement a form is to manually create an Automatic Row Processing (DML) process. This approach offers three advantages. First, you are not required to provide any SQL coding. Second, Oracle Application Express performs DML processing for you. Third, this process automatically performs lost update detection. Lost update detection ensures data integrity in applications where data can be accessed concurrently.

To implement this approach you need to:

• Add items, define the Item Source Type as Database Column, and specify a case-sensitive column name.

• Select the option Always overrides the cache value.

To create an Automatic Row Processing (DML) process:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition"

2. Under Processes, click the Create icon.

3. Select the process Data Manipulation.

4. Select the process category Automatic Row Processing (DML).

5. Specify the following process attributes:

   1. In the Name field, enter a name to identify the process.

   2. In the Sequence field, specify a sequence number.

   3. From the Point list, select the appropriate processing point. In most instances, select Onload - After Header.

   4. From the Type list, select Automated Row Processing (DML).

6. Follow the on-screen instructions.

Creating a Process that Contains One or More Insert Statements

In this approach to form handling, you create one or more processes to handle insert, update, and delete actions. Instead of having the Application Express engine handling everything transparently, you are in complete control.

For example, suppose you have a form with three items:

• P1_ID - A hidden item to store the primary key of the currently displayed row in a table.

• P1_FIRST_NAME - A text field for user input.

• P1_LAST_NAME - A text field for user input.

Assume also there are three buttons labeled Insert, Update, and Delete. Also assume you have a table T that contains the columns id, first_name, and last_name. The table has a trigger that automatically populates the ID column when there is no value supplied.

To process the insertion of a new row, you create a conditional process of type PL/SQL that executes when the user clicks the Insert button. For example:

BEGIN
  INSERT INTO T ( first_name, last_name )
     VALUES  (:P1_FIRST_NAME, :P1_LAST_NAME);
END; 

To process the updating of a row, you create another conditional process of type PL/SQL. For example:

BEGIN
    UPDATE T
       SET first_name = :P1_FIRST_NAME,
           last_name = :P1_LAST_NAME
    WHERE ID = :P1_ID;
END; 

To process the deletion of a row, you create a conditional process that executes when the user clicks the Delete button. For example:

BEGIN
    DELETE FROM T
    WHERE ID = :P1_ID;
END;

Using a PL/SQL API to Process Form Values

For certain types of applications, it is appropriate to centralize all access to tables in a single or a few PL/SQL packages. If you created a package to handle DML operations, you can call procedures and functions within this package from an After Submit PL/SQL process to process insert, updates, and delete requests.

Populating Forms

Oracle Application Express populates a form either on load or when the Application Express engine renders the page. You can populate a form in the following ways:

• Create a process and define the type as Automated Row Fetch.

• Populate the form manually by referencing a hidden session state item.

To create an Automated Row Fetch process:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Processes, click Create.

3. Select the process type Data Manipulation.

4. Select the process category Automatic Row Fetch.

5. Specify the following process attributes:

   1. In the Name field, enter a name to identify the process.

   2. In the Sequence field, specify a sequence number.

   3. From the Point list, select the appropriate processing point.

   4. From the Type list, select Automated Row Fetch.

6. Follow the on-screen instructions.

You can also populate a form manually by referencing a hidden session state item. For example, the following code in an Oracle Application Express process of type PL/SQL would set the values of ename and sal. The example also demonstrates how to manually populate a form by referencing a hidden session state item named P2_ID.

FOR C1 in (SELECT ename, sal
FROM emp WHERE ID=:P2_ID)
LOOP     
     :P2_ENAME := C1.ename;
     :P2_SAL := C1.sal;
END LOOP;

In this example:

• C1 is an implicit cursor.

• The value of P2_ID has been set.

• The process point for this process would be set to execute (or fire) on or before Onload - Before Regions.

Creating a Validation

To create a validation:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Validations, click the Create icon.

3. When the Create Validations Wizard appears, follow the on-screen instructions.

   Validation Types are divided into two categories:

   • Item. These validations start with the term Item and provide common checks you may want to perform on the item with which the validation is associated.

   • Code. These validations require that you provide either a piece of PL/SQL code or SQL query that defines the validation logic. Use this type of validation to perform custom validations that require verifying values of more than one item or accessing additional database tables.

4. Follow the on-screen instructions.

Associating a Validation with a Specific Item

To associate an item with a validation and specify error message text:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Validations, select the validation item you want to associate.

   The attributes page for the validation appears.

3. Scroll down to Error Message:

   • In Error message display location, verify the display location.

   • In Associated Item, select the item you want to associate with this validation.

4. Click Apply Changes.

About Error Message

Error message display location identifies where a validation error message displays. Validation error messages can display on an error page or inline within the existing page. Inline error messages can display in a notification area (defined as part of the page template) or within the field label.

To create a hard error that stops processes, including any remaining validations, you must display the error on an error page.

Supported Calendar Substitution Strings

Application Builder supports a number of date format substitution strings. You can view a complete list of supported substitution strings on the Calendar Templates page.

To view a list of supported substitution strings for calendars:

1. Navigate to the appropriate calendar template.

2. View the Substitution Stings list on the right side of the page.

Adding a Calendar to an Existing Page

Oracle Application Express supports the creation of one calendar per page.

To add a calendar to an existing page:

1. Navigate to the Page Definition. See "Accessing a Page Definition".

2. Under Regions, click the Create icon.

   The Create Region Wizard appears.

3. Select Calendar and click Next.

4. Select the type of calendar you want to create and click Next:

   • Easy Calendar creates a calendar based on the date column and display column you specify.

   • SQL Calendar creates a calendar based on a SQL query you provide.

5. Follow the on-screen instructions.

Adding a Calendar to a New Page

To create a calendar on a new page:

1. Navigate to the Workspace home page.

2. Click the Application Builder icon.

3. Select an application.

4. Click Create Page.

5. Select Calendar and click Next.

6. Select the type of calendar you want to create and click Next:

   • Easy Calendar creates a calendar based on the date column and display column you specify.

   • SQL Calendar creates a calendar based on a SQL query you provide.

7. Follow the on-screen instructions.

Accessing the Calendar Attributes Page

To access the Calendar Attributes page:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Regions, click Calendar next to the region name.

   The Calendar Attributes page appears.

3. Edit the appropriate attributes. To learn more about a specific item on a page, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark.

4. Click Apply Changes.

About the Calendar Attributes Page

The topics that follow describe specific sections of the Calendar Attributes page. You can use these attribute to specify general calendar formatting, define the dates included in the calendar, or create a link on the column or a day in the calendar.

About Flash Charts

Flash chart support in Oracle Application Express is based on the Anychart Flash Chart Component. Anychart is a flexible Macromedia Flash-based solution that enables developers to create animated, compact, interactive flash charts. Flash charts are rendered by a browser and require Flash player 8 or later. Flash charts used in interactive reports require Flash Player 9 or later. For more information about Anychart, go to

http://www.anychart.com

About SVG Plug-in Support

SVG is an XML-based language for Web graphics from the World Wide Web Consortium (W3C). SVG charts are defined using an embed tag. When evaluating whether an SVG chart is the appropriate chart type for your application, remember that:

• Some Web browsers do not support SVG charts.

• Most Web browsers that support SVG charts require that users download an SVG plug-in.

The Adobe SVG plug-in can handle data encoded in UTF-8, UTF-16, ISO-8859-1, and US-ASCII. Encoding of an SVG chart is determined by the database access descriptor (DAD) database character set. If the DAD character set is not UTF8, AL32UTF8, AL16UTF16, WE8ISO8859P1, or US7ASCII, SVG charts may not render properly in the Adobe SVG plug-in.

Adding an HTML Chart to an Existing Page

To add an HTML chart to an existing page:

1. Navigate to the Page Definition. See "Accessing a Page Definition".

2. Under Regions, click the Create icon.

   The Create Region Wizard appears.

3. Select Chart and click Next.

4. For Region, select HTML Chart and click Next.

5. For Display Attributes

   1. Specify the following:

      • Title

      • Region Template

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

6. For Source:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

   2. Specify relevant chart attributes. To learn more, click the item label.

7. Click Create Region.

Adding an HTML Chart to a New Page

To create an HTML chart on a new page:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Click Create Page.

4. Select Chart and click Next.

5. For Region, select HTML Chart and click Next.

6. For Page Attributes:

   1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Column

      • Breadcrumb

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

7. Specify whether to include tabs and click Next.

8. For Chart Definition:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

   2. Specify relevant chart attributes. To learn more, click the item label.

   3. Click Next.

9. Click Finish.

Adding a SVG Chart to an Existing Page

To add a SVG chart to an existing page:

1. Navigate to the Page Definition. See "Accessing a Page Definition".

2. Under Regions, click the Create icon.

   The Create Region Wizard appears.

3. Select Chart and click Next.

4. For Region, select SVG Chart.

5. For Region, select a chart type and click Next. See "About SVG Chart Types".

6. For Display Attributes

   1. Specify the following:

      • Title

      • Region Template

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

7. For Source:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

   2. Specify relevant chart attributes. To learn more, click the item label.

8. Click Create Region.

Adding a SVG Chart to a New Page

To create a SVG chart on a new page:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Click Create Page.

4. Select Chart and click Next.

5. Select SVG Chart.

6. Select a chart type and click Next. See "About SVG Chart Types".

7. For Page Attributes:

   1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Chart Color Theme

      • Breadcrumb

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

8. Specify whether to include tabs and click Next.

9. For Query:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

   2. Specify the remaining attributes. To learn more, click the item label.

   3. Click Next.

10. Click Finish.

About SVG Chart Types

Table 7-7 describes the SVG chart types available in Application Builder.

Understanding SVG Chart Cascading Style Sheet Classes

When you create a new chart, Oracle Application Express renders it based on cascading style sheet (CSS) classes associated with the current theme. You can change the appearance of a chart by referencing another CSS or by overriding individual classes in the CSS section of the Edit Attributes page

The following sample contains the CSS classes for the dial chart in Sample Application. This example contains all the available CSS classes. Class names appear in boldface.

text{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
tspan{font-family:Verdana, Geneva, Arial, Helvetica, sans-serif;fill:#000000;}
text.title{font-weight:bold;font-size:14;fill:#000000;}
text.moredatafound{font-size:12;}
rect.legend{fill:#EEEEEE;stroke:#000000;stroke-width:1;}
text.legend{font-size:10;}
#background{fill:#FFFFFF;stroke:none;}
rect.chartholderbackground{fill:#ffffff;stroke:#000000;stroke-width:1;}
#timestamp{text-anchor:start;font-size:9;}
text.tic{stroke:none;fill:#000000;font-size:12}
line.tic{stroke:#000000;stroke-width:1px;fill:none;}
#dial{stroke:#336699;stroke-width:2px;fill:#336699;fill-opacity:.5;}
#dial.alert{fill:#FF0000;fill-opacity:.5;}
#dialbackground{stroke:#000000;stroke-width:none;fill:none;filter:url(#MyFilter);}
#dialcenter{stroke:none;fill:#111111;filter:url(#MyFilter);}
#dialbackground-border{stroke:#DDDDDD;stroke-width:2px;fill:none;filter:url
(#MyFilter);}#low{stroke-width:3;stroke:#336699;}
#high{stroke-width:3;stroke:#FF0000;}
#XAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;}
#YAxisTitle{letter-spacing:2;kerning:auto;font-size:14;fill:#000000;text-anchor:middle;writing-mode:tb;}
.XAxisValue{font-size:8;fill:#000000;}
.YAxisValue{font-size:8;fill:#000000;text-anchor:end;}
.nodatafound{stroke:#000000;stroke-width:1;font-size:12;}
.AxisLine{stroke:#000000;stroke-width:2;fill:#FFFFFF;}
.GridLine{stroke:#000000;stroke-width:0.3;stroke-dasharray:2,4;fill:none;}
g.dataholder rect{stroke:#000000;stroke-width:0.5;}
.legenditem rect{stroke:#000000;stroke-width:0.5;}

Table 7-8 describes all supported CSS classes. Note that certain classes only apply to specific chart types.

rect.legend{fill:#CCCC99;stroke:none;} 

#background{fill:#FFFFFF;stroke:#FFFFFF;stroke-width:2;}

rect.chartholderbackground(display:none;)

"#timestamp{display:none;}" 

Referencing a Custom SVG Chart Cascading Style Sheet

You can reference a custom cascading style sheet for a chart using the CSS section of the Chart Attributes page. When you reference an external CSS, you can reference it entirely or simply override specific styles.

To reference a custom chart CSS:

1. Upload the CSS to Application Builder. See "Uploading Cascading Style Sheets".

2. Create a chart. See "Creating a SVG Chart".

3. Navigate to the Page Definition. See "Accessing a Page Definition".

4. Under Regions, click Chart next to the region name.

   The Chart Attributes page appears.

5. Scroll down to the CSS section.

6. From Use Custom CSS, select Yes.

7. To reference an external CSS exclusively:

   1. In Custom CSS, Link, enter a link to a custom CSS. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      

   2. Specify that the CSS should be used exclusively. In Custom CSS, Inline enter the following:

      /**/
      

8. To reference a custom CSS and override specific styles:

   1. In Custom CSS, Link, enter a link to a custom style sheet. For example:

      #IMAGE_PREFIX#themes/theme_4/svg.css
      

   2. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Specifying Custom CSS Styles Inline for SVG Charts

You can override specific styles within the default CSS, using the Custom CSS, Inline attribute on the Chart Attributes page.

To override specific styles within the default CSS:

1. Create a chart. See "Creating a SVG Chart".

2. Navigate to the Page Definition. See "Accessing a Page Definition".

3. Under Regions, click Chart next to the region name.

   The Chart Attributes page appears.

4. Scroll down to CSS.

5. From Use Custom CSS, select Yes.

6. In Custom CSS, Inline, enter the custom CSS styles you want to override.

Adding a Flash Chart to an Existing Page

To add a flash chart to an existing page:

1. Navigate to the Page Definition. See "Accessing a Page Definition".

2. Under Regions, click the Create icon.

   The Create Region Wizard appears.

3. Select Chart and click Next.

4. For Region, select Flash Chart.

5. For Display Attributes

   1. Specify the following:

      • Title

      • Region Template

      • Display Point

      • Sequence

      • Column

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

6. On Chart Preview, configure the chart attributes. Click Update to refresh the preview image.

7. Click Next.

8. For Source:

   1. Specify a query by either:

      • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

      • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

   2. Specify relevant chart attributes. To learn more, click the item label.

9. Click Create Region.

Adding a Flash Chart to a New Page

To create a flash chart on a new page:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Click Create Page.

4. Select Chart and click Next.

5. Select Flash Chart.

6. For Page Attributes:

   1. Specify the following:

      • Page Number

      • Page Name

      • Region Template

      • Region Name

      • Breadcrumb

      To learn more, click the item label. When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

   2. Click Next.

7. Specify whether to include tabs and click Next.

8. For Chart Preview, configure the chart attributes. Click Update to refresh the preview image.

9. Click Next.

10. For Query:

    1. Specify a query by either:

       • Entering a SQL query in the field provided. See "About Creating SQL Queries for Charts".

       • Clicking the Build Query button. When the Build Chart Query Wizard appears, follow the on-screen instructions.

    2. Specify the remaining attributes. To learn more, click the item label.

    3. Click Next.

11. Click Finish.

About Migrating SVG Charts to Flash

You can automatically migrate single or multiple SVG charts to Flash.

About Navigation Alternatives

The Chart Attributes page is divided into sections. You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Creating an HTML Button

Buttons can be placed in a predefined region template position or among items in a form. To create an HTML button, select one of the following while running the Create Button Wizard:

• Under Task, select Create a button in a region position.

• Under Button Type, select a button type and then HTML Button (default).

Editing Button Attributes

You can edit button attributes on the Edit Pages Buttons page.

To edit attributes for an existing button:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Buttons, select the button name.

   The attributes page for the button appears.

3. To learn more about a specific item on a page, click the item label.

   When Help is available, the item label changes to red when you pass your cursor over it and the cursor changes to an arrow and question mark. See "About Field-Level Help".

4. Click Apply Changes.

Using the Edit All Icon to Edit Multiple Buttons

You can edit multiple buttons at once by clicking the Edit All icon on the Page Definition. The Edit All icon resembles a small grid with a pencil on top of it.

Clicking the Edit all icon displays the Buttons page, which contains a table listing the buttons in the region. Use this icon to edit multiple buttons at once.

From the Buttons page, you can delete multiple buttons or view a history of recent changes.

To edit multiple buttons:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Buttons, click the Edit All icon.

   The Buttons page appears.

3. Edit the attributes on the Buttons page, or click the Edit icon to edit the attributes for a single button.

4. Click Apply Changes.

Using the Reorder Buttons Icon

You can quickly edit a button label or change a button position within a region by clicking the Reorder Region Buttons icon on the Page Definition. The Reorder Region Buttons icon resembles a light green downward arrow and upward arrow and displays next to the region name.

To edit buttons using the Reorder Region Buttons icon:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Under Buttons, click the Reorder Region Buttons icon.

   The Reorder Buttons page appears. Use this page to edit the button label, select a region position, or change the button order.

3. To edit the button label, enter a name in the Label field.

4. To change the region position, make a selection from the Position list.

5. To change the order in which buttons display, click the up and down arrows in the far right column.

   Note that you can also control the order in which buttons display by editing the Sequence attribute. See "Editing Button Attributes".

6. Click Apply Changes.

About the List of Values Page

Once you create an LOV, it appears on the List of Values page.

Use the Navigation bar at the top of the page to search for a LOV by name or change the page display. For example, you can change the default display by making a selection from View list. Available options include:

• Icons (the default) displays each LOV as a large icon. To edit an LOV, click the appropriate icon.

• Details displays each LOV as a line in a report. To edit an LOV, click the name.

About Navigation Alternatives

The Edit List of Values page is divided into the following sections: Name, Subscription, Source, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Bulk Edit of Static LOVs

You can edit the display values of all static LOVs by clicking the Grid Edit button on the Edit List of Values page.

To perform a bulk edit of static LOVs:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Click Shared Components.

4. Under User Interface, select Lists of Values.

   By default, LOVs display as icons.

5. Change the default display. Select Details from the View list and click Go.

6. Locate the Static LOV and select the LOV name.

7. Click the Grid Edit button located under Subscription.

8. Edit the appropriate display values and click Apply Changes.

Search

Click Search to display the Search Dynamic Lists of Values page. Use this page to search the queries that make up dynamic LOVs. Enter a query in the Query Contains field and click Go.

Subscription

Click Subscription to display the List of Values Subscription page. This page displays all subscribed LOVs in your application.

Utilization

Click Utilization to display the List of Values Utilization page. This page displays LOVs used in the current application. To edit an LOV, click the LOV name.

History

Click History to display the List of Values History page. This page displays a history of recently changed LOVs by date.

Text with JavaScript Escaped Single Quotes

Use this type of shortcut to reference a shortcut inside of a JavaScript literal string. This shortcut defines a text string. When the shortcut is referenced, it escapes the single quotation marks required for JavaScript.

Message

Use this type of shortcut to reference a translatable message at run time. Note that since this shortcut does not have a shortcut body, the name of the shortcut must match the corresponding message name. At run time, the name of the shortcut expands to the text of the translatable message for the current language.

Message with JavaScript Escaped Single Quotes

Use this type of shortcut to reference a shortcut inside of JavaScript literal string and reference a translatable message at run time.

About the Shortcuts Page

Once you create a shortcut, it appears on the Shortcuts page. You control how the page displays by making a selection from the View list. Available options include:

• Icons (the default) displays each shortcut as a large icon. To edit a shortcut, click the appropriate icon.

• Details displays each shortcut as a line in a report. To edit a shortcut, click the name.

About Navigation Alternatives

The Edit Shortcut page is divided into the following sections: Name, Subscription, Source, Configuration, and Comments.

You can access these sections by scrolling down the page, or by clicking a navigation button at the top of the page. When you select a button at the top of the page, the selected section appears and all other sections are temporarily hidden. To view all sections of the page, click Show All.

Subscribed Shortcuts

Click Subscription to display the Subscribed Shortcuts page. This page displays all subscribed shortcuts in your application.

Shortcut History

Click History to display the Shortcut History page. This page displays a history of recently changed shortcuts by date.

Creating an Access Control from the Application Home Page

To create an access control list from the Application home page:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

   The Application home page appears.

3. Click Create Page.

4. For page type, select Access Control and click Next.

   The Access Control Wizard appears.

5. Specify a page number and click Next.

6. Select a tab option and click Next.

7. Review the confirmation page and click Finish.

Creating an Access Control from the Page Definition

To create an access control list from the Page Definition:

1. Navigate to the appropriate Page Definition. See "Accessing a Page Definition".

2. Click the Create button next to the navigation bar at the top of the page.

3. Select New page and click Next.

4. For page type, select Access Control and click Next.

   The Access Control Wizard appears.

5. Specify a page number and click Next.

6. Select a tab option and click Next.

7. Review the confirmation page and click Finish.

Creating an Access Control List from the Developer Toolbar

To create an access control list from the Developer toolbar:

1. Run the application. See "Running a Page or Application".

2. On the Developer toolbar, click Create.

   The New Component Wizard appears.

3. Select New page and click Next.

4. For page type, select Access Control and click Next.

   The Access Control Wizard appears.

5. Specify a page number and click Next.

6. Select a tab option and click Next.

7. Review the confirmation page and click Finish.

Selecting an Application Mode

To select an application mode:

1. Create an access control list. See "Creating an Access Control List".

   The wizard creates a page named Access Control Administration.

2. Run the Access Control Administration page. See "Running a Page or Application".

3. Select an Application Mode. Options include:

   • Full access to all, access control list not used.

     Select this option to enable all users access to an application.

   • Restricted access. Only users defined in the access control list are allowed.

     Select this option to restrict access to users on the Access Control List. Only users on the Access Control List can view pages and components associated with an authorization scheme.

   • Public read only. Edit and administrative privileges controlled by access control list.

     Provides public access to pages and components associated with the access control - view authorization scheme.

   • Administrative access only.

     Only users with Administrator privileges can access pages or components associated with an authorization scheme.

4. Click Set Application Mode.

   
   Description of the illustration access_control_1.gif
   
   

   Note that the user interface of your page is dependent upon the theme you selected for your application. See "Managing Themes".

   Next, add users to the Access Control List.

Adding Users to the Access Control List and Selecting Privileges

To add users to the Access Control List:

1. Under Access Control List, click Add User.

   A new row appears.

   
   Description of the illustration access_control_2.gif
   
   

2. Enter a user in the Username field.

3. Associate a privilege with the user. Available options include:

   • Administrator

   • Edit

   • View

4. Click Apply Changes.

5. Repeat steps 1 to 5 for all users.

Removing Users from the Access Control List

To remove users from the Access Control List:

1. Select the user to be removed by selecting the check box to the left of the user name.

2. Click Delete.

Incorporating JavaScript into the HTML Header Attribute

One way to include JavaScript into your application is to add it to the HTML Header attribute of the page. This is a good approach for functions that are very specific to a page and a convenient way to test a function before you include it in the .js file.

You can add JavaScript functions to a page by simply entering the code into the HTML Header attribute of the Page Attributes page. In the following example, adding the code would make the test function accessible from anywhere on the current page.

To add JavaScript code in the HTML Header attribute:

1. On the Workspace home page, click the Application Builder icon.

2. Select an application.

3. Select a page.

4. Under Page, click the Edit page attributes icon.

5. Scroll down to HTML Header.

6. Enter code into HTML Header and click Apply Changes.

For example, adding the following would test a function accessible from anywhere on the current page.

<script type="text/javascript">
  function test(){
    window.alert('This is a test.');
  }
</script>

Including JavaScript in a .js File Referenced by the Page Template

In Oracle Application Express, you can reference a .js file in the page template. This approach makes all the JavaScript in that file accessible to the application. This is the most efficient approach since a .js file loads on the first page view of your application and is then cached by the browser.

The following demonstrates how to include a .js file in the header section of a page template. Note the line script src= that appears in bold.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <title>#TITLE#</title>
    #HEAD#
    <script src="http://myserver.myport/my_images/custom.js" type="text/javascript"></script>
</head>
<body #ONLOAD#>#FORM_OPEN#

Defining Help for a Page

To define page Help text:

1. Navigate to the Page Definition for the page for which you want to add page Help. See "Accessing a Page Definition".

2. Under Page, click the Edit page attributes icon to view the existing page attributes.

3. Scroll down to Help.

4. Enter your Help text in the field provided.

5. Click Apply Changes.

Repeat the previous procedure for each page requiring page Help text.

Defining Help Text for an Item

To define item Help text for an item:

1. Navigate to the Page Definition for the page for which you want to add item Help. See "Accessing a Page Definition".

2. Under Items, click the name of the item you want to edit.

3. Scroll down to Help Text.

4. Enter your Help text in the field provided.

5. Click Apply Change.

Repeat the previous procedure for each item requiring Help text.

Editing Multiple Item Help Topics Simultaneously

If you are including item Help in your application, you can edit multiple item Help topics at once using the Bulk Edit Item Help report.