Grouping Problem in Sap Crystal Report

26 Views

Hi ,

I made this query for Indent Vs Purchase Report .

SELECT 'I' 'IND',T0.DocEntry ,T0.LineId ,T0.U_ITEMCODE AS ITEMCODE,T0.U_RQTY AS QTY ,T1.U_INDTE ,T3.Price ,

T1.U_DPTNAME ,T1.U_EMPNAME ,T5.U_GroupName

FROM [@AC_NDT1] T0 INNER JOIN [@AC_INDT] T1 ON T0.DOCENTRY = T1.DOCENTRY

INNER JOIN OITM T2 ON T2.ITEMCODE=T0.U_ITEMCODE

INNER JOIN ITM1 T3 ON T2.ItemCode=T3.ItemCode

INNER JOIN OPLN T4 ON T4.ListNum=T3.PriceList

INNER JOIN OITB T5 ON T2.ItmsGrpCod=T5.ItmsGrpCod

WHERE T4.ListNum=2

--ORDER BY T0.DocEntry

and T0.U_ITEMCODE='MLGFAL1600'

UNION ALL

SELECT 'P' 'IND',T0.DocEntry ,T0.LineNum ,T0.ItemCode, -T0.Quantity AS QTY,T1.DocDate ,T0.Price ,t0.OcrCode ,'',t3.U_GroupName

FROM POR1 T0 INNER JOIN OPOR T1 ON T0.DocEntry=T1.DocEntry

left outer join oitm t2 on t0.itemcode=t2.itemcode

left outer join oitb t3 on t2.ItmsGrpCod=t3.ItmsGrpCod

where T0.ITEMCODE='MLGFAL1600'

My Out Put is :

Type Dep.code Emp.code

I210MLGFAL16001500.0000002013-03-24 00:00:00.000804.00000013GLASS VACUME FLASKI710MLGFAL16001000.0000002013-04-25 00:00:00.000804.00000013GLASS VACUME FLASKI810MLGFAL16001000.0000002013-05-10 00:00:00.000804.00000013GLASS VACUME FLASKI95MLGFAL16001184.0000002013-04-15 00:00:00.000804.000000548GLASS VACUME FLASKI105MLGFAL1600872.0000002013-04-30 00:00:00.000804.000000548GLASS VACUME FLASKI115MLGFAL160016.0000002013-04-30 00:00:00.000804.000000548GLASS VACUME FLASKI124MLGFAL1600388.0000002013-05-20 00:00:00.000804.000000548GLASS VACUME FLASKI145MLGFAL1600432.0000002013-06-20 00:00:00.000804.000000548GLASS VACUME FLASKI165MLGFAL1600484.0000002013-07-20 00:00:00.000804.000000548GLASS VACUME FLASKI175MLGFAL1600820.0000002013-08-20 00:00:00.000804.000000548GLASS VACUME FLASKP691MLGFAL1600-1000.0000002013-05-07 00:00:00.000371.000000NULL
GLASS VACUME FLASKP871MLGFAL1600-3000.0000002013-05-07 00:00:00.000371.000000NULL
GLASS VACUME FLASKP600MLGFAL1600-2872.0000002013-04-05 00:00:00.000376.000000NULL
GLASS VACUME FLASKP820MLGFAL1600-1200.0000002013-06-07 00:00:00.000376.660000NULL
GLASS VACUME FLASKP480MLGFAL1600-3556.0000002013-03-07 00:00:00.000376.000000NULL
GLASS VACUME FLASK

I want group by Department and Employee name but problem is Indent table only have the values of Department and Employee.
So The Purchase values are not showing when i group by Department and Employee.

Let me know if anyone know the solution .

Thanks and Regards ,

Rajesh N