on 10-06-2008 7:45 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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.
Problem solved special thanks to Andrew Flynn
By
Kalai
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.