Skip to Content
avatar image
Former Member

Not Calculating Return and Credit Amount in Cross Tab option in Crystal Report

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
10|10000 characters needed characters exceeded

  • Get RSS Feed

0 Answers