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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Distinct Field Values on Header line

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Distinct Field Values on Header line
 Login/Join
 
Guru
posted
I just got this strange request to put a list of distinct values for a particular field on the header line. I'm not quite sure how to code this. This is what I hope to accomplish using the car file:

     SALES REPORT FOR ENGLAND
BODYTYPES: CONVERTIBLE, HARDTOP, SEDAN

SEATS DEALER_COST RETAIL_COST SALES 
    2      11,719      13,978     0 
    4      14,940      17,850     0 
    5      11,194      13,491 12000 



Does that make sense?

Dev Studio 7.1.3, WinXP, SQL Server 2005


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
Virtuoso
posted Hide Post
Try this snippet of code:
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
BY BODYTYPE
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD
END
-RUN
-SET &DSTHEAD = '';
-SET &SEP = '';
-LOOP
-READ HOLD &BDTP.12
-IF &IORETURN NE 0 THEN GOTO ENDLOOP;
-SET &DSTHEAD = &DSTHEAD || &SEP | &BDTP;
-SET &SEP = ', ';
-GOTO LOOP
-ENDLOOP
-SET &DSTHEAD = &DSTHEAD || '.';

TABLE FILE CAR
HEADING
"SALES REPORT FOR ENGLAND"
"BODYTYPES: &DSTHEAD"
SUM DCOST RCOST SALES
BY SEATS
WHERE COUNTRY EQ 'ENGLAND'
END

GamP


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
GamP

this is a smart solution.
thanks

BTW
would you be so kind to update your signature...

(het is zo makkelijk als je kunt zien welk platform iedereen gebruikt)




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
Thank you GamP!
Although, I forgot to mention that England is a Subhead. So, I actually won't have a WHERE COUNTRY EQ 'ENGLAND' clause, it would print each country's report one after the other. So, I guess I'll have to do the &DSTHEAD computation for every country, which means a TABLE FILE CAR...HOLD... for each country? Let me think about this some more.


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
Member
posted Hide Post
dump all cars into save file. loop around reading them one at a time for the number of countries you have. place the country in a subhead with a where clause that is derived from the save file as it rewads thru it one at a time.
You can always (filedef) append each output together or create a compound report.
see below

TABLE FILE CAR PRINT COUNTRY
ON TABLE SAVE AS THESAVE
END
-SET &REX = &RECORDS;
-TYPE # OF COUNTRIES CAPTURED: &REX
-THELOOP
-REPEAT ENDREPEAT &REX TIMES
-READ THESAVE &COUNTRY.A10.
TABLE FILE CAR
"BODYTYPES: ON TABLE SUBHEAD
"FOR COUNTRY: &COUNTRY"
SUM DCOST RCOST SALES BY SEATS
WHERE COUNTRY EQ '&COUNTRY'
END
-ENDREPEAT

ira unix 5.3.6
 
Posts: 29 | Registered: March 30, 2007Report This Post
Platinum Member
posted Hide Post
Here's an example using hold files:

-*
-** HOLD DATA FROM CAR FILE
-*
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HLD_CAR
END
-*
-** CREATE HOLD FILE(HLD_BTS) - BODYTYPES BY COUNTRY
-*
TABLE FILE HLD_CAR
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS HLD_TMP1
END
-*
DEFINE FILE HLD_TMP1
BT_LIT/A138 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(138, BT_LIT, 1, 124, 124, 'A124') || ', ' || BODYTYPE
ELSE BODYTYPE;
END
-*
TABLE FILE HLD_TMP1
SUM
BT_LIT
BY COUNTRY
ON TABLE HOLD AS HLD_BTS FORMAT FOCUS INDEX COUNTRY
END
-*
-** JOIN HLD_CAR TO HLD_BTS AND CREATE REPORT
-*
JOIN COUNTRY IN HLD_CAR TO COUNTRY IN HLD_BTS AS J1
-*
TABLE FILE HLD_CAR
HEADING
"SALES REPORT FOR "BODYTYPES: " "
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY NOPRINT
BY BT_LIT NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
END

Jim


WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
 
Posts: 118 | Location: Lincoln Nebraska | Registered: May 04, 2005Report This Post
Platinum Member
posted Hide Post
I'll try again with HTML disabled.

