Oracle Apex 19.2: Create Faceted Search Region (New Feature)

In this tutorial, you will learn how to create the Faceted Search region in Oracle Apex 19.2, which is a new feature of Oracle Apex in version 19.2. The faceted search gives the ability to filter a report based on (Classic Report) without changing the query. I didn't test yet on other report types.

Steps to Create a Faceted Search Region for a Classic Report in Oracle Apex

To demonstrate this example, I have created a classic report region based on the following EMPLOYEES table:

CREATE TABLE  "EMPLOYEES" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25), 
	"EMAIL" VARCHAR2(25), 
	"PHONE_NUMBER" VARCHAR2(20), 
	"HIRE_DATE" DATE, 
	"JOB_ID" VARCHAR2(10), 
	"SALARY" NUMBER(8,2), 
	"COMMISSION_PCT" NUMBER(2,2), 
	"MANAGER_ID" NUMBER(6,0), 
	"DEPARTMENT_ID" NUMBER(4,0)
   )
/

To test this example, you can create this table in your schema and then insert the data as follows:

INSERT INTO employees VALUES 
   ( 110
   , 'John'
   , 'Chen'
   , 'JCHEN'
   , '515.124.4269'
   , TO_DATE('28-SEP-1997', 'dd-MON-yyyy')
   , 'FI_ACCOUNT'
   , 8200
   , NULL
   , 108
   , 100
   );
INSERT INTO employees VALUES 
   ( 111
   , 'Ismael'
   , 'Sciarra'
   , 'ISCIARRA'
   , '515.124.4369'
   , TO_DATE('30-SEP-1997', 'dd-MON-yyyy')
   , 'FI_ACCOUNT'
   , 7700
   , NULL
   , 108
   , 100
   );
INSERT INTO employees VALUES 
   ( 112
   , 'Jose Manuel'
   , 'Urman'
   , 'JMURMAN'
   , '515.124.4469'
   , TO_DATE('07-MAR-1998', 'dd-MON-yyyy')
   , 'FI_ACCOUNT'
   , 7800
   , NULL
   , 108
   , 100
   );
INSERT INTO employees VALUES 
   ( 113
   , 'Luis'
   , 'Popp'
   , 'LPOPP'
   , '515.124.4567'
   , TO_DATE('07-DEC-1999', 'dd-MON-yyyy')
   , 'FI_ACCOUNT'
   , 6900
   , NULL
   , 108
   , 100
   );
INSERT INTO employees VALUES 
   ( 114
   , 'Den'
   , 'Raphaely'
   , 'DRAPHEAL'
   , '515.127.4561'
   , TO_DATE('07-DEC-1994', 'dd-MON-yyyy')
   , 'PU_MAN'
   , 11000
   , NULL
   , 100
   , 30
   );
INSERT INTO employees VALUES 
   ( 115
   , 'Alexander'
   , 'Khoo'
   , 'AKHOO'
   , '515.127.4562'
   , TO_DATE('18-MAY-1995', 'dd-MON-yyyy')
   , 'PU_CLERK'
   , 3100
   , NULL
   , 114
   , 30
   );
INSERT INTO employees VALUES 
   ( 116
   , 'Shelli'
   , 'Baida'
   , 'SBAIDA'
   , '515.127.4563'
   , TO_DATE('24-DEC-1997', 'dd-MON-yyyy')
   , 'PU_CLERK'
   , 2900
   , NULL
   , 114
   , 30
   );
INSERT INTO employees VALUES 
   ( 117
   , 'Sigal'
   , 'Tobias'
   , 'STOBIAS'
   , '515.127.4564'
   , TO_DATE('24-JUL-1997', 'dd-MON-yyyy')
   , 'PU_CLERK'
   , 2800
   , NULL
   , 114
   , 30
   );
INSERT INTO employees VALUES 
   ( 118
   , 'Guy'
   , 'Himuro'
   , 'GHIMURO'
   , '515.127.4565'
   , TO_DATE('15-NOV-1998', 'dd-MON-yyyy')
   , 'PU_CLERK'
   , 2600
   , NULL
   , 114
   , 30
   );
INSERT INTO employees VALUES 
   ( 119
   , 'Karen'
   , 'Colmenares'
   , 'KCOLMENA'
   , '515.127.4566'
   , TO_DATE('10-AUG-1999', 'dd-MON-yyyy')
   , 'PU_CLERK'
   , 2500
   , NULL
   , 114
   , 30
   );
INSERT INTO employees VALUES 
   ( 120
   , 'Matthew'
   , 'Weiss'
   , 'MWEISS'
   , '650.123.1234'
   , TO_DATE('18-JUL-1996', 'dd-MON-yyyy')
   , 'ST_MAN'
   , 8000
   , NULL
   , 100
   , 50
   );
INSERT INTO employees VALUES 
   ( 121
   , 'Adam'
   , 'Fripp'
   , 'AFRIPP'
   , '650.123.2234'
   , TO_DATE('10-APR-1997', 'dd-MON-yyyy')
   , 'ST_MAN'
   , 8200
   , NULL
   , 100
   , 50
   );
Commit;

1. Create a Page in Oracle Apex

Create a page in Oracle Apex and set the following properties:

• Page Mode: Normal
• Page Template: Left Side Column

2. Create a Classic Report Region

Create a Classic Report region based on the following query:

select
       EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL,
       PHONE_NUMBER,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       COMMISSION_PCT,
       MANAGER_ID,
       DEPARTMENT_ID
  from EMPLOYEES

3. Create a Faceted Search Region

To create a Faceted Search region, do the right-click on the Region and select Create Region option and set the following properties:

• Title: Filter
• Type: Faceted Search
• Filtered Region: Report 1 (This is the classic report region on my page)
• Position: Left Column

Below is the screenshot of the above setting for your reference:

4. Create Facets in the Faceted Search Region

Now create facets to filter the report. To create a facet do the right-click on the Facets then select Create Facet option and set the following properties:

• Type: Checkbox
• Label: Job ID
• List of Values Type: Distinct Values
• Database Column: JOB_ID

See the below image for reference:

5. Create Search Facet

We will create one more facet for the Faceted search region. Do the right-click on the Facets and select Create Facet option and set the following properties:

• Type: Search
• Label: Search
• Search Type: Row Search

See the below image for the above settings:

Save the changes and run the page. You will have the output as shown in the below image:

Now when you will click on any Job ID checkbox at the filter region, it will filter the report for the selected Job ID and if you will enter some value in the search box and hit enter it will filter the report for that value.

You can experiment more in faceted search as there are many more options.

Related Tutorials:

• Oracle Apex 19.2: Create Star Rating Page Item (New Feature)
• Oracle Apex 19.1 – Create Switch Example