Running Cumulative Balance in Crystal Reports

[former_member541807]

hi all,

this is the continuation of my previous post located at ,

since i can now get the Opening balance, now my problem is how to get the cumulative account balance, i have tried to do it but no luck.

please help and thanks in advance.

Fidel

[Former Member · Accepted solution]

Hi Avelino Fidel.

Account balance current time, you can use CurrTotal feild in table OACT.

regards

H2.

[former_member541807 · Accepted solution]

hi Hoe,

thanks for the response.

i need a Business Partner cumulative running total using crystal reports...i think i dont need the currTotal in OACT.

regards,

Fidel

[Former Member · Accepted solution]

Hi Avelino Fidel,

i don't understand you in first reply . if you want to select Business Partner cumulative running total.

you can use this query:

SELECT dbo.OCRD.CardCode, dbo.OCRD.CardName , SUM(dbo.JDT1.Debit - dbo.JDT1.Credit) AS BPBalance

FROM dbo.JDT1 INNER JOIN

dbo.OCRD ON dbo.JDT1.ShortName = dbo.OCRD.CardCode

GROUP BY dbo.OCRD.CardCode, dbo.OCRD.CardName

I hope it can help you.

H2.

[former_member541807 · Accepted solution]

hi hoe,

thanks for the response. but you may miss understood my question... i need the running cumulative balance of a business partner using Crystal reports. i have successfully fetch my data that i need in the report. what i need is how to get the running cumulative balance in the report please see this image http://i264.photobucket.com/albums/ii184/abing430/OpenBalance.png

pls. help..

thanks

[Former Member · Accepted solution]

Hi Avelino Fidel,

running cumulative balance of a business partner.

The first, you create one funtion run cumulative.

CREATE FUNCTION [dbo].[FUN_CUMULATIVE_BP]( @CARDCODE NVARCHAR(20) ,@TRANSID INT )

RETURNS FLOAT

BEGIN

DECLARE @RESULT AS FLOAT

SET @RESULT = 0

SELECT TOP 1 @RESULT = SUM(T0.Debit - T0.Credit)

FROM dbo.JDT1 AS T0 INNER JOIN dbo.OCRD AS T1 ON T0.ShortName = T1.CardCode

WHERE (T1.CardCode = @CARDCODE ) and T0.transID <= @TRANSID

RETURN @RESULT

END

second, you can use this query for crystal.

SELECT OJDT.TransId,dbo.OJDT.RefDate , dbo.JDT1.BaseRef ,dbo.OCRD.CardCode, dbo.OCRD.CardName,

(dbo.JDT1.Debit - dbo.JDT1.Credit) AS BPBalance , [dbo].FUN_CUMULATIVE_BP(dbo.OCRD.CardCode,OJDT.TransId) AS BPCumulative

FROM dbo.JDT1 INNER JOIN

dbo.OCRD ON dbo.JDT1.ShortName = dbo.OCRD.CardCode INNER JOIN

dbo.OJDT ON dbo.JDT1.TransId = dbo.OJDT.TransId

WHERE (dbo.OCRD.CardCode = 'C100001')

order by OJDT.TransId

you should create parameter BPcode in crystal .

Regards,

H2

[kvbalakumar · Accepted solution]

Hi Fidel,

Try this,

1. Create a formula field (Field1)

If OnFirstRecord then OpeningBalance_Field + Debit - Credit
   Else Debit - Credit

2.Now create a runningTotal for the above formula field(Field1)

Hope this helps.

Regards,

Bala

[former_member541807 · Accepted solution]

@ Hoe,

thanks for the response and effort for helping me.

@ Julie

thanks for your response,

@Balakumar

i have manage to figure out how to achieve my requirement before checking the forum w/ the ff. formulas.

//Head

whileprintingrecords;

if OnFirstRecord then Shared numbervar OB := {Command.Open Balance}

//Line

whileprintingrecords;

shared numbervar OB;

OB := OB + {BP_AccountBalance;1.Credit} - {BP_AccountBalance;1.Debit}

//Footer

whileprintingrecords;

Shared numbervar OB

its more or less almost the same with your solution/formula. many thanks for you.

best regards,

Fidel

[Former Member]

Have you tried creating a running total field in your report & adding that to your report?