Year wise Sales report summary by Customer by month by item in Qty

[MukeshSingh]

Expert,

Please help we need a report from SAP Business One

Year wise Sales report by customer by month by item in Qty

Example

                                                                                                         Monthwise Data

Customer Name ! Customer Group ! Item Code ! Description ! UoM ! jan ! Feb ! march ! April ! May ! June ! July ! Aug ! Sep ! Oct ! Nov ! Dec

ABC Co                Export              F00001         MS Plate      MT    5     5.5    4          3        0       9       5       1       12     .5     9       6

XYZ Co                 Local                F00002         MS Chanel   MT    4     4.5    3          9        6       5       7       0       9.5     4     6       9

Thanks in advance export guru

Thanks again

Mukesh Singh

[nikunjmehta2290]

Can you help me this report into SAP Business One HANA?

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT P.CustCode, P.CustName, P.ItemNo, P.Name,
ISNULL([1],0) as 'Jan',

ISNULL([2],0) as 'Feb',

ISNULL([3],0) as 'Mar',

ISNULL([4],0) as 'Apr',

ISNULL([5],0) as 'May',

ISNULL([6],0) as 'Jun',

ISNULL([7],0) as 'Jul',

ISNULL([8],0) as 'Aug',

ISNULL([9],0) as 'Sept',

ISNULL([10],0) as 'Oct',

ISNULL([11],0) as 'Nov',

ISNULL([12],0) as 'Dec'

FROM

(SELECT T1.CardCode as CustCode, T1.CardName as CustName, (T0.ItemCode) as ItemNo, T0.Dscription as Name,

T0.Quantity as Qty,

MONTH(T1.docdate) as month

FROM dbo.dln1 T0 Inner Join ODLN T1 ON T0.DocEntry=T1.DocEntry

WHERE Year(T1.docdate) = 2013 and  (T1.CardCode between 'XXX' and 'XXX' or T1.CardCode between 'XXX' and 'XXX')

UNION

SELECT T1.CardCode as CustCode, T1.CardName as CustName, (T0.ItemCode) as ItemNo, T0.Dscription as Name,

T0.Quantity as Qty,

MONTH(T1.docdate) as month

FROM dbo.rdn1 T0 Inner Join ORDN T1 ON T0.DocEntry=T1.DocEntry
WHERE Year(T1.docdate) = 2013) S

PIVOT (SUM(Qty) FOR month IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
Group by  P.CustCode, P.CustName, P.ItemNo, P.Name,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Note: Replace XXX with your card code

Hope helpful.

Thanks & Regards,

Nagarajan

[MukeshSingh · Accepted solution]

Nagarajan Sir,

Thanks for your query, but need one extra help

this query need parameter only BP customer group wise and year wise

It's possible then please suggest

Thanks boss again

Mukesh Singh

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query only with customer group selection. Not possible to select year wise.So you need to change Year(t1.docdate) = 2012 or 2013  in above query.

/* select  * from OCRG k1 */
DECLARE @name as nvarchar(25)
SET @name = /* k1.groupname */ '[%0]'

Select [code] as CustomerCode, [Na] as CustomerName, [P] as Item#, [U] as UOM, [N] as Groupname,ISNULL([1],0) as 'Jan',

ISNULL([2],0) as 'Feb',

ISNULL([3],0) as 'Mar',

ISNULL([4],0) as 'Apr',

ISNULL([5],0) as 'May',

ISNULL([6],0) as 'Jun',

ISNULL([7],0) as 'Jul',

ISNULL([8],0) as 'Aug',

ISNULL([9],0) as 'Sept',

ISNULL([10],0) as 'Oct',

ISNULL([11],0) as 'Nov',

ISNULL([12],0) as 'Dec'

from(

SELECT T1.[CardCode] as Code , T1.[CardName] as Na, T0.[ItemCode] as P, T0.[Dscription] as D, T0.[unitMsr] as U, sum(T0.[Quantity]) as Qty,t3.groupname as N, month(t1.docdate) as month FROM DLN1 T0  INNER JOIN ODLN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.BaseCard = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode  where  t3.groupname = @name and year(t1.docdate) = 2013
GROUP BY T1.[CardCode], T1.[CardName], T0.[ItemCode], T0.[Dscription], T0.[unitMsr],t3.groupname,t1.docdate

union all

SELECT T1.[CardCode] as Code , T1.[CardName] as Na, T0.[ItemCode] as P, T0.[Dscription] as D, T0.[unitMsr] as U, -sum(T0.[Quantity]) as Qty,t3.groupname as N, month(t1.docdate) as month FROM RDN1 T0  INNER JOIN ORDN T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OCRD T2 ON T0.BaseCard = T2.CardCode INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode  where  t3.groupname = @name and year(t1.docdate) = 2013
GROUP BY T1.[CardCode], T1.[CardName], T0.[ItemCode], T0.[Dscription], T0.[unitMsr],t3.groupname,t1.docdate

) S

pivot
(sum(qty) for MONTH IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

group by [code],[Na],[P], [U],[N],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Hope helpful.

Thanks & Regards,

Nagarajan

[MukeshSingh · Accepted solution]

Nagarajan Sir,

Thanks you very much

Regards,

Mukesh Singh

[nikunjmehta2290 · Accepted solution]

How to convert this query into SAP Business One HANA?