I am trying to print a list of the purchaser with the highest purchase quantity per vendor. I am using the purchase receipt header and purchase receipt line tables to try and achieve this.
The purchaser Code is from the purchase receipt header and the quantity is from the purchase receipt line table
I am unable to sort the main rows returned based on purchaser quantity and I guess that is because the two tables are not linked via the Purchaser code key
Here is what I'm trying=NL("Rows","Purch. Rcpt. Header","Purchaser Code","Buy-from Vendor No.",1001,"Order Date",$C$2,"+=NL(""Sum"",""Purch. Rcpt. Line"",""Quantity"",""Document No."",D3)","*")Cell D3 contains=NL("Filter","Purch. Rcpt. Header","No.","Buy-from Vendor No.","@@"&F7,"Order Date",$C$2,"Purchaser Code","@@"&B3)
Any help would be much appreciated …
This is easily achieved in SQL , so was wondering if this was possible in jet reportsSELECT [Purchaser Code], SUM(Quantity)
FROM dbo.[Provincial Fruit Co_$Purch_ Rcpt_ Header] prh
INNER JOIN dbo.[Provincial Fruit Co_$Purch_ Rcpt_ Line]
ON prh.No_ = [Document No_]
WHERE prh.[Buy-from Vendor No_] = '1001'
GROUP BY [Purchaser Code]
Date
Votes
2 comments
-
Jet Reports Historic Posts Are you getting an error or is it creating the rows but not sorted?
-
Jet Reports Historic Posts If you use the Item Ledger Entry table this gets easier. posted document lines have very few keys that are useful for reporting, as they are held to allow re-printing of documents only. Wherever possible use the ledger entries as they have lots of keys and contain most of the data that you probably need.
Malcolm
Please sign in to leave a comment.