DEFINE FILE SQLOUT
DUMMY/A1 = ' ';
END
TABLE FILE SQLOUT
BY DUMMY NOPRINT
-SET &LicenseNumber = IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate = IF &StartDate EQ '' THEN FOC_NONE ELSE &StartDate;
-SET &TaxPeriod = IF &TaxPeriod EQ '' THEN FOC_NONE ELSE &TaxPeriod;
WHERE ( Tax_period EQ '&TaxPeriod');
WHERE ( License_no EQ '&LicenseNumber');
WHERE ( Refund_Date FROM DT(&StartDate) TO DT(&EndDate) );
PRINT
     License_no AS 'License,Number'
     Trade_Name AS 'Trade,Name'
     Legal_Name AS 'Legal,Name'
     Tax_period AS 'Tax,Period'
     Refund_Amount AS 'Refund,Amount'
     Refund_Date/HMDYY AS 'Refund,Date'
     SAP_Transmittal_Number AS 'SAP,Transmittal,Number'
BY DUMMY NOPRINT
ON DUMMY SUBFOOT
"Total Refunds: &LINES "
HEADING
"Refund Report:&DATEMDYY "  

Why is this &LINES not working on the Total refunds line? What am I missing?

All I want to do is count the number of lines on the report. Should be simple so I must be missing something easy.

Thanks a bunch for the help.

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

Have you tried getting the number of records FIRST before tring to use &LINES?

I would do the following:

  
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TEST1
END
-RUN
TABLE FILE TEST1
BY DUMMY NOPRINT
:
:
:
ON DUMMY SUBFOOT
"Total Refunds: &LINES "

quote:

Why is getting total records on a report so difficult?

Probabley because you are making a fundamental mistake.

&LINES is only available after a -RUN

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD
END
-RUN
-SET &HOWMANYLINES=&LINES;
TABLE FILE HOLD
" I have this many lines &HOWMANYLINES"
PRINT *
END

... or in 1 go

TABLE FILE CAR
HEADING
"Number of lines: <CNT.MODEL"
COUNT MODEL NOPRINT
SUM
DEALER_COST
BY COUNTRY BY CAR BY MODEL
END

HTML reports can handle it this way:

TABLE FILE CAR
SUM
SALES
BY COUNTRY

ON TABLE SUBFOOT
"TOTAL LINES: !IBI.AMP.LINES;"

ON TABLE HOLD AS H001 FORMAT HTMTABLE
END
-RUN

-HTMLFORM BEGIN
!IBI.FIL.H001;
-HTMLFORM END

quote:

ON TABLE SUBFOOT
"TOTAL LINES: !IBI.AMP.LINES;"

Nice, hadn't considered this way before.

LET WebFOCUS= How many ways to skin a chicken (or what ever your local variant is)

&LINES, is not calculated until AFTER the table file.
You could:

TABLE FILE SQLOUT
BY DUMMY
ON TABLE HOLD
END
-RUN
-SET &TOTLINES=&LINES;
TABLE FILE SQLOUT
. 
.
.
ON DUMMY SUBFOOT
"Total Refunds: &TOTLINES "

OR AN EASIER SOLUTION:
TABLE FILE SQLOUT
SUM COMPUTE CNTR/I4=CNTR+1; NOPRINT
BY DUMMY
.
.
.
.
ON DUMMY SUBFOOT
"Total Refunds: <ST.CNTR "

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

Have you tried creating a define field.
Try this version of your report.
/*
DEFINE FILE SQLOUT
DUMMY/A1 = ' ';
RECORD_COUNT/I9=1;
END
TABLE FILE SQLOUT
BY DUMMY NOPRINT
-SET &LicenseNumber = IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate = IF &StartDate EQ '' THEN FOC_NONE ELSE &StartDate;
-SET &TaxPeriod = IF &TaxPeriod EQ '' THEN FOC_NONE ELSE &TaxPeriod;
WHERE ( Tax_period EQ '&TaxPeriod');
WHERE ( License_no EQ '&LicenseNumber');
WHERE ( Refund_Date FROM DT(&StartDate) TO DT(&EndDate) );
PRINT
License_no AS 'License,Number'
Trade_Name AS 'Trade,Name'
Legal_Name AS 'Legal,Name'
Tax_period AS 'Tax,Period'
Refund_Amount AS 'Refund,Amount'
Refund_Date/HMDYY AS 'Refund,Date'
SAP_Transmittal_Number AS 'SAP,Transmittal,Number'
BY DUMMY NOPRINT
ON DUMMY SUBFOOT
"Total Refunds: HEADING
"Refund Report:&DATEMDYY "

*/

WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit

