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

  • 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

  • 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

  • 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.

  • Hi,

    Thank you for your reply.

    Regards

    Ravi

  • 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

  • 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

  • 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

  • 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.

  • 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

  • Hi,

    Ok I'll reply if I get the answer.

    Thanks for sharing.

    Puja Shah

  • 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.

  • Hi Puja,

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

    Regards

    Ravi

  • Hi,

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

    Puja Shah

  • 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

  • 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

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

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