Print page 1, page 2, page 1, page 2 even when page 1 has a repeater table?

[pwchristian]

Hey guys, long time reader first time poster.  I'm very stumped and would really appreciate some advice.

I have a PDF invoice report printed double-sided that has a front page with order header and detail info, and a back page with info on how to return items.  The report has two SQL queries, a header query (order info), and a detail query (order lines with item ordered, qty ordered, etc).   

The front page has a header with a repeater table with header SQL.  The page body has a repeater table with header SQL, and a list with the order lines (detail SQL), with master detail relationship defined and max rows = 10.  The footer has a repeater table with the header SQL. The back page is just text. 

I created a page set with query as Header SQL, and grouped by order number.  Both "detail" pages have query as Header SQL, then created a master detail relationship to connect the Header SQL to itself by order number.  This is the only way it'd work.

If the order has 20 order lines, it will print the front page double sided (10 rows on each page), then the back invoice page, then I have a conditional block that prints a blank page. 

They now want the back of the invoice on every side of the front page.  So if an order has 30 lines, print front page with first 10 order lines, then back invoice page, then front page with next 10 lines, then back invoice page, then front page with third 10 lines, then back invoice page. 

Is this possible?!?!?! I have wasted many days of my life trying to think of a solution. It seems like Cognos thinks of the front invoice page as 1 page that cannot have pages inbetween because its a repeater. 

[dougp]

...and I'll bet it won't save them days of work time in a year.

So in the PDF, you'll have this...

page 1  order lines 1-10
page 2  info page
page 3  order lines 11-20
page 4  info page
page 5  order lines 21-30
page 6  info page

...then you print it double-sided?

Or is it more like this...

page 1  order lines 1-10
        info page
page 2  order lines 11-20
        info page
page 3  order lines 21-30
        info page

...?

Is the info page the same for every order?
Do page numbers matter?  Or could you get by with an order number and total lines in the header, then order line numbers on the lines?  Or do page numbers matter, but only the order lines pages count?

Here's what you could do:
Print a ream of one-sided info pages.  Let's call this, invoice paper.
When someone needs to print invoices, they remove the blank paper from the printer and insert the invoice paper.  Some training will be needed because it needs to be printable on the blank side (not over the instructions) and right-side-up.
Print the order lines pages, one-sided, to the blank side of the invoice paper.

It boggles the mind that people think every problem needs to be solved by using an electronic gadget.  Fingers were designed for more than clicking mouse buttons.

[pwchristian]

I appreciate the response dougp and your solution would definitely work however the end users are in a different country and we already have problems training them!  There's an English invoice report and a French invoice report depending on the destination so the user would have to swap out paper depending on order batch. But the back return info page is the same for every page of it's respective report.

Your 2 PDF options look similar to me. 

If we have:
Order 1 with 10 order lines
Order 2 with 30 order lines
Order 3 with 10 order lines

It'd need to print physically double-sided:
Front of Page 1:  Order 1 1-10 order lines
Back of Page 1:  Invoice Page
Front of Page 2:  Order 2 1-10 order lines
Back of Page 2: Invoice Page
Front of Page 3: Order 2 11-20 order lines
Back of Page 3: Invoice Page
Front of Page 3: Order 2 21-30 order lines
Back of Page 3: Invoice Page
Front of Page 4: Order 3 1-10 order lines
Back of Page 4: Invoice Page


It'd be very helpful if the order line pages were numbered so orders can easily be separated to go inside each carton. If it's not possible, I can write logic in the SQL to display which page number it is if I cannot use the PageNumber function in Cognos and have it work correctly for what I'm trying to accomplish.

Again, thanks for taking the time to advise.  I work with Manhattan Associates (warehouse management software) and definitely not an expert with Cognos.  This was a very complicated report for my Cognos skillset.

[dougp]

I don't think this bulletin board will allow me to paste or upload a report spec, so I'll try to explain this.  I did it using GOSales.  Here's my process:

create a report
use the GO Sales (query) package

Create a query named PageFront.
  Add a query calculation:  page = 'front'
Create a query named PageBack.
  Add a query calculation:  page = 'back'
Create a query named Page.
  Add a union to Page, then add PageFront and PageBack to the union.
  Add page (from the union)
  Add a query calculation:  join = 'a'
Create a query named Country.
  Add [Sales (query)].[Branch].[Country]
  Add a query calculation:  page = 'front'
  Add a query calculation:  join = 'a'
Create a query named CountryPage
  Add a join to it and join Country and Page on join = join.
  Add [Country].[Country]
  Add [Page].[page]
Create a query named Detail.
  Add [Sales (query)].[Branch].[Country]
  Add [Sales (query)].[Branch].[City]
  Add a query calculation:  page = 'back'

Create a string variable Page = [CountryPage].[page] with values front and back.

Go to Page1
Select the report page and change the width to 4 inches.
Add to the page a Repeater using Country.
  Sort the repeater by Country ascending.
  Set Rows per page = 1
Add to the repeater a Repeater using CountryPage.
  Sort the repeater by Country ascending and page descending.
  Set Rows per page = 1
Add 3 blocks to the repeater.
Select the top block and set the render variable to Page = front
Select the middle block and set the render variable to Page = front
Select the bottom block and set the render variable to Page = back
In the top block, add a table with 2 columns and 1 row.
  Set the horizontal alignment in the left cell to left and in the right cell to right.
  Add to the left cell a singleton using CountryPage (and make it bold)
  Drag [CountryPage].[Country] to the singleton.
  Add to the right cell a text item:  "Page "
  Add to the right cell a layout calculation:  (PageNumber () + 1) / 2
Add to the middle block a repeater table using Detail (I set mine to 10 across and 1 down)
Add to the bottom block two blocks.
  In the top block add a text item:  Return Instructions (and make it bold)
  In the bottom block add a text item:  blah, blah, blah,...

I think I got everything, but I don't plan to follow these instructions to do it all again to verify.

That should provide the structure you need.  Now you just need to adapt it to your data and formatting requirements.