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     [SOLVED] Count Records Other than By Field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Count Records Other than By Field
 Login/Join
 
Gold member
posted
Hello All,

First time poster and very new with WebFOCUS. Please don't laugh!

I have an item listing:


ITEM#     WHSE     ON HAND INVENTORY    #DC's
2288210   002      10                   x
2288210   003      20                   x  
2288210   006      30                   x



I want the detail above to stay the same (by Item, By WHSE) however, I would like the last column to just inform the user that this is stocked in 3 Warehouses (X)... (and repeat that in all 3 rows).

I need to keep the level of detail the same.

Hoping to be able to use this example over and over in other reports.

Thank you for all help!

JC

This message has been edited. Last edited by: John C.,


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Virtuoso
posted Hide Post
This is what was termed a multi-set request, sort groups within Report Painter.
TABLE FILE filename
SUM CNT.DC NOPRINT
BY ITEM
SUM ON_HAND_INVENTORY
COMPUTE NUM_DC/I4 = CNT.DC;
BY ITEM
BY WHSE
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
Alan,

Thank you for taking a look, this is the type of result we got.


ITEM#     WHSE     ON HAND INVENTORY    #DC's
2288210   002      10                   x
2288210   003      20                   x  
2288210   006      30                   x

          
** NUM_DC     3 

  


Is there anyway to do it as an inline statment as a column?

This message has been edited. Last edited by: John C.,


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Virtuoso
posted Hide Post
John

Can you post the code you have created. There is a subtotal or something in there.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Hi John,
That typically happens when your COMPUTE is in the wrong place.
You will notice that in Alan's code the COMPUTE is after the SUM'd fields, but before the BY statements.
If one uses the CAR file you should be able to use the following code to see if it is what you are after:
 
TABLE FILE CAR
SUM CNT.CAR NOPRINT
BY COUNTRY
SUM SALES
COMPUTE NUM_CAR/I4 = C01;
BY COUNTRY
BY CAR
END
 

If, however, I misplace the COMPUTE statement as per the example below, then I will get the "wrong" result.
 
TABLE FILE CAR
SUM CNT.CAR NOPRINT
BY COUNTRY
COMPUTE NUM_CAR/I4 = C01;
SUM SALES
BY COUNTRY
BY CAR
END
 


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Expert
posted Hide Post
John,

To supplement Alan's answer with your data, try this code -
-* This section is purely to mimic your data
FILEDEF JOHNCMAS DISK JOHNCDAT.MAS
-RUN
-WRITE JOHNCMAS
-WRITE JOHNCMAS FILENAME=JOHNCDAT, SUFFIX=FOC
-WRITE JOHNCMAS SEGNAME=SEG01, SEGTYPE=S1
-WRITE JOHNCMAS FIELD=WHSE,     ALIAS=WHSE,      USAGE=A03, ACTUAL=A03, $
-WRITE JOHNCMAS FIELD=ITEM,     ALIAS=ITEM,      USAGE=A07, ACTUAL=A07, $
-WRITE JOHNCMAS FIELD=ONHAND,   ALIAS=ONHAND,    USAGE=I03, ACTUAL=I03, $
CREATE FILE JOHNCDAT
MODIFY FILE JOHNCDAT
FREEFORM WHSE ITEM ONHAND
DATA
002,2288210,10,$
003,2288210,20,$  
006,2288210,30,$
002,2288211,10,$
003,2288211,20,$  
006,2288212,30,$
END
-* End of data creation
TABLE FILE JOHNCDAT
  SUM CNT.WHSE                   NOPRINT
   BY ITEM
  SUM ONHAND                     AS 'On Hand Inventory'
      COMPUTE CNT/I9 = CNT.WHSE; AS '#DC''s'
   BY ITEM                       AS 'Item#'
   BY WHSE                       AS 'Warehouse'
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET BYDISPLAY ON
END

The first CNT.WHSE primes the value for the subsequent COMPUTE and will give you the value that you are after. You'll notice that I've added two extra items to allow a comparison when more than one item is present.

The first BY ITEM ensures that the count of warehouses is calculated for each item number, if you didn't have this then the DC's would be 6 for the above data sample.

Finally, as Alan says, the output you show does not reflect what Alan has suggested, therefore you have something different in your code such as a subhead or footing. If you can demonstrate what you are doing but using the data above (extended if necessary) then we might be able to understand what you are trying to do a little more.


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Gold member
posted Hide Post
I copied and pasted what Alan had posted, however when I opened the report in the GUI I think it manipulated it. This worked perfectly, however it eliminated our ability to use the GUI (sort of getting used to not being able to use it, however there are syntax we don't yet have memorized).

  

TABLE FILE E3ITEMA
  SUM CNT.IWHSE NOPRINT
  BY IITEM
  SUM IONHND                    AS 'On Hand Inventory'
  COMPUTE CNT/I9 = CNT.IWHSE;   AS '#DC''s'
  BY IITEM                      AS 'Item#'
  BY IWHSE                      AS 'Warehouse'
WHERE IITEM EQ '2288210';



Really appreciate everyone response to help out a newbie-- much appreciated!


WF 7703 Outputs all
Windows 7 32
DB2 CLI
 
Posts: 59 | Registered: August 09, 2012Report This Post
Master
posted Hide Post
John: You are in the difficult position of learning WebFOCUS the "new" way, which is entirely via the GUI. In this forum, unfortunately for you, there are many old farts like me who learned the code first. So you will find that most, if not all, helpful suggestions come in the form of code. I think most of us only use the GUI for styling - and some don't even do that.

What you are trying to do sounds on the surface to be uber-simple, so you should follow the suggestion to post your code!


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Expert
posted Hide Post
quote:
old farts

I heard that! Wink

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Never one to pass up a challenge - I'll assume that you can do the basics regarding Dev Studio so this will be briefish). This may be useful to other "newbies" as well as I am sure this has been asked before?

In the Dev Studio Tool, click new report and select the CAR master file.

Add COUNTRY, CAR, RETAIL_COST and DEALER_COST to the report and change COUNTRY and CAR to BY fields with the other two a SUMmated fields.


Add a new column in front of the existing CAR column and change from a BY to SUM. Also change the prefix under options/general to CNT. Make the column invisible.


Create a new computed field called [anything you like] and make the format I9 and equate it to CNT.CAR. Move it to the last column (if it is not already!).



Now run your report and it should look like this -


Good luck!!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
Tony: Where's the damn code? !


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Expert
posted Hide Post
just for you George Wink

TABLE FILE CAR
SUM CNT.CAR NOPRINT
BY COUNTRY
SUM RCOST DCOST COMPUTE CNT_CAR/I8 = CNT.CAR;
BY COUNTRY
BY CAR
END

And that took far less time - even on an iPad - than doing the same in the GUI.

But then you knew that! Music

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 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     [SOLVED] Count Records Other than By Field

Copyright © 1996-2020 Information Builders