Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Repetition of field values in Crystal Report

Hi Experts

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

BY

Kalai

replied

Hi Kalai,

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.

Regards,

Andrew.

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question