Wednesday, November 18, 2009

Updateable Interactive Report - Websheets style

For a current project the customer needed an Interactive Report with update functionality - in an Excel like style. Since we couldn't wait for APEX 4.0, we decided to build it in the current version.
The requirement is that the user can change some fields in the report (the Expenses, budget and estimates) for every year of the project life cycle and then the funding should be calculated according a defined formula (to financially smooth things out).
So I created a nice view with a formula to show the budget en estimate and the calculate the funding and created an Interactive Report on top of it.
The editable fields are created using the apex_item.text function with the p_attributes parameter set to:
'onclick=editField(<type>,<key1>,<key2>) readonly class="editField"'.
Once the user clicks on the field, the field is presented in a different style, made editable and with two little icons next to it.
After making the change, the user hits the green tick and an Application Process is fired to update the value in the database - and the report is requeried (showing the updated amounts for the fundings). If he hits the little red cross the edit is canceled; the same happens when he clicks another field.

You can see the result here. You can edit the blueish fields and, once you hit the green tick, the fundings for that year are automagically recalculated. I guess even Websheets doesn't do that...
If you are interested in the details of the code, just take a look at the source of the HTML-page!

Wednesday, November 11, 2009

Collecting ideas for APEX 4.0 Plug-Ins...using Google Wave

As you all know by now APEX 4.0 will have some new functionality regarding the creation of Custom Item Types and Custom Region Types. These types will be called "Plug-Ins". The idea is that there will be a public App Store like library where you can up- and download plug-ins.

A snippet from the Oracle Open World APEX 4.0 presentation:
  • Easy way to enhance the existing built-in functionality of Oracle APEX with new item types, region types, dynamic actions, etc.
  • Developers use in similar fashion to native widgets
  • Wizard support and declarative setting of attributes
  • Included in APEX application export
  • By providing this plug-in system in APEX, we want to engage the APEX community to create a rich ecosystem around Oracle APEX.
The APEX Development Team can’t possibly incorporate all the widgets that developers would like to utilize. By using plug-ins developers can readily incorporate additional item / region types to enhance the functionality, appearance and user friendliness of their applications. Once defined, plug-in based components are created and maintained very much like standard APEX components. We believe that the APEX community will build many plug-ins and make them available to others. Much like with the SQL Developer plug-ins it will be up to the contributors whether these are freeware, etc.

In the demo the APEX Dev Team is showing an Amazon style 'star rating' (as an Item Type Plug-In). (like the image above)

So maybe now is time to gather ideas for Plug-Ins (using this cool collaboration tool). So don't be shy and add your ideas about the Plug-Ins you think you could use (or even create yourself !) or are just very cool....

Custom Item Types
  • Amazon like star rating
Custom Region Types
  • Google Wave
  • Google Visualizations
So please add your own ideas to this wave, and let's make collaboration happen...

Monday, November 09, 2009

Oracle Application Express Forms Converter Book - Review

As I promised earlier I should write a review of the Oracle Application Express Forms Converter book by Douwe Pieter van den Bos. I finally found the time to work it doesn't take that much time, because it is "just" 150 pages thick (or thin).
The book contains 8 chapters, in which the reader is guided through an Forms2APEX conversion project from start to finish. It starts of with "Understanding your Project" (1), wherein understanding the reasons for conversion as well as the functionality of the Forms application is stressed as important, because these will influence the way you'll solve things in the APEX application. One or more examples should have been useful here.
The next chapter, "Preparing your Forms Conversion" (2), covers the creation of XML files from the source files. BTW, not only Forms, also "Reports" are - briefly - touched (and can be converted as well). Also different ways of creating a target database are mentioned (but IMHO you usually can just re-use your current Forms development environment).
Chapter (3), "Create your Forms Conversion Project", covers the next step in the project: uploading the XML files and understanding the Project Overview Page.
The next chapter is about "Planning your Project" (4). It shows how you can dive into the different components (blocks, triggers and what's not), and how to use the Annotations, Tags, Assignments and Applicablity to estimate the work you've done and still have to do. That last one is rather difficult, because only the number of objects-to-do are counted - and, as we all know - that doesn't tell you everything about the time needed to accomplish things. But it might help you in planning and monitoring the project.
Chapter (5), "Getting your Logic Right!", is probably the key-chapter of the book. The difference between Original, Enhanced and Custom Query is explained in detail. Alas one line "Implementing the business logic is done post-generation, execpt for some Post-Query triggers" says it all... I now the conversion utlity is not a silver bullet, but some examples on how to convert Forms logic to the APEX equivalent would have been a very useful add-on!
Chapter (6) covers "Generating your Application", and doesn't tell anything new if you're already an APEX Developer (actually there is nothing more to tell about it than what covered here).
"Reviewing and Customizing your Application" is the penultimate chapter (7). It handles tweaking the generated LOV's, Titles and how to create a Validation based on what was an WHEN-VALIDATE-RECORD trigger in the Form.
The last chapter (8), on "Delivering your Application", discusses (very briefly) how to create an Application Export and import that in a Test-, Acceptance or Production environment. No specific Forms2APEX stuff.

All in all it is a good book when you need an overview of the steps you have to take for converting an Oracle Forms application to APEX - more on a Project Management level than a Developer level. If you really need to get your hands dirty and do the actual conversion yourself, this book is just the beginning of a great adventure...

Thursday, November 05, 2009

A Google Wave in an APEX Page

That is soooo simple, click here

My first blogpost using Google Wave

It is somewhat experimental, but here it is...and you probably can't see it if you don't have a Wave account.

There should be a "Bloggy" robot to facilitate postings from within Wave with just one click, but that doesn't seem to work. Instead I used Embeddy ( to give me some javascript with the Wave ID. I copied that and pasted it in the HTML of the Blogger Template. The DIV is just added to thi post.
You can only have either one blogpost with a Wave or need a line of javascript for each Wave you need to publish. So a working version of Bloggy might be handy...

Tuesday, November 03, 2009

Handling information overload: Showing data on demand in an IR

Using APEX Interactive Reports you can let the user decide which columns to show or hide in a report. But even then, reports with over 20 columns are still being used. So I tried to come up with a solution where you can hide less important (detail) column information and still reveal that information with minimal effort - like a mouse-over event. In this tiny example the list of employees of a department pops up when the user moves his mouse over the little question mark icon.
By adding some HTML and Javascript in the select statement (I know, that is not a "best practice"...but if you've got a better idea, please drop a comment) and some CSS you can use this technique - even for more complex "detail" reports!
The select statement used in this example is:
select dept.deptno
, dname
, loc
, 'onmouseover="javascript:$(''.overlay'').hide();$x_Toggle('''||dept.DEPTNO||''')">'||
'' enames
from dept
, ( select deptno, listagg( ename, ', ') within group (order by sal) enames
from emp
group by deptno
) emps
where emps.deptno (+) = dept.deptno
The CSS used in that statement is defined by:
One remark: You should disable sorting, filtering etc. on the "enames" column, because that makes no sense...

Edit: Thanks to Alex (see comments) the solution can be changed to something more elegant:
Just select the ENAMES column, and set these properties
- Link Text = <img src="#IMAGE_PREFIX#apps_info.gif" alt=""></a><div class="overlay" id="#DEPTNO#" style="display:none">#ENAMES#</div>
- Link Attributes = onmouseover="javascript:$('.overlay').hide();$x_Toggle('#DEPTNO#');"
- Target (URL) = javascript:void(0);

So the SQL is "clean" now and you even can enable the sorting etc again!