As Published In
Oracle Magazine
March/April 2008

DEVELOPER: Browser-Based


Building Interactive Reports

By 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.
2. Enter King Enterprises in the Name field and leave other options with their default values; click Next.
3. In the Add Page area, select Report . Select Table from the Page Source list, select EMP from the Table Name search list, and select Interactive from the Implementation list. Click Add Page.
4. Click the Page Name link (under Emp ) within the top region of the page to open the New Page Definition.
5. In the Page Definition area, change Page Name to King Enterprises. In the Report Columns area, replace the contents of the Heading / Label boxes with, respectively, Employee, Name, Job, Manager, Hire Date, Salary, Commission, and Department. Click Apply Changes.
6. Complete the wizard by clicking Create , and click Create again on the confirmation page.
7. Click Run Application.

The application displays the interactive report shown in Figure 1.

You can use the Create Page and Create Region wizards to add interactive reports to an existing application. Note that when you use either wizard, you must use a SELECT query to specify the report source.

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 formats—from CSV, PDF, RTF, and XLS—users 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 functions—sum, average, minimum, maximum, median, or mode—on 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.

End-User Customizations

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.
2. Click the Department column head, and then click the Break Column icon. The report now breaks into departments. (You can see the break specified under the search bar, with disable and remove options.)
3. Click the Action Menu icon and choose Select Columns.
4. Remove Employee and Manager by selecting each one and then clicking the left-pointing Remove arrow. Click Hire Date and move it above Job by clicking the Up arrow on the right. Click Apply.

figure 1
Figure 1: Standard interactive report
figure 2
Figure 2: Column options
figure 3
Figure 3: Action Menu options
figure 4
Figure 4: User-customized interactive report

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.

Next Steps



 READ more Browser-Based

 LEARN more about Oracle Application Express

 DOWNLOAD Oracle Application Express

 VISIT the Oracle Application Express Forum


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
(david.peake@oracle.com) is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.

Send us your comments