I am fairly new to Epicor and this website is a tremendous amount of help to a newby like myself. I tried researching this issue, but I haven’t come across a definitive explanation.
I have been making a lot of BAQs lately and one problem that seems to persist is when my BAQ is showing more lines than what is desired. An example of this is when I recently was asked to make a report showing each of our product lines and the revenue next to it. I was able to successfully do this, but then I was asked to add COGS and Margin. Instead of having one line for each product category (like it showed when it was simply looking at revenue), the report tripled in the amount of lines and I cannot figure out why.
I have played around with the Group-by function a lot and I still cannot figure out what it is supposed to do. From what I can glean from Epicor…whenever I add a calculated field and check the syntax, I get an error message saying I need to add “___” field to the aggregate or group-by. So, I have gotten around this by trial & error from checking and unchecking the group-by’s until the report will run.
I’m guessing there are people here with a much deeper understanding of Group-by. Perhaps, I could get some advice?
I always had trouble with Group-bys when I started learning SQL, the basic rule you have to remember is ALL of the display fields either have to have Group By checked, or be some sort of calculated field, that could be SUM, AVG, etc. But if you have a field that is not calculated and not included in the group by, you’re gonna get an error.
GROUP BY is windowing on hard mode. This isn’t a bad summary of how to window aggregates: Window Functions in SQL | GeeksforGeeks And there are many other “window function” tutorials online if that one doesn’t make sense.
If you can understand windowing, you will understand GROUP BY. GROUP BY work exactly the same way, but with added gotchas and constraints. The syntax is split into two different places. It can only aggregate over the SELECT statement, not part of it, not anything outside of it. It requires maintenance whenever you update your SELECT statement.
GROUP BY is honestly only worth the hassle for legacy SQL functions that haven’t been made compatible with windowing, like STRING_AGG. STRING_AGG is the only place I have to use GROUP BY any more. When I do, I partition the GROUP BY in an inline table expression (subquery in BAQ-ish) to limit the scope of having to deal with it.
That’s how I recommend using GROUP BY - make a little GROUP BY jail in a subquery so you can ignore it like it’s a windowing function.
So, if you have duplicate rows that you aren’t expecting… a bandaid is to group everything, or return only distinct rows.
However, I challenge you to look at your data and really understand why you are getting rows that you didn’t expect. Either a bad join that isn’t matching things properly, or a table that you don’t quite understand the data. Not doing that can really get you into trouble by unintentionally combining rows.
I would only use the group by when you are actually doing some aggregate function, where you are summming up something, then intentionally combining rows.
Here’s a screenshot of what the BAQ is doing (there are numbers to the right of these words, but I don’t want to get in trouble for posting those). Again, before I added the costs (to get to the GM), it listed each of these categories only one time with the total revenue next to each).
You also mentioned you are getting duplicate rows.Like @Banderson above me said, its important to try to understand why you are getting rows you arent expecting.
In this example, I am getting those duplicate rows because this is the PODetail table. Each “duplicate” PO num is actually a different line in the PO!
Thanks Ian. If I could ask you a couple questions about your example:
If I were trying the BAQ you showed, I would create the calculated field (as you did) and then I would remove “DocExtCost” from the display fields pane. However, when I do this…I get an error saying “DocExtCost” is not included in the aggregate or group by. How does one get around this?
Let’s say you add something to your BAQ such as “supplier name”. I would expect that it wouldn’t add lines to your BAQ - because even though PO 32125 has 6 lines, the supplier is a single name. However, my experience has been it changes the BAQ closer to what your left screenshot is and lists the supplier name (in this case) 6 times. Hopefully that made sense what I just tried to explain, but…does this happen to you as well?
Like the Example @imak posted you’re probably linking into a *Dtl table which could have many rows per PONum in their case. This is why you have multiple rows.
You can consider a SubQuery for the Detail table or the windowing functions mentioned by @kananga as two possible solutions to your duplication problem. Plus like @Banderson I recommend you get some basic SQL select query knowledge. This video could be helpful to you to learn how Epicor database (relational databases) work.
Thanks Brandon. I haven’t watched the entire thing yet, but the video is good. I have already learned a few things from it.
Side note: at a previous job, one of our suppliers was Stephen Gould but we pronounced Gould as “Gold” (I thought that was how it was pronounced before watching your video). Therefore, I was going to say "Thanks Brandon. That video was “Gould”. Trust me, it would have been funny
