How-To Document

Based on Application Express (formerly HTML DB) version 1.5

After completing this How-To, you should be able to understand:

• Changing column headings
• Updating column alignment
• Using different column attributes to control report column formatting

Introduction

In Oracle HTML DB a report is simply the formatted result of a SQL query. You can generate reports by:

• Selecting and running a built-in wizard
• Defining a report region based on a SQL query
• Defining a report region based on a PL/SQL function returning a SQL query

The HTML DB Application Builder offers a great deal of flexibility in defining report attributes. Using the Report Attributes page, you can specify the report template, column display sequence, display report headings, column and heading alignments, column formatting, and sort order. You can further refine attributes of a specific column using the Column Attributes page.

Prerequisites

• Oracle HTML DB

Column Headings and Alignment

You can use the following report column attributes to customize your report:

• Define column headings: column names (upper case or init cap), custom headings, PL/SQL function or no headings.
• Set column and column heading alignment.
• Show and hide columns.
• Building sums on numeric columns
• Enable column heading sorting and define default sort sequence.

Steps to modify the column headings and alignment:

• This example uses a standard HTML DB report built on the employee (EMP) table. The screenshot below shows the result of the following query: select * from emp.



Fig. 1: HTML DB default report on employee table

• To modify column headings and column alignment, navigate to the report attributes page following the steps below:
  • Navigate to the page definition by clicking on "edit page" in the developer toolbar, or by clicking on the page name from the Application Builder home page.
  • Navigate to the report attributes page by clicking on the region name of your report region or on the "Q" link.

  

  Fig. 2: Page Definition, Regions

• On the reports attribute page a table with column definitions for your report is shown, followed by general report attibutes.



Fig. 3: Report Attributes, editing column definitions

• Perform the following tasks to modify report column attributes:
  • Choose the column headings type, in this example we will use custom headings.
  • Enter names for all your columns headings or keep the suggested default names.
  • Pick column alignment and column heading alignment from the select lists shown next to the column heading names.
  • Click on apply changes to save the changes to your report.



Fig. 4: Report Attributes, updateting column alignment and column headings

• Run your page to see how your modifications changed the headings and column alignment in the report.



Fig. 5: Updated employee report

Column Formatting

You can use the following column formatting properties to further customize column display:

Steps to modify the formatting of your report columns:

• Navigate to the report attributes page using the region name link or directly by clicking the "Q" link:

  

  Fig. 6: Page Definition, Regions

• Each column definition on the report attributes page show an "Edit" icon next to the column alias name (see Fig. 7), by clicking on one of those icons you'll get to the column attributes page.



Fig. 7: Report attributes, edit column icons

• On the column attributes page you can define general column attributes such as alignment, sorting, etc., you're also able to define column formatting, column links and conditions. To save your changes, click on the "Apply Changes" button, to change another column, or to switch back and forth between different columns use the back and forward buttons next to the "Apply Changes" button (see Fig. 8).



Fig. 8: "Apply Changes" button and buttons used to navigate to the next and previous column

• The first column we want to modify is the hire date; we want to define a new date display format and highlight the month November, the steps to achieve this are:
  • Navigate to the hire date column definition either by clicking the edit link on the report attributes page or by using the navigation buttons provided on the column attributes page.
  • On the report column attribute page scroll down to "Column Formatting".



Fig. 9: Control report column formatting

• Number and date format masks can be typed into the corresponding field directly, or you can pick a pre-defined mask. Clicking on the flashlight icon next to the format mask input field (see Fig. 10 ) will open a popup window from where you can pick a format mask from a list several different format masks.



Fig. 10: Build in date and number format masks

• Additionally we will use the "fielddatabold" class definition from our style sheet to display the data column values in a bold font. The definition for the class "fielddatabold" can be found in the core.css style sheet, which is part of Oracle HTML DB applications. You can define your own style sheets and use them by including a reference to your CSS file with your page templates. For additional information about style sheets and templates, please refer to the white paper "Oracle HTML DB Templates".
• We also choose to highlight the month November by typing the string "NOV" into the "highlight words" attribute. Instead of static strings you can also refer to items using the &item_name. syntax.



Fig. 11: Using CSS styles and highlighting words

• In addition to formatting the hire date, we want to combine the salary and commission values into one column and define an alternate formatting for those two values.
  • Navigate to the salary column definition, either via report attributes page or directly using the next and previous buttons.
  • Once salary is the current column, scroll down to "HTML expression"
  • Type in any kind of HTML using the #COLUMN_NAME# substitution string syntax to define how to display the current column values.



Fig. 12: Using HTML expressions

• Figure 8 shows the report with a new date format for the Hiredate column and the Salary and Commission combined into one column.



Fig. 13: Report using CSS styles, highlighting and HTML expressions