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.
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
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.