OraFAQ Forum: Reports & Discoverer » print mulitple copies of specific entries

Home » Developer & Programmer » Reports & Discoverer » print mulitple copies of specific entries (forms 6i/report 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

print mulitple copies of specific entries [message #429254]

Mon, 02 November 2009 16:18

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.

Report message to a moderator

Re: print mulitple copies of specific entries [message #429635 is a reply to message #429254]

Wed, 04 November 2009 16:30

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

Littlefoot
Messages: 21808
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:

If PAR_DUPLICATE = 'Y', this is the result:

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

Attachment: rep_dup_n.PNG
(Size: 3.05KB, Downloaded 1522 times)
Attachment: rep_dup_y.PNG
(Size: 5.09KB, Downloaded 1509 times)

Report message to a moderator

Re: print mulitple copies of specific entries [message #429729 is a reply to message #429658]

Thu, 05 November 2009 08:19

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 05:38:25 CDT 2024