Written by Ricky Burke, Oracle Architect and Instructor
Oracle Application Express (APEX) is a free development tool for the Oracle database. Using only a web browser and limited programming experience, you can rapidly develop and deploy professional, scalable and secure web-based applications.
APEX is easy to use, flexible and highly productivity. No client-side software installation is necessary, and when configured with the XML DB HTTP server built into the Oracle database, there is no need for a separate web server.
One of the best features of Application Express is the new Interactive Report. This article starts by covering the complete list of features provided automatically by using Interactive Reports.
Next, it shows you how to try out the Interactive Report features on an existing application.
Then, several resources are discussed that show you how to build an application, from scratch, that uses Interactive Reports. Oracle has an entire subsite devoted to APEX at http://apex.oracle.com and I’ll tell you about some additional APEX Interactive Report resources, including Audio Visual tours.
APEX Interactive Reports
The new Interactive Report feature of Application Express is – in a word – amazing!
Comparing a bland, mono-spaced SQL*Plus report to an APEX interactive report is like comparing a skate board to a space ship, a scratchy phonograph recording to a Technicolor movie with Dolby/THX Surround sound, or a one-room cabin to the Hearst Castle.
What the Interactive Report really does is allow a SQL query to inherit the same capabilities available in an Excel report, which is – once again – amazing!
All of the features of Interactive Reports are available to your APEX application by:
1) creating a Report Region
2) selecting the implementation type
3) dropping a SQL statement into the Source field
If you are interested in making use of APEX Interactive Reports, the first step is to become familiar with the many cool features available to you.
Overview of APEX Interactive Report Features
A developer need only define a single SQL query and all the features described, unless explicitly disabled, will be available to the end users. Developers can enable and disable features to customize the reports for their specific use case. Interactive Reports is a significant step forward for Web data reporting. Database reporting has always been strength of Application Express and Interactive Reports takes Application Express to a whole new level.
Column Sorting and Filtering
Many actions can be performed just by clicking on a column heading. This allows for sorting, hiding the column, creating a control break, viewing of help text for the column and the ability to select a value to create a quick filter.
Clicking on a Column heading brings up a Column-specific area that provides the following features:
- Single Column Sort (Ascending or Descending)
- Hide Column
- Control Break
- Column-specific Search field (that searches only the values in the chosen column).
The Control Break removes the column from report and then groups records together that have the same column value. It then precedes each group of records with a line showing the column name and current value.
Note: If you are in Developer mode, and the “Show Edit Links” feature is enabled in the Developer menu bar at the bottom of the page, then there will also be an “Edit” icon link that will take you directly to the Report Attributes page for the Report Region, where you can change things like column titles and data formats (e.g., currency, percentage, dates).
In the Search Bar section of the Report Attributes page, you can specifically enable or disable any of the features of the Interactive Report tool area. In the Link Column section, you can change the column values to hyperlinks to provide drill-down or other link features.
Single Row View
To view the details of a single row at a time, click the single row view icon on the row you wish to view. If available, the single row view will always be the first column. Depending on the customization of the Interactive Report, the single row view may be the standard view or a custom page that may allow update.
The single row view has an option to Exclude Null Values; showing only columns with non-NULL data, which is very helpful for records that have many unused columns. It also has an option to show only the Displayed Columns (the Interactive Report can retrieve column data that may not be displayed by the standard or current report view).
Search Bar
Allows for quick searching of the data, changing the number of rows displayed, and invokes the Actions Menu. Searches here will go against all text data. Clicking the drop-down menu next to the Magnifying Glass icon on the left of the Search text field allows you to specify if the search should be done on “All Columns” or only on a single, specified column.
Actions Menu
The Actions Menu contains many tasks that are useful in manipulating your Interactive Report. This includes all the items listed below, plus Multiple Column Sorting and Filtering.
Highlight. Highlighting allows you to define a filter. The rows that meet the filter are highlighted using the characteristics associated with the filter. You can highlight the entire row or just the affected cell and can select a new color both for the background and the text.
Compute. Computations allow you to add computed columns to your report. These can be mathematical computations (e.g. NBR_HOURS/24) or standard Oracle functions applied to existing columns (some have been displayed for example, others, like TO_DATE, can also be used).
Aggregate. Aggregates are mathematical computations [such as Sum, Count, or Average] performed against a column. Aggregates are displayed after each control break and at the end of the report within the column they are defined.
Chart
You can include one chart per Interactive Report. Once defined, you can switch between the chart and report views using links below the search bar. The available chart types are horizontal bar, vertical bar, pie or line.
Flashback
Flashback performs a flashback query to allow 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 per database.
Disabling or Removing Customizations
Reset brings the report back to the default settings, removing any customizations that you have made. You can also uncheck the checkbox next to any customization to temporarily disable it or click the icon with the red x to remove the customization. The area showing your customizations can also be expanded and contracted.
Downloading Results
APEX allows the Report data to be downloaded in a Comma Separated Value (CSV) format, which may either be saved, or opened directly in Excel. If BI Publisher is installed, the report data may also be downloaded in either Adobe PDF format, XLS for direct storage in Excel format, or Rich Text Format (RTF), which may be saved, or opened directly in Word.
NOTE: BI Publisher is not free; but Apache Formatting Objects Processor (FOP) is free and allows output to PDF format.
Finding Out About More Features
There are many Oracle documents that describe all of the features of the APEX Interactive Report. Perhaps the most concise list of features, with illustrated examples (screen shots), is on Oracle Technology Network (TechNet), at:
http://www.oracle.com/technology/products/database/application_express/html/irrs.html
Trying Out the APEX Interactive Report Features
There are currently 15 APEX Podcasts and Viewlets, located at:
http://www.oracle.com/technology/products/database/application_express/html/podcasts.html
There is a wonderfully nice lady, standing by 24 hours a day, every day of the year (even leap years), ready to personally walk you through the aforesaid Amazing features of the Interactive Report.
Simply click on the “Interactive Reporting, in Application Express 3.1″ link from the Podcasts and Viewlets page noted above to have her begin your private Audio/Visual tour of the fantastic features of the APEX Interactive Reports.
Once you are familiar with the list of features of the APEX Interactive Reports, you will likely next want to try these features out on an existing application, without the hassle of actually having to create an application yourself. You can do this without installing your own APEX instance or even signing up for a Free APEX Hosted account.
You can access one of the many Packaged Applications that are already installed on the Oracle APEX Hosted site. There are currently 20 Packaged Applications and 11 Sample Code packages available for APEX. All of these are available for download, and most have a “Preview” link which allows you to actually run the application without any set-up on your computer. All you need to run the sample application is a browser. The list of Packaged Applications and Sample Code is located at:
http://www.oracle.com/technology/products/database/application_express/packaged_apps/packaged_apps.html
For a working example of an APEX Interactive Report, look at the Customer Tracker Packaged Application Preview at:
http://apex.oracle.com/pls/otn/f?p=27349
To log in, use the username “demo” and password “demo”, and then click “Customers”. Here you can actually try out all the features of the Interactive Reports for yourself. (Notice that most of the other reports in this application are also implemented as Interactive Reports.)
Building APEX Applications Using Interactive Reports
After you are familiar with the list of features of the APEX interactive reports and how they work, you may want to begin creating your own applications that include interactive reports.
When you create a Report Region and select the Interactive Report implementation type, the default Template (under Region Definition / User Interface) is “No Template”. This does not include the familiar border that is the default for a standard SQL Report, so the Interactive Report tool area does not seem “connected” to the report table.
However, you can always change the Template to “Reports Region” which restores the border surrounding both the Interactive Report tool area and the report table, thereby providing a visual connection between these two objects.
APEX Interactive Report Resources
Three sources are available from Oracle which walk you through building applications using Interactive Reports – Oracle Magazine Articles, Oracle By Examples and the Oracle Application Express Manuals.
Oracle Magazine Articles
All Oracle Magazine Articles are located at:
http://www.oracle.com/technology/oramag/oracle/index.html
The Mar/Apr 2008 issue contains the article “Building Interactive Reports”, which is perhaps the simplest example of creating an application using Interactive Reports, from scratch. This article is located directly at:
http://www.oracle.com/technology/oramag/oracle/08-mar/o28browser.html
Oracle By Examples
There are currently 13 Oracle By Examples (OBEs) for Application Express. This number is really 25, since one of the OBEs, Oracle Develop 2008 Hands On Labs, actually contains 13 separate OBEs.
The APEX Oracle By Examples are located at:
http://www.oracle.com/technology/products/database/application_express/html/obes.html
4 of the top level OBEs, and another 3 in the Oracle Develop 2008 Hands On Labs section are devoted to APEX Interactive Reports.
Oracle Application Express Manuals
The Oracle Application Express Manuals are located at:
http://www.oracle.com/technology/products/database/application_express/html/doc.html
In the Oracle Application Express Advanced Tutorials manual, Chapter 15, “How to Build and Deploy an Issue Tracking Application”, walks you through building a complete Issue Tracking system that uses Interactive Reports. Completing this entire exercise could take several hours, and covers many topics in addition to Interactive Reports. This is a very good tutorial if you are looking for APEX experience in general, but could take several days of part-time effort to complete. If you are looking specifically for quick Interactive Reports experience, start with the resources mentioned above.
Application Express Discussion Forum
Finally, if you have additional questions about APEX Interactive Reports, the Application Express Discussion Forum is an excellent resource. It is located at:
http://forums.oracle.com/forums/forum.jspa?forumID=137
First, try to determine if the question has already been answered by typing the relevant information into the “Search Forum” field on the right side of the screen. (Note: The default date range is “Last 90 Days”. If you do not find your answer in the initial result set, try setting the Date Range to “All”.)
If the question has not already been answered, try posting your question in its very simplest form. Create a new application that shows only the feature you are having a problem with, and include any relevant code snippets, in their simplest form, in your post.
Remember, the Application Express Discussion Forum is composed of volunteers that are kind enough to help you with your questions, so treat them graciously, even if their suggestions do not seem to work for you.
Oracle MetaLink
If you have access to MetaLink, you can ask the same questions you may have asked on the Application Express Discussion Forum. MetaLink is staffed by Oracle professionals that can usually provide answers to your questions within 24 hours.
Oracle Application Express Classes
Community colleges sometimes offer hand-on classes on Oracle Application Express. These can take you from a Novice to an Intermediate-Level Application Express developer over the course of dozen sessions. Plus, these courses are normally taught by professionals who use the tools in everyday life.
The “Application Express – Making Oracle Fun and Highly Productive!” series of articles is written by Ricky Burke, an Oracle Architect/Developer and past President of the Dallas Oracle Users Group. Mr. Burke has taught Oracle Certification Courses at Dallas Colleges, and is currently teaching a class on Oracle Application Express at Collin County Community College.