Building and Customizing an Interactive Grid in Oracle Application Express 5.1

Purpose

This tutorial shows you how to build, use, and customize an Interactive Grid in Oracle Application Express 5.1.

Time to Complete

Approximately 40 minutes.

Overview

Oracle Application Express (Oracle APEX) 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 an Oracle Database Cloud Service.

In this tutorial, you use Oracle Application Express Release 5.1 to create, use, and customize an Interactive Grid. 

What Do You Need?

Before starting this tutorial, you should:

• 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.
• Download and unzip the files.zip into your working directory.
• Execute Create_Tables.sql from the extracted files to create the required database objects.

• 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:

  • Create_Tables.sql

• Execute Create_Tables.sql from the files folder to create the required database objects.

In this section, you create an application with an Interactive Grid.

Application Express includes Interactive Grids that are editable and read-only. In the create application wizard, you have three different options to include an Interactive Grid:

• Select Report and then select Interactive Grid. This creates a read-only interactive grid. You can later convert this in to an editable interactive grid.
• Select Editable Interactive Grid and follow the on-screen instructions. This creates an Editable Interactive Grid.
• Select Report and Form and then for the Report Type, select Interactive Grid. This creates a read-only interactive grid. You can later convert this to an editable interactive grid.

In this OBE, you log in to your Oracle Application Express Workspace, either on-premises or in Oracle Database Cloud Service, and then create an application containing an Interactive Grid. In the Create Application wizard, you select Report, and then select Interactive Grid. Perform the following steps:

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 App Builder icon.
   

   

3. Click Create.

   

4. Click the Desktop icon.

   

5. Enter Interactive Grids OBE for Name, and click Next >.

   

6. By default, the Home page gets created. To add another page, click Add Page.

   

7. Select Report for Page Type. Select Home(1) for Parent Page, Table for Page Source, EMPLOYEES for Table Name, Interactive Grid for Report Type, and click the Add Page button.

   

8. Click Next >.

   

9. Accept the default, and click Next >.

   

10. You want to specify the type of authentication used to access this application. Select Application Express Accounts for Authentication Scheme, select DD-MON-YYYY as  Date Format for the entire application, and click Next >.

    

11. Click Create Application to create a new application containing Interactive Grid.

    

12. Click Run Application to see this application in action.

    

13. The Log In screen appears. Enter your Oracle Application Express credentials and click Log In.

    

14. The Home page of your application is displayed. Select Employees in the navigation menu.The Interactive Grid containing employee records is displayed. Notice that the date format is set according to the date you specified when you created the application.

    In the next section, you examine some of the Interactive Grid features.

    

Now that the application containing an Interactive Grid is created, you can manipulate the grid in many ways. In this section, only some of them are discussed. Perform the following steps:

1. You can search for values in the Interactive Grid. Enter SA_REP in the search bar, and click Go.

   

2. Notice that only rows containing employees with Job Id 'SA_REP' is listed on screen. A filter was also created above the Interactive Grid data.
   

   

3. To resize a column, you can click and hold the edge of a column heading and adjust it with the mouse. In this case, place your mouse at the edge of the Phone Number column and adjust the mouse to display the complete column heading.
   

   

4. To hide a column, you can select the column header and then click the Hide icon. In this case, click the Phone Number header, and select the Hide icon.

   

5. Notice that the Phone Number column is no longer displayed in the grid.
   Note: To view the column in the grid again, use the Columns option from the Actions menu.

   

6. To freeze a column, you can select the column header link and click the Freeze icon. Freezing a column excludes it from the scrollable area. Select the First Name column heading and click Freeze.

   

7. Scroll the page to your right, you see that the First Name column is excluded from the scrollable area. Scroll back.
   

   

8. To sort on a column, hover the mouse in the column heading you want to sort on and select the Sort Ascending or Sort Descending icon. Select the First Name column and select the Sort Ascending icon.

   

9. The grid is now sorted on First Name.
   

   

10. You can remove a filter by selecting the Remove Filter icon next the to filter or deselect a filter by unchecking the check box next to the filter.
    In this case, click the Remove Filter icon next to the filter.

    

11. To create a control break on a column, click the Job Id header, and select the Control Break icon.
    Alternatively, from the Actions menu, select Format, and then select Control Break.

    

12. The control break is created. Notice the Job ID column is eliminated from the grid and now appears before each break point in the grid.

    

13. To total the Salary, click the Salary header, and select the Aggregate icon. In the pop-up dialogue box, ensure that Salary is selected for Column, select Sum for Aggregation, and click Save.

    

14. The grid displays total salary for each Job Id.

    

15. To reorder columns, select the column you want to move, place your mouse pointer at the start of the column header, and drag the column to the desired location. Move the Manager Id column after Email.

    

