SAP Crystal Report Query for Row Split in Column

[former_member252592]

Hi experts

i made the following query

SELECT

T3.DocNum, T4.ItemCode, sum(T3.[PlannedQty]) as 'Print Qty' , sum(T4.PlannedQty) as 'Plan Qty',

T0.[u_Dname] as 'Dept Name',T0.[u_Oname] as 'Optr Name',sum (T0.U_PRWAST1) as 'Pr Wast',

sum(T0.U_PLWAST1) as 'Pl Wast',sum (T0.U_TWAST1) as 'T Wast',

datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) as 'Month'

FROM

OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry

left outer join oitm t2 on T2.[ItemCode]=T1.[ItemCode]

LEFT OUTER JOIN OWOR T3 ON T3.DOCNUM=T1.[BaseRef]

left outer join WOR1 T4 on T4.DocEntry = T3.DocEntry

where t1.itemcode like 'SF-L%%'

and (T4.ItemCode like 'FL-%%' or T4.ItemCode like 'SF-P%%')

and datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) = 'February - 2015' and T3.DocNum = '12593'

group by T0.[u_Dname],T0.[u_Oname],datename (MONTH, T3.PostDate),datename (Year, T3.PostDate), T4.ItemCode,T3.DocNum

Order by  datename (Year, T3.PostDate), T0.[u_Dname], T0.[u_Oname]

which shows me the following result in this Pic

there are two types of item codes one is starts from FL and other is SF-P  in Itemcode column all i want is to split item code in two columns one is FL other is SF-P and there Plan Qty are Different so they also split in two column according to item code but Print Qty are same for both item code

can it be possible.

Regards,

Jamil

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this:

SELECT

T3.DocNum,T5.ItemCode as 'Film', T6.Itemcode as 'Print', sum(T3.[PlannedQty]) as 'Print Qty' , sum(T4.PlannedQty) as 'Plan Qty',

T0.[u_Dname] as 'Dept Name',T0.[u_Oname] as 'Optr Name',sum (T0.U_PRWAST1) as 'Pr Wast',

sum(T0.U_PLWAST1) as 'Pl Wast',sum (T0.U_TWAST1) as 'T Wast',

datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) as 'Month'

FROM

OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry

left outer join oitm t2 on T2.[ItemCode]=T1.[ItemCode]

LEFT OUTER JOIN OWOR T3 ON T3.DOCNUM=T1.[BaseRef]

left outer join WOR1 T4 on T4.DocEntry = T3.DocEntry

left join oitm t5 on T5.[ItemCode]=T4.[ItemCode] AND T5.ItemCode like 'FL-%%'

left join oitm t6 on T6.[ItemCode]=T4.[ItemCode] AND T6.ItemCode like 'SF-P%%'

where t1.itemcode like 'SF-L%%'

and (T4.ItemCode like 'FL-%%' or T4.ItemCode like 'SF-P%%')

and datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) = 'February - 2015' and T3.DocNum = '12593' and T5.ItemCode is not null

group by T0.[u_Dname],T0.[u_Oname],datename (MONTH, T3.PostDate),datename (Year, T3.PostDate), T5.ItemCode, T6.Itemcode,T3.DocNum

Order by  datename (Year, T3.PostDate), T0.[u_Dname], T0.[u_Oname]

Thanks.

[Former Member]

HI Jamil,

Try:

SELECT

T3.DocNum, T5.ItemCode, T6.Itemcode, sum(T3.[PlannedQty]) as 'Print Qty' , sum(T4.PlannedQty) as 'Plan Qty',

T0.[u_Dname] as 'Dept Name',T0.[u_Oname] as 'Optr Name',sum (T0.U_PRWAST1) as 'Pr Wast',

sum(T0.U_PLWAST1) as 'Pl Wast',sum (T0.U_TWAST1) as 'T Wast',

datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) as 'Month'

FROM

OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry AND t1.itemcode like 'SF-L%%'

LEFT JOIN OWOR T3 ON T3.DOCNUM=T1.[BaseRef]

left join WOR1 T4 on T4.DocEntry = T3.DocEntry

left join oitm t5 on T5.[ItemCode]=T4.[ItemCode] AND T5.ItemCode like 'FL-%%'

left join oitm t6 on T6.[ItemCode]=T4.[ItemCode] AND T6.ItemCode like 'SF-P%%'

where datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) = 'February - 2015' and T3.DocNum = '12593'

group by T0.[u_Dname],T0.[u_Oname],datename (MONTH, T3.PostDate),datename (Year, T3.PostDate), T5.ItemCode,T6.ItemCode,T3.DocNum

Thanks,

Gordon

[former_member252592]

Thanx for your Reply Gordon i tried your query with some slight changes

SELECT

T3.DocNum,T5.ItemCode as 'Film', T6.Itemcode as 'Print', sum(T3.[PlannedQty]) as 'Print Qty' , sum(T4.PlannedQty) as 'Plan Qty',

T0.[u_Dname] as 'Dept Name',T0.[u_Oname] as 'Optr Name',sum (T0.U_PRWAST1) as 'Pr Wast',

sum(T0.U_PLWAST1) as 'Pl Wast',sum (T0.U_TWAST1) as 'T Wast',

datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) as 'Month'

FROM

OIGN T0  INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry

left outer join oitm t2 on T2.[ItemCode]=T1.[ItemCode]

LEFT OUTER JOIN OWOR T3 ON T3.DOCNUM=T1.[BaseRef]

left outer join WOR1 T4 on T4.DocEntry = T3.DocEntry

left join oitm t5 on T5.[ItemCode]=T4.[ItemCode] AND T5.ItemCode like 'FL-%%'

left join oitm t6 on T6.[ItemCode]=T4.[ItemCode] AND T6.ItemCode like 'SF-P%%'

where t1.itemcode like 'SF-L%%'

and (T4.ItemCode like 'FL-%%' or T4.ItemCode like 'SF-P%%')

and datename (MONTH, T3.PostDate)+' - '+ datename (Year, T3.PostDate) = 'February - 2015' and T3.DocNum = '12593'

group by T0.[u_Dname],T0.[u_Oname],datename (MONTH, T3.PostDate),datename (Year, T3.PostDate), T5.ItemCode, T6.Itemcode,T3.DocNum

Order by  datename (Year, T3.PostDate), T0.[u_Dname], T0.[u_Oname]

it gives me the following result

but i need the following result

Is it Possible?

Thanks & Regards,

Jamil