World Writer Query

[MultiServ]

I am trying to create a WorldWriter that show the maximum transaction date for all items in the F4111. All I want is to have the LASTEST (ie max) GL date for each item. I am trying to use the max function in TotLvl but end up getting a list of items with every single transaction date next to each item. Any ideas, please?

 

[mgerritt]

I am not sure this can be done with a world writer, which linits the use of the underlying SQL. What you need is a sql that is (not sure what you mean by transction date so fill in the appropriate field name):

select illitm, max(transdate) from F4111
group by illitm

That will list each item with the last date for the transaction (in Julian, use the F00365 to convert to regular dates if you want).

If WW let's you group by item, then this may work as WW too.

 

[setuthill]

Did you try going into additional parameters and specifying T (Totals)
instead of D (Detail)???

 

[Eric_Holm]

I've never tried this but the following should work:
sub-total on the item no.
At that level, take the max. date.
Then run the report in Totals only mode.
Open the fold in the Data sort and totaling section and use &fieldname to print the item no in the sub-total line.

I hope this helps.

JDEdwards A7.3.8 and A7.3.10

Eric M. Holm, CA
CGI Information Systems and Management Consultants, Inc.
600 Federal Street
Andover, MA  01810
B:   978-946-3269
Fax: 978-687-4305

 

[Jim_rubino]

You need to be sure to have the short item number and the date field, you
need for your maximum value, in your list of output specifications.
Then sort the report by short item number and then date making the short
item a total level and then on the total level summary functions select the
date field from for Max.
When you are on the Sort and Totaling screen press F4 to unfold the lines
and on Total Line Text line enter &ILLITM and &xxxxx (xxxx = the name of the
date field). This will print on the report so you will know what the item
is and the date.
Then under the Additional Parameters screen set Query Detail to a T.
Now run the report.

Jim Rubino
Senior Programmer Analyst
FIKE CORPORATION (r)
704 South 10th Street
Blue Springs, Mo. 64015
(816) 229-6216 Ext. 213
World Software Technical Editor for JDEtips

 

[MultiServ]

Thanks to Eric, Mark and Seth. Works fine, all good contributions. Problem now is I wasnt to display to file and I can;t becasue of the Totals display. Any ideas around that one?
Cheers

 

[Eric_Holm]

That's one of the drawbacks of WW that you can't output totals to a workfile.
One way could be to take the report from the spoolfile and send it to an already defined file using the CPYSPLF command. Once there, it can be downloaded (using Rumba file transfer or Client Access file transfers) to an ASCII text file. then you can open it in MS Excel and parse it manually. A lot of work the first time throught but becomes easier with repetition.
I'm not sure if AS/400 query will write totals to a workfile but maybe it does.
Another solution is to buy a third party software package that will do what you want, such as SEQUEL from Advanced Systems Concepts in IL at 847-605-1311. there are others out there as well.

 

[setuthill]

If you know how to use ibm's query.. (wrkqry) you can output the summaries
to file...

 

[scott_parker]

IBM AS/400 Query will write total lines only to a file.




Scott Parker
Grote Industries, LLC.
WorldSoftware Version 8.1.2 AS/400 V5R2

 

[fmartin76]

Eric -

AS/400 Query does a great job of writing total lines. It will do as
many subtotal levels records/lines as you want as well.

Frank Martin
Director - Operations Planning
Kaydon Corporation