So I’ve been tasked with finding out what our top 10 selling standard products are from two of our shops for the past 5 years. They want it based on Sales Orders (not invoices) and measured by quantity (not revenue) and we will use OrderHed.OrderDate to measure date. At first I ran the BAQ and was going to pull it over to Excel to aggregate manually, but then I realized a subquery could much more quickly accomplish this. (Well, it would have had I been able to create it yesterday when I started trying lol.) I have only created a couple subqueries and it’s been a while, however, so I could really use some help. I’ve gone back to the Epicor Learning Center video and re-read the Tools User guide to try to get some clarity, but it’s not helping enough, as those examples are pretty basic, and I need to exclude a few customers and also exclude all our parts on the fly. I would so much appreciate some (specific!) help from someone who really knows their way around a subquery! Please and thank you in advance!!
I wouldn’t mess around with a subquery I don’t think… the group by with a few calculated fields should get you there.
OrderHed and Order Dtl joined. Filter out your customers and maybe bring in the part table and join it to OrderDtl to filter out parts on the fly. For display fields, put out part number. Add a filter on the OrderHed table for your date range.
For the part number field you are displaying, check the group by box. Then do a calculated field. You can do aggregate expressions without grouping them… so Sum ( order detail qty)…
I would personally do all that in excel and get the detail for all related transactions so I could look at the trend by year and what not without making the BAQ more complicated then it needs to be. Unless you are trying to put it in a dashboard or report on it in some way and don’t want the manual step since it isn’t a one time thing.
Thanks for your suggestion! I’m actually trying it now without a subquery, so I’ll start fresh using your post and see what happens. I’ll update once I’m done.
Hey! So I checked mine with yours and they’re the same (at the moment) but when I create the calculated field I get this error…how do I fix it? I’ve tried many ways except the right way.
Not sure. Did you check the group by box after you checked the calculated field. The timestamp on your screenshot is from after but not sure when you checked it. Maybe check again. I just tried with only the orderdtl table and it works fine like this…
I went back and checked and it still gave the same error. But then, just to see, I tried a different column (OrderDtl.OrderQty) in the expression > SAVE > Check syntax, and look what the error says? It’s still referencing the previous field (OrderDtl.SellingQuantity). Why? I deleted the calculated column and started fresh with the OrderDtl.OrderQty field again but still am receiving error for OrderDtl.SellingQuantity. Strange. I’m going to reboot and come back in. If you have any thoughts on why this might be happening, I’m all ears.
Aha! I was sorting by the SellingQty field. I removed that and replaced with the OrderQty field and now it’s all working. Thank you so much! I am embarrassed to admit I’ve been tinkering with this for two full days now. Smh.
