ApEx Flashchart Headache

Something like this can drive you nuts. I lost several hours trying to figure out why something that is supposed to work (and I do see it working), doesn't work for my customer. I created a simple drill-down chart and did my usual testing. After completing the work, I forwarded it to my customer to see if everything is as they expected. However, they complained it didn't show the right values. It took me a while to remember I answered one similar question in the forum here by proposing a workarround. I do my work in FF and my customer uses IE. There we go.

The problem seem to be simple - IE doesn't refresh a flash chart once the link has been used for the second time within a session. I was curious and checked if this is also the case with XML Charts. XML Charts did behave the same way if I used IE. It was easy to solve the problem with XML Chart package by adding an additional parameter to the link in form of a random number

v_random := DBMS_RANDOM.random;

After adding this, the problem was not there any more. I think, the same needs to be done with the Flash Charts in ApEx in one of the next releases. Eventually using the same method.

If you want to see a practical example of what I'm talking about, go to my Demo Application

http://apex.oracle.com/pls/otn/f?p=31517:169

and follow these steps:

1. Use IE (Internet Explorer),

2. Make sure your Internet Options / Browsing History / Settings is either set to "automatically" or "never",

3. Make a round trip and click all three pie slices of the pie chart showing departments,

4. After you click on any of the slices for the second time, the second flash chart will not show you the right result but will remain the same all the time.

5. You will need to submit the page in order to get the values change.

And now, the best thing is that the XML Chart will always show the right values. Even if the IE settings Internet Options / Browsing History / Settings are set to "never".

ApEx Item - Workarround

There may be some cases where you need to use apex_item (although, Patrick Wolf doesn't think so). The downside of that approach is:

1. your Query looks awkward,

2. you can't sort on your columns,

3. you can't display your column totals

In case you didn't know, you may use a trick to work around that problem:

a) crate a normal query like this:

SELECT LPAD (ROWNUM, 4, 0) ROW_NUM, deptno, empno, ename, 
       sal, comm
  FROM emp

b) In the HTML expression of your columns put the following for each column accordingly:

<input id="f03_#ROW_NUM#" type="text" style="text-align: right;" 
value="#EMPNO#" maxlength="4" size="4" name="f03"/>

c) You may use the items in your custom update process and in your javascript the same way you do when you use apex_item or the wizard generated table.

Now, you may create column totals and sort on such created items. And your SQL query looks much better.

As always, there is a proof of concept in my Demo Application on this page http://htmldb.oracle.com/pls/otn/f?p=31517:168.

By the way. Around 100 people from all over the globe asked and received a personal account for the Demo Application. If you are also interested to look behind the curtains, please feel free to send me an email with your contact details.

Sorting on apex_item

If you use apex_item syntax in your query, you will not be able to sort on the columns in a way you would expect because ApEx will sort the column on the string you use as input. This means it will sort on something like

<input id="f04_9" type="text" style="text-align: right;" value="2300" maxlength="12" size="12" name="f04"/>

To overcome this problem you may want to use the following in front of your apex_item:

'<INPUT TYPE="HIDDEN" VALUE="'
|| LPAD (sal, 20, '0')
|| '" />'
|| apex_item.text (4,
TO_CHAR (sal),
12,
12,
'style="text-align:right" ',
'f04_' || ROWNUM,
NULL
) sal_editable



You could use any other value instead of the original one as well (ROWNUM, ename) and use it for sorting.

Here, you will find a working example:

http://htmldb.oracle.com/pls/otn/f?p=31517:167

Enjoy.

Filtering a Shuttle Item

Very often a shuttle item is much convenient than a multiselect list. But what if your LOV has many hundreds or even thousands of items? Check this example:

http://htmldb.oracle.com/pls/otn/f?p=31517:166



The most of the code can be reused except of the application process. However, this
could be written as a dynamic PL/SQL block as well.

The example I used is based on a table containing around 5000 records. Having in mind this runs on apex.oracle.com, the performance of filtering is quite satisfying.