ENGINE SQLMSS SET DEFAULT_CONNECTION LCIGAR
SQL SQLMSS PREPARE SQLOUT FOR
select TV.License_no, CL.Trade_Name, CL.Legal_Name, TV.Tax_period, TV.Transaction_Amount as 'Refund Amount', TV.Transaction_DTTM as 'Refund Date',
TV.DLN as 'SAP Transmittal Number' from transactionView TV
inner join clgninfo CL on TV.License_no = CL.license_no
where transaction_type = 'D'
order by TV.License_no, TV.Transaction_DTTM
;
END
TABLE FILE SQLOUT
ON TABLE HOLD
END
-RUN
-SET &TOTLINES=&LINES;
TABLE FILE SQLOUT
-SET &LicenseNumber = IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate = IF &StartDate EQ '' THEN FOC_NONE ELSE &StartDate;
-SET &TaxPeriod = IF &TaxPeriod EQ '' THEN FOC_NONE ELSE &TaxPeriod;
WHERE ( Tax_period EQ '&TaxPeriod');
WHERE ( License_no EQ '&LicenseNumber');
WHERE ( Refund_Date FROM DT(&StartDate) TO DT(&EndDate) );
PRINT
     License_no AS 'License,Number'
     Trade_Name AS 'Trade,Name'
     Legal_Name AS 'Legal,Name'
     Tax_period AS 'Tax,Period'
     Refund_Amount AS 'Refund,Amount'
     Refund_Date/HMDYY AS 'Refund,Date'
     SAP_Transmittal_Number AS 'SAP,Transmittal,Number'

BY DUMMY NOPRINT
ON DUMMY SUBFOOT
"Total Refunds: &TOTLINES "

HEADING
"Refund Report:&DATEMDYY "  

I tried this last suggestion and I have no idea why I cannot get this to work.

The help is greatly appreciated.

Erney,

First of all can you please show us the reults of your report?
Another thing, you will only get the correct reuslts if you use the same selection criteria in both reports. Otherwise, the first report will give you the count of all the records on your output file and not the ones that matc hyour selection cirteria.

In your latest example, the column "DUMMY" does not exist.

DEFINE FILE SQLOUT
DUMMY/A1 = ' ';
END
TABLE FILE SQLOUT
BY DUMMY NOPRINT
-SET &LicenseNumber = IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate = IF &StartDate EQ '' THEN FOC_NONE ELSE &StartDate;
-SET &TaxPeriod = IF &TaxPeriod EQ '' THEN FOC_NONE ELSE &TaxPeriod;
WHERE ( Tax_period EQ '&TaxPeriod');
WHERE ( License_no EQ '&LicenseNumber');
WHERE ( Refund_Date FROM DT(&StartDate) TO DT(&EndDate) );
ON TABLE HOLD
END
-RUN
-SET &TOTLINES=&LINES;
TABLE FILE SQLOUT
PRINT
     License_no AS 'License,Number'
     Trade_Name AS 'Trade,Name'
     Legal_Name AS 'Legal,Name'
     Tax_period AS 'Tax,Period'
     Refund_Amount AS 'Refund,Amount'
     Refund_Date/HMDYY AS 'Refund,Date'
     SAP_Transmittal_Number AS 'SAP,Transmittal,Number'
BY DUMMY NOPRINT
ON DUMMY SUBFOOT
"Total Refunds: &TOTLINES "
HEADING
"Refund Report:&DATEMDYY "  

OK i changed things up a bit in the code but im getting a "0" for &TOTLINES even though there are 3 records showing up on the report.

Put these lines before the DEFINE

-SET &LicenseNumber = IF &LicenseNumber EQ '' THEN FOC_NONE ELSE &LicenseNumber;
-SET &StartDate = IF &StartDate EQ '' THEN FOC_NONE ELSE &StartDate;
-SET &TaxPeriod = IF &TaxPeriod EQ '' THEN FOC_NONE ELSE &TaxPeriod;

Am I missing something? Why all the holds and parameters?

TABLE FILE IBISAMP/EMPDATA
SUM COMPUTE CNTPIN/I9 = CNT.PIN; NOPRINT
PRINT  *
BY DEPT
HEADING 
"TOTAL RECORDS <TOT.CNTPIN"
ON TABLE SUBFOOT 
"CNT <TOT.CNTPIN"
END  

dhagen, you were correct. I used your code and it worked.

Thank you all for the contributions.

dhagen, wouldn't that give you a count of the rows read by the request, not the number of rows in the output, so, good for a PRINT, but not for a SUM?

F,

TABLE FILE IBISAMP/EMPDATA
SUM COMPUTE CNTPIN/I9 = CNT.PIN; NOPRINT
SUM SALARY COMPUTE CNTROWS/I9 = 1; NOPRINT
BY DEPT
HEADING 
"TOTAL RECORDS <TOT.CNTPIN"
ON TABLE SUBFOOT 
"CNT <TOT.CNTPIN <ST.CNTROWS"
END