How to calculate running balance for a requested date in reports using Reporting Services 2005?

Question

Post reply

How to calculate running balance for a requested date in reports using Reporting Services 2005?

Puja Shah

SSCommitted

Points: 1724
More actions
August 25, 2008 at 5:28 am

#211500

Hello,
I am creating reports for a Bank.
I want to calculate Running Balance in which OpBal(opening balance) value is used only once throughout the calculation of Running Balance for each general ledger.
1st time calculating running balance:
runBal=opBal+credit-debit
then onwards calculation for running balance;
runBal=runBal(previously calculated value)+credit-debit
Is there any way in reports generated using reporting services to get the value of previous textbox/cell ???
Any help will be greatful.
Thanking in advance,
Puja Shah

Viewing 15 posts - 1 through 15 (of 43 total)

You must be logged in to reply to this topic. Login to reply

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 1

Hi,

I am also facing similar kind of issue.If you got the answer,if send the reply.if i get i will send the reply.

Thanks

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 2

Hello Ravi,

I got the answer. To calculate running balance, I've used PREVIOUS function.

=IIF(ISNothing(Previous(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value)),Format(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value,"N"),Format(Previous(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value)+Fields!credit.Value-Fields!debit.Value,"N"))

Hope this will help you.

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 3

p.nagaravikumar

SSC Eights!

Points: 844

August 27, 2008 at 12:38 am

#863515

Hi,

Thank you for your reply.

Regards

Ravi

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 4

Hi,

Can u please explain why u r using isnothing function here.In iif() function if condition is true first part is will execute and other case second part will execute.I think isnothing function will return boolean value either 0 or 1.Can u please explain about isnothing function.Where we need to use.I tried with your iif statement.I problem also solved.I want to know abt isnothing.

Regards

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 5

Hi Ravi,

You are right, IsNothing() returns a Boolean value.

=IIF(ISNothing(Previous(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value)),Format(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value,"N"),Format(Previous(Fields!GLCrDr.Value+Fields!credit.Value-Fields!debit.Value)+Fields!credit.Value-Fields!debit.Value,"N"))

I this case, when you are using PREVIOUS() function directly, you will not get the correct result. The reason is, for calculating first value you cannot use PREVIOUS function. So the condition in IIF() will be true only once.

Hence, when to calculate next values, I think this condition works fine.

I have used this condition according to my problem.

Hope I gave you the answer.

Regards,

Puja Shah

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 6

Hi,

Thank you for your reply.I am still facing some problem.Sorry for the disturb,when u r free please look the attachment.i gave the table script and rdl layout diagram and obtained output.If u observe the output 660,700 giving worng result.Please check and tell where i am doing worng.

Thanks

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 7

Hi Ravi,

I've same problem. I'm getting correct value for first general ledger, but for other I'm getting only second value correct which should be first value.

If you get the answer please do reply.

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 8

Hi,

I think we need to find the running balance in backend itself.For that we need to write the stored procedure and then call that one in rdl.This is just my idea.I am trying ,if u got the answer or any idea please share with me.

Regards

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 9

Hi,

Ok I'll reply if I get the answer.

Thanks for sharing.

Puja Shah

gardenlady SSCommitted Points: 1974 More actions · Answer 10

I needed a running subtotal to start over again when account, acct_unit or subaccount changes.

I put this in the textbox in Layout:

=Code.CalcSubTotal(ReportItems!txtBegBal.value,ReportItems!txtDebitAmount.value,ReportItems!txtCreditAmount.Value,ReportItems!txtAcctUnitHiddenForCalc.value,ReportItems!txtAcctNoHiddenForCalc.value,CInt(ReportItems!txtSubHiddenForCalc.value))

In Report, Report Properties, Code, I put the following:

dim RAcctUnit as string

dim RAccount as integer

dim RSubacct as Short

dim Balance as double

dim Counter as integer

Function CalcSubTotal(BegBal as double, Debits as double, Credits as double,AcctUnit as string,Account as integer, Sub_Acct as Integer)

If RAcctUnit<> AcctUnit or RAccount <> Account or RSubacct <> Sub_Acct

Balance = BegBal + Debits + Credits

Else

Balance = Balance + Debits + Credits

End if

Counter = Counter +1

RAccount = Account

RSubacct = Sub_Acct

RAcctUnit = AcctUnit

Return Balance

End Function

It's a little VB code, which I'm not an expert at, but it works.

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 11

Hi Puja,

I got the answer.Check the attachment.Follow the steps and let me know if you have any difficult.

Regards

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 12

Hi,

Thanks for the reply. I'll try and tell whether it works or not.

Puja Shah

p.nagaravikumar SSC Eights! Points: 844 More actions · Answer 13

Hi Puja,

I got the answer.Please see the attachment and follow the steps and let me know if you have any difficulty.

Thanks

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 14

Hi Ravi,

I'm getting very arbitrary values. I did exactly same as you have instructed.

Here is the query that i'm using:

SELECT dbv.transid,dbv.branchid,dbv.glid,

transdate,

dbv.scrollno,

dbv.voucherno,VoucherDate,dbv.gldesc,

dbv.transdetid,dbv.instrno,InstrDate,

dbv.debit,dbv.credit,dbv.narration,opbal.GLCrDr,

(select sum(isnull(GetOpeningBalance.GLCrDr,0)+isnull(credit,0)-isnull(debit,0)) from DayBookVw,GetOpeningBalance(@ason) where transid<=dbv.transid

and GetOpeningBalance.glid=daybookvw.glid) as RunBal

FROM daybookvw AS dbv,

(SELECT GetOpeningBalance.GLid,GLCrDr FROM GetOpeningBalance(@ason)) AS opbal

where branchid=@branchid and approve=1 and opbal.glid=dbv.glid

Please tell am I doing wrong?

Puja Shah