cancel
Showing results for 
Search instead for 
Did you mean: 

production report displaying months

Former Member
0 Kudos
83

hi ,

can anyone help me in getting month wise production report... i e from jan to dec.... it shoulld display all months and show month wise production....

example

ITEM ITEM DESCRIPTION JAN FEB MARCH

DS01 SCREW 10 10 30

regards,

Vignesh

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member1269712
Active Contributor
0 Kudos

Hi,

Try this...

select DATENAME(month,T0.Docdate),T1.ItemCode,T1.Dscription,sum(T1.Quantity) from oign T0 
inner join ign1 T1 on T0.Docentry=T1.Docentry
Where T0.JrnlMemo like 'Receipt from %' 
group by DATENAME(month,T0.Docdate),T1.ItemCode,T1.Dscription

If you want tabular format then use pivot table for same.

Thanks

Sachin

Former Member
0 Kudos

hi sachin,

i am new to sap , can u help me to pivot monthwise....thanks in advance

regards,

Vignesh

former_member1269712
Active Contributor
0 Kudos

Hi Vignesh,

Try this....

select T1.ItemCode,T1.Dscription,
sum(Case DATENAME(month,T0.Docdate) when 'January' then T1.Quantity else 0 end) Jan,
sum(Case DATENAME(month,T0.Docdate) when 'February' then T1.Quantity else 0 end) Feb,
sum(Case DATENAME(month,T0.Docdate) when 'March' then T1.Quantity else 0 end) Mar,
sum(Case DATENAME(month,T0.Docdate) when 'April' then T1.Quantity else 0 end) Apr,
sum(Case DATENAME(month,T0.Docdate) when 'May' then T1.Quantity else 0 end) May,
sum(Case DATENAME(month,T0.Docdate) when 'June' then T1.Quantity else 0 end) Jun,
sum(Case DATENAME(month,T0.Docdate) when 'July' then T1.Quantity else 0 end) Jul,
sum(Case DATENAME(month,T0.Docdate) when 'August' then T1.Quantity else 0 end) Aug,
sum(Case DATENAME(month,T0.Docdate) when 'September' then T1.Quantity else 0 end) Sep,
sum(Case DATENAME(month,T0.Docdate) when 'October' then T1.Quantity else 0 end) Oct,
sum(Case DATENAME(month,T0.Docdate) when 'November' then T1.Quantity else 0 end) Nov,
sum(Case DATENAME(month,T0.Docdate) when 'December' then T1.Quantity else 0 end) Dec
from oign T0 
inner join ign1 T1 on T0.Docentry=T1.Docentry
Where T0.JrnlMemo like 'Receipt from %' 
group by DATENAME(month,T0.Docdate),T1.ItemCode,T1.Dscription

Hope it will help you...........

Thanks

Sachin

former_member1269712
Active Contributor
0 Kudos

Hi,

Also you can use pivot table as below...

SELECT T1.ItemCode, [1]as [Jan], [2]as [Feb], [3] as [Mar], [4]as [Apr], [5]as [May], [6]as [Jun],
[7]as [Jul],  [8] as [Aug], [9]as [Sep], [10]as [Oct], [11]as [Nov], [12]as [Dec] FROM 
(select DATENAME(month,T0.Docdate),T1.ItemCode,T1.Quantity from oign T0 
inner join ign1 T1 on T0.Docentry=T1.Docentry
Where T0.JrnlMemo like 'Receipt from %') p
PIVOT
(sum(Quantity) for Quantity in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))
AS pvt
ORDER BY ItemCode

Thanks

Sachin

Former Member
0 Kudos

thanks a lot sachin....... really solved my problem so easily.

Former Member
0 Kudos

If your problem solved, please close the thread by marking it as answered.

Thanks,

Gordon