As Published In
Oracle Magazine Logo Social black
March/April 2017

TECHNOLOGY: Oracle Application Express

 

See Better Results

By Joel Kallman

 

Build better data visualizations with Oracle Application Express 5.1 charts.

The data visualization engine of the recently released Oracle Application Express 5.1 is now powered by Oracle JavaScript Extension Toolkit (Oracle JET), a modular open source toolkit based on modern JavaScript, CSS3, and HTML5 design and development principles. The charts in Oracle Application Express 5.1 are powered by Oracle JET and fully HTML5 capable, and they work in any modern browser, regardless of platform or screen size. These charts provide numerous ways to visualize a data set, including bar, line, area, range, combination, scatter, bubble, polar, radar, pie, funnel, and stock charts.

In this Oracle Magazine article, you’re going to build a web application on top of US flight arrival data, and using the newly integrated charts from Oracle JET, you will easily create several data visualizations to better interpret the results.

This article’s sample application is built in Oracle Application Express 5.1. If you’re not already running Oracle Application Express 5.1 or later locally, you can request a free workspace at https://apex.oracle.com. Alternatively, you can download the Database App Development Virtual Machine from the Oracle Technology Network, which includes a preconfigured Oracle Database 12c Release 1 Enterprise Edition database, Oracle Application Express 5.1, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. You will also need to download and unzip the comma-separated value (CSV) file for this article to load the sample data.

Creating the Application

Begin your Oracle Application Express 5.1 data visualization exploration by creating the initial application:

  1. In a web browser, log in to Oracle Application Express and click the App Builder icon.
  2. Click the Create icon, and then click the From a spreadsheet link.
  3. Select Upload file, and click Next.
  4. Choose the supplied airline_delay_causes_102016.csv file, enter " for Optionally Enclosed By, and click Next.
  5. Enter AIRLINE_DELAYS for Table Name, and click Next.
  6. Change Application Name to Airline Delays, choose Interactive Grid for Report Type, choose Single Page for Page Type, and click Create Application.

You’ve now created a table in Oracle Database; inserted your spreadsheet data as rows into your table; and created a fully functional, multiuser, editable grid on this table, accessible from a web browser.

Click the Run Application icon, and log in with your workspace credentials. Your application should look similar to Figure 1.

o27apex-f1

Figure 1: Application home page with interactive gird

Interactive Grid Data Visualizations

The interactive grid, a new component in Oracle Application Express 5.1, enables you to easily manipulate data simply by clicking on a cell and editing its value. The interactive grid includes many features for powerful reporting, including fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, computations, and more. A future Oracle Magazine article will discuss the interactive grid in greater depth. Right now, let’s move on to data visualizations!

The Oracle Application Express 5.1 charting engine for interactive grids has been directly integrated with Oracle JET charts, which results in greatly improved visualizations and also a wider array of built-in chart types (such as bubble, polar, radar, range, and scatter). Charts can be easily created in interactive grids and saved as custom views. End users can also create and save their own custom charts on the data.

Now add a pie chart to your Oracle Application Express 5.1 application:

  1. Ensure that you’re running page 1 of your application, the report on the AIRLINE_DELAYS table.
  2. Click the Actions button at the top of the report, and click Chart.
  3. Click the Pie icon.
  4. Choose Carrier Name for Label, Arr Del15 for Value, and Sum for Aggregation, and then click the Save button.

o27apex-f2

Figure 2:
Pie chart showing flight delays on a percentage basis

Your application should look similar to Figure 2. You have created a pie chart showing, on a percentage basis, the total number of flight delays 15 minutes or greater in the US for October 2016. Hover over the slices of the pie chart to get greater detail about this particular data point. Hover over each of the airline names in the legend, observing the highlighting of the respective slice. Click an airline name in the legend to remove that portion of data from the pie chart. These are all examples of the out-of-the box functionality present in Oracle JET charts.

About Oracle Application Express


Oracle Application Express is a high-productivity, low-code platform for creating modern, responsive, and accessible web applications. A no-cost feature of Oracle Database, it is a compelling application development platform available in all Oracle Database Cloud services.

You can save your chart as a custom view of the data in the interactive grid. Click the Actions button at the top of the report, click Report, and then click Save. At the top of the report, you’ll now notice an icon, enabling you to toggle between the grid view and the chart view.

