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>,
September 13, 2004, 05:58 PM
reFOCUSing
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
September 13, 2004, 06:19 PM
Lloyd
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