Grouping Problem in Sap Crystal Report

[Former Member]

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

I	2	10	MLGFAL1600	1500.000000	2013-03-24 00:00:00.000	804.000000	1	3	GLASS VACUME FLASK
I	7	10	MLGFAL1600	1000.000000	2013-04-25 00:00:00.000	804.000000	1	3	GLASS VACUME FLASK
I	8	10	MLGFAL1600	1000.000000	2013-05-10 00:00:00.000	804.000000	1	3	GLASS VACUME FLASK
I	9	5	MLGFAL1600	1184.000000	2013-04-15 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	10	5	MLGFAL1600	872.000000	2013-04-30 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	11	5	MLGFAL1600	16.000000	2013-04-30 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	12	4	MLGFAL1600	388.000000	2013-05-20 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	14	5	MLGFAL1600	432.000000	2013-06-20 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	16	5	MLGFAL1600	484.000000	2013-07-20 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
I	17	5	MLGFAL1600	820.000000	2013-08-20 00:00:00.000	804.000000	5	48	GLASS VACUME FLASK
P	69	1	MLGFAL1600	-1000.000000	2013-05-07 00:00:00.000	371.000000	NULL		GLASS VACUME FLASK
P	87	1	MLGFAL1600	-3000.000000	2013-05-07 00:00:00.000	371.000000	NULL		GLASS VACUME FLASK
P	60	0	MLGFAL1600	-2872.000000	2013-04-05 00:00:00.000	376.000000	NULL		GLASS VACUME FLASK
P	82	0	MLGFAL1600	-1200.000000	2013-06-07 00:00:00.000	376.660000	NULL		GLASS VACUME FLASK
P	48	0	MLGFAL1600	-3556.000000	2013-03-07 00:00:00.000	376.000000	NULL		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

[abhilash_kumar]

Hi Rajesh,

Do you have a better explanation of the issue please?

From the output table, what fields do you want the report to be grouped on and what happens when you do that?

-Abhilash

[Former Member]

Hi ,

1.My Output value is union all of Indent and Purchase order .

2.Indent table only having values of Department and Employee name .

3.I want group by Department name and Employee name

4.I want output will  sum(indent.qty-Poqty) but when i group by Department name and Employee name.

It will give only sum(Indqty)

Thanks

--Rajesh N

[Former Member]

Hi ,

Exact problem is Purchase order table don't have Department and Employee name  so

it wont display Purchase Quantity  when i group by depart name and employee name .

-Rajesh N

[Former Member]

Hi Rajesh,

Here you are trying to relate two unrelated data set using union object.

You have to relate both queries using some column say T0.LineId ( which is common column as per my understanding) and the calculate the qty column.

you can try the query some thing like below:

SELECT sum(A.QTY-B.QTY) AS  QTY,

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

( 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') A,

(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')B

WHERE A.T0.LineId=B.T0.LineId

GROUP BY 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

then you can try grouping the way which you want.

Uday.