I am being asked to write a report that shows the top products a customer has bought. The top 5 are basically the top 5 products that customer has bought in the past 12 months. How would i set up the expression to do this?
Well, best way is to break down the task and ask yourself, “What data do I need to accomplish this?” Obviously CustID and Part# but what else?
Also define “past 12 months”: By Order Date? Ship Date? Invoice Date?
“Top 5 parts” by what metric? Price? Qty ordered?
Getting those answers will lead you to what other data tables you’d need; ie Invoice and/or Order info. From this you can gather your list of Table and Field names and build your query to pull the data.
From the above you can make a query to pull all the data from X-CustID for Y-months then use Excel (or other spreadsheet) to aggregate the data. Another is to expand the query to do it inside Epicor.
2 Likes
Sorry for not responding sooner. Was out to a funeral yesterday. But the basic BAQ is easy to write, my question really lies in how to get Epicor to show me in the BAQ only the top 5. Is that even possible? I am wanting to import hit into a Dashboard and so excel is not much of an option.
Yes, in the SubQuery Options tab set Result Set Rows to “top” and the Top Clause settings per your needs. You may need to use a subquery to aggregate the data as well.
Sorry for not being more plain, I know that is a areal problem when someone is trying help me. So, I see where ti takes the top 5 rows, but I am looking for top 5 “revenue.”
I have customer–>OrderHead–>OrderDtl
“Revenue” = sum(orderdtl.docextpricedtl)
My other two displays are customer id and part number.
I am needing the top 5 part numbers based on the “revenue.”
You’ll want to sort order by “Revenue”
1 Like
How can I make it do the top 5 per customer? That I still haven’t figured out.
On subquery options, select resultset rows of TOP and put 5 in the ROWS box.
This may get you what you need. (Works for E9)
With TopSellers
AS
(
SELECT c.Name
,id.partnum
,SUM(id.DocExtPrice) as ‘SalesAmt’
,ROW_NUMBER() OVER (PARTITION BY c.Name ORDER BY sum(id.DocExtPrice) DESC) AS RowNum
FROM invcHead i
inner join customer c with (nolock) on i.CustNum = c.Custnum and i.company = c.company
Inner join InvcDtl id with (nolock) on i.invoicenum = id.invoicenum and i.company = id.company
Inner join Part p with (Nolock) on id.partnum = p.partnum and id.company = p.company
where i.InvoiceDate BETWEEN DATEADD(DAY, -365, GETDATE()) AND GETDATE()
AND i.company = YourCompany
GROUP BY c.Name, id.PartNum
)
SELECT * FROM TopSellers Where RowNum <= 5
1 Like
I tried that it it just takes the top 5 of the whole report not per customer.
Break this down into subqueries and place the TOP condition on the proper subquery maybe?
Sounds like you are looking for like a group By as well with the customer so this is in line with that.
2 Likes
@Matthew_Lawless, yes it works for E10 with a few tweaks in SQL SSMS. I was also thinking of how to do a count on the Customer and then use that to filter the “top 5” so thanks for posing the SQL Query. Just be sure to change ‘YourCompany’ to the real value.
With TopSellers AS (SELECT c.name ,id.partnum ,SUM(id.DocExtPrice) as [SalesAmt] ,ROW_NUMBER() OVER (PARTITION BY c.Name ORDER BY sum(id.DocExtPrice) DESC) AS RowNum FROM erp.InvcHead as i inner join customer as c on i.CustNum = c.Custnum and i.company = c.company Inner join erp.InvcDtl as id on i.invoicenum = id.invoicenum and i.company = id.company Inner join Part as p on id.partnum = p.partnum and id.company = p.company where i.InvoiceDate BETWEEN DATEADD(day, -365, GETDATE()) AND GETDATE() AND i.company = 'YourCompany' GROUP BY c.Name, id.PartNum ) SELECT * FROM TopSellers Where RowNum <= 5
3 Likes
I wonder if you could use a combination of these two ideas. Do the sub with the top 5, then concatenate those top five into a list. Then you can feed that sub query into each row like it’s own little sub query so that you would get all 5. They would be comma (or whatever character you want) delimited, and I suppose if you felt like doing the work, could pull them back out into columns again. They you would have row per customer and a column for the top, second, third etc.
Seems convoluted, but I bet it could work. (and there’s probably an easier way)
I got it to work by making a BAQ with a subquery no TOP clauses needed at all. I built it off of Invoice tables as it’s more accurate with actual billed orders but same concept if you want to use Orders.
2 Likes
If you reference the SQL query that @Matthew_Lawless posted, it has a calculated field that counts the rows per custnum by the sum of Invoice totals:
ROW_NUMBER() OVER (PARTITION BY customer.Name ORDER BY sum(invcdtl.DocExtPrice) DESC)
Which I put in the subquery then used Criteria to limit the main query to <= 5 per the first screen shot.
This isn’t perfect, if a customer orders the same part over time this BAQ will not total all those invoices for that part. It may need a another subquery to sum up all invoices (orders) by partnumbers.
Wouldn’t just a sum(invoice amount) give you the sum for each part?
You could try that.
It looks like it works. Thanks for all the help!
1 Like