Hello fellow sufferers,
I am in need of some real guidance regarding the group by functions with Epicor BAQ. I have watched what tutorials i can, but when i try to apply it into practice, I am continually running into issues. Currently, I am trying to create a Daily Production Tracking BAQ that will allow our supervisors the ability to see by Job, what was completed and when, to include the overall completed to date and calculated efficiency. I would like a version of this that doesnt repeat for every single labor transaction but instead aggregates or summarizes by Job.
Anyone willing to help walk me through this process? I am willing to paypal over a little compensatory thank you if anyone can see me through it, I am kind of at my wits end.
I see your offer to pay for help has garnered some attention! Haha. In all seriousness, you can do this!! Hang in there!
Start with something simple. For example, just pull in LaborDtl table for example. Add the JobNum column to your display. Check the Group By box.
Now, add a calculated field. sum(LaborDtl.LaborHrs)
Run it. This should give you labor hours by job. Whenever you run it, it would update with the latest data. If you want qty instead of hours, you can pick a different column to sum that is Qty based.
My advice is to start super simple. Then slowly add on.
Adding “Group By” will make multiple rows into a single row if all columns with “Group By” checked have the same values. If you add group by to any column, all regular columns need to have it checked. You can also add “Aggregate” columns that will add all the values of the grouped columns together.
Example: I have a BAQ of InvcHead (AR Invoices). I have the fields Company and CustNum selected and “Group By” checked. I have an additional Calculated field “CustTotal” with the expression SUM( InvcHead.InvoiceBal ) added and not grouped. This will give me a single row for each customer (Grouping all the rows with the same Company and CustNum). My aggregate field adds together the Invoice Balance for all rows for that customer.
If I added the “InvoiceNum” field, it would still show every separate invoice, even if I had GroupBy checked. This is because the values in this field are not the same (unique invoice numbers), so it doesn’t group them together.
For your example, you probably have a field from the LaborDtl added (time or date, maybe LaborDtlSeq). Since those values are different for each row, it will not group them together.
Dan, i think things might have finally clicked for me after doing it one step at a time like this. I had been utilizing expressions in the advanced group by function where i should have been using a combination of calculated fields and standard group by check boxes. I may be wrong in that assumption, but if not, i may be cooking over here.