I have to create a report that shows open orders, the maximum that can be shipped (there's an over and under allowance), the value if we ship the max and the shipping releases for the orders (there can be multiple releases for an order).
Column Definitions (with examples):
Ord# - Order#
Qty - Quantity Ordere
UP - Unit Price
Ovr% - Allowed Qty to overship, as a percentage of ordered
Max - max we can ship
Value - Sales value of the max
Date - Planned shipping date
QtyShp - Qty to ship (as ordered, do not add overage allowance)
---------------------------------------------
[ABC] <--- Grouping per customer
Ord# Qty UP Ovr% Max Value Date QtyShp
1234 100 1.00 5% 105 105 08/20 60
09/01 40 <--2nd Rls of above order
1244 200 .50 2% 204 102 09/01 200
Customer ABC Total ... 207
----------------------------------------------
Value is totalled for each customer (grouping).
The significant tables are ORDERS and SHIP_RLS. I built a query to capture the information. Of course, for each release there is a row (which is why the Value gets messed up). I tried something I knew wouldn't work, but, had to do try, right? - I selected "Yes" to Hide Duplicates for the Value column. Of course, the Total comes to 312.
Is this a situation for a sub-report? If so, how. If not, what's the technique?
Column Definitions (with examples):
Ord# - Order#
Qty - Quantity Ordere
UP - Unit Price
Ovr% - Allowed Qty to overship, as a percentage of ordered
Max - max we can ship
Value - Sales value of the max
Date - Planned shipping date
QtyShp - Qty to ship (as ordered, do not add overage allowance)
---------------------------------------------
[ABC] <--- Grouping per customer
Ord# Qty UP Ovr% Max Value Date QtyShp
1234 100 1.00 5% 105 105 08/20 60
09/01 40 <--2nd Rls of above order
1244 200 .50 2% 204 102 09/01 200
Customer ABC Total ... 207
----------------------------------------------
Value is totalled for each customer (grouping).
The significant tables are ORDERS and SHIP_RLS. I built a query to capture the information. Of course, for each release there is a row (which is why the Value gets messed up). I tried something I knew wouldn't work, but, had to do try, right? - I selected "Yes" to Hide Duplicates for the Value column. Of course, the Total comes to 312.
Is this a situation for a sub-report? If so, how. If not, what's the technique?