8 Replies Latest reply on Mar 5, 2013 7:36 AM by EEG

    Search Box In Interactive Reports


      I'm using Apex 4.2.1 against Oracle 11gR2 and Oracle App Server (mod_plsql).

      I created an Interactive Report that includes the standard Search field along with the Actions menu.

      When users enter text within the Search field and click the GO button, rows are found in the report only if report columns containing this text are actually being displayed in the report. If such columns are de-selected by the user, then the search returns "no data found".

      This behavior can be confusing, especially since my users do not know before hand which report columns might contain the text entered in the Search field. A "no data found" message may be erroneous depending on which columns are displayed or not displayed in the report.

      I worked around this behavior by deliberately disabling (i.e., hiding) the standard Search field and replacing it with a regular Apex text field (say, P36_SEARCH). I then added a WHERE clause to my IR report query.

      And so, assume that my IR report is on the EMP table, my SQL query looks like:
      select *
       instr(to_char(EMPNO), nvl(:P36_SEARCH,to_char(EMPNO))) > 0 or
       instr(upper(ENAME),upper(nvl(:P36_SEARCH,ENAME))) > 0  or
       instr(to_char(HIREDATE,'dd-mon-yyyy'),nvl(:P36_SEARCH,to_char(HIREDATE,'dd-mon-yyyy'))) > 0  or
       instr(to_char(SAL), nvl(:P36_SEARCH,to_char(SAL))) > 0 or
       instr(to_char(COMM), nvl(:P36_SEARCH,to_char(COMM))) > 0 or
       instr(to_char(DEPTNO), nvl(:P36_SEARCH,to_char(DEPTNO))) > 0 
      This works. However, because this is not part of the IR functionality, users are un-able to save such searchs as part of a saved report.

      Here, then, are my questions.

      1) Is there some way to get the IR report to apply text entered into the Standard IR Search field against all report columns, whether they are displayed in the report or not?

      2) If not, then is there some way to allow users to save my ad-hoc solution as part of a saved report?

      Thank you.

        • 1. Re: Search Box In Interactive Reports
          1) You should check out the following method for filtering interactive reports (IR_ROWFILTER), perhaps in combination with page branching when you submit the search.
          See if this works for your requirements.

          1 person found this helpful
          • 2. Re: Search Box In Interactive Reports
            Vite DBA
            Hi Elie,

            to me, the way the quick search field in an IR works would be the desirable result, in that it only considers filtering on the columns that the user can see. I would consider it more confusing for a user if the search returns rows that don't appear to contain the search criteria. If a user wishes to filter on a hidden column, then that is still possible through the specific column filters.

            1 person found this helpful
            • 3. Re: Search Box In Interactive Reports
              Hi, Scott.

              Thank you for the link about IR_ROWFILTER. I was not aware of this.

              However, this is not really what I need. I'm not linking to an IR report. Rather, the IR report appears when the user clicks on a tab in my app. And so, I'm not linking to it via a url (as explained by the doc in your link). In any case, doing this is something that realistically only a developer would be doing. End users typically do not want to be bothered with this nor know how.

              Again, thank you for your help.

              • 4. Re: Search Box In Interactive Reports
                Hi, Andre.

                Thank you for your advice.

                I do, however, respectfully disagree.

                Think about this from the end users' point of view. Most end users expect ANY criteria (lwhether it be a quick search filter or an explicit filter) to check all columns in a report, not just those displayed. If the user receives a "no data found" message after invoking a quick search filter, what should they do: begin adding each of the hidden columns one at a time to see if the "no data found" message goes away? That would get very frustrating. And I believe it would be equally frustrating if users got inconsistent results based on the type of filter applied. Using an explicit filter searches all report columns while a quick search filter only checks displayed columns-- confusing for end users.

                This has been my experience and see these reactions from end users.

                In any case, that's my two cents on it.

                Again, thank you for your help. It is appreciated.

                • 5. Re: Search Box In Interactive Reports
                  Perhaps I wasn't clear, or misunderstood your needs.

                  The developer would build in functionality to accept the value entered from your suggested P36_SEARCH field, and after the user pressed Submit the branch would construct a URL that sets the IR_ROWFILTER (or similar item value) to communicate with the interactive report - as opposed to embedding the code in the where clause with NVLs.

                  The user need not have any idea what's going on behind the scenes, nor should be expected to. Often this feature is used underneath various buttons to automatically restrict an IR to certain set conditions.

                  1 person found this helpful
                  • 6. Re: Search Box In Interactive Reports
                    Hi, Scott.

                    Thanks very much for continuing to help me.

                    The difficulty in using your suggestion of the IR_ROWFILTER (and the similar "IR<operator>_<target column alias>") feature is cleanly summarized by your own words: restrict an IR to certain set conditions.

                    Therein is my problem. Users want to change their reports: add/modify/delete criteria, add highlighting based on conditions, add report grouping, and a other things that an IR report offers. They do not want a developer setting up conditions that will be invoked via the IR_ROWFILTER feature.

                    And so, my IR reports need that flexibility. That's why our end users like IR reports over Apex "classical" type reporting.

                    I hope that I'm making my needs clear. Or, perhaps I've misunderstood what you've shared?

                    Thanks very much.

                    • 7. Re: Search Box In Interactive Reports
                      Then perhaps you're between a rock and a hard place, and you're limited to educating the users regarding the out-of-box functionality - and if they want to limit unseen data they need to use an explicit filter for a specific column.
                      • 8. Re: Search Box In Interactive Reports

                        Yes, I suppose you're correct about building explicit filters on hidden columns and educating my users.

                        Thank you for your help.