DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Parameter Reports

  1. #1
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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.

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    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.

  3. #3
    Join Date
    Jun 2000
    Location
    French Polynesia
    Posts
    16

    Restrict the where clause

    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.

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    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

  5. #5
    Join Date
    Jun 2000
    Location
    French Polynesia
    Posts
    16

    Remove a lexical part of a SQL order

    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]

  6. #6
    Join Date
    Apr 2001
    Posts
    51

    Smile

    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;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width