Tuesday, September 29, 2015

Column Group Headings in an Interactive Report (APEX 5.0)

In 2008 I blogged about how to add Group Headings into an Interactive Report in Oracle Application Express (APEX 3.x!).

Here's an example what I mean with Group Headings, you want to logically group certain columns (e.g. Empno, Ename) and have a group name above them (e.g. Group 1):

The approach I used at that time worked, but it required a lot of manual work. In 2009 Martin D'Souza improved it and wrote a blog post about that. In 2010, when plugins were introduced in APEX 4, Martin wrapped it all in a nice APEX plugin which made adding Column Group Headings almost declarative. The plugin was further improved and put on GitHub.

Today, having APEX 5, and the Interactive Reports being changed a lot behind the scenes, the plugin was broken. As we're in the middle of restructuring how to share code, plugins etc., I forked Martin's git repo and made the plugin compatible with APEX 5 and Universal Theme (see version 1.0.4), so when you need it, you can grap it from the forked repo. At a later time the plugin will be moved to OraOpenSource, so depending when you read this post, check out OraOpenSource as Martin, I and many other developers will start putting our open source code on OraOpenSource.

Here's a quick how to use the plugin.

Step 1: Download the file dynamic_action_plugin_com_clarifit_apexplugin_ir_column_grouping.sql
Step 2: Import the plugin in your APEX application
Step 3: Define the Groups in the Interactive Report (you can right click on Column Groups in the  Attributes of your Interactive Report and add the Groups you need):

Step 4: Assign the group to the columns of your report (you can multi select the columns and in the right pane you can select the group):

Important: define for every column that has a group defined, a static id equal to the name of the column (e.g. ENAME).

Step 5: Add the dynamic action plugin to the page
- Event: After Refresh
- Selection Type: Region
- Region:
- IR Column Grouping [Plug-In]

That's it... note that when you scroll down and you have sticky headers, the group will stay with it.


JP said...

Dimitri, thanks a lot! Another step on the way to Apex 5.. JP

Adam Kanzmeier said...

Great update!! Thanks Dimitri!

Sunil Bhatia said...

Hi Dimitri,

Your plugin is not working, in case of multiple interactive reports on page, and we wish to put the column grouping on interactive report which is not the first one.

Please suggest solution.

Sunil Bhatia

Amit said...

Awesome post! thanks.

But as one post mentioned, it does not allow to do this on multiple interactive reports on 1 page, which is a real shame. Is there an easy solution for this?

Dimitri Gielis said...

We would need to update the plugin to take into account the static id of the region.
Feel free to give that a try - fork on Github or update in new branch...

Unknown said...

I have this working for for multiple IR reports. Not fully vetted out yet. Both of my IRs freeze the headers and therefore Oracle creates two different tables, one for the headers and one for the data. Both have the same class and id ( I was shocked to find this). As I result, I was getting to many table objects initially. I fixed by selecting on div class and table class. This will likely break if you go away from fixed headers. I can send over my plugin but just beware. Until oracle fixes the code that generates the tables this the issue we have to deal with.

Anonymous said...

Is there a way to download the Grouping in to csv? I tried but the grouped headers doesn't show up in exported csv.

Dimitri Gielis said...

we are working on a feature in www.apexofficeprint.com to download your Interactive Report to Excel with everything...

koloo kumo said...

Hi Dimitri,
i trying to get the plugin to work with Apex 5 but iam having some unknown issues.
After importing plugins and creating groups adding columns to it and adding the stated dynamic action still it doesnt work and there is no error....what could be the problem?

thank you.

yok anand said...

Hi Dimitri,

I have a different requirement. Is it possible to name column headers dynamically for Interactive report.

It is possible for tabular form region. We can give Tabular heading as PL/SQL function body and we can return headers from a function.

But I am not sure how to give column header dynamically for Interactive report. Is there any way to do it.


Dimitri Gielis said...

Hi Yok,

Yes it's a bit harder to do, but you can use JavaScript to replace the headers.
see: http://api.jquery.com/replacewith/

Hope that works,

Stirl said...

nice. It is a shame it doesn't work if you have a control break on your report.

Anonymous said...

Great, thanks a lot!

Anonymous said...

Great, thanks a lot!

Anonymous said...

Hi Dimitri

thank you for your work. I've downloaded the plugin file and imported it but then when I try to install it, the system says it's not a valid plugin... How can I fix this ? Is there a newer version of the plugin ?

Thanks for your previous work and thanks for your help.