Continuing with our focus on APEX Grids and Reports customizations, this final post of the series dives into advanced techniques for creating dynamic grids and reports. Here, we'll cover how to use escape filters for substitution strings and how to build fully dynamic grids or reports that show and hide columns with unique names.
Escape Characters
If you want a column with dynamic HTML generated by a PL/SQL function within your Interactive Grid (IG), assign the value to a hidden column and reference it in the same or another column. For example:
function (options) {
options.defaultGridColumnOptions = {
cellTemplate:
`
&CUSTOM_HTML_COL.
`
};
return options;
}
The column could be referenced without problems in a report; uncheck the “Escape special characters” option in the Security part of the column with the custom HTML, and the content will show as HTML. However, since IGs lack this option, the only way to get the HTML raw text is to use the escape filters, in this case, we can use the ‘RAW’ escape filter to get the original value like this:
function (options) {
options.defaultGridColumnOptions = {
cellTemplate:
`
&CUSTOM_HTML_COL!RAW.
`
};
return options;
}
Check out the Oracle APEX documentation for additional escape filters like ‘HTML’ and ‘JS.’ The ‘RAW’ filter is highly effective, but consider the other filters depending on your context. All of these are available to use in Items, IG columns, or report columns.
Creating a Dynamic Grid or Report
There are some limitations on how dynamic an IG or IR can be, but this should take care of your needs in most cases. Both of these have a limit of 200 columns that you can use, either visible or hidden. For this example, we will use those 200 columns to dynamically show a preview of a CSV uploaded with the data, with column names the same as the file and only showing the uploaded columns, not the 200 columns.
First, you will need to create your IG defining the 200 columns; for this to work, the report or grid has to define the maximum number of columns you will need so APEX can generate the metadata. In this case, we will take the uploaded file from the temporary table APEX uses to parse the CSV file like this:
SELECT line_number, col001, col002, ... , col199
FROM apex_application_temp_files f,
table( apex_data_parser.parse(
p_content => f.blob_content,
p_add_headers_row => 'Y',
p_max_rows => 102,
p_skip_rows => 2,
p_file_name => f.filename ) ) p
WHERE f.name = :P1_FILE;
P1_FILE is the item used to upload the file. Then, we must create the hidden items to store the column names. Usually, I like to create a new region to hold all these items and have those organized; in this example, I will create the items like this: P1_COL001_H, P1_COL002_H, P1_COL003_H, …, P1_COL199_H. This could be a tedious task, but you can use the export and import functionality in your favor, create just the first item, this will work as a template, then import just the page, and you can manually create the rest of the items using the first one as a template with a query that creates the other 198, then export the page again, and you should have all your items created.
Now that we have all the items created to store the column names, we need to assign values to them. This will dictate whether the column should be shown or hidden. If the item is empty, the column will be hidden; otherwise, it will be displayed. I will use apex_util.set_session_state to dynamically set the items as needed with the values of the first row of the CSV file uploaded like this:
for r in (
select 'P1_'||col_name||'_H' as item_name, headers as header
from (
select p.*
from apex_application_temp_files f,
table( apex_data_parser.parse(
p_content => f.blob_content,
p_add_headers_row => 'Y',
p_max_rows => 1,
p_file_name => f.filename ) ) p
where f.name = :P1_FILE
)
unpivot (headers for col_name IN (col001, col002, ... , col199))
)
loop
apex_util.set_session_state(r.item_name, r.header);
end loop;
Finally, we will use the items to set the header name and condition the column to whether it has to be hidden or not. In the heading, we will reference the item like this: &P1_COL001_H. In the server-side condition, we will set the type to ‘Item is not null’ and select the column item ‘P1_COL001_H.
Every dynamic column created must repeat this. You can use the same trick with the import and export used for the items; in this case, be careful with the IDs of the IG and the columns.
Now, you have a completely dynamic grid or report to preview any CSV uploaded; this is just an example of what can be done with a fully dynamic report or grid. You have the basic knowledge now, so have fun playing with it and exploring all the possibilities.
Conclusion
You’ve now mastered essential APEX customization techniques for dynamic and user-friendly grids and reports. Combining these with the formatting tips from previous posts lets you create better-looking, highly flexible, and functional applications. I hope this series has expanded your APEX toolkit—happy coding, and may your grids be ever dynamic!
SUBMIT YOUR COMMENT