Home » Developer & Programmer » Reports & Discoverer » Needs a Solution (Report, 6i, XP)
Needs a Solution [message #343347] Wed, 27 August 2008 00:39 Go to next message
myclassic
Messages: 127
Registered: December 2006
Location: Pakistan
Senior Member
Dear

I have got three type of employee types in a table
Administration
Doctors
Staff

...... Administration and Doctors are considered as Officers
and Staff is Staff.

I want to generate a report of employees while passing a parameter of either Staff or Officer.

Staff is no problem. But Officers are devided into two groups (administration and Doctors)......

how to get this report????????
remember parameter will be only one(Staff or Officer)....


regards.
Re: Needs a Solution [message #344150 is a reply to message #343347] Thu, 28 August 2008 22:20 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

show your employees structure
and in what way you have stored employees type and give me the sample data.

wbr
kanish
Re: Needs a Solution [message #344420 is a reply to message #343347] Fri, 29 August 2008 14:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Let's assume that table looks like
SQL> select * from test order by job;

ENAME      JOB
---------- --------------
FORD       Administration
SCOTT      Administration
BLAKE      Doctor
JONES      Doctor
MILLER     Staff
KING       Staff
TURNER     Staff
ADAMS      Staff
JAMES      Staff
MARTIN     Staff
WARD       Staff
ALLEN      Staff
SMITH      Staff
CLARK      Staff

Create two parameters; first one will be an "ordinary" parameter (let's call it "par_job", character, length = 1), while another one will be lexical parameter (let's call it "lex_where" (character, length = 100)).

Query will look like this:
select employee_name, job
from test
&lex_where

Lexical parameter will get its value in the AFTER PARAMETER FORM trigger, which will look like this:
if :par_job = 'S' then
   :lex_where := 'where job = ''Staff''';
elsif :par_job = 'O' then
   :lex_where := 'where job in (''Administration'', ''Doctor'')';
end if;

Run the report; if "par_job" parameter value is 'S', you'll get all employees whose job column equals "Staff"; if parameter value is 'O', you'll get Administrators and Doctors. This part of the lexical parameter value - regarding values you've mentioned - might be of two kinds: the first one is already suggested:
where job in ('Administration', 'Doctor')
and the second one might be
where job <> 'Staff'
Use whichever you prefer.
Re: Needs a Solution [message #344671 is a reply to message #344420] Sun, 31 August 2008 21:39 Go to previous messageGo to next message
myclassic
Messages: 127
Registered: December 2006
Location: Pakistan
Senior Member
Dear Sir,

Amazing......Thanks a Lot.......

this is what I needed.

Regards.
Re: Needs a Solution [message #346555 is a reply to message #343347] Mon, 08 September 2008 19:15 Go to previous messageGo to next message
dude4084
Messages: 221
Registered: March 2005
Location: Mux
Senior Member
Wonderful work by Littlefoot.

But i think it can be achieved like this as well (without using lexical parameter).

select employee_name, job
from test
where ((:par_job='O' and JOB!='Staff') or  
      (:par_job='S' and JOB='Staff') )



-Dude
Re: Needs a Solution [message #346564 is a reply to message #346555] Mon, 08 September 2008 21:37 Go to previous message
myclassic
Messages: 127
Registered: December 2006
Location: Pakistan
Senior Member
Nice work dear.

this is rather more simple than previous one.

thanks a lot.

regards.
Previous Topic: SerialNo
Next Topic: Can' connect to Oracle Application for Discoverer
Goto Forum:
  


Current Time: Sun Dec 11 00:11:40 CST 2016

Total time taken to generate the page: 0.11711 seconds