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 EMPLOYEES3. 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.
