Skip to Main Content

Tuesday, November 27, 2012

APEX classic report "instant search"

APEX Tips and Tricks

Here is how trigger APEX classic report refresh when user stop entering to text field.

First create classic report to your page.

In this example I have used following query joining EMP and DEPT tables:

SELECT e1.empno
    ,e1.ename
    ,e1.job
    ,e2.ename AS mgr
    ,e1.hiredate
    ,e1.sal
    ,e1.comm
    ,d.dname
    ,d.loc
FROM emp e1
LEFT JOIN emp e2
    ON e1.mgr = e2.empno
JOIN dept d ON e1.deptno = d.deptno

When you create report set "Enable Search" to "Yes" on wizard "Report Attributes" page and select columns for search.

List of values definition

Go edit report attributes and add to "Page Items to Submit" text item created by report wizard.

List of values definition

Edit page attributes and add page JavaScripts Function and Global Variable Declaration:

var gTimer;

and to Execute when Page Loads:

$("#Px_REPORT_SEARCH").keypress(function(e){
 return e.keyCode!==13;
});

Replace Px_REPORT_SEARCH with text item name created by report wizard.

List of values definition

Create dynamic action:

  • Name: Report instant search
  • Event: Key Release
  • Selection Type: Item(s)
  • Item(s): {select text item created by report wizard}
  • Condition: JavaScript expression
  • Value:
    this.browserEvent.keyCode!==13
  • Action: Execute JavaScript code
  • Fire On Page Load: False
  • Code:
    var lTrg=$(this.affectedElements);
    clearTimeout(gTimer);
    gTimer=setTimeout(function(){
     clearTimeout(gTimer);
     lTrg.trigger("apexrefresh");
    },900);
  • False Action: Execute JavaScript code
  • Fire On Page Load: False
  • Code:
    clearTimeout(gTimer);
    $(this.affectedElements).trigger("apexrefresh");
  • Selection Type: Region
  • Region: {select your report region}

Now run page and type to search text field. After small delay report is refreshed and shows your search result.

See working example.

Sample is also available for download.

Pagination

Comments

  • Jari Laine 18 Jan 2021

    There isn't similar highlight feature in interactive report or interactive grid.

    You could do same with custom JavaScript function that you execute using dynamic action after report refresh.

    Regards,
    Jari

  • Tom 18 Jan 2021

    Hi Jari,
    Thx!
    Is it possible to do the same in Interactive report? As I see this highligth option is missing there.
    Thanks,
    Tom

  • Jari Laine 18 Jan 2021

    Hi Tom,

    You need go edit report columns. In report column attributes find Highlight Words. Place to field your search item substitution like below

    &Px_MY_SEARCH_ITEM.

    You need do this for all columns where you like highlight search string

    Regards,
    Jari

  • Tom 18 Jan 2021

    Hi Jari,
    Can you tell me how can I achieve the text highlithing in this instant search? I mean, the text color change to red color, based on the search text value. I dont find it in the sql source code.
    Thanks and Regards,
    Tom

  • MAHENDRA 12 Dec 2020

    this example i shown from other website, it is nice but everthing happened after ENTER key,
    it should be done on CHARACTER ENTERED , MEANS ON KEYPRESS
    THANKS
    MAHENDRA - INDIA

  • Saeed 14 Jul 2014

    Dear Jari,

    Thank you for solving my problem.

    Best regards,

    Saeed.

  • Jari Laine 14 Jul 2014

    Hi Saeed,

    Change your dynamic action event scope to dynamic.

    Regards,
    Jari

  • Saeed 14 Jul 2014

    Dear Jari,

    I know it.Unfortunately, I typed wrongly in this post.Please,check this sample completely.

    Best regards,

    Saeed.

  • Jari Laine 14 Jul 2014

    Hi Saeed,

    JavaScript is case sensitive that's why there is no function called "Alert". It must be all lower case. Try change dynamic action code to

    alert('selected');

    Regards,
    Jari

  • Saeed 14 Jul 2014

    Dear,

    Thank you for your sample, but I have a problem when I added checkbox item to classic report and refreshed report with a ajax.I used this query in your sample:

    SELECT 
            Apex_Item.CheckBox2(p_idx        => 2, 
                                p_value      => e1.empno||','||e1.ename, 
                                p_attributes => 'class="check_select"',
                                p_item_id    => 'f02_'||lpad(ROWNUM,4,'0')) " ",
    e1.empno
    ,e1.ename
    ,e1.job
    ,e2.ename AS mgr
    ,e1.hiredate
    ...

    In addition, I create a dynamic action:

    Name: Checkbox Selected

    Event: Click

    Selection Type: JQuery Selector

    jQuery Selecto : .check_select

    Condition: JavaScript expression

    Value:

    $(this.triggeringElement).prop('checked')

    Action: Execute JavaScript code

    Fire On Page Load: False

    Code:

    Alert('selected');

    Next I run page and type to search text field. After small delay report is refreshed and shows the search result then I selected the one of the checkbox item but the dynamic action "Checkbox Selected" does not work.how can I solve it.

    Best regard,

    Saeed