Using Interactive Grid Regions in Oracle Application Express 5.1

Before performing any manipulations to an Interactive Grid, you need to create or import an application containing an Interactive Grid.To install a packaged application containing an Interactive Grid in Application Express 5.1, 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 down arrow next to the Packaged Apps tab, and select Sample Apps.

   

3. From the list of sample applications, select Sample Database Application.

   

4. Click Install Packaged App to initiate the process of installing the sample application.

   

5. In the Install Packaged App window, accept the defaults and click Next>.

   

6. Click Install Packaged App. The application gets installed in your workspace.

   

7. Click the Run icon to run the application.

   

To perform search in an Interactive Grid, perform the following steps:

1. In the Sample Database Application home page, select Customers in the navigation menu.
   
   Note: In case, you are executing this tutorial in On-Premises, a Log In screen appears. Enter your Oracle Application Express credentials, and click Log In to view the application's home page. Then, select Customers in the navigation menu.

   

2. In the Sample Database Application, Customers page is the only Interactive Grid. Other pages, such as, Products and Orders are Interactive Reports. In the Customers page, enter la in the Search bar, and click Go.
   

   

3. The search is not case sensitive, so it finds 'la' in all text columns. In this case, the search returns three rows. It also displays the filter at the top of the grid.

   

4. To create another filter, enter william in the text area, and click Go.

   

5. Notice that the only one row is displayed in the grid, which means only one row met both the filter criteria.
   

   

6. You can remove a filter by clicking the Remove Filter icon shown beside it. Click the Remove Filter icon beside Search for 'la'.

   

7. Notice that only one filter is applied to the report now. Click the Remove Filter icon beside Search for 'william' to remove the other filter.
   

   

8. Both the filters are removed, and the grid displays all rows.

   

9. You can create a filter for just a column by using the select columns icon. Click the Select Columns icon and select Address.

   

10. Enter drive in the Search bar, and click Go.

    

11. Notice the filter is created on Address and returns two rows because there are two rows which contain 'drive' in its Address. Click the Remove Filter icon beside 'address contains drive'.

    

You can edit which columns to display and in what order you want to display them by selecting Columns in the Actions menu.

1. In the Customers page, click the Actions menu and select Columns.

   

2. In the Columns dialog, uncheck Tags, select the City column, click the Move up button, update the Minimum Column Width (Pixel) value to 200, and click Save.

   

3. You see that the Tags column does not appear in the grid, the City column is displayed next to the Customer Name column, and the column width is reduced to 200 pixels. Click the Reset button to revert back the changes.

   

You can filter a column in an interactive grid with a specified operator and value. Perform the following steps:

1. In the Customers page, click the Actions menu and select Filter.

   

2. In the Filters dialog, select Postal Code as the Column, select contains as the Operator, enter 6 as the value, and click Save.

   

3. All customer records containing '6' in their postal code is displayed in the grid.

   

4. Delete the filter by clicking the Remove Filter icon beside 'Postal Code contains 6'.

   

You can perform sorting (ascending or descending) of a column by selecting Sort on the Data submenu. You can also specify how to handle nulls (use the default setting, always display them first or last). Perform the following steps:

1. In the Customers page, click the Actions menu, select Data, and select Sort.

   

2. In the Sort dialog, select City for Column, accept the default selections for Direction and Nulls, and click Save. 

   

3. You see that the grid is now sorted by City. The sort indicator is located next to the City column to indicate the sort column. The arrow indicates whether it is in ascending or descending order.

   Note: To change the sort to descending order, you can click on the sort ascending icon in the header for City and it will change to sort descending.
   

   

You can highlight specific rows or cells based on a filter by selecting Highlight in the Actions, Format submenu. The rows or cells that meet the filter criteria are highlighted using the characteristics associated with the filter. Perform the following steps:

1. Click the Actions menu, select Format, and then select Highlight.

   

2. To highlight all the repeat customers, In the Highlight dialog box, perform the following steps:

   • Enter Repeat Customers for Name
   • Select Row for Highlight
   • Click Colors and choose Green for Background Color
   • Click Colors and choose Red for Text Color
   • Select Row for Condition Type
   • Enter repeat customer for Search
   • Click Save

   

3. You see all the repeat customers are highlighted in the grid. Click Remove Filter next to 'Repeat Customers' to remove the filter.

   

The other way to group specific rows using a filter is by creating a control break. You can break an interactive grid by column by selecting Control Break in the Actions, Format submenu. Perform the following steps:

