Home » Developer & Programmer » Reports & Discoverer » print mulitple copies of specific entries (forms 6i/report 6i)
print mulitple copies of specific entries [message #429254] Mon, 02 November 2009 16:18 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

I am not sure if this is a forms or report issue. I created a label report. I have a parameter form with checkboxes where the user select the specific product they want to print on the label report, this works fine I am using &pWhere in order to pull the specific products. Now, I need to have the option of printing multiple copies of a specific product, so really create duplicate labels. How do I go about doing this, if the user controls which products they want duplicate labels for.
Re: print mulitple copies of specific entries [message #429635 is a reply to message #429254] Wed, 04 November 2009 16:30 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Im trying another approach, each row has a print label button and I am trying to use the COPIES parameter to create the duplicates but nothing is happening on the screen, it just displays the first record. I am going to try and set it to go directly to the printer see if that helps. To be continued...

ADD_PARAMETER(pl_id,'COPIES',TEXT_PARAMETER,:control.Qty2Print);

[Updated on: Wed, 04 November 2009 16:32]

Report message to a moderator

Re: print mulitple copies of specific entries [message #429658 is a reply to message #429635] Thu, 05 November 2009 01:22 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If "multiple" copies means a single duplicate, then you might try something like this example, based on Scott's DEPT table:
  • create a report based on a query which uses UNION ALL set operator:
    select deptno, dname, loc
    from dept
    union all
    select deptno, dname, loc
    from dept
    &lex_where
    order by deptno
    I have created a lexical parameter (lex_where) that will be used to distinguish whether you want a duplicate or not. In order to do that, I'll need another parameter: PAR_DUPLICATE, character (Y/N).
  • Lexical parameter will get its value in the After Parameter Form trigger:
    function AfterPForm return boolean is
    begin
      if :par_duplicate = 'Y' then
         :lex_where := 'WHERE 1 = 1';
      else
         :lex_where := 'WHERE 1 = 2';
      end if;
      return (TRUE);
    end;
    What does that mean? If "1 = 1", I'll return some records. If "1 = 2", return nothing. Here's an example:
    SQL> select * from dept
      2  where deptno = 10
      3    and 1 = 1;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
    
    SQL> select * from dept
      2  where deptno = 10
      3    and 1 = 2;
    
    no rows selected
    
    SQL>

    The idea is: create an union of these two SELECT statements in order to create either one ("1 = 2") or two ("1 = 1") records as the result:
    SQL> select * from dept
      2  where deptno = 10
      3  union all
      4  select * from dept
      5  where deptno = 10
      6    and 1 = 2;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
    
    SQL> select * from dept
      2  where deptno = 10
      3  union all
      4  select * from dept
      5  where deptno = 10
      6    and 1 = 1;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
    
    SQL>
    As "duplicating" condition depends on an input parameter (PAR_DUPLICATE), the easiest way to create a "dynamic" WHERE clause is to use a lexical parameter.

    OK, now we have a duplicate. Let's try to put it into the report.
  • Create a default tabular layout (that's easy). Run the report.
    If PAR_DUPLICATE <> 'Y', the result is as follows:
    ./fa/6961/0/

    If PAR_DUPLICATE = 'Y', this is the result:
    ./fa/6962/0/
    It appears that duplicates ARE created.

Now, I don't know whether this suits your needs or not; this *might* work if you really need a duplicate, but - if you need multiple copies, huh, that's a little bit more difficult. Didn't try, but here's how I imagine the process:
  • create a single SELECT statement
  • PAR_MULTIPLE_COPIES would be a numeric parameter whose value should represent number of copies
  • in a loop (looping from 1 to PAR_MULTIPLE_COPIES value), create a lexical parameter that would contain a whole SELECT statement (including UNION ALL at the beginning).
Re: print mulitple copies of specific entries [message #429729 is a reply to message #429658] Thu, 05 November 2009 08:19 Go to previous message
deahayes3
Messages: 203
Registered: May 2006
Senior Member

Thanks soooo much for the reply, I am going to try the latter of your reply because "multiple" means more than one duplicate. The user will select the number of copies they need for a specific item it can be 1, 3, 5 etc...

Adding the following parameter also works if it goes directly to the printer (it will not display the duplicate copies to the screen)
 ADD_PARAMETER(pl_id,'COPIES',TEXT_PARAMETER,:control.Qty2Print);

So thanks, now I have to just choose between the two.

form parameter :
Begin
p_lex_where := 'UNION ALL select deptno, dname, loc
     from dept
     where dept_id = :dept_id';

  FOR X IN 1.. PAR_MULTIPLE_COPIES LOOP
   
     p_lex_where:= p_lex_where||' ';
  END LOOP;  
 
end;

[Updated on: Thu, 05 November 2009 08:48]

Report message to a moderator

Previous Topic: Matrix report or accross group
Next Topic: Database connection through report without prompting
Goto Forum:
  


Current Time: Fri Dec 09 09:34:11 CST 2016

Total time taken to generate the page: 0.23198 seconds