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
When:
- Event: After Refresh
- Selection Type: Region
- Region:
Action:
- 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.


9 comments:

JP said...

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

Adam Kanzmeier said...

Great update!! Thanks Dimitri!
-Adam

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.

Thanks
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.