Custom Data Visualizations

The formatting of charts in interactive grids is concise and limited, intended to cover the majority of basic charting needs. Oracle Application Express 5.1 also supports chart regions, which offer a greater array of customization options and showcase the power of Oracle JET charts and the native declarative integration with Oracle Application Express.

Add a page to the application with a new chart region to show the aggregate counts of airline delays by carrier:

  1. Navigate in your web browser to the browser tab running App Builder (and not the browser tab running your application).
  2. Click the Create Page button.
  3. Click the Chart icon.
  4. Click the Bar icon.
  5. Enter Delays by Carrier for Page Name, and click the Next button.
  6. Click Create a new navigation menu entry for Navigation Preference, and click Next.
  7. Ensure that SQL Query is selected for Source Type, and enter the following SQL query:

    select sum(carrier_ct) value, carrier_name, 'Carrier' 
    as series from airline_delays group by carrier_name
    union all
    select sum(weather_ct) value, carrier_name, 'Weather' 
    series from airline_delays group by carrier_name
    union all
    select sum(nas_ct) value, carrier_name, 'National Aviation System' 
    as series from airline_delays group by carrier_name
    union all
    select sum(late_aircraft_ct) value, carrier_name, 'Late Aircraft' 
    as series from airline_delays group by carrier_name
    order by carrier_name
    

The full power of Oracle SQL can be used in Oracle Application Express to create beautiful data visualizations.

  1. Click Next.
  2. Choose CARRIER_NAME for Label Column, choose VALUE for Value Column, and click Create.
  3. Click the blue Run button in the upper right to run your page.

You have now created a vertical bar chart, showing the sum of all delays for each airline and specific type of delay.

Next Steps


DOWNLOAD

READ

REQUEST a free Oracle Application Express workspace.

TRY Oracle Database Cloud services.

At this point, the chart probably looks jumbled and confusing to you, but with the adjustment of a few small attributes, you can easily improve it:

  1. Click Edit Page 2 in the developer toolbar at the bottom of the page. This will bring you back to the tab running App Builder, ready to edit your newly created page and chart.
  2. In the tree pane on the left side of the page, click Series 1. The properties for the chart series will be displayed in Property Editor, on the right side of Page Designer.
  3. Scroll down the list of properties, and select SERIES for Series Name.
  4. Click the Run button in the upper right to save your changes and run your page again.

The chart is looking better, now that you have distinguished the separate series in the chart. The data labels in the chart, however, simply add clutter and should be removed. The information will be easier to consume if the chart orientation is changed too:

  1. Navigate in your web browser to the browser tab running App Builder and Page Designer (and not the browser tab running your application).
  2. In the tree pane on the left side of the page, click Attributes under Delays by Carrier. These are the overall attributes associated with the chart.
  3. In Property Editor on the right side of Page Designer, set Orientation to Horizontal. Also change Stack to Yes.
  4. In the tree pane on the left side of the page, click Series 1.
  5. In the list of series properties in Property Editor on the right side of the page, in the Label region, change Show to No.
  6. Click the Run button in the upper right to save your changes and run your page again.

By adjusting a few attributes, and without writing any additional code, you’ve created a very attractive horizontal stacked bar chart, comparing different delays across different airlines. Your chart page should now look similar to Figure 3.

o27apex-f3

Figure 3: Stacked bar chart showing flight delays

Click one segment of the bar chart, and then use the arrow keys on your keyboard to navigate around the various segments of the chart. Resize your browser window until it approximates the dimensions of a smartphone. Another great benefit of Oracle Application Express applications and the new Oracle JET charts is that they are responsive out of the box. Your applications will display appropriately across desktop and mobile devices.

Conclusion

The new charting engine in Oracle Application Express 5.1, based on Oracle JET, is a great advancement in the breadth and quality of data visualizations possible with Oracle Database and Oracle Database Cloud, and the low-code nature of Oracle Application Express provides a high level of developer productivity. As you’ve seen in this article, you can quickly and easily enhance a bar chart without writing a lot of code, and no separate deployment steps are required between development and execution.


Oracle Senior Director of Software Development Joel Kallman is responsible for the development and product management of Oracle Application Express. He is also a contributing author of several books on Oracle technology.

Send us your comments