Parameter Reports

[irehman]

I'm using Reports 6i.

Example:

I have a report with query
Select * from emp
where deptno = :P_1;

My parameter is based on following query.

Select distinct deptno from emp;


Report runs just fine. Qusetion is when Parameter forms comes up I can select deptno 10,20, or 30, but what I want is this not just deptno 10,20, or 30 come up but it also give me option to select "ALL" so I can also select all the deptno as well.

How can I do that, I'm pretty new o DEV 2000

Thanks in Advance.

[kmesser]

Your query to populate the list is:

select distinct deptno from emp;

How about:

select distinct deptno from emp union select 0 from dual;

then in your after parameter form trigger, check for deptno of 0. If that is selected, remove the part of your where clause which restricts the output to a specific dept.

[rbouissou]

in order to remove the where clause in a report as "where deptno = :P_1" use a lexical parameter that will be nullified in cas of a global extraction.

[irehman]

Hello rbouissou
Can you give me steps to do the following

in order to remove the where clause in a report as "where deptno = :P_1" use a lexical parameter that will be nullified in cas of a global extraction

Thanks

[rbouissou]

Create a user parameter X with the initial value as: where deptno = :P_1, the type will be VARCHAR2 or CHAR and this parameter won't be displayed in the parameter form.
In the AFTER PARAMETER FORM trigger or the BEFORE REPORT trigger check the value of DEPTNO entered in the parameter form.

In the cas EPTNO=0
assign null to the parameter X as :X := NULL;

In the other cases
do nothing

the query will be written this way:
select * from EMP &X

&X is a lexical reference to the prameter X
&X will be substitued by where deptno = :P_1.

Good luck, bye Rayd



[Edited by rbouissou on 04-19-2001 at 02:16 PM]

[miritika]

if u want to display the records for all the deptno then just modify your select statement by writing

select * from emp where deptno = nvl(:d,deptno);

this implies that if no deptno is selected it will display the records for all the deptno.

instead of writing

select * from emp where deptno = :d;