cancel
Showing results for 
Search instead for 
Did you mean: 

Repetition of field values in Crystal Report

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi

1) Select field what do you want to hav'nt to be dublicated;

2) Right Click for context menu and select Field Format;

3) Select tab Common (1-st tab) an check to true the checkbox Omit if duplicate or Omit duplicated.

It's all.

I think this can help you.

Regads

Sierdna S.

Former Member
0 Kudos

Hi thanks for ur reply

My report setup is

Table Links OCRD.Cardname->OINV.Cardname->ORCT.cardname.

Report fields(column wise)

CardName OINV.Doctotal ORCT.DocTotal

Give me a solution plz.

It solved the issue in ORCT.DocTotal

But it doesn't works in OINV.DocTotal. it gives repetition of same values

By

Kalai

Former Member
0 Kudos

Hi

Sorry, I can help you with logic of your query.

You may try to see group by fields methods, or select distinct rows.

These queries was generated by CR Database Expert.

OCRD (pk=CardCode) is parent table and OINV (pk=DocEntry), ORCT (pk=DocEntry) are childrens.

You need to make link by CardCode and not by CardName.

DISTINCT


 SELECT DISTINCT "OCRD"."CardName", "OINV"."DocTotal", "ORCT"."DocTotal", "OCRD"."CardCode"
 FROM  ("SBOBPW"."dbo"."OCRD" "OCRD" 
  INNER JOIN "SBOBPW"."dbo"."OINV" "OINV" ON "OCRD"."CardCode"="OINV"."CardCode")
  INNER JOIN "SBOBPW"."dbo"."ORCT" "ORCT" ON "OCRD"."CardCode"="ORCT"."CardCode"

SELECT


 SELECT "OCRD"."CardName", "OINV"."DocTotal", "ORCT"."DocTotal", "OCRD"."CardCode"
 FROM   ("SBOBPW"."dbo"."OCRD" "OCRD" 
  INNER JOIN "SBOBPW"."dbo"."OINV" "OINV" ON "OCRD"."CardCode"="OINV"."CardCode") 
  INNER JOIN "SBOBPW"."dbo"."ORCT" "ORCT" ON "OCRD"."CardCode"="ORCT"."CardCode"

Regards

Sierdna S.

Former Member
0 Kudos

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.

Former Member
0 Kudos

HI

Your answer is almost solved my problem. Thanks a lot.

If i have any other dobts I'll post it again. Once I solved I'll close this thread.

Once again Thanks for all who made efforts to solve.

By

Kalai

Answers (2)

Answers (2)

Former Member
0 Kudos

Problem solved special thanks to Andrew Flynn

By

Kalai

Former Member
0 Kudos

Hi

The issue is related to how you make your payment.Are you doing partial payment or payment on account .Or

I think it is not the data issue and looks like logic issue .

Well, you can try with stored procedure provided by the experts and test your solution .I think you have to use RCT1 table instead of ORCT.

If it's not the logic issue, in crystal report you can use term ,suppress if duplicated to show in one line .

Hope this works out for you

Bishal