IN Clause

[LPGAST]

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 I would rather a more robust solution. I would even like to create a hold file of the accounts and use that.

--------------------
Lloyd Prendergast
Michael Kors (USA), Inc.
Phone: 212-201-8176
Fax: 646-354-4776
Lloyd.Prendergast@Michaelkors.com

 

[kiddpete]

Use 'ON fieldname SUBFOOT'. If you only want one as a total for the report, used a DEFINEd variable which has only one value.

 

[LPGAST]

I want both total lines. One, a total of all columns and rows in the report. and a Second with a total of all columns minus certain rows in the report. ON SUBFOOT will give me the break but how do I get the sum minus the list accounts. My problem isn't with the rport break it's more of how do I sum all rows and also sum specific rows ( or not include certain rows in the sum)?

 

[kiddpete]

SUBFOOT supports multiple lines, and you can do RECAPs which feed directly into the SUBFOOT:

ON fieldname RECAP
Variable/format=etc.

You can probably get the data you need with nonprinting defined variables. However, if you can't work out the logic with a RECAP, then you will have to use Extended Matrix Reporting.

EMR will allow you to 'label' data at the line level, and perform computations at the cell level. The downside of EMR is that it needs all the lines in the report explicitly identified in the code. EMR can also be confusing, so there is a learning curve involved.

 

[craigiep]

The synatx you're after is

DTY_WTD/D12.1 = IF NOT CUST_ID IN (' M1040' ' M1041') THEN ACTUAL_$ ELSE 0;

You should then be able to use a subfoot something like this

ON TABLE SUBFOOT
"All Totals <TOT.ACTUAL_$"
"Totals excluding value <TOT.DTY_WTD

 

[LPGAST]

Thanks craigiep, that's just the syntax I was looking for. Now a second request, how could I incorporate a Focus File with simmilar syntax, so I don't I have the List hardcoded in my fex?

 

[craigiep]

I'm not sure you can do it with a Focus file, but with a sequential file you can do this
.
ON TABLE SAVE AS VALUES
END

.
WHERE NOT CUST_ID IN FILE VALUES

 

[Tewy]

You can use Focus to check values in a file. You need to use ON TABLE HOLD AS filename FORMAT ALPHA, then filedef the file. You can then use

IF fieldname IS (filename)

or in a similar vein IF fieldname IS NOT (filename) in your code. You need to put this at the end of your WHERE statements or the code will error

Tewy