I have a BAQ created for open lines on an order. I need to pull out the open order value - subtotal the lines by order number. Any help for this newbie?
Post what you have so far. Off the top of my head, you need a calculated field sum(orderamount) and group by the order number. Filter your table by open line.
But, without some more context of what you want/need to see how, I don’t know for sure which is the best way. There are lots of ways to do this. (and you probably don’t understand what I am talking about if you are really new to BAQ’s 
)
Do you need to see closed lines in the report too?
If not:
- filter out the closed lines by adding a Table Criteria to OrderDtl with OpenLine = 1.
- Add a calculated field containing SUM(OrderDtl.ExtCost). All other displeyd lines would need to have their Group By box checked.
1 Like
I don’t need to see closed lines, only open lines.
I need the sum of the Calculated_OpenLineValue for each different OrderHed_OrderNum.
So in your calculated field that you already have, add
Sum(thestuffyoualreadyhave)
then in all of the other fields check the group by box. You will have to remove the OrderLine, order qty, and OrdBasedPrice fields, because those will be different so they won’t group. (and anything that is different by line that that I might have missed)
If you leave the open or closed in there you will get a value for open lines and closed lines. I see you already have your criteria in there. You can remove that field in the display if you want, it won’t hurt anything though.
Make sense?
2 Likes
To follow up on Brandon says here:
If you in fact need those fields, you can make your query a sub query then on the top level, link your main table with all the fields you want back to the subquery.
1 Like
now I am lost. I haven’t had success with that grouping field/column and now you are talking sub query. I need to educate myself a bit more…
1 Like
As always, start with the help files and tools guide, but here is another thread where I walk someone through a subquery step by step.
1 Like
select
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[OrderHed].[OpenOrder] as [OrderHed_OpenOrder],
[SubQuery2].[Calculated_OpenLineValues] as [Calculated_OpenLineValues]
from Erp.OrderHed as OrderHed
inner join (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
(Sum(OrderDtl.OrdBasedPrice * OrderDtl.OrderQty)) as [Calculated_OpenLineValues]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OpenLine = 1)
group by [OrderDtl].[Company],
[OrderDtl].[OrderNum]) as SubQuery2 on
OrderHed.Company = SubQuery2.OrderDtl_Company
and OrderHed.OrderNum = SubQuery2.OrderDtl_OrderNum
1 Like
I included the BAQ, you can download it then import it in Business Activity Query > Actions> Import. This is the subquery. You can make a subquery by using the buttons in the subquery ribbon. I am being vague because like @Banderson said, reference the tools help guide, it will explain it a lot better than I can.
OrdersWithOpenLinesSum.baq (22.9 KB)
1 Like
Thank you all for your help!
2 Likes