CASE in Interactive Report (APEX)

In APEX 3.1.2 there is a problem with the CASE statement when you use it in the Compute function of an Interactive Report. You might see following error:


The above issue got fixed in APEX 3.2, as you can see here. So if you want that functionality, it's worth upgrading to APEX 3.2. There are more smaller fixes in like these and a number of security enhancements, so I recommend to use APEX 3.2 if you can.

But... when you try this CASE statement (use of smaller than, larger than or BETWEEN) you get an Invalid computation expression message.


Joel Kallman told me this is a bug (Bug 8347332) and it will be fixed in the APEX 3.2.1 patch set. The Apex development team was too restrictive in the operators they allow inside this type of expression.

If I can ask for one more enhancement: let us also be able to use Enters, because if I type some more when clauses it becomes a very long string. It would be nice if I could structure it like in the next screenshot. It would also nice if we could make that Computation field resizeable. As Patrick is now in the team, he already did that in his ApexLib and as JQuery will be in APEX, I hope to see it in 4.0 ;-) Thanks so much!


A workaround at the moment is to include the case statement directly in your SQL statement.
If you can't do that (like our client wanted to specify the above dynamic), you need to build it yourself or search for an alternative function that does work.

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

Using Interactive APEX Report in a Fun way

You don't know Interactive Reports in Oracle Application Express yet? You miss something.
From APEX 3.1 onwards there's a killer feature included in APEX: Interactive Reports.

You can turn a boring report into an interactive one, where you can play with the data, do sums, averages, highlighting rows based on a condition, search and sort in an intuitive way and a lot more.

I didn't think about using it in DG Tournament until my brother asked me if he could have a special Bet ranking. He wanted to have a rank where he could see only his friends or the people playing in his group. I told him it was already possible by using the location field (which I also wrote in my FAQ).

It made me thinking... I also wanted to see in one view how I'm doing with the rank compared to my family or the people in Belgium or everybody. Another feature I wanted to have was to go back in time. When a game is played I adapt the score and the betting gets calculated, but wouldn't it be interesting to see what your rank was before the latest game and now?
Wouldn't it be nice if we could just save all these reports so next time when you login you can see it again?


All that and a lot more (for ex. highlighting) is now included in DG Tournament! And the good thing about it, DG Tournament is completely written in APEX, so it did take me almost no time to implement this new feature! I believe this is the first time the Interactive Reports make a use in a fun way, as this application is completely for fun ;-)

I wonder if non-APEX and non-technical people understand this feature. If you used the interactive report in DG Tournament, put a comment on this blog if it was difficult to use or if you found it fairly simple.