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.,

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,

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.,

John

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

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
 

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

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!

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!

quote:

old farts

I heard that!

T

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

Tony: Where's the damn code? !

just for you George

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!

T