Hi All,

I have the following report that should be stored in PDF format:

 				PAGE 1
	key_column		value_column
	KEY1			10
	KEY1			10
	KEY1			10
	KEY2			10
	KEY2			10

					PAGE 2
	key_column		value_column
	KEY2			10
	KEY3			10
	KEY4			10
 

After the previous report is generated I have to create another report that should looks like this:

 	
	key_column		value_column	page_number
 	KEY1			30			1
 	KEY2			30			1
 	KEY3			10			2
 	KEY4			10			2

 

Basically the second report is a sum of value_column per key_column group from the first one. The problem here is that somehow I have to print the page number for each first group element from the first report.

Do you have any idea how I can do that?

Thanks in advance

This message has been edited. Last edited by: Kerry,

Not logical at all.

The key columns in report 1 span pages so page_number means nothing unless you mean
1st page it appears on.

The easy solution is to SET LINES so that you know exactly howmany lines ore on a page.
Use a counter created via a compute.
Then using a simple compute the integer part of counter / lines should give you the first page.

L_G,

It is an interesting problem.

There is a WF field called TABPAGENO which holds the page number. It is usually used in a HEADING. I tried

putting it in the report body. For example:

TABLE FILE CAR
PRINT SALES TABPAGENO
BY COUNTRY
ON TABLE HOLD
END
TABLE FILE HOLD
SUM SALES FST.TABPAGENO
BY COUNTRY
END

The problem with this was that, when HOLDing, WF doesn't write out the values of TABPAGENO, only zeros - at least

in 7.6.5 which I am using.
So I thought of using the WP format:

TABLE FILE CAR
PRINT SALES TABPAGENO
BY COUNTRY
ON TABLE HOLD FORMAT WP
END

If you run this and look at the file, you will see that TABPAGENO is saved. However:
- COUNTRY does not appear on each line; so it is necessary to PRINT COUNTRY and add NOPRINT to the BY.
- We have heading and title lines.
- And something else, the number of lines in the PDF output does not correspond to the number of lines of the WP

output. This is due to PDF lines being a function of the page and font sizes. In order to match the number of

lines between PDF and WP, you have to play around a bit.

Here is what I came up with. First, the PDF output:

-* File TABPAGEPDF.fex
-*
-* Concatenate the CAR file a few times in order to have more than one page of output:
USE
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
END
TABLE FILE CAR
PRINT COUNTRY SALES
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT PDF
END

Then the "Summary" output:

-* File tabpageno.fex
-*
-* Concatenate the CAR file a few times in order to have more than one page of output:
USE
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
C:\IBI\APPS\IBISAMP\CAR.FOC
END
-*
-* Match number of lines in WP to PDF
SET LINES=62
-*
-* Use an extra field to mark body output lines to exclude headings and titles
DEFINE FILE CAR
P/A1='+';
END
-*
-* Output the WP file
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
PRINT P COUNTRY SALES 
TABPAGENO 
BY COUNTRY NOPRINT
ON TABLE HOLD AS LG FORMAT WP
END
-RUN
-* Create a MASTER for the WP file
FILEDEF LGMAS DISK LG.MAS
-RUN
-WRITE LGMAS FILENAME=LG, SUFFIX=FIX
-WRITE LGMAS SEGNAME=LG, SEGTYPE=S0
-WRITE LGMAS FIELDNAME=F1, FORMAT=A1, ACTUAL=A2, $
-WRITE LGMAS FIELDNAME=P, FORMAT=A1, ACTUAL=A3, $
-WRITE LGMAS FIELDNAME=COUNTRY, FORMAT=A10, ACTUAL=A10, $
-WRITE LGMAS FIELDNAME=SALES, FORMAT=I6, ACTUAL=A8, $
-WRITE LGMAS FIELDNAME=PGNO, FORMAT=I5, ACTUAL=A11, $
-RUN
-*
-* Reissue the FILEDEF for the WP file (WF defines the file differently)
FILEDEF LG CLEAR
FILEDEF LG DISK LG.WP
-* 
-* Create the "summary" report
TABLE FILE LG
SUM SALES/I8 FST.PG
BY COUNTRY
IF P EQ '+'
END

Good luck!!!

You could try this out.

It uses the function PUTDDREC that writes data out to a DDname during processing.

The issue is where in the process it writes, in relation to TABPAGENO.

This is why there is a test for 0, and change it to 1.

Also if the report needs to be sorted, it needs to be done prior to the report as the page numbers get screwed up, hence the TABLEF.

TABLE FILE SHORT
PRINT HOLDER
      BALANCE
BY CONTINENT
 
ON TABLE HOLD AS SORTED
END
 
-RUN
 
 
FILEDEF PUTDD1 DISK putdd1.dat 
 
SET COMPOUND = OPEN
 
TABLEF FILE SORTED
PRINT HOLDER
      BALANCE
COMPUTE PAGENO/I5 = IF TABPAGENO NE LAST TABPAGENO THEN LAST PAGENO + 1 ELSE LAST PAGENO ; NOPRINT
COMPUTE EMP1/A31  = CONTINENT | EDIT(PAGENO) | FTOA(BALANCE,'(F16)','A16') ; NOPRINT
COMPUTE OUT1/I1   = PUTDDREC('PUTDD1',6, EMP1, 31, OUT1); NOPRINT
BY CONTINENT
 
ON TABLE PCHOLD AS PUTDD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, $
 
ENDSTYLE
 
END
 
-RUN
 
-* Write out a master to read the PUTDD1 list
EX -LINES 6 EDAPUT MASTER,PUTDD1,CV,FILE
FILENAME=PUTDD1, SUFFIX=FIX,$
SEGNAME=PUTDD1, $
  FIELD=CONTINENT,ALIAS=  ,A10 ,A10 ,$
  FIELD=PAGENO   ,ALIAS=  ,I5  ,A5  ,$
  FIELD=BALANCE  ,ALIAS=  ,D16 ,A16 ,$
 
-RUN
 
DEFINE FILE PUTDD1
 PageNo/I5 = IF PAGENO EQ 0 THEN 1 ELSE PAGENO ;
END
 
SET COMPOUND = CLOSE
 
TABLE FILE PUTDD1
SUM   MIN.PageNo
      BALANCE
BY    CONTINENT
END

Have you looked at the PDF Layout Painter and the coordinated reports option on the same high-level sort key? All your reports for key1 would end up on the same page, etc.