This discussion is archived
8 Replies Latest reply: Mar 4, 2013 11:36 PM by EEG RSS

Search Box In Interactive Reports

EEG Newbie
Currently Being Moderated
Hi.

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 *
from
(
select
empno,
ename,
hiredate,
sal,
comm,
deptno
from
emp
)
where
(
 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.

Elie
  • 1. Re: Search Box In Interactive Reports
    scott.wesley Guru
    Currently Being Moderated
    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.
    http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35125/bldapp_rpt_int.htm#BABDHIDJ
    See if this works for your requirements.

    Scott
  • 2. Re: Search Box In Interactive Reports
    Vite DBA Pro
    Currently Being Moderated
    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.

    Regards
    Andre
  • 3. Re: Search Box In Interactive Reports
    EEG Newbie
    Currently Being Moderated
    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.

    Elie
  • 4. Re: Search Box In Interactive Reports
    EEG Newbie
    Currently Being Moderated
    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.

    Elie
  • 5. Re: Search Box In Interactive Reports
    scott.wesley Guru
    Currently Being Moderated
    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.

    Scott
  • 6. Re: Search Box In Interactive Reports
    EEG Newbie
    Currently Being Moderated
    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.

    Elie
  • 7. Re: Search Box In Interactive Reports
    scott.wesley Guru
    Currently Being Moderated
    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
    EEG Newbie
    Currently Being Moderated
    Scott,

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

    Thank you for your help.

    Elie

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points