Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
In Clause
 Login/Join
 
Platinum Member
posted
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>,
 
Posts: 157 | Location: Secaucus, NJ | Registered: May 21, 2004Report This Post
Guru
posted Hide Post
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
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 157 | Location: Secaucus, NJ | Registered: May 21, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders