Adding Charts to your Existing Database Application in Oracle Application Express 5.1

Purpose

In this tutorial, you learn how to add charts to your existing database application using Oracle Application Express 5.1.

Time to Complete

Approximately 20 minutes

Background

Oracle Application Express is a rapid web application development tool for the Oracle Database. Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure. Oracle Application Express is available with the Oracle Database, whether it's on-premises or in the Oracle Cloud.

In this tutorial, you use Oracle Application Express Release 5.1 to create and run a database application. In release 5.1, charts in Oracle Application Express are powered by the Oracle JET charting library. Oracle JET charts are a component of the Oracle JavaScript Extension Toolkit (JET), an open source toolkit based on modern JavaScript, CSS3 and HTML5 design and development principles. Oracle JET charts are fast, highly customizable and versatile. Charts are fully HTML5 capable and work on any modern browser, regardless of platform, screen size, or features.

What Do You Need?

• Have access to an Oracle Database 11g or later release, either on-premises or in a Database Cloud Service.
• Install Oracle Application Express Release 5.1 into your Oracle Database (for on-premises only). If you are using Oracle Database Cloud Service, the application is already installed for you.
• Provision at least one Oracle Application Express user. See Creating New User Accounts in Oracle Application Express Administration Guide.
  
• Have access to the HR tables. If necessary, download and install HR Sample Objects available from github.com.

• Have the files.zip file downloaded and extracted into your working directory.

• All files for this hands-on activity are located in the Home/Desktop/APEX_Track/files folder.

• The following files are used in this hands-on activity:

  • f100.sql

• Have the f100.sql application imported to your workspace along with the supporting objects.

• The f100.sql application must be imported to your workspace along with the supporting objects.

In this section, you create a Pie chart in a new page.

1. In the Application Express login page, enter the following login credentials and click Sign In:

   • Workspace: obe

   • Username: obe

   • Password: oracle

   

2. From the Oracle Application Express home page, click the down arrow next to the App Builder tab and choose Database Applications.

   

3. Click Projects Tasks Application. This is the application you imported from the Files folder in the What Do You Need? section.
   

   The application ID for the Project Tasks Application used in this tutorial is 100. You may see a different application ID when you perform this tutorial.

   

4. Click Create Page.

   

5. In the Create a Page modal window, click Chart.

   

6. Select Pie for Chart Type.

   

7. Enter Tasks per Project Chart for Page Name and click Next.

   

8. Select Create a new navigation menu entry for Navigation Preference, select Home for Parent Navigation Menu Entry, and click Next.

   

9. In the Source Tab, select SQL Query for Source Type, enter the following in the SQL Query field, and click Next.

   select null link,
          p.project label,
          t.task_count value
   from (select project_id,
         count(*) task_count from obe_tasks group by project_id) t,
        obe_projects p
   where t.project_id = p.project_id

   

10. Select LABEL for Label Column, VALUE for Value Column, and click Create.

    

11. The new chart page is created. Click Save and Run Page.

    

12. You may receive a Log In screen, where you must enter your Oracle Application Express credentials, and click Log In.

13. Observe the animation as the chart loads. The pie chart shows the numbers of tasks per project. Keep your mouse over the pie to display tool tip for the chart data. Also notice that the corresponding entry in the Legend getting highlighted while other entries fade out.

    

14. Click Edit Page <n> in the developer toolbar.

    

In this section, you add a Bar chart to the an existing page by using Page Designer. You add the chart to the same page where you created the Pie chart (in the previous section).

1. View the page in Page Designer. In this tutorial, the page 4 (Tasks per Project Chart) is opened in Page Designer.

2. In the Gallery at the bottom of the central pane, click Regions and locate Chart.

   

3. Right-click Chart in the Gallery to view a context menu. Select Add To, Content Body, Tasks per Project Chart and choose Column After.

   

   Page Designer indicates what actions are required next.

4. In the Region tab, enter Tasks per Project Chart 2 for Title and select the Attributes node in the Rendering tab.

   

   

5. In the Attributes tab, select Bar for Type, Horizontal for Orientation and select the New node in the Rendering tab.

   

   

6. In the Identification tab, enter Tasks for Name. In the Source tab, select SQL Query for Type and enter the following in the SQL Query field.

   select null link,
          p.project label,
          t.task_count value
   from (select project_id,
         count(*) task_count from obe_tasks group by project_id) t,
        obe_projects p
   where t.project_id = p.project_id

   

7. In the Column Mapping tab, select LABEL and VALUE for the Label and Value field respectively.

   

