Enhancing Flexibility: Creating Highly Dynamic Pivot Reports in Oracle APEX
Oracle APEX Version 23.2.4
The Interactive Report in Oracle APEX already offers us a useful pivoting feature. Unfortunately, this is linked to a few restrictions. For example, it is not possible to define more than three pivot and row columns or to customize the column headers. So an alternative to the option in the UI is to pivot directly via the calling query. In this case, however, we must ensure that pivot columns that are subject to frequent data changes are always mapped correctly. And we also have to render the report dynamically. But is this even possible without having to write XML code? Of course it is!
A big thank you to Florian Grasshoff from the Oracle team, who provided me with a crucial information on this topic.
Prepare table and data
Let's start by establishing the data content.
create sequence seq_warehouses start with 1 increment by 1;
create sequence seq_items start with 1 increment by 1;
create sequence seq_items2warehouses start with 1 increment by 1;
/
create table t_warehouses (
id number default seq_warehouses.nextval,
warehouse_name varchar2(100 char) not null,
warehouse_location varchar2(500 char) not null,
constraint pk_warehouses primary key ( id ),
constraint uq_warehouses unique ( warehouse_name, warehouse_location )
);
/
create table t_items (
id number default seq_items.nextval,
item_label varchar2(100 char) not null,
item_description varchar2(500 char),
price number,
manufacturer varchar2(100 char),
date_added date default sysdate,
constraint pk_items primary key ( id ),
constraint uq_items unique ( item_label )
);
/
create table t_items2warehouses (
id number default seq_items2warehouses.nextval,
whs_id number not null,
item_id number not null,
quantity number not null,
constraint pk_items2warehouses primary key ( id ),
constraint fk_items2warehouses_whs_id foreign key ( whs_id )
references t_warehouses ( id ),
constraint fk_items2warehouses_item_id foreign key ( item_id )
references t_items ( id ),
constraint uq_items2warehouses unique ( whs_id,
item_id )
);
/
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Stuff N Fluff Depot',
'123 Fluff Street, Los Angeles, CA, 90001, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Box Bonanza',
'456 Box Lane, Dallas, TX, 75201, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Clutter Castle',
'789 Clutter Court, Chicago, IL, 60601, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Warehouse Whimsy',
'101 Whimsy Way, Miami, FL, 33101, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'The Storage Shack',
'202 Storage Street, Seattle, WA, 98101, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Toy Treasure Trove',
'303 Treasure Blvd, New York, NY, 10001, USA'
);
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Gizmo Galaxy',
'404 Galaxy Road, San Francisco, CA, 94101, USA'
);
commit;
/
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Teddy Bear',
'A soft and cuddly teddy bear.',
round(dbms_random.value(10, 100), 2),
'ToyCo',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Action Figure',
'A cool action figure with movable joints.',
round(dbms_random.value(10, 100), 2),
'ActionFigures Inc.',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Doll',
'A beautiful doll with changeable outfits.',
round(dbms_random.value(10, 100), 2),
'DollMakers',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Lego Set',
'A Lego set to build various structures.',
round(dbms_random.value(10, 100), 2),
'Lego Group',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Puzzle',
'A challenging puzzle for all ages.',
round(dbms_random.value(10, 100), 2),
'PuzzleWorks',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Toy Car',
'A miniature toy car.',
round(dbms_random.value(10, 100), 2),
'CarToys',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Train Set',
'A complete train set with tracks.',
round(dbms_random.value(10, 100), 2),
'TrainMasters',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Building Blocks',
'Colorful building blocks for creative play.',
round(dbms_random.value(10, 100), 2),
'BlockBuilders',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Board Game',
'A fun board game for the family.',
round(dbms_random.value(10, 100), 2),
'GameZone',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Stuffed Animal',
'A plush stuffed animal.',
round(dbms_random.value(10, 100), 2),
'Plushies Inc.',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Robot',
'A programmable robot toy.',
round(dbms_random.value(10, 100), 2),
'RoboTech',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Superhero Figure',
'A superhero figure with accessories.',
round(dbms_random.value(10, 100), 2),
'HeroToys',
sysdate
);
insert into t_items (
item_label,
item_description,
price,
manufacturer,
date_added
) values (
'Puzzle Cube',
'A puzzle cube for brain teasers.',
round(dbms_random.value(10, 100), 2),
'BrainTease Co.',
sysdate
);
commit;
/
declare
v_whs_id t_warehouses.id%type;
v_item_id t_items.id%type;
v_quantity number;
v_attempts number := 0;
v_max_attempts constant number := 100;
begin
for i in 1..30 loop
v_attempts := 0;
loop
-- Randomly select a whs_id from t_warehouses
select
id
into
v_whs_id
from
(
select
id
from
t_warehouses
order by
dbms_random.value
)
where
rownum = 1;
-- Randomly select an item_id from t_items
select
id
into
v_item_id
from
(
select
id
from
t_items
order by
dbms_random.value
)
where
rownum = 1;
v_quantity := round(dbms_random.value(50, 5000)); -- Random quantity between 50 and 5000
begin
insert into t_items2warehouses (
whs_id,
item_id,
quantity
) values (
v_whs_id,
v_item_id,
v_quantity
);
-- If the insert succeeds, exit the loop
exit;
exception
when dup_val_on_index then
-- If a unique constraint violation occurs, increment attempts and retry
v_attempts := v_attempts + 1;
if v_attempts > v_max_attempts then
raise_application_error(-20001, 'Too many attempts to insert unique records.');
end if;
end;
end loop;
end loop;
commit;
end;
/PL/SQL Function and Package
Now the interesting part: First, we need a function returning a column list from any query (udf_get_columns_from_query).
create or replace function udf_get_columns_from_query (
p_query clob,
p_separator varchar2, -- separator used in column list
p_separator_subst varchar2 default null -- possible replacement of a character if this is already used as a separator
)
return varchar2 is
l_cursor number;
l_col_cnt number;
l_desc_tbl dbms_sql.desc_tab2;
l_col_num number;
l_col_name varchar2(1000 char);
l_query_in clob;
l_query_out clob;
l_column_list clob;
begin
l_cursor := dbms_sql.open_cursor;
l_query_in := p_query;
execute immediate l_query_in into l_query_out;
-- interpretation of query
dbms_sql.parse(l_cursor, l_query_out, dbms_sql.native);
-- description of columns
dbms_sql.describe_columns2(l_cursor, l_col_cnt, l_desc_tbl);
for l_col_num in 1 .. l_col_cnt loop
l_col_name := l_desc_tbl(l_col_num).col_name;
-- replace separator
if p_separator_subst is not null then
l_col_name := replace(l_col_name, p_separator, p_separator_subst);
end if;
l_column_list := l_column_list || l_col_name || p_separator;
end loop;
l_column_list := rtrim(l_column_list, p_separator);
dbms_sql.close_cursor(l_cursor);
return l_column_list;
end;
/We also need to create a package (pkg_dynamic_pivot), which encapsulates two further functions:
- get_stock uses the warehouse_names of the table t_warehouses to build a dynamic query that results in a pivot table of all warehouses. The returned query can then be used later as a data source for the corresponding report.
- get_stock_column_names calls the udf_get_columns_from_query and assembles the corresponding column list using the query returned by get_stock.
create or replace package pkg_dynamic_pivot as
function get_stock
return clob;
function get_stock_column_names
return clob;
end pkg_dynamic_pivot;
/
create or replace package body pkg_dynamic_pivot as
function get_stock
return clob is
l_sql clob;
l_where_clause varchar2(1000 char);
begin
l_sql := q'[
select * from (
select
itm.item_label as "Label",
itm.item_description as "Description",
itm.price as "Price [€]",
itm.manufacturer as "Manufacturer",
itm.date_added as "Date added",
whs.warehouse_name,
i2w.quantity
from
t_items itm
left join
t_items2warehouses i2w on itm.id = i2w.item_id
left join
t_warehouses whs on i2w.whs_id = whs.id
)
pivot (
sum(quantity) for warehouse_name in (
]';
for cur in (
select
warehouse_name
from
t_warehouses
where
/*
999 columns possible in total;
we expect a maximum of 45 warehouses
*/
rownum <= 45
order by
warehouse_name asc
)
loop
l_sql := l_sql || q'[']' || cur.warehouse_name || q'[']' || ' as "' || cur.warehouse_name || '",' || chr(10);
end loop;
l_sql := rtrim(l_sql, chr(10) || ',');
l_sql := l_sql || q'[
)
)
]' || l_where_clause;
return l_sql;
end get_stock;
function get_stock_column_names
return clob is
l_column_list clob;
c_separator constant varchar2(1 char) := ':'; -- APEX requires ':' as separator of the header list
c_separator_subst constant varchar2(1 char) := '-';
c_query constant varchar2(136 char) := q'[
select
pkg_dynamic_pivot.get_stock
from
dual
]';
begin
select
udf_get_columns_from_query(c_query, c_separator, c_separator_subst)
into
l_column_list
from
dual;
return l_column_list;
end;
end pkg_dynamic_pivot;
/Classic Report
Now it's time to define the APEX region.
For this purpose, a classic report with the following properties is created. Our function pkg_dynamic_pivot.get_stock serves as the data source.
The crucial point is that Use Generic Column Names must be enabled.
The Generic Column Count should be at least the number of pivot columns defined in pkg_dynamic_pivot.get_stock plus the number of row columns. A total of 999 is permitted.
The bad but inevitable thing here is that when using Use Generic Column Names, the column identification in the Page Designer is not editable. For large data sets with a lot of columns I recommend to write a mapping function helping you to keep an overview.
Tip: Try not to make any changes to the columns at Page Designer level that are not absolutely necessary. Data formatting and co should already be done in the query. The reason: If the query is changed directly (e.g. adding / removing row columns) or by changing the data basis, the order of the columns could change. For APEX, however, column COL03 remains the same in the Page Designer, regardless of whether the data basis has changed or not.
We have now created the region, but the column names are still displayed as COL01-COL50 in the UI. To set the headers correctly, we need to define them in the region attributes. Here our pkg_dynamic_pivot.get_stock_column_names comes in handy:
Now our report looks like this:
If we add a warehouse and reload the page …
insert into t_warehouses (
warehouse_name,
warehouse_location
) values (
'Zigzag Toy Emporium',
'130 Happy Street, San Francisco, CA, 94101, USA'
);
commit;
/… we get our additional column:
Filter and Sorting
Of course, if you are already building a dynamic query, you can also add filters and complex sorting. In this case, simply change the function pkg_dynamic_pivot.get_stock: Let it accept parameters which are then used in the WHERE and ORDER BY conditions of the dynamic query.
Kommentare
Keine Kommentare