Repetition of field values in Crystal Report
I am trying generate a report in Crystal report.
I made link between tables OCRD,OINV and ORCT by Cardcode and Card name.
I designed report like
Card Name Opening Balance OINV.Doc Toatl ORCT.DocToatl
The issue is,
When I generate the report it gives repetition of values
Eg. XYZ customer has 5 invoice totals 3 payment transaction
it displays 5 invoice totals correctly but in payment column it repets the existing value to match the invoice total rows. It means, ORCT.Doc Total has only 3 transaction but it show 5 rows with existing values again.
Requesting solution solve this problem
From experience I would never let Crystal create table joins for you, its not smart enough to understand how SAP tables interact with each other. You can't INNER JOIN OCRD with OINV and ORCT all at once or you will get incorrect results - the above generated queries are incorrect. I would suggest creating a stored procedure to return your result set and just base the Crystal Report on this.
If you wanted to return different types of customer transactions using a single SELECT statement then you would need to base your query on JDT1 and use several LEFT JOINs back to OINV, ORCT, ORIN (and other tables) on TransID to get base document info if required.
Using your example, if you just want to display a list of invoices and incoming payments per customer then why not UNION ALL two separate SELECT statements, put them inside a stored procedure and base the Crystal Report on that?
Something like the following sample:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = 'sp_SampleTransListing') BEGIN DROP PROCEDURE [dbo].[sp_SampleTransListing] END GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE proc sp_SampleTransListing ( @CardCode VARCHAR(15) ) AS BEGIN SELECT o.CardName, 'IN' [DocType], i.DocNum, i.DocTotal [Total] FROM SBODemo_US.dbo.OCRD o INNER JOIN SBODemo_US.dbo.OINV i ON o.CardCode = i.CardCode WHERE o.CardCode = @CardCode UNION ALL SELECT o.CardName, 'RC' [DocType], r.DocNum, r.DocTotal * -1 [Total] FROM SBODemo_US.dbo.OCRD o INNER JOIN SBODemo_US.dbo.ORCT r ON o.CardCode = r.CardCode WHERE o.CardCode = @CardCode ORDER BY DocType, DocNum END
Hopefully some of this is of use.