Skip to Content
0

SAP B1 Top Selling Product/Item Per Month Query

Aug 22, 2017 at 07:00 AM

58

avatar image
Former Member

Hi Guys

How ca I make a query that can generate top selling products per month that is less returns from customers all ready (Credit memo),

Thanks in advance

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Aug 23, 2017 at 06:20 AM
0

Hi,

For Crystal Report purposes you may try this query:

DECLARE @FROM AS DATETIME = CAST('2017/01/01' AS DATETIME)
DECLARE @TO AS DATETIME = CAST('2017/12/31' AS DATETIME)
SELECT i.ItemCode
     , i.ItemName
     , ISNULL(s.Quantity, 0) - ISNULL(c.Quantity, 0) AS Quantity
     , ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0) AS TotalAmount
     , ISNULL(s.GrossProfit, 0) - ISNULL(c.GrossProfit, 0) AS GrossProfit
     , (ISNULL(s.GrossProfit, 0) - ISNULL(c.GrossProfit, 0))
       / CASE
          WHEN ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0) = 0 THEN 1
          ELSE ISNULL(s.LineTotal, 0) - ISNULL(c.LineTotal, 0)
         END AS GrossProfitPercentage
FROM OITM i
     LEFT OUTER JOIN (SELECT sr.ItemCode, SUM(sr.Quantity) AS Quantity,  SUM(sr.LineTotal) AS LineTotal
                          , (SUM(sr.LineTotal) - SUM(sr.GrossBuyPr * sr.Quantity)) AS GrossProfit
                      FROM INV1 sr
                           INNER JOIN OINV sh ON sr.DocEntry = sh.DocEntry
                      WHERE sh.DocDate BETWEEN @FROM AND @TO
                      GROUP BY sr.ItemCode) s ON i.ItemCode = s.ItemCode
     LEFT OUTER JOIN (SELECT cr.ItemCode, SUM(cr.Quantity) AS Quantity,  SUM(cr.LineTotal) AS LineTotal
                           , (SUM(cr.LineTotal) - SUM(cr.GrossBuyPr * cr.Quantity)) AS GrossProfit
                      FROM RIN1 cr
                           INNER JOIN ORIN ch ON cr.DocEntry = ch.DocEntry
                      WHERE ch.DocDate BETWEEN @FROM AND @TO
                      GROUP BY cr.ItemCode) c ON i.ItemCode = c.ItemCode
ORDER BY GrossProfit DESC

Please note that you can test this query as is, but that you will have to edit the first two lines before you can use it with Crystal Reports.

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Johan Hakkesteegt Aug 22, 2017 at 07:10 AM
0

Hi,

No need for a query. You may use the Sales Analysis report (Sales - A/R > Sales Reports > Sales Analysis).

Choose the Items tab page, and run the report by invoices. This report you can sort either by sales or gross profit to see the top x selling items.

In the report you may double click on the line number to get a detailed report of invoices and credit memos on which the line's information is based.

Regards,

Johan


Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Johan Hakkesteegt

thank you for the advice ! but I need the query so i can make it in crystal report sir.

hmmm

0