Wednesday, November 28, 2012

Using the Modal Page plugin as a modal popup list in a tabular form


I use a plugin by SkillBuilders, the Modal Page, which is free by the way. Pay them a visit here:

Example application

Can be found over at
You can download the application aswell. It comes with supporting objects (2 tables and some seeding data) and is pretty much selfcontained. The application is developed in 4.1 and imports into 4.2 without problems.

Diving straight in

Page 1

Tabular form: wizard generated and based on table DEMO_BOOK_STORE. I added in a column to be used as a link column (popuplist):
NULL popuplist
I hid the book_id and topic columns. I specified link column properties for column popuplist
  • Link Text: Select Item
  • Link Attributes: onclick="return false;" class="modalshow"
  • Target: Page in this Application
  • Page: 2
Add Button: I changed the button action from "Redirect to URL" to "Defined by Dynamic Action". This is because an extra action needs to happen when a row is added. In page attributes: Javascript:
var gaInputMapping = new Array();
var gRowSelect, gTriggerTable;
      var lsHeader = $(this).val(),
          lsName = 'f'+('0'+(index+1)).slice(-2),
          lsNName = $("[name='"+lsName+"']")[0].nodeName,
          lMap = {"header":lsHeader,"name":lsName,"nodeName":lsNName};
function getNameWithHeader(pHeader){
   var lsName;
   $.each(gaInputMapping, function(index){
         lsName= gaInputMapping[index].name;
   return lsName;
function getHeaderWithName(pName){
   var lsHeader;
   $.each(gaInputMapping, function(index){
         lsHeader= gaInputMapping[index].header;
   return lsHeader;
function getSelector(pHeader){
   var lsSel;
   $.each(gaInputMapping, function(index){
         lsSel= gaInputMapping[index].nodeName + "[name='" + gaInputMapping[index].name + "']";
   return lsSel;
function getObject(pHeader){
   var lRet;
   $.each(gaInputMapping, function(index){
         lRet= gaInputMapping[index];
   return lRet;
function setRowDetails(pTable, pRow, pItemId){
          {"p_request"      : "APPLICATION_PROCESS=get_item_details",
           "p_flow_id"      : $v('pFlowId'),
           "p_flow_step_id" : $v('pFlowStepId'),
           "p_instance"     : $v('pInstance'),
           "x01"            : pItemId},
              var lItem = $.parseJSON(data);
              $.each(lItem, function(){
                 var lSelector = getSelector(this.COLUMN);
                    $(lSelector, pTable.find('tbody > tr').eq(pRow)).val(this.VALUE).change();
HTML Header and Body Attribute: HTML Header:
<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8.14/ui/minified/jquery.ui.button.min.js"></script>
4 Dynamic actions:
"Button Icons"
  • Event: After Refresh
  • Selection Type: Region
  • Region: tabular form region
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: checked
  • Code:
"Modal closed: fetch details"
  • Event: Manual Close [SkillBuilders Modal Page]
  • Selection Type: DOM Object
  • DOM Object: document
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
   lItemId =;
      setRowDetails(gTriggerTable, gRowSelect, lItemId);
"Show modal"
  • Event: Click
  • Selection Type: jQuery Selector
  • jQuery Selector: .modalshow
  • Event Scope: live
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
gTriggerTable = $(this.triggeringElement).closest('table');
gRowSelect = $(this.triggeringElement).closest('table').find('tbody > tr').index($(this.triggeringElement).closest('tr'));
True action: SkillBuilders Modal Page [Plug-in]
  • Fire on page load: unchecked
  • Dialog Title: Select an item
  • URL Location: Statically Defined
  • Static URL: f?p=&APP_ID.:2:&APP_SESSION.:::2:::
  • Dialog Height/Width Mode: Static by pixels
  • Height: 700
  • Width: 600
  • Modal Page ID: selectitem
"Add Row"
  • Event: Click
  • Selection Type: Button
  • Button: ADD (Add Row)
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: unchecked
  • Code:
  • Affected Elements:
    • Selection Type: Region
    • Region: tabular form region
Page Process:
  • Type: PL/SQL anonymous block
  • Process Point: On Demand
  • Code:
   lJSON VARCHAR2(1000);
   select '[{"COLUMN":"BOOK_ID", "VALUE":"' || book_id ||'"},'||
           '{"COLUMN":"SUBJECT", "VALUE":"' || subject ||'"},'||
           '{"COLUMN":"PRICE",   "VALUE":"' || price   ||'"},'||
           '{"COLUMN":"AUTHOR",  "VALUE":"' || author  ||'"},'||
           '{"COLUMN":"TOPIC",   "VALUE":"' || topic   ||'"}]'
     into lJSON
     from demo_book_list
    where book_id = apex_application.g_x01;


Page 2

Page template: I made a copy of the popup page template in the theme. In my example I am using theme 23 and since the popup template has a table layout with a useless right column, I removed the table markup and just replaced it with a div. This is my “Body” code:
Standard report based on DEMO_BOOK_LIST, with 3 columns: book_id, subject, author. Edit column BOOK_ID and change heading to 'Select'. Specify Column Link attributes:
  • Link Text: Select me!
  • Link Attributes: onclick="return false;" class="modalclose"
  • Target: URL
  • URL: #BOOK_ID#
1 Dynamic Action:
"Button Icons"
  • Event: After Refresh
  • Selection Type: Region
  • Region: report region
  • Event Scope: bind
True action: Execute javascript code
  • Fire on page load: checked
  • Code:
   var retval = {"item_id":$(this).attr('href')};
   parent.$(parent.document).apex_modal_page('close', retval);

Some Clarifications

javascript in page 1:
Input mappings:
var gaInputMapping = new Array();
var gRowSelect, gTriggerTable;
      var lsHeader = $(this).val(),
          lsName = 'f'+('0'+(index+1)).slice(-2),
          lsNName = $("[name='"+lsName+"']")[0].nodeName,
          lMap = {"header":lsHeader,"name":lsName,"nodeName":lsNName};
This builds of the input map that apex generates for wizard generated tabular forms. This will read the map and store it in a global javascript array variable. The type of the referenced item will also be stored, meaning that whether the item is a select list or an input item.
The columns in the map are also generated in the same order as the assigned f##-arrays for each column. So the first item in the fmap array will be array f01, the second will be f02,...
<input id="fmap_001" type="hidden" value="CHECK$01" name="fmap">
<input id="fhdr_001" type="hidden" value="Select Row" name="fhdr">
<input id="fmap_002" type="hidden" value="BOOK_ID" name="fmap">
<input id="fhdr_002" type="hidden" value="Book Id" name="fhdr">
<input id="fmap_003" type="hidden" value="TOPIC" name="fmap">
<input id="fhdr_003" type="hidden" value="Topic" name="fhdr">
<input id="fmap_004" type="hidden" value="PRICE" name="fmap">
<input id="fhdr_004" type="hidden" value="Price" name="fhdr">
<input id="fmap_005" type="hidden" value="AUTHOR" name="fmap">
<input id="fhdr_005" type="hidden" value="Author" name="fhdr">
<input id="fmap_006" type="hidden" value="SUBJECT" name="fmap">
<input id="fhdr_006" type="hidden" value="Subject" name="fhdr">
The several functions below that are functions allowing easy retrieval of a column or item through either the headers or the array name.
Understand that this is a different approach from retrieving input items through the headers on a TD! In particular, hidden items can ruin your plans of referencing items. In my sample app i have 3 hidden columns: rowid, book_id and topic. Apex will generate these hidden items in the last column:
<td headers="SUBJECT">
<label class="hideMeButHearMe" for="f06_0001">Subject</label>
<input id="f06_0001" type="text" value="Easy Oracle" maxlength="2000" size="40" name="f06" autocomplete="off">
<input id="f02_0001" type="hidden" value="2" name="f02" autocomplete="off">
<input id="f03_0001" type="hidden" value="5" name="f03" autocomplete="off">
<input id="fcs_0001" type="hidden" value="2C9BDC219934F8ED33AF9602262E2391" name="fcs" autocomplete="off">
<input id="frowid_0001" type="hidden" value="AAAqsGAAjAAAWQrAAA" name="frowid" autocomplete="off">
<input id="fcud_0001" type="hidden" value="U" name="fcud" autocomplete="off">
So when you try to reference the input items through columns headers: $("td[headers='SUBJECT'] input") you'll be greeted by an array of 6 inputs. Of course, you could then add the :visible filter to only target the visible text item. But how will you deal with the hidden items then? Assign something to them? You will need to hardcode the arrays somewhere.
By reading the fmap array this can be avoided. And items which are hidden are still targetable through the headers, which is a lot more intuitive than using the f## arrays (which change when you change the column ordering or add or remove a column...). Retrieve details:
function setRowDetails(pTable, pRow, pItemId){
          {"p_request"      : "APPLICATION_PROCESS=get_item_details",
           "p_flow_id"      : $v('pFlowId'),
           "p_flow_step_id" : $v('pFlowStepId'),
           "p_instance"     : $v('pInstance'),
           "x01"            : pItemId},
              var lItem = $.parseJSON(data);
              $.each(lItem, function(){
                 var lSelector = getSelector(this.COLUMN);
                    $(lSelector, pTable.find('tbody > tr').eq(pRow)).val(this.VALUE).change();
This will call the application process and process the data. A book id is passed in and it will receive the details. The process will return a json string:
   select '[{"COLUMN":"BOOK_ID", "VALUE":"' || book_id ||'"},'||
           '{"COLUMN":"SUBJECT", "VALUE":"' || subject ||'"},'||
           '{"COLUMN":"PRICE",   "VALUE":"' || price   ||'"},'||
           '{"COLUMN":"AUTHOR",  "VALUE":"' || author  ||'"},'||
           '{"COLUMN":"TOPIC",   "VALUE":"' || topic   ||'"}]'
     into lJSON
     from demo_book_list
    where book_id = apex_application.g_x01;
This will parse to an array of objects. Each object holds the column name and the value. I did this simply because it is easier to loop over and generalize. The javascript will loop over the returned array, and then for each column it'll find the matching input mapping and return a selector. With this selector the correct input in the same row can be manipulated. Take note of the jQuery selector:
$(lSelector, pTable.find('tbody > tr').eq(pRow)
This will find the input item within the given context: the clicked row in the clicked table. pTable and pRow are set when the 'select item' button is clicked in global variables. Dynamic action "Show Modal"
gTriggerTable = $(this.triggeringElement).closest('table');
gRowSelect = $(this.triggeringElement).closest('table').find('tbody > tr').index($(this.triggeringElement).closest('tr'));
Again, this allows for avoiding the tedious item_id parsing which includes the 'rownum'. And i prefer this way over targeting the item array and picking out one at the row index. Again, take not of the triggering of the change event on each column. When no change event is triggered, a new row will not be marked as a changed row but rather remain marked as deleted, and no insert will occur (same logic for updating a row of course).
Basically, in sequence:
  1. click the button
  2. table and row are saved in js
  3. modal page is opened
  4. item is selected
  5. modal page is closed and a return value is specified (book id)
  6. the book details are retrieved through ajax
  7. in the ajax success callback the book details are filled out into the items of the (saved) clicked row


It might look a little daunting, but it doesn't have to take too long. Some of the javascript can easily be put in a file and then included. The jquery buttons are also a bit extra-curricular, but i like them for being easy to implement and how they improve look and feel.
The modal page requires some setup, and you will need to create a page to be used in the modal popup. However, i consider this a strength as it allows for maximum flexibility. I'm providing this code and example as an example of how you could use the modal popup and javascript to greatly enhance a tool. This could be a tabular form, an item on a page, a button in an interactive report,...
I also implemented the ajax retrieval of the book details as part of the example. You could skip ajax and return all details from the modal page through its return value object! But this is cooler ;-)
And last but not least, you can just create and maintain a standard wizard generated tabular form. No need to grasp for apex_item generated forms this way, which is a real strength in my opinion.

Thursday, September 13, 2012

A non-standard export to excel 2010 (.xlsx)

Refer to the fantastic diagromographic to see what I created! Bliss.

Why, Tom?

Standard CSV export is not able to do this. No formatting, no styling, freezing headers. A report was also no option, since that is not the point of this file.
During a migration project there was this excel being used somewhere. It was on the server machine of the database, because it had to have access to an oracle client so it could connect through use of macros. These macros would load in data in 3 extra sheets in the workbook, so that on the first sheet a vertical lookup could gather data from those sheets. Full tables there!
The generated excel would then be saved without macros, and sent to distributors who could then check the information and alter columns where necessary. An example would be to alter the production values with their corrected values. They would then save the excel and send it back.
This excel would then be opened and saved as a csv. This csv was then to be placed in a specific drive/folder of the same server machine it was created on, so that a scheduled task could pick it up to serve it to a processing procedure, which would read line per line.
The question then was: can we move a part of all of this to apex?
What happens now is: to generate the excel the user navigates to an apex page, selects the required parameters, and the excel is generated and served to download. Any required alterations are made, and the excel is sent to the distributor(s). There any required alterations are made, and the excel is sent back. It then can simply be uploaded and processed.
No more macros, vlookups, save to csv, no more client or scheduled tasks or access to machines with an oracle client. There are 2 apex screens with some parameters and that’s it.
Also, there is almost no change in the look of the excel file the distributors get sent. This was quite crucial too: with lots and lots of distributors, changing the look and potentially the working method was not an option (well, it would have cost a lot of time and effort). Only the end point interaction has changed, and those people were easily trained.

Sample Application

How it works

I’ll briefly touch on how things are set up. You can check how it works on my sample app on, and you can download the sample application. It includes the database objects such as tables, sequences and packages and some data seeding in the Supporting Objects. The deinstallation script is also in place and will drop all objects the supporting objects create.
Now first of all, I’ve used some packages from the Alexandria PLSQL library: a great project bundling a lot of interesting packages and information. In my supporting objects I only included the bare minimum of objects required to make the application work (sql_util, zip_util, xlsx_builder).
Props to Anton Scheffer, for his blogs and his xlsx_builder package:
And to
Odie, check this mind-boggling blogpost:


In short, all it does is use the xlsx_builder_pkg api. All the things pointed out in the picturesque diagramographic can be done through it. Those less familiar with plsql code can take a look at how I built the excel and build on it.  If you’re concerned about performance, I’m not really. A download really goes very fast, about 1-2 seconds for ~200 records and 26 weeks: 200 rows *31 cells. It really does depend on having a performant select-statement, since the xlsx-builder is in-memory.
As a “bonus”, the generating code also holds some week-creation voodoo. The first week is retrieved, and further date arithmetic is done based on this initial date.

Serving as download

This may not be all that new to you if you had to offer files up for download before. Basically I call my package which generates the excel file and returns it as a blob. Then I set some http headers. The file is then served as a download through wpg_docload, and finally the page processing is called to a halt.

   l_excel BLOB;
   l_excel := demo_product_excel_pkg.create_excel
       p_week      => :P1_WEEKNR
      ,p_year      => :P1_YEAR
      ,p_nr_weeks  => :P1_WEEKS_AMOUNT
      ,p_user      => :APP_USER
   -- Prepare Headers
   htp.p('Content-Length: '||dbms_lob.getlength(l_excel));
   htp.p( 'Content-Disposition: filename="PRODUCT_PRODPLAN_' || '2012' ||'_'|| '35' || '.xlsx' || '"' );


   apex_application.g_unrecoverable_error := true;


The selected file is uploaded to the wwv_flow_files table, so the first thing that has to happen is to get this blob. It is then passed on to the package for processing. Finally, the record is removed from wwv_flow_files.


The processing is something I changed a lot to. The Alexandria plsql library has, as you may have noticed, an ooxml_util package with which you can read an xlsx file. However, it wasn’t 100% what I was looking for and not as fast as I’d wanted.
For example, the get_xlsx_cell_value function takes the excel file as a blob. This means that multiple calls will each take the blob, unzip it and get the value.
Function get_xlsx_cell_values can retrieve an array of cells, but requires you to define that array beforehand.
Function get_xlsx_cell_values_as_sheet has the same issue as the regular cell_values.
Function get_xlsx_cell_array_by_range is a lot more interesting: it’ll retrieve all cells starting from a cell to a cell, and give them back in an array.

Still I was not satisfied, so I went alternative.  I started by copying over the functions get_xml, get_worksheet_file_name and get_xlsx_column_number. This way I’d be able to open the excel blob and retrieve the xml just once, and do my reads on them. I also kept things simple. My excel sheet IS simple. There are numbers and text there, and that is all that matters to me.
See this diagram about the ooxml structure (all props to Odie – see the large image on his blog)
I decided to directly read the xml files I require, pump them in temp tables, and join those together to get my result. This means: read the rows and values, and fetch shared strings where required.
Another wonderful paintjob brought to you, by me.

  -- Process the sheet data
   -- get each row element and extract the column elements as an xmltype
   -- then process this column xml and extract the values
   INSERT INTO DEMO_LOAD_EXCEL_CELLS_GTT (rn, cref, cval, ctype)
   SELECT wsx.rn,, wsc.val, wsc.type
   FROM xmltable(xmlnamespaces(default ''),
                 passing l_xml
                 columns rn number path '@r' , cells xmltype path 'c') wsx,
        xmltable(xmlnamespaces(default ''),
                 passing wsx.cells
                 columns cn varchar2(30) path '@r'
                       , val number path 'v'
                       , type varchar2(10) path '@t') wsc;

   -- Process the shared strings xml
   -- get the shared strings extracted from the sst element
   -- Their order defines their index which is to be zero-based
   SELECT (rownum-1) ssi, wsss.val
   FROM xmltable(xmlnamespaces(default ''),
                 passing l_shared_strings
                 columns val xmltype path 'si') wss,
        xmltable(xmlnamespaces(default ''),
                 columns val varchar2(100) path 't') wsss;

Then these are put in the final table:

   INSERT INTO DEMO_LOAD_PRODPLAN (run_id, creator, filename, rowno, colno, value)
   SELECT l_run_id, l_user, l_file_name, c.rn, get_xlsx_column_number(regexp_replace(c.cref, '[0-9]', '')), nvl(s.cval, c.cval)
       ON c.ctype = 's' AND c.cval = s.strindex;

Which then results in:

Since the “width” of a table (columns) is not a dynamic things, it was never my intention of trying to achieve that. How many columns would you need? How will you iterate over it? Are you going to write checks for each column or even cell?
It made a lot more sense to store each value with a row and column number. And in my case I was able to change the original code which read line per line without a lot of impact. I can simply iterate over each row (line), and each value has a position (column): same as if you’d read a comma-separated line.

Ok, so that was not briefly. Sue me.

Enjoy! =)

Friday, August 31, 2012

Interactive Report Quick Filter: show all columns

Normally, the quick-filter column selection would not allow you to pick a non-displayed column to filter on. You could however apply a filter through the Actions > Filter menu.
If applying a contains-filter through the filter menu is no problem, why would it be through the quick filter? So i tapped into the code that fetches the columns for the dropdown and replaced it with an own call to a function returning me ALL the columns. That's it. The original code to apply the filters is still there, and it obviously has no issue applying an IR filter for a non-displayed column.

An example can be found at:

This IR has 2 columns not shown.
  • COMM is a HIDDEN column
  • DEPTNO is a non-displayed column

 Install and setup
To install the plugin, go to your application, select “Shared Components”, then go to “Plugins”. From there select “Import”, and browse to the sql file.
To use it on a page, create a dynamic action on your page for the “Page Load” event. As true action you can find the plugin under the “Initialize” group.


$("#apexir_SEARCHDROPROOT") is the looking glass icon. The generated onclick has to go and is replaced with an own handler.
In this handler an ajax call is made to an ajax function specified in the plugin. It doesn’t do too much:
  select 'All columns' D, '0' R, '0' C
    from dual
   union all
  select sys.htf.escape_sc(report_label) D, column_alias R, '1' C
    from apex_application_page_ir_col 
   where application_id = :APP_ID
     and page_id = :APP_PAGE_ID
     and interactive_report_id = !' ||l_ir_base_id
This bit of code will fetch all the columns defined for the IR, joined with the ‘All columns’ entry also found in the default dropdown. I got the markup from inspecting the ajax calls made by the IR when it retrieves the columns.
Ajax success handler:
function(data, status, obj){
                             p = obj;
                                gReport.l_Action = "CONTROL";
                                gReport.current_control = "SEARCH_COLUMN";
This is probably the most interesting. The returned object is stored in “p”, a global variable used by apex in its ajax processes.
gReport is the javascript variable created and instantiated by apex for the interactive report functionalities. L_Action and current_control are variables used in the ajax calls to determine what is asked for and what should happen. _Return is a function that would normally handle the IR ajax success callback.
So effectively I’m making the ir javascript think that it has just put out a call to retrieve the columns, and it should now handle the return (which is obj). The assignment of obj to p is still necessary because some checks are made against that variable aswell. From obj the responsetext is actually the most important.

Download: HERE

Thursday, August 30, 2012

Record navigation: refinement

First off: Dan McGhan, Thanks ;-) I wouldn’t have figured this one out by myself!

Second: my previous posts included the package APEX_IR. It has now been replaced by APEX_IR_PKG because APEX_IR is a new API in apex 4.2!

Finally, the code has been cracked :-) : it IS possible to retrieve the currently active interactive report through SQL! This is actually stored in a preference, residing in wwv_flow_preferences$, but also retrievable through apex_util.get_preference.
Code below is how the report id is retrieved:

IF p_report_id IS NULL THEN
      SELECT interactive_report_id
        INTO v_report_id
        FROM apex_application_page_ir
       WHERE application_id = p_app_id
         AND page_id = p_page_id;

      apex_debug_message.log_message('interactive report_id: '||v_report_id);

      lv_pref := apex_util.get_preference(p_preference => 'FSP_IR_'||p_app_id||'_P'||p_page_id||'_W'||v_report_id, p_user => p_app_user);
      lv_pref := substr(lv_pref, 1, instr(lv_pref, '_')-1);
      apex_debug_message.log_message(': '||lv_pref);

      SELECT report_id
        INTO v_report_id
        FROM apex_application_page_ir_rpt
       WHERE application_id = p_app_id
         AND page_id = p_page_id
         AND base_report_id = lv_pref
         AND session_id = p_session_id;
      WHEN no_data_found THEN
         apex_debug_message.log_message('no IR id could be found. Check input parameters. -> end');
   v_report_id := p_report_id;

The returned preference value could look like ‘3522014783654717____X’. We’re only concerned with the first value as this is the id of the currently active report. Note that the preference name uses a report id: this is the id of the interactive report itself and not of a saved or session instanced version. The id we get from the preference is the id of the SAVED version of the report that is instanced for the session of the user. If you take a look at how the ids and saved reports work below you’ll understand.

There is a lot of writing I have done already on these report structures, and there is more in the comments in the package, but here is another illustration:

Metadata for Interactive Reports
Interactive Report Id:
Region Id:

Metadata for saved reports and session instances of those
For example (from my sample app on

•    Reports with no session_id are saved reports.
•    All reports are based on the same interactive report
•    All reports have a unique id: REPORT_ID
•    Default (Primary) and alternative have an own application user. Named reports use the name of the creator evidently. Note that in my example app the report "Only Space Two" is a PRIVATE report! You'll see this of course when you log in with the test user.
•    My own instances of the reports are in the view aswell. You can identify them because they have a session_id, base_report_id and their report_type.
•    The base_report_id is the report_id of the report it is based on. "Report it is based on": the report_id of a saved version of the interactive report

Debugging from sql command line is still possible. There are two changes in the parameter list: p_app_user and p_report_id have been added. App_user is straightforward, but p_report_id is not. Leaving p_report_id blank from a command line to resolve the report_id is not possible. The preference fetch will only work in an apex session. You’ll have to retrieve the id yourself.
To do this, basically take the code block above, but query wwv_flow_preferences$  instead of using apex_util.
SELECT attribute_value
FROM apex_040100.wwv_flow_preferences$
AND preference_name like 'FSP_IR_130_P11%'

Use preference_name like 'FSP_IR_130_P11_W5555555555' if you have retrieved the base interactive report id (that’d be from apex_application_page_ir). And of course, substitute the application and page id!

   v_next      VARCHAR2(50);
   v_prev      VARCHAR2(50);
   v_top       VARCHAR2(50);
   v_bot       VARCHAR2(50);
   v_cur_tot   VARCHAR2(50);
   v_debug     VARCHAR2(5000);
   v_binds     DBMS_SQL.VARCHAR2_TABLE;
   v_binds_val DBMS_SQL.VARCHAR2_TABLE;
    v_binds(1) := 'P52_COMPROD_ID';
    v_binds_val(1) := '106672'; 

      p_app_id      => 190,
      p_session_id  => 4133013019922250,
      p_column_id   => 'ID', --column id for IR!
      p_value       => 153876,
      p_page_id     => 52, 
      p_report_id => 55555555555555,
      p_app_user => ‘YOUR_APEX_USERNAME’, --APP_USER in apex session
      p_use_session_state => FALSE,      
      p_binds       => v_binds,
      p_binds_val   => v_binds_val,
      p_next        => v_next,
      p_prev        => v_prev,
      p_top         => v_top,
      p_bot         => v_bot,
      p_cur_tot     => v_cur_tot,
      p_debug       => v_debug 

   dbms_output.put_line('v_next: '||v_next);
   dbms_output.put_line('v_prev: '||v_prev);
   dbms_output.put_line('v_top: '||v_top);
   dbms_output.put_line('v_bot: '||v_bot);
   dbms_output.put_line('v_cur_tot: '||v_cur_tot);
   dbms_output.put_line('v_debug : '||v_debug );
Download: HERE

Wednesday, May 9, 2012

Interactive Report filtering in Apex

Interactive Report filtering in Apex

I like interactive reports, and how they can put some serious query potential in a user’s hands with filters. But there are still some places where it falls a bit flat on its face. One such place is the popup you get when you click on the header of a column and search is enabled for that column.

For example, this report I have here is based on a table with only 27k records, and is meant as a replacement for an old Forms form. Users are used to working with IDs, and it is sometimes astonishing how they know these series of numbers by heart, or pick up new ones. So before, they went in the form, entered query mode, typed in the ID they had to look for (these IDs are everywhere!) and hit query: boom, here are your results. Find all addresses starting with 10? (which is a piece of the code referring to a country code) Enter ‘10%’.

What is the issue?

Now how do users like to work with the interactive reports? Well, probably the same way I do: least amount of clicking and follow the most intuitive path. Clickable headers? Yes please! So, I click this ID column header, and am presented the best things: sorting, hiding, breaking, and searching.

Great! Let’s start finding some things.


User: “What the …? Where is my id starting with 1054? Is this right? It IS right there isn’t it? Why is it not in the box? How do I have to search for it?”
Let’s scroll down when nothing is typed in; maybe it’ll be more downwards…

User: “Eh? I scrolled all the way to the bottom and my values aren’t there? HELP!”
Let’s take a look at another report.

Notice the warning up top ‘more than 10k rows’.
Now I click on the “Station” header:

*BONK*(Sound of user falling from chair or hitting head on the desk)

Which is when I swoop in and take care of this by telling them: “Yes I know, sorry. You can’t actually use the box when there is a certain amount of unique records trespassed. You’ll need to go through Actions > Filter > select your column and operator, enter your search value and then click Apply (no, don’t hit Enter because Enter doesn’t work, I know, sorry).”
And they usually hang up when they see the Filter screen, or at the very least the operator dropdown. No matter they’ve worked their whole life with a LIKE operator in forms, when you don’t actually ever write SQL you wouldn’t know nor care either.

For the record: yes, the filter screen works great. But I consider it a workaround in this case. My users don’t want to go through that filter step each time they need to look up a record. I can make them of course; say it is within the limitations of this tool. But I feel like a tool each time I need to explain this.
So the actual problem here is the amount of retrieved values, and even distinct values when an amount has been surpassed! I checked out what happened through Firebug and this lead me to the interactive reports javascript file. When the header is clicked, an ajax call is done to retrieve the settings for the selected column: sorting, hiding, breaking, filtering. If filtering is enabled, values are fetched and returned, and kept in the DOM. But not ALL values; and the retrieved values are STATIC. STATIC!

Breaking out my toolbox, I set out to change this. Thankfully, I had a large piece of my work done already thanks to my form record navigation based on an IR query. Since the searchbox has to find the distinct values of the selected column within the IR query, I could reuse some of that code.

Now when a popup is shown, I automatically hide the values scroll list since it is useless. The search box I then turn into a jQuery Autocomplete widget, with an Ajax source. This ajax callback searches the IR query for the distinct values, and narrows down the values to the entered value. I wrapped this all up in a nice plugin too! Let me point out one thing though: I can’t stop the ajax call that apex does from getting its own list of values since it is too baked in for me to do anything about it.

Demo Application

Found here:
Log in with apex_demo / demo. For example, click dname and select “Sales”. Then click ename: you will only see the names for dname=sales here. Also note the hiredate column: no range selection.
My settings: search behaviour: contains, filter behaviour: take existing filters into account, value fetching delay: 750, min length before fetching: 1

So how does it work now?

When the popup is opened, the scroll list will no longer be shown.

When the user types in some values, the autocomplete kicks in after a short delay, and a loading icon will be shown during the fetching of the values.

The values will then be shown. The first item in the dropdown will always be highlighted, so when a user presses enter, that value will be selected. Handy when they narrowed the list down to one match and want to select it without having to use the arrow keys or mouse.

As you can see, I now find my value I couldn’t find with the standard scroll list!

When I select the value, the filter is added, which is identical behavior to the standard list.

Adding the plugin to a page:



Name it something convenient, I name mine ‘IR AC on heading’


Select “Page Load” for event, so the plugin will run when your page has finished loading.
 True Action
Select the plugin from the actions list, you can find it grouped under ‘Execute’

As for settings:

Search Behaviour allows you to alter the way results are fetched. The standard ‘contains’ is like the default behavior, and searches for any occurrence of the search value in the values of the column. Like allows users to filter the results with “%” and “_”, see the next screen.

Filtering behavior
With Filtering on:

This also means that for instance my Ordernumbers would only display the orders available for line V22, and I can only pick a value from those. With all values shown I could pick any number, which could result in a no-data-found of course.

With filtering enabled: (542 is my first value!)

With filtering off: (3 is my first value!)

Value Fetching Delay: how long before the fetching kicks in after the first keystroke. This may be useful when the users usually type in a long series of characters, and you don’t want too many ajax calls. Useful when your data-set can be very large and lots of matches are possible.
Min length before fetch: how many characters have to be present before fetching will kick in.

Quick peek under the hood:

The javascript code which is run onload:
function create_autocomplete(ajaxIdent, fetchDelay, charAmount){
   // _Finished_Loading is called when the IR is done with a GET action
   // because the posts are synchronous in this report, and no events
   // or hooks are available, the best way to preserve functionality
   // yet extending it is to override the original function, yet 
   // keep the base code
   // apexafterrefresh cant be used since it is not triggered after
   // the widget ajax
   var or_Finished_Loading = gReport._Finished_Loading;
   gReport._Finished_Loading = function(){
       //overriden, but still have to call orinigal!
      //SORT_WIDGET is the widget containing all the header elements
         // hide the original dropdown box
         //let's do an initial search so the user has some initial 
         //options and doesn't have to start typing before being
         //prompted with some. Also great when there are not many
         //distinct options
         //The set minlength has to be disregarded for this though.
         var lSearchField = $("#apexir_search"), 
             lMinLength = lSearchField.autocomplete("option","minLength");
         //empty the search field, but dont trigger a search if length=0
         lSearchField.autocomplete("option","minLength", 5);
         lSearchField.autocomplete("option","minLength", 0);
         lSearchField.autocomplete("option","minLength", lMinLength);
         //the search field has to receive focus, otherwise the 
         //values list will not be hidden when the user clicks anywhere
         //but the popup. The popup would be hidden, but not the values.
         //Either case, it is good form to set focus here, a user would 
         //expect this behaviour.
      //alert('gReport finished loading');

   //prevent the dropdown from showing up when user starts typing

   //convert the item into an autocomplete item
      source: function(request, response){         
                {"p_request"      : "PLUGIN="+ajaxIdent,
                 "p_flow_id"      : $v('pFlowId'),
                 "p_flow_step_id" : $v('pFlowStepId'),
                 "p_instance"     : $v('pInstance'),
                 "x01"            : gReport.current_col_id.substring(7),
                 "x02"            : request.term,
                 "x03"            : $v('apexir_REPORT_ID')}, 
      select: function(event, ui){
         //when making a selection, a filter has to be added to the IR
         //this is the same code executed when a user selects a value
         //from the original dropdown box
         //ltemp array: column id, operator, search value, -, -
         //-> array for htmldb_get action
         var lTemp = [gReport.current_col_id,'=',ui.item.value,'',''];
      autoFocus: true, //automatically highlight first item
      delay: fetchDelay, // wait a bit before sending a request
      minLength: charAmount, // how many chars have to be present
      open: function(event, ui){
         //when the popup opens, the search is triggered and the ajax
         //will fire up. If a user however clicks somewhere so the 
         //popup is hidden again, the value list should not be 
         //displayed. Without this check, the list would be attached to
         //the document top left.
Special note on gReport._Finished_Loading : this is in override of the function in interactive reports. I do this because there is no hook for an after-loading event in the case of the popup widget. The “apexafterrefresh” event is only fired in some cases, and the widget is not one of them. Finished_Loading however is, and it is safe to override it. I do keep the original function alive, as I actually just want to extend it.

Provided code

You’ll find 2 plugin files in the zip: 1 with and 1 without package calls. If you can, put the package in your database as it’ll save on the amount of code in the plugin. The no-package-plugin has comments stripped out in the plsql code too to save on space, but that is why the source files are there.
(The package APEX_IR contains code also found in my record navigation plugin. I plan to change the package there to so it is up to date.)

Limitations and remarks

Date columns: date columns get values retrieved by their to_char values. The original popup with the date range restriction filters are not there. As of yet I’m still unsure of how to best solve this. One way would be to simply allow the standard box to show here instead of an autocomplete. I’d love to provide 2 date picker items so a ‘between and’ filter could be put up, but i can’t find out how to provide the second date unfortunately - for now.
Other column types: if you’re using things like blob, html tags, apex_item, etc in your report, this may all fall flat on the face.
Amount fetched: I only fetch up to 500 values, which should be more than plenty as I don’t believe a user will willingly scroll through a thousand entries: that’s what the progressive searching is for.
Newlines and double quotes: these are removed since they break the JSON return parsing.
Saved reports, aka multiple versions of an ir: I haven’t really tried this out, but i believe everything should work without problem. The only thing you might need to change is the dynamic action: if the dropdown is not returning the correct values after you changed to another saved report, then try changing the DA from “page load” to “after refresh” on the IR region.

Edit: computations also break the functionality. No solution for this yet.


Ah, this actually is a bit harder. I’d strongly suggest using Firefox + the Firebug plugin to trace the ajax call. Due to the most code being ajax calls, i can’t really put debug messages in. If the ajax call is not working (for example: the loading graphic just keeps going and going, it usually means an erroneous return), then first take a look at the response. It could be you find html for an error page there and you can glean the errorcode from there, since this’ll be the sqlcode from the plsql part of the callback.
Please note that when you click a header 2 ajax callbacks will be fired:

The first callback is the default call issued by apex when a header is clicked. This retrieves the settings for the column, and whether the sort/break/hide buttons should be shown. If you’d look at the response, you’d also see the default unique values list, which is not something i can stop since this is retrieved due to the ‘search allowed’ settings.

The second callback is the callback to the plugin. x01 is the column being searched on, x02 is the search value, x03 is the report id. The response would contain the possible (or at least up to 500) values in json format.
From there, i only have one advice: use the code from the package you can find in the source code folder. Look at procedure “get_column_ac_values”, and uncomment the “dbms_output.put_line” lines. Now run the code from a sql-command window, providing the correct parameters.
This is the spec for “get_column_ac_values”:

   FUNCTION get_column_ac_values
      p_app_id             IN  NUMBER,   -- application id (APP_ID)
      p_session_id         IN  NUMBER,   -- session id (APP_SESSION)
      p_column_id          IN  VARCHAR2, -- the column for which to get the next/prev vals
      p_value              IN  VARCHAR2, -- the current search value for p_column_id IF NULL THEN ALL
      p_page_id            IN  NUMBER,   -- Page number of the interactive report
      p_report_id          IN  NUMBER,   -- id of the selected IR, this can be null
      p_use_session_state  IN  BOOLEAN DEFAULT TRUE, -- true for using apex session state bind vars. If False p_binds+vals are to be filled.
      p_binds              IN  DBMS_SQL.VARCHAR2_TABLE, -- plsql table with bind variables
      p_binds_val          IN  DBMS_SQL.VARCHAR2_TABLE, -- plsql table with bind variables VALUES
      p_search_behaviour   IN  VARCHAR2 DEFAULT 'CONTAINS', -- LIKE, CONTAINS: how results are fetched
      p_filter_behaviour   IN  VARCHAR2 DEFAULT 'FILTER' -- FILTER, ALL: filtering of results

Some of the parameters you can, again, retrieve from the plugin ajax call, but the most important parameter is the p_report_id.
Here is an example plsql call to the procedure. One issue though: if an error is thrown then this won’t work in an Apex SQL Command window. it will only show the sql error message, and no dbms_output at all. I suggest running this code in a sql sheet in for example sql developer (which you can get at, it is a free tool).
Take note of parameter p_column_id: this is the name of the column searched for in the IR query. The actual name of the column in the query, so if you have aliased that column, then that alias will need to be provided, and not the base column name and neither the heading you can alter in the report attributes!
p_use_session_state: if you run this code from a sql command window, you won’t have session state for your items. If your query contains bind variables, you will need to provide the value for those! Set this parameter to FALSE, and provide the name of the bind vars in your query through parameter p_binds, and the values in p_binds_val. The relatation of binds and values in both these arrays is a 1-on-1 relation: the bind in position 1 in p_binds has the value stored in position 1 in p_binds_val.

  binds_table    DBMS_SQL.VARCHAR2_TABLE;
  values_table   DBMS_SQL.VARCHAR2_TABLE;
  v_retval       CLOB;
  -- If your query for example references a page item,
  -- then you will have to provide this to the query.
  -- If you have no bind vars in the query, you still
  -- need to provide the empty variables though.
  -- binds_table(1) := 'P10_SOME_FIELD';
  -- values_table(1) := 'SOMEVALUE';
  v_retval :=
     p_app_id              => 130,
     p_session_id          => 3214271424298960,
     p_column_id           => 'DELADR_ID',
     p_value               => '',
     p_page_id             => 11,
     p_report_id           => 3522014783654717,
     p_use_session_state     => FALSE,
     p_binds               => binds_table,
     p_binds_val           => p_binds_val,
     p_search_behaviour    => 'CONTAINS',
     p_filter_behaviour    => 'FILTER'
  dbms_output.put_line('return value: '||v_retval);
  -- the return should be a json-formatted string: [{}{}...{}]

Demo Application

Found here:
Log in with apex_demo / demo. For example, click dname and select “Sales”. Then click ename: you will only see the names for dname=sales here. Also note the hiredate column: no range selection.
My settings: search behaviour: contains, filter behaviour: take existing filters into account, value fetching delay: 750, min length before fetching: 1


you can find a zip with everything you need inside, here. I might put it up on sometime.

Update: i'm working on an advanced filter for date columns, which will show 2 datepickers in the popup when a date column is selected. It's looking good so far :-)