This is probably something straight forward for some of you and I believe there is a better way to do it, I’m working in a BAQ to get the Total Orders (count), Total Order Amount (sum of OrderHed.OrderAmt), Total Selling Qty (sum of OrderDtl.SellingQuantity) and Total Shipped Qty (sum of OrderRel.SellingStockShippedQty) of the current month besides that I need to filter by the current user some data from a UD table (this is linked to the orderrel table) which stores sales rep commissions so only the sales rep can see its information. What I’m doing is to create two inner subqueries to get the sum of the OrderDtl and OrderRel values and then in the top level query getting the count of orders and the sum of the order amount, the problem is that I’m filtering in each inner subquery the current month get linking each table again. Can someone of you take a look on the BAQ and suggest me what changes I need to do in order to improve it please? It is in Epicor 10.1.600
I think you’re overcomplicating things. You don’t need the sub query to get the summary data in this instance. The only other thing I see missing is a field to get the month and year. These are functions in the BAQ or you can use SQL datepart function.
Thank you Jason for the answer, the filtering is not the main problem I can use the baq constant to filter it, the problem I’m dealing with is to get a single record for example in the current month there are 7 orders if I group by Company, OrderNum I get the 7 order with the total amount and the total selling qty but I would like to only display a single record as the Grand Summaries:
