Single Article

APEX classic report "instant search"

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

Social Buttons

Comments

  • 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

        
  • Jari Laine 12 Jan 2014  

    Hi srinivas,

    Could you please create example to apex.oracle.com and share developer login details to workspace so I can take a look.
    Please create all items you need and place those to report where clause. Also some example data is required.

    Regards,
    Jari

        
  • srinivas 12 Jan 2014  

    Hi Jari,

    I tried to add search criteria as u shown in this blog post, but later I got the requirement to add the select list two text-fields and a go button for searching in the report. When I tried the instant search it gives me good result. Can we add the select list, two-text fields in the search bar and get the required result?

    If that is not the case related to your blog post can u just tell me your email so that I can contact u personally.

    Thanks in advance.

        
  • Jari Laine 12 Jan 2014  

    Hi srinivas,

    In query example I did give all items session state need to be NULL when report shows all rows. Also columns in where clause should not have NULL values.

     

    BTW, I do not see how your question relates to this blog post.

     

    Regards,
    Jari

        
  • srinivas 11 Jan 2014  

    Hi jari,

    Thanks for the reply, but unfortunately it didn't helped me in getting what I expected. When I changed my query as u suggested, when I refreshed the page with all the page items not set, I didn't have the report in the page instead it shows no data found. I want to show all the rows of the report if these page items are not set.

    and If any of these are set then the report should show as per the search criteria.

    select b.*
    from "#OWNER#"."Books"  b
    where i_type = 'CU' 

    is the classic report source

    P46_STATE, P46_CITY, P46_NAME and P46_GO are the page items.

    P46_STATE is a select list which also contain 'ALL' as a value- When they select this we just show all the rows. else should show as per the selection.

    Hope I am clear with my doubt. Please help me out to get this done. Thanks

        
  • Jari Laine 11 Jan 2014  

    Hi srinivas,

    Maybe something like this

    select b.*
    from "#OWNER#"."Books" b
    where b.i_type = 'CU'
    and state = COALESCE(:P46_STATE, b.state)
    and city  = COALESCE(:P46_CITY, b.city)
    and name  = COALESCE(:P46_NAME, b.name)

     

    BTW, I advice you not use SELECT * in report query. It is always best define column names.

    Regards,
    Jari

        

Global Right Column

Search

Archives

Subscribe in a Reader