August 25, 2008 at 5:28 am
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
August 26, 2008 at 10:26 pm
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
August 26, 2008 at 11:56 pm
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.
August 27, 2008 at 12:38 am
Hi,
Thank you for your reply.
Regards
Ravi
August 27, 2008 at 3:20 am
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
August 27, 2008 at 3:40 am
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
August 27, 2008 at 4:07 am
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
August 27, 2008 at 4:26 am
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.
August 27, 2008 at 7:08 am
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
August 27, 2008 at 7:23 am
Hi,
Ok I'll reply if I get the answer.
Thanks for sharing.
Puja Shah
August 27, 2008 at 7:52 am
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.
August 27, 2008 at 8:06 am
Hi Puja,
I got the answer.Check the attachment.Follow the steps and let me know if you have any difficult.
Regards
Ravi
August 27, 2008 at 8:06 am
Hi,
Thanks for the reply. I'll try and tell whether it works or not.
Puja Shah
August 27, 2008 at 8:12 am
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
August 27, 2008 at 8:40 am
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