1. Click the Actions menu, select Format, and then select Control Break.

   

2. To group all customers based on tags, select Tags under Column and click Save.

   

3. You see that the control break is created and the Tags column is extracted from the grid. In this case, the grid now shows a list of customers based on tags.
   

   

You can save your changes as a new report within the application. You can save a grid as a Public Report so that other authenticated users can see the report or you can save the grid as a Private Report that only you can view. If you log out, your changes will not be saved unless you have saved the grid. You can save multiple versions of a private or public reports and each one will appear in the list of reports. Perform the following steps:

1. Click the Actions menu, select Report, and then select Save As.

   

2. In the Report - Save As dialog box, make sure the Type is set to Private, enter My Report as the name of the report, and click Save.

   Note: In this step, you are creating a private report called My Report. If another user logs into the application, that user will not see your private report. You can also create a Public report that can be accessed by all users by selecting Public as the type of report.
   

   

3. A drop down list automatically appears with the report you just created being selected.

   

You can obtain the data of an interactive grid as a CSV or HTML file by selecting Download in the Actions menu. Perform the following steps:

1. To download the private report that you created in the previous section, click the Actions menu and select Download.

   

2. In the Download dialog box, select the format as CSV and click Download. Notice that CSV is the default format to download a report. If you do not select a format and click Download, the report by default will be downloaded in CSV format.

   

3. The downloaded report in Microsoft Excel looks like this.

   

   Note: If you want to download the report in HTML format, in the Download dialog box, select the format as HTML and then click Download.

Apart from downloading, you can also email a report to one or more recipients. Perform the following steps:

1. Click the Actions menu and select Download.

   

2. To email the file, select the check box Send as Email, complete the fields for the recipients such as, Address, Subject, and Message, and click Send as Email.

   

You can revert an interactive grid to a specific point in time by selecting the Flashback option in the Interactive Grid. Perform the following steps:

1. To revert your interactive grid by 10 minutes, click the Actions menu, select Data, and select Flashback.

   

2. In the Flashback dialog box, enter 10 for minutes ago and click Save.

   

   The grid reloads to the state it was in 10 minutes ago.

   Note: The screenshot for this step is not included as the Flashback option reloads your grid to a state that it was in 10 minutes ago.

3. Under the list of reports, select Primary Report to navigate back to your primary grid.

   

You can resize a column by clicking and holding the edge of a column heading and adjusting it with the mouse.

1. Place your mouse at the edge of the Address column and adjust the mouse to reduce the column size.

   

2. The resized column looks like this.

   

You can rearrange a column by selecting the column you want to move, placing your mouse pointer at the start of the column header, and draging the column to the desired location.

1. Place your mouse pointer at the start of the Postal Code column. Click and drag the column after Customer Name and drop it.

   

2. The rearranged columns look like this.

   

You can sort a column in ascending or descending order by hovering the mouse in the column heading you want to sort on and selecting the Sort Ascending or Sort Descending icon.

1. Hover your mouse on the Customer Name column and select the Sort Ascending icon.

   

2. The grid is sorted on the Customer Name column.

   

You can create a control break by clicking the column header and selecting the Control Break icon. When control break is created, the column becomes a master record for the grid.

1. Select the Tags Header, and select the Control Break icon.

   

2. The grid has a control break created on the Tags column. Click the Remove Control Break icon to remove the break.
   

   

You can hide a particular column by selecting the Hide Column icon from the column header.

1. Select the City Header, and select the Hide Column icon .

   

2. Notice that the City column no longer appears in the grid.

   Note: To view the column in the grid again, use the Columns option from the Actions menu.

   

You can select the column header link and then click the Snowflake icon to freeze and unfreeze a column. Freezing a column excludes it from the horizontal scrollable area and unfreezing a column includes it in the horizontal scrollable area.

1. In your Sample Database Application, the Freeze option is already selected for the Customer Name column. Scroll the page to your right, you see that the Customer Name column is excluded from the scrollable area.

   

2. Select the Customer Name column heading and click Unfreeze.

   
   

   

3. The scroll bar resizes to fit the Customer Name column to its scrollable area.

   

You can filter an interactive grid by the contents of a column by entering keywords in the Search field of the Column Heading menu.

1. To filter the grid by the Address column, click the Address column header, Click within the Filter... text area and Enter Drive. Press Enter to execute the filter.
   

   

2. The interactive grid redisplays with the filter applied.