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?

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 bett…

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 t…

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.

I don’t need to see closed lines, only open lines. I need the sum of the Calculated_OpenLineValue for each different OrderHed_OrderNum. [2019-02-04%2016_48_25-Business%20Activity%20Query%20Designer]

[image] tracyjohnson: I don’t need to see closed lines, only open lines. I need the sum of the Calculated_OpenLineValue for each different OrderHed_OrderNum. [2019-02-04%2016_48_25-Business%20Activity%20Query%20Designer]

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 differen…

To follow up on Brandon says here: [image] Banderson: You will have to remove the OrderLine, order qty, and OrdBasedPrice fields, because those will be different so they won’t group If you in fact need those fields, you can make your query a sub query then on the top level, link your main ta…

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…

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. [image] BAQ results on two lines rather than on 1 line ERP 10 Below is a pictorial step by …

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…

This is the TOP level query [TOP%20Query]

Subtotaling order lines within BAQ at order number

Epicor ERP 10

Banderson (Brandon Anderson) February 5, 2019, 12:08pm 9

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.

Topic		Replies	Views
BAQ "subquery" help Kinetic ERP baq	20	1121	May 6, 2025
Need a BAQ (w/subquery) to return top 10 standard products sold out of two shops for the past 5 years Epicor ERP 10 baq	8	620	March 17, 2021
Sum lines of multiple quotes in a BAQ Epicor ERP 10 e10 , baq	4	1553	September 16, 2021
BAQ Challenge - How to convert this SQL with sub-selects (as Calc'd fields and where clauses) to a BAQ Epicor ERP 10 baq	21	6627	February 25, 2019
BAQ sum orders added by week Kinetic ERP baq	5	1522	September 21, 2022

Subtotaling order lines within BAQ at order number

Related topics