Building Interactive ReportsBy David Peake
Users can customize interactive reports with Oracle Application Express.
Typically, Web applications designed for displaying and modifying data are not user-customizable. Application developers build Web pages to display data based on business requirements, and users who want the data displayed differently either export it and load it into a spreadsheet program or ask the developers to modify the report layout (which usually requires all other users to agree to the change).
Oracle Application Express Version 3.1 breaks this mold with interactive reports functionality. End users can modify their own data layouts directly in the Web application to display secure, up-to-date data. Each user can rearrange a report's data and save multiple layouts for later reference, without impacting other users.
This column first takes you through the procedure for building an application in Oracle Application Express that includes an interactive report and then gives you a detailed look at the range of manipulations and customizations that end users can perform with interactive reports. You can run through these steps on the hosted instance of Oracle Application Express 3.1 at apex.oracle.com. (You must request a free workspace to use this hosted instance.) You can also run through the steps on a local instance of Oracle Application Express 3.1. The example application is based on the EMP table, which is part of the SCOTT sample schema.
Building an Interactive Report
The following steps illustrate how to build an interactive report region for a new application:
1. From the Oracle Application Express home page, select Application Builder -> Create Application -> Create Application.
The application displays the interactive report shown in Figure 1.
Customizing Interactive Reports
You can customize an interactive report's region definition with conditions, header and footer, authorization, build option, and comments. In addition, a separate Interactive Report Attributes page is available for customizing the report's interactive features.
To explore the Interactive Report page, go to the Oracle Application Express home page and select King Enterprises from Application Builder . Click the King Enterprises icon. In the Regions area of the page definition for page 1, click Report to open and explore the Interactive Report page. (Note that the other tab takes you to the Region Definition page.) The key sections for determining what end users can do with their interactive reports are the following:
Column attributes. Click the Edit icon adjacent to any column's label to open the Column Attributes page for that column. By setting column attributes, you can determine on a column-by-column basis if the user can sort, filter, aggregate, add breaks, and perform computations on that column.
Column groups. Column groups are used to separate columns in the user's single-record view. The group name is shown as a header in the single-record view, with the columns in that group below it in a collapsible list. To add a group, click Add Group and enter a group name and description. (The description isn't shown to the end user.) After you click Apply Changes , the groups you've created appear in the Column Attributes page, where you can select the group to associate with each column.
Functionality. Here you select which controls are available to the interactive report's end user. You can show or hide controls for selecting columns, filtering, sorting, highlighting, doing computation, aggregating, creating a chart, creating a calendar, and flashing back to get information from an earlier time as well as for saving, resetting, and downloading the report. (You'll find more detail on these controls in the next section, "The User Experience.") You can also choose the download formatsfrom CSV, PDF, RTF, and XLSusers can select. Finally, you can grant or deny users the options of excluding null values and hiding extra columns from the detail view.
The User Experience
When a user runs an interactive report, it initially looks much like the standard Web report shown in Figure 1. Clicking on a column heading invokes various options, as shown in Figure 2.
On the right-hand side of the search bar is the Action Menu icon. Figure 3 shows the Action Menu's contents:
Select Columns. Choose which columns to show or hide and the order in which those columns are displayed onscreen.Filter. Create a filter using relational operators and relevant values (for example, Salary > 1500).
Sort. Sort up to six columns.
Highlight. Highlight a row or cell with customized background and text color by entering a name and column combined with relational operators.
Compute. Create a new column as a computation of other columns.
Aggregate. Perform aggregate functionssum, average, minimum, maximum, median, or modeon a specified column.
Chart. Display data in a bar, pie, or line chart and select the label, value, and aggregate function.
Calendar. Select the date field and the field to be displayed on the user's calendar.
Flashback. Specify the number of minutes to go back and get the details of the interactive report as it existed at that point in time.
Save Report. Enter a name and description to save the current report settings. Saved reports will still be there whenever the user returns to this region.
Reset. Remove all customizations and return the report to its original state.
Download. Download the report contents in CSV, PDF, RTF, or XLS format.
Now you'll follow these steps to modify the standard report shown in Figure 1, creating the customized interactive report shown in Figure 4:
1. Run the King Enterprises application in Oracle Application Express.
5. Choose Compute from the Action Menu.
6. Enter Remuneration in the Label field. Enter $9,999,990.00 in the Format Mask field. In the Computation field, enter F + nvl(G, 0) manually or by clicking the column names and symbols. (Clicking a column name inserts the corresponding letter for that column.) Click Apply.
7. Choose Sort from the Action Menu.
8. Select ** Remuneration and Descending as the first sort and Hire Date and Ascending as the second. Click Apply.
9. Click the Action Menu icon and choose Aggregate.
10. Select Sum from the Aggregate list and ** Remuneration from the Column list. Click Apply.
11. Choose Highlight from the Action Menu.
12. Enter Top Remuneration in the Name field. Select Cell as the Highlight Type, Blue as the Background Color , and Yellow as the Text Color . Under Highlight When , select ** emuneration and >= and enter 3000 . Click Apply.
13. Choose Highlight from the Action Menu.
14. Enter Review Salary in the Name field. Select Yellow as the Background Color and Red as the Text Color . Under Highlight When , select ** Remuneration and <= and enter 1000 . Click Apply.
Figure 4 shows the results of the customizations you've just made.
David Peake (firstname.lastname@example.org) is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.