Skip to Content
0
Jan 11, 2011 at 02:16 AM

BP Account Balance using Crystal Reports

235 Views

hello all,

i Have the FF. stored procedure.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[BP_AccountBalance]

AS
BEGIN
	SET NOCOUNT ON;
	SELECT	DISTINCT		dbo.OJDT.RefDate AS [Posting Date],
					dbo.OJDT.BaseRef AS [Origin #],
					dbo.OJDT.ObjType AS Origin,
					dbo.OJDT.DueDate,
					dbo.OJDT.Memo,
					dbo.JDT1.Debit, 
                    dbo.JDT1.Credit,
					dbo.OPCH.U_INVOICE,
					dbo.VPM2.U_BPIN,
					dbo.OVPM.DocNum,
					dbo.JDT1.ShortName,
					dbo.OJDT.TransType
	FROM         dbo.OJDT INNER JOIN
                      dbo.JDT1 ON dbo.OJDT.TransId = dbo.JDT1.TransId LEFT OUTER JOIN
                      dbo.VPM2 INNER JOIN
                      dbo.OVPM ON dbo.VPM2.DocNum = dbo.OVPM.DocNum ON dbo.OJDT.TransId = dbo.OVPM.TransId LEFT OUTER JOIN
                      dbo.PCH1 INNER JOIN
                      dbo.OPCH ON dbo.PCH1.DocEntry = dbo.OPCH.DocEntry ON dbo.OJDT.TransId = dbo.OPCH.TransId

END
GO

i am trying to make an BP Account Balance report identical with the one in SBO using Crystal Reports, the reason why doing it in crystal reports because i want to add 2 UDF fields (this udf are located in PCH1 and VPM2), base on my requirement. now i am having trouble with

1. Duplicated rows

2. Cumulative Balance.

please help.

thanks

FIdel