×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

IN Clause

IN Clause

IN Clause

(OP)
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:

CODE

 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

RE: IN Clause

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

RE: IN Clause

(OP)
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)?

RE: IN Clause

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.

RE: IN Clause

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"

RE: IN Clause

(OP)
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?

RE: IN Clause

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

RE: IN Clause

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close