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!
JCThis message has been edited. Last edited by: John C.,
WF 7703 Outputs all Windows 7 32 DB2 CLI
September 17, 2012, 12:49 PM
Alan B
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
September 17, 2012, 02:06 PM
John C.
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
September 17, 2012, 06:40 PM
Alan B
John
Can you post the code you have created. There is a subtotal or something in there.
Alan. WF 7.705/8.007
September 18, 2012, 05:02 AM
Twanette
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
September 18, 2012, 05:07 AM
Tony A
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
September 18, 2012, 08:31 AM
John C.
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
September 18, 2012, 09:14 AM
George Patton
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!
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