Creating a Dashboard in APEX using parameterized view (SQL Macro)

Starting with Oracle Database release 19c, version 19.7, you can use SQL table macros. SQL table macros are expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) view.

Let's try with a simple APEX Dashboard App.

I will use the "Tasks Spreadsheet" sample dataset present in APEX Utilities

The dataset is a table with the following fields:

Let's create a Standard App with the dataset

And Let's go to the Dashboard Page

I'd like to add some filters:

• Project
• Status
• Assigned To
• Date Start1, Date Start2
• Date End1, Date End2

The filters still don't work, I have to modify the query in each region chart:

For Istance I have to change this

With this

And add Page Items to Submit

Now we need a Dynamic Action to refresh Charts when Filters change

And we've done! But ...

We have 4 ugly query inside the Charts

select t.id
       t.project,
       t.task_name,
       t.start_date,
       t.end_date,
       t.status,
       t.assigned_to,
       t.cost,
       t.budget
  from eba_tasks_ss t
 where nvl(:p2_status,t.status)          ||':' like '%'|| t.status     ||':%' 
   and nvl(:p2_assigned_to,t.assigned_to)||':' like '%'|| t.assigned_to||':%' 
   and nvl(:p2_project,t.project)        ||':' like '%'|| t.project    ||':%' 
   and trunc(t.start_date) between nvl(:p2_start_date1,trunc(t.start_date)) and nvl(:p2_start_date2,trunc(t.start_date))
   and trunc(t.end_date)   between nvl(:p2_end_date1  ,trunc(t.end_date  )) and nvl(:p2_end_date2,  trunc(t.end_date  ))
        

We can't create a view!

We could create a pipelined table function but NOW we can have a MACRO!

create or replace package eba_tasks_pkg
is 
    function eba_tasks_ss(p_status      varchar2 default null,
                          p_assigned_to varchar2 default null,
                          p_project     varchar2 default null,
                          p_start_date1 date     default null,
                          p_start_date2 date     default null,
                          p_end_date1   date     default null,
                          p_end_date2   date     default null
                          ) return varchar2 sql_macro ;
end eba_tasks_pkg;         

So now we can change the query in the Charts into: