Hi experts
i had made a Query which is perfectly running in normal Crystal report, but when i use the same query in Cross Tab option in Crystal and in coloum Option i had opeted Month wise
then am getting some Calculation Issue, when i analysis it is found that Credit note and Ar credit memo document value are not including in the Calculation and getting the difference.
pls find my attached query
pls help me to sort it out
Regards
select Distinct ODLN.DocNum AS 'DocNumber',ODLN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ODLN.CardName AS 'Customer Name',ODLN.DiscSum as 'Discount Sum',ODLN.DocTotal as 'Doc Total',ODLN.U_PMOD AS 'Payment Type', ODLN.DocDate as 'Posting Date',NNM1.SeriesName,ODLN.DocType as 'Document Series',ODLN.ObjType from ODLN Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry left outer join NNM1 on ODLN.Series = NNM1.Series left outer join ocrd on ODLN.CardCode = OCRD.CardCode Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode WHERE ODLN.CANCELED <> 'C' union select Distinct ODLN.DocNum AS 'DocNumber',ODLN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ODLN.CardName AS 'Customer Name',ODLN.DiscSum as 'Discount Sum','-'+cast(ODLN.DocTotal as nvarchar) as 'Doc Total',ODLN.U_PMOD AS 'Payment Type', ODLN.DocDate as 'Posting Date',NNM1.SeriesName,ODLN.DocType as 'Document Series',ODLN.ObjType from ODLN Left Outer JOIN DLN1 ON ODLN.DocEntry = DLN1.DocEntry left outer join NNM1 on ODLN.Series = NNM1.Series left outer join ocrd on ODLN.CardCode = OCRD.CardCode Left Outer JOIN OITM on DLN1.ItemCode = OITM.ItemCode where ODLN.CANCELED = 'C' Union select Distinct ORDN.DocNum AS 'DocNumber',ORDN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORDN.CardName AS 'Customer Name',ORDN.DiscSum as 'Discount Sum','-' + cast(ORDN.DocTotal as nvarchar) as 'Doc Total',ORDN.U_PMOD AS 'Payment Type', ORDN.DocDate as 'Posting Date',NNM1.SeriesName,ORDN.DocType as 'Document Series',ORDN.ObjType from ORDN Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry left outer join NNM1 on ORDN.Series = NNM1.Series left outer join OCRD on ORDN.CardCode = OCRD.CardCode Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode where ORDN.CANCELED <> 'C' union select Distinct ORDN.DocNum AS 'DocNumber',ORDN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORDN.CardName AS 'Customer Name',ORDN.DiscSum as 'Discount Sum', + cast(ORDN.DocTotal as nvarchar) as 'Doc Total',ORDN.U_PMOD AS 'Payment Type', ORDN.DocDate as 'Posting Date',NNM1.SeriesName,ORDN.DocType as 'Document Series' ,ORDN.ObjType from ORDN Left Outer JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry left outer join NNM1 on ORDN.Series = NNM1.Series left outer join OCRD on ORDN.CardCode = OCRD.CardCode Left Outer JOIN OITM on RDN1.ItemCode = OITM.ItemCode where ORDN.CANCELED = 'C' Union select Distinct ORIN.DocNum AS 'DocNumber',ORIN.NumAtCard as 'Doc Ref No',OCRD.CardFName as 'Customer Ref No', ORIN.CardName AS 'Customer Name',ORIN.DiscSum as 'Discount Sum','-' + cast(ORIN.DocTotal as nvarchar) as 'Doc Total',ORIN.U_PMOD AS 'Payment Type', ORIN.DocDate as 'Posting Date',NNM1.SeriesName,ORIN.DocType as 'Document Series' ,ORIN.ObjType from ORIN Left Outer JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry left outer join NNM1 on ORIN.Series = NNM1.Series left outer join OCRD on ORIN.CardCode = OCRD.CardCode Left Outer JOIN OITM on RIN1.ItemCode = OITM.ItemCode where ORIN.DocStatus = 'O'
Add comment