My report is sorted by salesman# and I give the subtotals and a page-break for each salesman#. I used the same sort field on the heading, but it displayed the sum of salesman# of the current page. Have anyone experienced this before? How can I do it properly? Thanks.

 TABLE FILE H_FINAL
SUM 
     'H_FINAL.SEG01.AFAKNA' AS 'Name'
     'H_FINAL.SEG01.BALANCE' AS 'Total Past Due'
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
PRINT 
     'H_FINAL.SEG01.ARALNB' AS 'Invoice#/,Cash txn#'
     'H_FINAL.SEG01.ARH6CD' AS 'Type'
     'H_FINAL.SEG01.INV_DATE' AS 'Invoice/,payment,Date'
     'H_FINAL.SEG01.TXNAMT' AS 'Invoice/,Payment,Amount'
     'H_FINAL.SEG01.DUE_DATE' AS 'Due Date'
     'H_FINAL.SEG01.BALANCE' AS 'Outstanding ,Amount'
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
     
ON H_FINAL.SEG01.SLSNO SUBTOTAL
     TXNAMT
     BALANCE MULTILINES AS 'Total for Salesrep'
ON H_FINAL.SEG01.SLSNO PAGE-BREAK REPAGE
     
ON H_FINAL.SEG01.CUSTOMER UNDER-LINE
HEADING
"Past Due Receivables by Salesman as of <+0>&DATE"
"SalesRep   - <H_FINAL.SEG01.SLSNO  - <H_FINAL.SEG01.SLSNM "       <==== sum of SLSNO displayed
" "
FOOTING BOTTOM
"Page <TABPAGENO "
ON TABLE SUBFOOT
"********* End of Report *********"
" "
ON TABLE SET EMPTYREPORT ANSI
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>).Select type of display output.  

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

You have...

TABLE FILE H_FINAL
SUM 
     'H_FINAL.SEG01.AFAKNA' AS 'Name'
     'H_FINAL.SEG01.BALANCE' AS 'Total Past Due'
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
PRINT 
...
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
...
ON H_FINAL.SEG01.SLSNO PAGE-BREAK REPAGE
     
HEADING
"Past Due Receivables by Salesman as of <+0>&DATE"
"SalesRep   - <H_FINAL.SEG01.SLSNO  - <H_FINAL.SEG01.SLSNM "       <==== sum of SLSNO displayed
" "
...

IIRC, fields imbedded in the heading text are (if necessary) added as Noprint verb objects to the first verb. In your case, the first verb has two BY phrases; so SLSNO will be "summarized" over all the distinct Customers within each SLSNO value, and you wind up with (number of Cust's) x (slsno value).

Try it with three verbs, so that the top verb is sorted only by the page-break column:

TABLE FILE H_FINAL

SUM 
     'H_FINAL.SEG01.SLSNM' NOPRINT
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  

SUM 
     'H_FINAL.SEG01.AFAKNA' AS 'Name'
     'H_FINAL.SEG01.BALANCE' AS 'Total Past Due'
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'

PRINT 
...
BY 'H_FINAL.SEG01.SLSNO' NOPRINT                                  
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
...

Also, to have the SLSNO field in the heading to really represent the data, shouldn't you have a page break for every SLSNO?

Jack,

Thank you for explaining what is happening with the embedded fields on headings. But it is still behaving the same with your sugguestion.

quote:

you wind up with (number of Cust's) x (slsno value).

I tried a few other ways, and it only works if there is no "SUM" verbs and have the total outstanding amounts as the subtotal at cus# level. Or if I really wanted the original layout, I have to change SLSNO to alpha field to display on the heading.


Francis, Yes I have a page-break on SLSNO.


Thanks,


Hua

Try computing a field in the first verb set to be the max value and use that field in your heading.

COMPUTE SLSNO_MAX/format=MAX.SLSNO;
and
HEADING
"SalesRep   - <SLSNO_MAX  "

Hua,
In a similar situation in the past I had solved this by coding it like this:

"SalesRep   - <FST.SLSNO  - <H_FINAL.SEG01.SLSNM "

Maybe this is also a solution in this case?

Gerard,

In this case, since the lowest level verb is PRINT there is no problem. However, if it were SUM, then using FST. is fine if the file is not a RDBMS. If it is, then one should use MIN or MAX or AVE, otherwise aggregation will not be done.

Another option is to convert the SALESNO to an alpha and sort/sum on that.
DEFINE MYFILE
SALESNO_A/A9=EDIT(SALESNO);
END
SUM
'H_FINAL.SEG01.AFAKNA' AS 'Name'
'H_FINAL.SEG01.BALANCE' AS 'Total Past Due'
BY SALESNO_A NOPRINT <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
PRINT
...
BY SALESNO_A NOPRINT <==== sort field
BY 'H_FINAL.SEG01.CUSTOMER' AS 'Customer'
...
ON SALESNO_A PAGE-BREAK REPAGE

HEADING
"Past Due Receivables by Salesman as of <+0>&DATE"
"SalesRep - " "

I see that the possibilities are unlimited. Thanks to all of you!

Hua

quote:

But it is still behaving the same with your sugguestion.

My suggestion would work if reporting from a hierarchical file (multiple-segment Focus file) with SLSNO as the root segment's key. From a flat file, you'll get (SLSNO key value) x (number of incoming row instances with that value).

Changing it to an Alpha field (in a DEFINE, not a compute!) is the way to go.

Jack,

I've always appreciated you for answering "why" it happenned.
For the sake of discussion, I have observed that any embeded field in heading, will be summarized as long as there is a summation verb that
alpha field = last occurence of the current group
numeric field = value x # ocurrence of the current group.

DEFINE FILE CENTGL
Mylevel/I5=CENTGL.ACCOUNTS.GL_ACCOUNT_LEVEL +1;
END
TABLE FILE CENTGL
SUM
     'CNT.CENTGL.ACCOUNTS.GL_ACCOUNT_LEVEL'
BY 'CENTGL.ACCOUNTS.Mylevel'
PRINT
     'CENTGL.ACCOUNTS.GL_ACCOUNT'
     'CENTGL.ACCOUNTS.GL_ROLLUP_OP'
     'CENTGL.ACCOUNTS.GL_ACCOUNT_CAPTION'
     'CENTGL.ACCOUNTS.SYS_ACCOUNT'
BY 'CENTGL.ACCOUNTS.Mylevel'
BY 'CENTGL.ACCOUNTS.GL_ACCOUNT_PARENT'
BY 'CENTGL.ACCOUNTS.GL_ACCOUNT_TYPE'
HEADING
"My level = <CENTGL.ACCOUNTS.Mylevel , parent =<CENTGL.ACCOUNTS.GL_ACCOUNT_PARENT "
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END  

Hua

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