Hi guys, im creating a report where I would like to have two grand-total lines, one a total of all accounts in my report and a second grand-total excluding a group a accounts.
If I were doing this in SQL or VB I would do a 'where Account not in(aaaa,bbbb,cccc,...)' but this type of logic isn't available for webfocus (or I can't find it in the manuals) , any ideas on how to do this?

sample code follows:



DEFINE FILE MK_SLS_R2
.
.
.
-*CALCULATE REPORT COLUMNS
-*WEEKLY
TY_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR)THEN ACTUAL_$ ELSE 0;
PL_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR)THEN PLAN_$ ELSE 0;
LY_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR -1) THEN ACTUAL_$ ELSE 0;
.
.
.
-* our accounts
DTY_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR) AND CUST_ID NOT IN (' M1040', ' M1041',' M1042',' M1043',' M1044',' M1045',' M1046') THEN ACTUAL_$ ELSE 0;
DPL_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR) AND CUST_ID NOT IN (' M1040', ' M1041',' M1042',' M1043',' M1044',' M1045',' M1046') THEN PLAN_$ ELSE 0;
DLY_WTD/D12.1 = IF (TRANS_WEEK = REPORT_WEEK AND TRN_YR = REPORT_YEAR -1) AND CUST_ID NOT IN (' M1040', ' M1041',' M1042',' M1043',' M1044',' M1045',' M1046') THEN ACTUAL_$ ELSE 0;
.
.
ON TABLE COLUMN-TOTAL AS 'GRAND TOTAL:' TY_WTD PL_WTD LY_WTD PL%_WTD LY%_WTD
ON TABLE COLUMN-TOTAL AS 'NON HOUSE ACCOUNTS:' DTY_WTD DPL_WTD DLY_WTD DPL%_WTD DLY%_WTD



P.S. I want to exclude any records, I need them all. I just want to do an exclusive SUM based on the record. I could probably do a 'IF x and (a or b or c or ..) but thats kinda ugly and rather a more robust solution. I would even like to create a hold file of the accounts and use that.

This message has been edited. Last edited by: <Mabel>,

Try moving the NOT. You need to have the NOT in front of the field name. Below are examples in a DEFINE and in a WHERE.

DEFINE FILE CAR
TEST/A10 = IF SEATS GE 5 AND NOT COUNTRY IN ('FRANCE', 'ENGLAND') THEN 'TRUE' ELSE 'FALSE';
END
-RUN
TABLE FILE CAR
PRINT
TEST
SEATS
BY COUNTRY
BY CAR
-*WHERE NOT COUNTRY IN ('FRANCE', 'ENGLAND')
END

Thanks Curtis, after a whole lot hair pulling I noticed that it was in the wrong place. Sort of an oddity (webFocus seems to have quite a few) because most other languages it's done the way I did it. Live and learn