-*
-** HOLD DATA FROM CAR FILE
-*
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HLD_CAR
END
-*
-** CREATE HOLD FILE(HLD_BTS) - BODYTYPES BY COUNTRY
-*
TABLE FILE HLD_CAR
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS HLD_TMP1
END
-*
DEFINE FILE HLD_TMP1
BT_LIT/A138 = IF COUNTRY EQ LAST COUNTRY
THEN SUBSTR(138, BT_LIT, 1, 124, 124, 'A124') || ', ' || BODYTYPE
ELSE BODYTYPE;
END
-*
TABLE FILE HLD_TMP1
SUM
BT_LIT
BY COUNTRY
ON TABLE HOLD AS HLD_BTS FORMAT FOCUS INDEX COUNTRY
END
-*
-** JOIN HLD_CAR TO HLD_BTS AND CREATE REPORT
-*
JOIN COUNTRY IN HLD_CAR TO COUNTRY IN HLD_BTS AS J1
-*
TABLE FILE HLD_CAR
HEADING
"SALES REPORT FOR <COUNTRY "
"BODYTYPES: <BT_LIT "
" "
SUM
DEALER_COST
RETAIL_COST
SALES
BY COUNTRY NOPRINT
BY BT_LIT NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
END


WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
 
Posts: 118 | Location: Lincoln Nebraska | Registered: May 04, 2005Report This Post
Guru
posted Hide Post
...and how about only one HOLD file and no JOIN:


-DEFAULTS &ECHO=ALL
TABLE FILE CAR
SUM
     DEALER_COST
     RETAIL_COST
     SALES
     COMPUTE BT_LIT/A138 = IF COUNTRY NE LAST COUNTRY 
	                       THEN BODYTYPE 
						   ELSE 
						      IF BODYTYPE EQ LAST BODYTYPE 
						      THEN LAST BT_LIT 
							  ELSE SUBSTR(138, LAST BT_LIT, 1, 124, 124, 'A124') 
							              || (', ' || BODYTYPE); 
BY COUNTRY 
BY HIGHEST TOTAL BT_LIT
BY BODYTYPE
BY SEATS
ON TABLE HOLD AS HOLDDATA
END
-RUN
DEFINE FILE HOLDDATA
BT_DISP/A138 = IF COUNTRY EQ LAST COUNTRY 
               THEN LAST BT_LIT
			   ELSE BT_LIT;
END
TABLE FILE HOLDDATA
HEADING CENTER
"SALES REPORT FOR <COUNTRY "
"BODYTYPE: <MAX.BT_DISP </1"
SUM
	 DEALER_COST
     RETAIL_COST
	 SALES
	 MAX.BT_DISP NOPRINT
BY COUNTRY NOPRINT
BY SEATS
ON COUNTRY PAGE-BREAK REPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
GRID=OFF, ORIENTATION=LANDSCAPE, SQUEEZE=ON,$
ENDSTYLE
END
  


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Virtuoso
posted Hide Post
And, to sum it all up into one table, one define, no hold file or join:

DEFINE FILE CAR
SEP/A2 = ', ';
BDTPL/I4   = ARGLEN(12, BODYTYPE, BDTPL);
CNTR/I4 WITH BODYTYPE = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE CNTR+1;
CHECK/A200 = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE CHECK;
BDTP/A200  = IF COUNTRY NE LAST COUNTRY THEN ' ' ELSE BDTP;
YES/I4     = POSIT(CHECK, 200, BODYTYPE, 12, YES);
CHECK      = IF CNTR EQ 1 THEN BODYTYPE ELSE 
             OVRLAY(CHECK, 200, BODYTYPE, 12, CNTR*12, 'A200');
LL/I4      = IF CNTR EQ 1 THEN ARGLEN(200,BODYTYPE,LL) ELSE ARGLEN(200,BDTP,LL);
BDTP       = IF YES NE 0 THEN BDTP ELSE
             IF CNTR EQ 1 THEN BODYTYPE ELSE
             OVRLAY(BDTP, 200, BODYTYPE, BDTPL, ARGLEN(200,BDTP,'I4')+3, 'A200');
BDTP       = IF YES NE 0 THEN BDTP ELSE
             IF CNTR EQ 1 THEN BDTP ELSE
             OVRLAY(BDTP, 200, SEP, 2, LL+1, 'A200');
END

TABLE FILE CAR
SUM DCOST RCOST SALES
 BDTP NOPRINT CHECK NOPRINT
BY COUNTRY NOPRINT PAGE-BREAK
BY SEATS
HEADING CENTER
"SALES REPORT FOR COUNTRY <COUNTRY"
"BODYTYPES: <BDTP"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
END


Thr trick is of course in the defines and the order they are being executed.
Pro of this solution is that it means one pass through the data and reusability.

GamP


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Guru
posted Hide Post
Hi guys -
Thank you so very much for all the sample code! They all worked well!
GamP, I'm taking your solution. One pass is definitely a big Pro and it is simple enough for me to understand months from now when I have to support it in production.


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
Guru
posted Hide Post
quote:
CNTR/I4 WITH BODYTYPE = IF COUNTRY NE LAST COUNTRY THEN 1 ELSE CNTR+1



The only way this DEFINE will work is if the data is presorted in COUNTRY order as DEFINEs are evaluated as each row of data is read from the database.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Distinct Field Values on Header line

Copyright © 1996-2020 Information Builders