Friday, November 21, 2008

Group Headings in an Interactive Report (APEX)

Did you ever wanted a group heading above your column headings in an Interactive Report in APEX? I wondered why that wasn't already included in APEX, as all components are there to get it to work. Let's create a little example to show what I mean.

Create an Interactive Report on the emp table (for ex. select * from emp).

In your Interactive Report details there's a section Column Groups where you can define groups.
But it also says: "Column Groups are used to group columns together on the single row view."
In my example I created two groups.


Next we need to edit the group to say which columns belong to which group.
In my example I specified that the columns Empno and Ename belong to Group 1. I specified some other columns for my Group 2.


If you run the page you'll see this IR (without group headings)


But when you select the single row view (the Edit icon in front of the row) you'll see this:


So you can see the groups nicely in the single row view but it's not there in the normal report...

So I thought, as APEX is completely dictionary driven, we should be able to find all the pieces and with some javascript we should be able to change the DOM element to include the group headings in the normal report too. The result:

You can see this example live here, the source code is also there, so you can see how I did things.

On the page I open the "Select Columns" automatically, so you can see the available columns and the groups in () and you can dynamically play with showing and hiding columns.

In short the way it works:
1) Query the APEX dictionary to see which columns and groups there are defined and return a string that can be parsed into a json object.
2) Create a javascript function to add the group headings above the column headers. I put some notes in the javascript and added a lot of console.log's. If you remove the // you'll see in Firebug a full trace what is happening.
3) To attach it to the Interactive Report and call it from your page you need to do some more. At OOW I had a chat with Carl about it, at the moment you can't call your own function at the end when the interactive report is called (but he was going to change that in future releases). Carl told me I was forced to put a little timeout there to make sure the Interactive Report was drawn before my javascript ran.

Feel free to try it in your own environment and give some feedback. The code is generic so you should be able to plug it into your environment without that much effort. I didn't use jquery as I wanted a generic solution for a "normal" APEX environment. Another thing I didn't do is looking if you defined a link (edit icon) before the row or not. So these might be enhancements for the next release...

9 comments:

Mark M said...

Excellent Dimitri. I was looking for just such a fix a few weeks ago. I'll definitely try it on my site (although my vacation takes precedence!). Originally, I kept playing with the column groupings not realizing at the time that they only applied to the single row view.

I've found a fair amount of benefit to extending APEX with JavaScript such as enabling and disabling dependent fields without having to do a submit, calling Ajax database calls when a field value is changed, etc.

Rgds/Mark M.

Doug said...

Dimitri,

There is a flaw in the way you've done this whereby if you close the Column Selection box and then re-open it, the Group Headings get included Again at the top of the report.

Each time you open and close the column selection dialog the group headings get added. You might want to add some kind of identifier you can check for to see if the group row already exists and only add it if it does not.

Niklas said...

Dimitri,

I'm running version 3.1.2.00.02 of Apex and there is no l_LastFunction available on the apex.worksheet.ws object. Is there an equivalent function that I can use? Is there any documentation available for apex.worksheet.ws stuff? Without the l_LastFunction I can still use your code but it will fail to prepend the group heading when the AJAX driven report refreshes selective parts of itself.

Stew said...

This is nicely done and I could see some good uses for it.

Somewhat off-topic, the thing I don't like about the groups is the way they display the group name next to the column names in a column drop-down list, for example when you use Select Columns.

They look like:

My Column (Group name)

We wanted to group long list of columns in the drop-downs in my current application, but the users said this looked "too geeky" (their words). So I ended up adding dummy column names like:

--------- Group name --------

to organize the columns. That seems very lame to me, but it works.

I wish Apex would add support for including the group names in the drop-down lists in this sort of form, maybe even allowing bolding of the group names.

Maybe this will give you an idea for a cool-new add-on?

Thanks for listening,

Stew

Martin Giffy D'Souza said...

Thanks for posting the solution! I've posted the updated code here

Shalini G said...

Yeah it was Excellent, I was looking for this techique....
I can't access your application, Please provide credentials for this technique as soon as possible.

Thanks in Advance.

Regards,
Shalini

Shalini G said...

Yeah it was Excellent, I was looking for this techique....
I can't access your application, Please provide credentials for this technique as soon as possible.

Thanks in Advance.

Regards,
Shalini

Andy said...

Dimitri,

Is it possible to download the report as CSV with the column groups?
I have made a IR with column groups but when I print it only the IR columns show but now the group.

Regards
Andy

Dimitri Gielis said...

Hi Andy,

You would need to create a procedure that generates the correct csv then. APEX itself or my blog post won't handle that.

Thanks,
Dimitri