Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Search Box In Interactive Reports

EEGFeb 27 2013 — edited Mar 5 2013
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
This post has been answered by Scott Wesley on Mar 3 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 2 2013
Added on Feb 27 2013
8 comments
602 views