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