16. You see the Manager Id column is moved next to Email. Click the Reset button to revert all the changes you made to the grid.
    

    

17. You see all the changes made to the grid is rolled back.

    

18. To create a Pie chart from the data in your Interactive Grid, select Chart from the Actions menu.
    

    

19. In the Chart dialogue, select Pie as the chart type, Job Id for Label, Salary for Value, and Sum for Aggregation. Click Save.

    

20. A pie chart displaying the proportion of the total salary divided by job ID is displayed for you. You can edit the chart by clicking the Edit Chart option above the chart.
    

    

21. You can save all the customizations you made to a saved report. Click the Actions menu, select Report, and click Save As.

    

22. Enter the name of the report as My Report and click Save.

    

23. You just created a private report called My Report. Notice that you now see a drop down list of the reports you have created. This customized view will be available each time you log in. Click the drop down list and select Primary Report go back to the primary grid.
    

    

As a developer you can change the way the Interactive Grid is rendered for all users. Perform the following steps:

1. To make an Interactive Grid editable, Select Edit Page 2 in the Developer toolbar.

   

2. In the Rendering tab, under Employees, click Attributes.

   

3. In the Property Editor, under Edit, choose Yes for Enabled and No for Add Row If Empty. Click Save and Run Page.

   Note: The Add Row If Empty attribute allows you to specify whether to add a new row if the interactive grid currently has no records returned. If enabled, the No Data Found message never displays since there will always be at least one record.

   

4. Notice that a new Selection Actions column appears before the Employee ID. This column allows you to perform row level actions. Click any row. You see that the Interactive Grid can now be edited directly. Scroll down to the bottom of the Interactive Grid. Notice that the total number of rows is displayed. Select Edit Page 2 in the Developer toolbar.

   

5. To add a page controller to your Interactive Grid, in the Property Editor, scroll down to the Pagination section, select Page for Type and select Yes for Show Total Row Count. Click Save and Run Page.

   

6. Scroll to the bottom of the page. Notice that only 22 rows out of 107 rows are displayed in the first page. In order to go to another page, you can select the page number or the Next icon. Select Edit Page 2 in the Developer toolbar.

   

7. in the Property Editor, in the Pagination section, select Scroll for Type and select No for Show Total Row Count. Click Save and Run Page.

   

8. Progressively scroll to the end of the page.

   

   You notice that the row count increases as you scroll down the page.

   

9. Apart from controlling pagination, you can edit the Attributes property to add or remove different controls on the Interactive Grid toolbar. Select Edit Page 2 in the Developer toolbar. In the Property Editor, scroll down to the Enable Users To section. Select Yes for Save Public Report. In the Download section, uncheck HTML in Download Formats and click Save and Run Page.
   

   

10. From the Actions menu, select Download.

    

11. You see that the user can download the primary report only in the CSV format as you have unchecked the HTML format.

    Note: The user can also email the primary report to the desired recipient by checking the Send as Email option. Click Cancel.

    

As an end user, you can directly update a grid’s structure and contents. You can add and delete rows, edit cell contents, and refresh the grid with the latest updates. Perform the following steps:

1. To update an employee's information directly, you simply click the desired row, update the value, and click Save. Or, you click on the row header, select Single Row View, click Edit to update the employee information in the form, and click Save. In this case, click the row containing Commission Pct value for Steven, update the value to .4 and click Save.

   

2. To update an employee's information using the row header, click the row header of employee Steven and select Single Row View. 

   

3. Select Edit to edit the employee Information. Update Phone Number to 515.321.7634 and click Save.

   

4. Click Report View to go back to the Interactive Grid.

   

5. You see that the information is saved in the Interactive Grid.
   

   Note: You can also add a new row into the table by clicking the Add Row button.
   

   

6. To delete a row, click the row header of the row you want to delete and select Delete Row. In this case, you delete the row with Employee Id 102.

   

7. You see that the row with Employee Id 102 is deleted from the Grid. You can click the Save button to make the changes permanent or click the row header and select Revert Changes to undo changes. In this case, you click on the row header of employee ID 102 and select Revert Changes.

   Note: You can also duplicate a row by clicking the row header of the desired row and selecting Duplicate Row.
   

   

8. To edit multiple rows at the same time, select the desired rows and click Selection Actions. In this case, select rows with Employee ID 100,101,102,103, and 104. Click Selection Actions. The menu allows you to duplicate rows, delete rows, refresh rows to display the updated information, and revert changes (if any). Select Delete Rows to delete the selected rows.

   

9. You see the rows with Employee ID 100,101,102,103, and 104 are deleted from the grid. You can click the Save button to make the changes permanent or click Selection Actions and select Revert Changes to undo changes. In this case, you click on the Selection Actions and select Revert Changes.

   

10. You see all changes made to the grid since it was last saved is reverted.

    

• Using an Interactive Grid