Faceted Search with Kanban Board - #JoelKallmanDay
Recently I published a short Tweet regarding a nice feature in the Faceted Search. It was about Multi Value Facets, which enables you to have a colon separated list of values in a report column and use a filter on the atomic values of the colon separated list.
The short video I published, showed the Material-Kanban-Board by Matthias Range combined with the Faceted Search. This was just because the functionality where we took advantage of the Multi Value Facet, is an implementation using the Kanban Board. Hence it was the easiest way for me to show.
But then I got questions about how we got the Kanban Board working with Faceted Search, since the Faceted Search does not support it directly.
I decided to write this blogpost to give a better insight and explain, how easy it is to implement Faceted Search with different reports, like we did in this example with the Kanban Board.
Consideration
As the faceted search is a fantastic feature in APEX, you may want to use it with report types different from Classic Report or even with region plugins, such as the Material-Kanban-Board.
And you may ask for a combination of Faceted Search and Interactive Report / Interactive Grid. Well, this will be possible with the proposed solution.
But let's spend a second on this idea. What would it mean to your application?
Think about a complex or simple report of your choice. Let it be an Interactive Report, which will of course come with a bunch of functionalities by default, especially filters. Then imagine filter region with a couple of facets aside. Wouldn't that be confusing? Why do we need two different filter implementations for one single report? And think about the users. What would they expect from your application if you provide two different filter styles on a single report? They will get confused as well. And the only thing I can imagine is, that there will be a lot of discussion about the behaviour between you and your customer and you will probably have side effects. As users expect clear and understandable behaviour from the created software, we should avoid confusing constructs. In my opinion the combination of Faceted Search and Interactive Report / Grid has the potential for very much confusion. This is probably the reason, why the Oracle APEX Dev Team decided to not implement Faceted Search Support for IR/IG.
Anyway, the proposed solution will enable you to implement the combination of both very easy. But honestly, I won't recommend!
The approach uses the function APEX_REGION.OPEN_QUERY_CONTEXT to access the query result of a Faceted Search in order to affect the query of any report of your choice. This topic has also been shown by Carsten Czarski in this article where he explained how to create a chart based on a Faceted Search result.
We used the same idea with the Material-Kanban-Board plugin.
But instead of getting all the columns of the according result set, we can be a bit more generic here. We do not need to access all the columns. We just need to get the primary key values (IDs). Once we have all the PK IDs of the Faceted Search result, we can easily modify the WHERE condition in the SQL data source of the Kanban-Board region.
Used components
- Classic Report with the core data, we want to use for the Kanban Board and for the filters
- Faceted Search region, connected to the Classic Report above
- Material-Kanban-Board region with WHERE condition using the result of the Classic Report, provided by PL/SQL function below
- PL/SQL package/function implementing access to the data of the Classic Report
- Dynamic Action to refresh the Kanban Board on facet changes
Prepare table and data
In this example I will use a simple table TODOS. I used Quick SQL to create the table and generate some demo data. If you do not know Quick SQL yet, this is the time to check it out. It is absolutely worth it, as it gives you great support for rapid prototyping.
Quick SQL generates the table DDL for you and provides demo data which will enable you to start very quick with the APEX application.
Let’s now start with the implementation of the well-known part, the Classic Report with Faceted Search.
Classic Report
Create a Classic Report on the table/view/query containing the data you want to use. In our sample we have a table named TODOS with some columns we want to use in the facets as well as in the Kanban cards.
Make sure to have the primary key column (here ID) in your report.
It is also important to assign a Static ID (here FACET_TODOS) to the Classic Report. The Static ID is used to get access to the data of the report via APEX_REGION.OPEN_QUERY_CONTEXT.
As this report is just necessary to access the result via PL/SQL, we do not want to see it in the application. Thus set Server-side Condition to Never.
Faceted Search
Next create a Faceted Search region and set the filtered region to the Classic Report region (here Facet Search Result).
Add a couple of facets to the filter region and map them to the columns of the Classic Report using the source attributes.
At this point there is nothing really new. We have just created a simple report with filters. Let’s now create the PL/SQL code to access the data result of the report region.
PL/SQL Package
As mentioned, we will use the function APEX_REGION.OPEN_QUERY_CONTEXT, which has the following signature:
[CODE]
FUNCTION APEX_REGION.OPEN_QUERY_CONTEXT (
p_page_id IN NUMBER,
p_region_id IN NUMBER,
p_component_id IN NUMBER DEFAULT NULL,
p_view_mode IN VARCHAR2 DEFAULT NULL,
--
p_additional_filters IN apex_exec.t_filters DEFAULT apex_exec.c_empty_filters,
p_outer_sql IN VARCHAR2 DEFAULT NULL,
--
p_first_row IN NUMBER DEFAULT NULL,
p_max_rows IN NUMBER DEFAULT NULL,
p_total_row_count IN BOOLEAN DEFAULT FALSE,
p_total_row_count_limit IN NUMBER DEFAULT NULL,
--
p_parent_column_values IN apex_exec.t_parameters DEFAULT apex_exec.c_empty_parameters )
RETURN apex_exec.t_context;
[/CODE]The function has only two mandatory parameters. We won't need more for what we are going to achieve.
We need to pass the PAGE_ID and the REGION_ID of the report we want to access. The data is returned as APEX_EXEC.T_CONTEXT.
With APEX_EXEC.NEXT_ROW we can loop through the result and get the values using APEX_EXEC.GET_NUMBER.
This is the implementation we need to get access to the data of the Classic Report. Create this package in your database schema.
[CODE]
create or replace package facet_util
is
type t_id_row is record (id NUMBER);
type t_id_table is table of t_id_row;
function faceted_search_result_ids (
in_app_id in number,
in_page_id in number,
in_region_static_id in varchar2,
in_id_column_name in varchar2
)
return t_id_table pipelined;
end facet_util;
/
create or replace package body facet_util
is
function faceted_search_result_ids (
in_app_id in number,
in_page_id in number,
in_region_static_id in varchar2,
in_id_column_name in varchar2
)
return t_id_table pipelined
is
l_context apex_exec.t_context;
l_region_id number;
l_column_index number;
begin
-- get region_id for given region_static_id
select region_id
into l_region_id
from apex_application_page_regions
where application_id = in_app_id
and page_id = in_page_id
and static_id = in_region_static_id;
-- open query / access result
l_context := apex_region.open_query_context (
p_page_id => in_page_id,
p_region_id => l_region_id);
-- find position of id column
l_column_index :=
apex_exec.get_column_position(
p_context => l_context,
p_column_name => in_id_column_name );
-- write data row by row to the pipe
while apex_exec.next_row( p_context => l_context ) loop
pipe row( t_id_row(
apex_exec.get_number( p_context => l_context, p_column_idx => l_column_index ) )
);
end loop;
apex_exec.close( l_context );
return;
exception
when no_data_needed then
apex_exec.close( l_context );
return;
when others then
apex_exec.close( l_context );
raise;
end faceted_search_result_ids;
end facet_util;
/
[/CODE]The pipelined table function faceted_search_result_ids expects four parameters:
- The APP_ID
- The PAGE_ID
- The STATIC_REGION_ID of the report we want to access (this is the Static ID, we assigned to the Classic Report before).
- The ID_COLUMN_NAME, which is the name of the ID column in the report. This is the column we are interested in, because we need all IDs of the data rows in the filtered result.
The function returns the list of IDs as a pipelined table, which can be easily used in the WHERE condition of the Kanban Board region.
Kanban Board Region
Now that we have the interface to access the data, we can create the Kanban Board using the Material-Kanban-Board plugin.
I will not explain how to handle the plugin in this article. It is well documented and very easy to use. If you have successful installed the plugin, you can just add the Material-Kanban-Board region to your page. There will be a sample SQL generating some sample rows which results into some sample cards in the Kanban Board. The sample SQL will help you to map your own data to the output.
For the source SQL query add something like that:
As you can see here, it is almost the same source as in the Classic Report. But the important point is to call the pipelined table function, we just created. Pass the APP_ID, PAGE_ID, Static ID of the Classic Report (here FACET_TODOS) and die name of the ID column (here ID). And then join it to the table TODOS. You can also use it in the WHERE condition with a subselect.
We are almost done. The Kanban Board is connected to the Faceted Search now. Last step we have to manage is to refresh the board when the facets change.
Dynamic Action
Add a dynamic action to the page with the following attributes:
Finally add the facets to the Page Items to Submit attribute of the Kanban Board region.
That’s it, you are all set!
While working on this article I came across some other interesting blogposts. One of them I'd like to highlight is this article, written by Steve Muench.
Steve describes how he solved a requirement about how to filter multiple report regions with just one filter region. His example deals with the Smart Filter. But in fact, it is pretty much the same, as I proposed in this article. Steve also uses APEX_REGION.OPEN_QUERY_CONTEXT to get the primary key values of the filtered rows of a hidden Classic Report.
Note
As Patrick Wolf remarked in his reply to my tweet, there is another solution at least with APEX 22.1.
Since APEX 22.1 it is possible to directly implement Faceted Search Support in the plugin. But this is another story we can point out in another article.
Prerequisites
Since APEX_REGION.OPEN_QUERY_CONTEXT was introduced with Oracle APEX Release 19.2, the proposed solution probably works with APEX 19.2 or later. Not sure about when the Event “Facets Change”, used in the Dynamic Action, was established. We are running the above solution with APEX 21.2 and it works fine. You can also use the custom event “facetschange”.
References
https://github.com/McRange/Material-Kanban-Board
https://blogs.oracle.com/apex/post/add-a-chart-to-your-faceted-search-page
https://diveintoapex.com/2022/06/05/smartfiltering-multiple-regions/
Kommentare
R.I.P. Joel
To the triology.de admin, Thanks for the informative post!