Oracle Apex: Creating Incremental Search for Reports

Oracle Apex: Creating Incremental Search for Reports

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

In this tutorial, I will show you how to create an Incremental Search option for reports in Oracle Apex.

Incremental search means, it starts searching as you type in the text input field. To create this feature in Oracle Apex, we will create a text field item and an interactive report. You can also use the classic report as well.

Steps to Create Incremental Search for Reports in Oracle Apex

Create a new page in Oracle Apex or use any of your existing page in which you want to create an incremental search option.

Then create a static region on it and create a page item on this region. Label this text field as Search or provide a placeholder value as "Type to start search...".

Create another region as an interactive report and specify a query for your tables. To demonstrate this example, I am using the Employees table and will filter the results on column FIRST_NAME, as the user will start typing in the input field we created above.

Now specify a static ID to this interactive report region. I specified the id as p14_emps.

Then I specified the WHERE clause for the interactive report to filter out the result. The following is the WHERE clause example:

upper(substr(first_name,1, nvl(length(:p14_search), length(first_name)))) = upper(nvl(:p14_search, first_name))

The item P14_SEARCH is the text field item we created in the static region above. By looking at the where clause above you can have the idea of what I am doing. You can make it more efficient for a single field and for multiple fields as per your needs.

Now click on the page name to show its properties and paste the following jQuery code in the JavaScript > Execute when Page Load section:

$('#P14_SEARCH').keyup(
function(evt) {
$('#p14_emps').trigger('apexrefresh');
}
);

The above jQuery code will add an event KEYUP to the text field P14_SEARCH and will refresh the interactive report region named p14_emps.

The following is the screenshot of the above settings:

Oracle Apex - Incremental search setting.

You are all set, now save the changes and run the report. Then start typing in the search field and your report result will start filtering out.

Incremental Search example output - Oracle Apex.

Related Tutorials:

This Post Has 3 Comments

  1. salim October 5, 2020

    Hi. your blog is amazing thank you a lot just a question i do similaire code and its work only when i press enter , it supposed work automatically cz we use KEYUP . can you help me thank you sir

  2. Jonas February 10, 2021

    You a legend

  3. steffen March 25, 2021

    Hi, but how can i search with wildcard ?
    select name
    from db where name like 'ti%'
    it works but how works that with a page item ?
    like this: select name
    from db where name like :P1_SEARCH
    so the variable is in the pageitem but how can i add the wildcard

Comments are closed.