14 Replies Latest reply: May 16, 2012 3:48 AM by VC RSS

    Interactive report search field with autocomplete , is it possible ?

    917038
      Hi all,
      As you know the APEX provides an in-built search for interactive reports . I would like to know if its possible to add autocomplete feature to this ?

      Thanks & regards
      Umer
        • 1. Re: Interactive report search field with autocomplete , is it possible ?
          VC
          Nice1 wrote:
          Hi all,
          As you know the APEX provides an in-built search for interactive reports . I would like to know if its possible to add autocomplete feature to this ?

          Thanks & regards
          Umer
          Hi Umer,

          You can't implement this directly in an interactive report because you won't know how many number of columns you will have in the report that should show up in the auto-complete.

          The workaround is creating an apex auto-complete item above the IR report region with your own sql matching to the actual interactive report column values and then on change of that you could set the value into IR search field or even trigger the search.

          So on-change of custom auto-complete just call

          *$('#apexir_SEARCH').val(this.value);*
          gReport.search('SEARCH')


          Thanks
          • 2. Re: Interactive report search field with autocomplete , is it possible ?
            917038
            I understood only some part of your answer . If you dont mind can you tell me the steps since I am a newbie to APEX ?

            Thanks & Regards
            Umer
            • 3. Re: Interactive report search field with autocomplete , is it possible ?
              VC
              Hi Umer,

              Here you go http://apex.oracle.com/pls/apex/f?p=46417:4
              login as test/test

              Thanks
              • 4. Re: Interactive report search field with autocomplete , is it possible ?
                917038
                Thanks cherukuri , that will be fine . I dont know where to paste my code and also I dont know Javascript
                • 5. Re: Interactive report search field with autocomplete , is it possible ?
                  VC
                  Okay then,

                  The best place to start learning the JavaScript is http://www.w3schools.com/js/

                  And this may help you understand how to incorporate JavaScript into apex http://docs.oracle.com/cd/E17556_01/doc/user.40/e15517/app_comp.htm#HTMDB05013

                  Thanks
                  • 6. Re: Interactive report search field with autocomplete , is it possible ?
                    917038
                    I have done that part , thanks VC. And 1 more ques , if someone enters a query in the auto-complete filed which I have not defined , how can I trigger a query for the same ?
                    I tried without adding any extra lines of code , but its not working
                    • 8. Re: Interactive report search field with autocomplete , is it possible ?
                      VC
                      Nice1 wrote:
                      I have done that part , thanks VC. And 1 more ques , if someone enters a query in the auto-complete filed which I have not defined , how can I trigger a query for the same ?
                      Sorry, I did not understand this point, when u create a textfield wtih auto-complete you must specify the SQL just like an select list
                      I tried without adding any extra lines of code , but its not working
                      When you say not working?
                      -- you have to tell if there is any error
                      -- what is not working?

                      Can i see your example?

                      Edited by: VC on May 9, 2012 8:58 AM
                      • 9. Re: Interactive report search field with autocomplete , is it possible ?
                        917038
                        The auto-complete field doesnt work in the following situtation
                        1)Consider that I have defined only the following items in the select list using sql
                        cat
                        rat
                        bat

                        Now I am a entering a word 'sat' ,it is not in the select list , and now I press enter for search . No search happens , eventhough there are words sat in my report.

                        The auto-complete works only for already defined items using select list .

                        How can I overcome this ?
                        • 10. Re: Interactive report search field with autocomplete , is it possible ?
                          VC
                          try this http://apex.oracle.com/pls/apex/f?p=9904351712:8:0:::::
                          • 11. Re: Interactive report search field with autocomplete , is it possible ?
                            917038
                            do i need to install jquery for this ? or is it inbult , my apex version is 4.0.2.00.08
                            • 12. Re: Interactive report search field with autocomplete , is it possible ?
                              VC
                              Hi,

                              You don't have to integrate jquery on yours, because it comes with apex 4.x

                              To implement the autocomplete on interactive report, follow the steps in the bottom of the screen (it is a custom solution/workaround)

                              Thanks
                              • 13. Re: Interactive report search field with autocomplete , is it possible ?
                                917038
                                Its nor working for me .

                                1) I created an textfiled item- P2_REPORT_SEARCH
                                2) added an interadctive report with the source as
                                SELECT * FROM (
                                select     "WSR"."ID" as "ID",
                                "WSR"."USER_ID" as "USER_ID",
                                "WSR"."FROM_DATE" as "FROM_DATE",
                                "WSR"."TO_DATE" as "TO_DATE",
                                "WSR"."ACTIVITIES" as "ACTIVITIES",
                                "WSR"."PROJECT" as "PROJECT",
                                'delete' from
                                "WSR" "WSR"
                                where
                                ("WSR"."USER_ID" = NVL(v('APP_USER'),USER) OR NVL(v('APP_USER'),USER) = 'ADMIN' ) AND (
                                "WSR"."FROM_DATE"=to_char(sysdate+ (2-to_char(sysdate,'D')))

                                ))

                                where (
                                instr(upper("USER_ID"),upper(nvl(:P2_REPORT_SEARCH,"USER_ID"))) > 0 or
                                instr(upper("ACTIVITIES"),upper(nvl(:P2_REPORT_SEARCH,"ACTIVITIES"))) > 0
                                instr(upper("PROJECT"),upper(nvl(:P2_REPORT_SEARCH,"PROJECT"))) > 0
                                )
                                3) added the folowing query to html header ,
                                <script>
                                apex.jQuery('#report_' + pId + '_catch').trigger('apexbeforerefresh', pId);

                                var l_URL =
                                'p='+$v('pFlowId')+':'+$v('pFlowStepId')+':'+$v('pInstance')+':FLOW_PPR_OUTPUT_R'+pId+'_';
                                if (pRefreshMode) {
                                if (pRefreshMode === 'current'){
                                // do nothing to url here
                                } else if (pRefreshMode === 'reset') {
                                l_URL += 'reset_R_' + pId;
                                }
                                } else {
                                if (!!pSort) {
                                l_URL += pSort+'::RP&fsp_region_id='+pId;
                                } else {
                                l_URL +=
                                'pg_R_'+pId+':NO&pg_max_rows='+pMax+'&pg_min_row='+pMin+'&pg_rows_fetched='+pFetched;
                                }
                                }


                                l_URL += ':NO::P2_REPORT_SEARCH:' + $('#P2_REPORT_SEARCH').val();

                                var lRequest = new apex.ajax.url(l_URL,
                                function(pResponse){
                                var l_s = pResponse.readyState;
                                var l_Id = lRequest.report_id;
                                if(l_s == 1){
                                }else if(l_s == 2||l_s == 3){
                                }else if(l_s == 4){

                                var lTemp = $u_js_temp_drop();
                                apex.jQuery('#report_'+l_Id+'_catch').attr('id', 'report_'+l_Id+'_catch_old');
                                apex.jQuery(lTemp).html(pResponse.responseText);
                                apex.jQuery('#report_'+l_Id+'_catch_old').replaceWith(apex.jQuery('#report_'+l_Id+'_catch'));
                                apex.jQuery(lTemp).empty();
                                apex.jQuery('#report_' + pId + '_catch').trigger('apexafterrefresh', pId);

                                }else{return false;}
                                }
                                );
                                lRequest.report_id = pId;
                                lRequest._get();
                                return;
                                }

                                </script>
                                4) a dynamic action:
                                >
                                Name: Instant Search

                                Event: Key Release

                                Items: P2_REPORT_SEARCH

                                Condition: No Condition

                                Action: Refresh

                                Fire when event result is: True

                                Selection type: Region

                                Region: WSR(Interactive reoprt region)
                                5) When I enter the query in P2_REPORT_SEARCH , no change happens in the result set of IR

                                Edited by: Nice1 on May 16, 2012 1:12 AM
                                • 14. Re: Interactive report search field with autocomplete , is it possible ?
                                  VC
                                  Hi Umer,

                                  To make it simple I have fixed my page to search with the extra values, this works for non existing values in autocomplete on enter key.

                                  Please look for the code and item attribute settings at the bottom

                                  http://apex.oracle.com/pls/apex/f?p=46417:4
                                  login as test/test

                                  Thanks