Technical Solutions For Single Search box Filter the Value with highlights in Multiple Reports

Overview

This document explains how to search single search box in Multiple reports with highlights using Oracle Apex

Technologies and Tools Used

The following technologies has been used to achieve the search single search box in Multiple reports with highlights using Oracle Apex

Oracle Apex
SQL

Use Case

Any requirement for customized search box with highlights, we can use this following steps. By default, create text box and more than one reports .After that Based on text box value filtered in multiple reports with highlights text.

Steps with Screenshot

Steps to be followed

Step 1: Create a region in Content Body

Below Properties:

Identification

Name : Search Region

Type : Static Content

Step 2: Then, Create a Page Item in Search Region

Below Properties:

Identification

Name : P9_SEARCH

Type : text Field

Then create a button in Search Region

Step 3: Then, create a Region For Report,

Identification

Name : Report 1

Type : Classic Report

Source

Type : SQL Query

SQL Query :

******************************************************************************

select regexp_replace(FIRST_NAME, REPLACE(NVL( :P9_SEARCH ,’%$#$^%$%’),’ ‘, ‘|’) ,'’||:P9_SEARCH||'’,1,0,’i’) FIRST_NAME,

regexp_replace(SECOND_NAME, REPLACE(NVL( :P9_SEARCH ,’%$#$^%$%’),’ ‘, ‘|’) ,'’||:P9_SEARCH||'’,1,0,’i’) SECOND_NAME

FROM TEST_DATA WHERE UPPER(FIRST_NAME) LIKE UPPER(‘%’||:P9_SEARCH||’%’) OR UPPER(SECOND_NAME) LIKE UPPER(‘%’||:P9_SEARCH||’%’)

***********************************************************************

Security

Escape Special Characters : off

Step 4: Then, Create a another Region for Report 2

Below Properties:

Identification

Name : Report 2

Type : Classic Report

Source

Type : SQL Query

SQL Query :

******************************************************************************

select regexp_replace(FIRST_NAME, REPLACE(NVL( :P9_SEARCH ,’%$#$^%$%’),’ ‘, ‘|’) ,'’||:P9_SEARCH||'’,1,0,’i’) FIRST_NAME,

regexp_replace(SECOND_NAME, REPLACE(NVL( :P9_SEARCH ,’%$#$^%$%’),’ ‘, ‘|’) ,'’||:P9_SEARCH||'’,1,0,’i’) SECOND_NAME

FROM TEST_DATA WHERE UPPER(FIRST_NAME) LIKE UPPER(‘%’||:P9_SEARCH||’%’) OR UPPER(SECOND_NAME) LIKE UPPER(‘%’||:P9_SEARCH||’%’)

***********************************************************************

Security

Escape Special Characters : off

Step 6: Finally, Save and Run the page.

Output:

Below screen shot shows the difference in the style of the Regular Check box and Customized Check box.

Abdul Rahman