8. Click Save and Run Page.

   

9. You see the new chart added to the application.

   

In this section, you  edit the attributes of a Bar chart using Page Designer.

1. View the page containing the Bar chart in the Page Designer. In this tutorial, the page 4 (Tasks per Project Chart) is opened in Page Designer.

   

2. In the left pane, locate Tasks per Project Chart 2 in the Rendering tab and click the Attributes node under the chart.

   

3. In the Appearance tab, select Vertical for orientation. In the Messages tab, enter No data found. for the When No Data Found field, and click Save and Run Page.

   

4. You see that the edits made to the bar chart reflects in the application.

   

In this section, you switch a Bar chart to a Line chart by editing the chart attributes in Page Designer.

1. View the page in Page Designer. In this tutorial, the page 4 (Tasks per Project Chart) is opened in Page Designer.

   

2. In the left pane, locate Tasks per Project Chart 2 in the Rendering tab and click the Attributes node under the chart. In this tutorial, chart attributes are already open in the Property Editor.

3. Expand the Chart section and select Line for Type.

   

   Page Designer indicates what actions need to be performed to define required Column Mappings.

4. In the Rendering tab, select Tasks under the Series node. 

   

5. In the Property Editor, navigate to the the Column Mapping tab. Select VALUE for Value, select LINK for Custom Tooltip, and VALUE for Custom Column.

   

6. In the Label tab, select Yes for Show and click Save and Run Page.

   

7. You see a Line chart in place of the Bar chart in the application. Hover over a value to read the corresponding tooltip.

   

Using Dynamic Actions, you specify an action that is performed when a defined set of conditions occur. You also specify which elements are affected by the action, and when and how they are affected. In this section, you create a dynamic action to refresh the chart when it's value changes.

1. View the page in Page Designer. In this tutorial, the page 4 (Tasks per Project Chart) is opened in Page Designer.

   

2. In the left pane, Click the Dynamic Actions tab.

   

3. Right-click Events and select Create Dynamic Action.

   

   Attributes for the dynamic action are displayed in the Property Editor.

4. Edit the following Dynamic Action attributes in the Property Editor:

   • In the Identification tab, enter Refresh as the name of the dynamic action.
   • In the When tab, select Change for Event, select Region for Selection Type, and Tasks per Project Chart 2 for Region.

   

5. In the Rendering tab, select Show under True.

   

6. In the Property Editor, change the Action to Refresh. In the Affected Elements tab, select Region for Selection Type and Tasks per Project Chart 2 for Region. In the Execution Options tab, select Yes for Fire on Initialization and click Save.

   

7. In the main navigation, click the down arrow next to the SQL Workshop tab, right-click SQL Commands and select Open link in new tab.
   

   

8. Enter the following code in the code box and click Run.

   Insert into OBE_TASKS VALUES 
           ( 17
           , 'Employee Satisfaction Survey'
           , 'Complete analysis'
           , TO_DATE('30-JAN-2005', 'dd-MON-yyyy')
           , TO_DATE('03-FEB-2005', 'dd-MON-yyyy')
   	, 'open'
           , 4
           , 2
   	);

   

9. Switch back to the charts window. You will see the Refresh dynamic action that we created in the previous steps has updated the number of tasks for Employee Satisfaction Survey to 4 in the Tasks per Project Chart 2 chart.

   

In this section, you add a JavaScript to change the appearance of an existing chart.

1. View the page in Page Designer. In this tutorial, the page 4 (Tasks per Project Chart) is opened in Page Designer.

   

2. In the Rendering tree, select the Attributes node.

   

3. To view all attributes in the Property Editor, click Show All to see all attributes for the chart.

   

4. In the Advanced tab, enter the following code in the JavaScript code box.

   function( options ) {
       console.log( "chart initialization options", options );
   
   // Setup a callback function which gets called when data is retrieved, to manipulate the series data
       options.dataFilter = function( data ) {
           data.series[ 0 ].color = "#ED6647";
           data.series[ 0 ].borderColor = "#0F3248";
           data.series[ 0 ].markerDisplayed = "on";
           data.series[ 0 ].markerShape = "plus";
           data.series[ 0 ].markerColor = "red";
           data.series[ 0 ].markerSize = 8;
           data.series[ 0 ].pattern = "smallChecker";
           return data;
       };
       return options;
   }
   

   

5. Click Save and Run Page.

   

6. You see the JavaScript code has customized the appearance of the chart while rendering.

   

• Creating Charts