I’m trying to educate myself on BAQs still and some more . . . . and need help generating a BAQ to rank our customers by the total of their invoices for the year. Can anyone give me some pointers?
What you’re going to want to do is create a simple BAQ with only the Customer and InvcHead tables. Add the Customer table first and then add the InvcHead table using the Company and CustNum as the Table Relation fields. For fields to add in the Display tab, I would only add the Customer.CustID, Customer.Name, and InvcHead.InvoiceNum. Once you’ve set up the query this way, you’ll want to make a Crystal Report for it do the following with the data:
- Group the records by the customer number or name
- Add the Invoice number field to the detail section and add a Unique Count summary to the customer group footer
- Sort the records by that summary count value
Unfortunately, as far as I’m aware, there isn’t a method to sort the data this way in a dashboard or query in Epicor 9. Please tell me if you’re having difficulty with any of my instructions!
There are probably several ways to do this, but one way is to bring the customer and invoice headers tables.
Join the invoice to the customer and then make the invoice header table a summary table. Add criteria to the invoice header table to limit records to this fiscal year.
Then build a calculated field to total up the invoice header amounts.
By the nature of the link the result will be a total invoice value per customer.
In the display/sort screen sort by the calculated field to get the ranking.
This same scheme can get you parts rankings, vendor rankings, etc. by changing out the tables.
Once you have what you want then push it into a dashboard.
Brad Boes
bradboes@boosterpconsulting.com
231-845-1090
Hey Brad,
I think I’m with you up until this part:
“Then build a calculated field to total up the invoice header amounts.”
Can you be more specific about how to perform this function?
OK, so because the invoice header table is in summary mode you cannot display any invoice header fields directly.
On the BAQ display tab use the calculated field button to start a new calculated field and title it InvSum or similar. Set it as a decimal field and give it a label.
Then go to the aggregate function and find the Total(). Then add the invoice amount field in the parentheses.
My example below is a vendor ranking, but very similar.
Brad Boes
bradboes@boosterpconsulting.com
231-845-1090
Just to note, there are some versions of Epicor 9 where this function doesn’t work correctly.
Ah. Thanks for the heads up.
I have this working on versions 9.05.702A SQL Unicode and 9.05.700 Progress.
Brad Boes
bradboes@boosterpconsulting.com
231-845-1090
Hey Brad,
I’m just now getting back to working on this BAQ due to the holiday schedule. I followed your instructions, and this is the error I get. Any suggestions?
Can you post a screen shot of your invoice header criteria?
And your calculated field?
It looks like one of those two.
Brad Boes
bradboes@boosterpconsulting.com
231-845-1090
Good afternoon, gentlemen. I am just now getting back to this project again and am still having problems. It seems you are both coming at it with a little different approach. Per Calvin’s suggestion, I added the field name “InvoiceDate” and then got an error message stating I couldn’t display the invoice date or invoice number because InvcHead is a summary table. So I removed those two items from the display and now get this message.
What does your invoice header table criteria look like now?
Brad Boes
bradboes@boosterpconsulting.com
231-845-1090