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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Getting a Count on a Report Field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Getting a Count on a Report Field
 Login/Join
 
Member
posted
Can anyone help me get a count on a field that gives me a value of 'Y'(es) for every unique value in another field?

For example, the report lists all vendors that are Located in the U.S. The output I need would look like this:

Vendor# Located in U.S? PO#
12345 Y abc
12345 Y def
67890 N hij
65432 Y klm
89012 Y nop
56789 Y qrs
56789 Y tuv

Total U.S. Vendors: 4

Note: Count is only 4 because vendors #12345 and #56789 are only counted once even though they have multiple PO's.


WebFOCUS v7.7.03
Windows 7 O/S
Oracle database
Platform Windows 2003 server 32bit
Output types are HTML, PDF, Excel 2003 Active Reports
 
Posts: 3 | Location: St. Petersburg, FL | Registered: September 14, 2007Report This Post
Platinum Member
posted Hide Post
This may work for you.
This assumes you are sorting by vendor.

TABLE FILE FILENAME
PRINT USFLAG PO#
COMPUTE USCNT/I4=IF VENDOR NE LAST VENDOR AND USFLAG EQ 'Y' THEN 1 ELSE 0; NOPRINT
BY VENDOR
ON TABLE SUBFOOT
"TOTAL US VENDORS %ST.USCNT"
END

Replace the % above with a left caret


Good Luck

et


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Virtuoso
posted Hide Post
TABLE FILE VENDORS
SUM CNT.DST.VENDOR
WHERE 'Located in US' EQ 'Y';
END

Will give you the number 4.

OR

TABLE FILE VENDORS
SUM CNT.DST.VENDORS
BY 'Located'
END

Will give

Y 4
N 1

and you can als calc the percentage in and out...




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
Virtuoso
posted Hide Post
quote:
Originally posted by ET:
Replace the % above with a left caret


E.T.

If you put your coding between
[CODE]
[/CODE]
you can type the normal code characters.




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
Platinum Member
posted Hide Post
Frank

Thanks for the tip.

et


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report 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     Getting a Count on a Report Field

Copyright © 1996-2020 Information Builders