I am trying to look at items that have demand. Then, based on the demand I want to sort them by Vendor. Next, do NL(Rows) to show open Sales orders and open Purchase orders.
I want the report to look like this.
Vendor A
Item 123 Qty on Hand 5
Sales order 123456 outstanding Qty 25
Sales order 234567 Outstanding Qty 10
Purchase Order 987654 Balance Due 25
Net amount to order on new PO 5
I cannot figure out how to filter my original NL(Row). When I look at the item table I want only items with negative availability. I want:
Qty on hand + Outstanding on PO - Outstanding on SO - Reorder Point.
I also need to have the NL(Row) group on Vendor No.
If someone can show me how to get the above calculation into the first NL(Row) that would be wonderful!!
Thanks,
Amy Becker
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi Amy -
To achieve a true grouping report, you would want to following the design as demonstrated in the five "Grouping Report" walkthrough videos found in the Jet Reports online Knowledgebase (http://kb.jetreports.com/article/AA-00731). They're under the "Creating Reports" section.
If what you want is to just sort your list by vendor #, see the sorting article (http://kb.jetreports.com/article/AA-00518).
It is certainly possible (albeit a bit challenging) to filter by a formula. Assuming that the names you list are the actual field names, it would look something like this:
=NL("Rows","{table_name}",,"=NF(,""Qty on hand"")+NF(,""Outstanding on PO"")"-NF(,""Outstanding on SO"")-NF(,""Reorder Point""),"NUMBER&<0")
I hope that help. -
Jet Reports Historic Posts thanks for the info. this sort of makes sense to me. However, I need to reference info from more than one table.
=NL("Rows","item",,"=NF(,""Qty on hand"")+NF(,""Outstanding on PO"")"-NF(,""Outstanding on SO"")-NF(,""Reorder Point""),"NUMBER&<0")
Qty on hand and Reorder Point - both from the Item table
Outstanding on PO - from Purchase line table
Outstanding on SO - from Sales line table
Is it possible to set up this line accessing more than one table?
Again, thanks for all of your help.
Amy
Please sign in to leave a comment.