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?

Viewing 15 posts - 16 through 30 (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 Puja,

i dont have the data for test that sp, you mentioned below lines in sp.

transid<=dbv.transid

and GetOpeningBalance.glid=daybookvw.glid) as RunBal

for what purpose you used GetOpeningBalance.glid=daybookvw.glid this condition.Execute by removing that line and test.Actually i dont know for what reason you placed that one.Do some randam changes to sp and test the output.For me it is working.If possible.Send the sample data with

Table script.Then i will try.First you try, if not send the script.

Regards

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 2

Hi Ravi,

The condition is to get opening balance for selected general ledgers. So that I've to keep in the query. If I don't keep that condition, then it gives me incorrect result.

I've used a table-valued function to get opening balance. I cannot change this function as it being used in some other process.

Are you fetching the values from a single table in your report??

Regards,

Puja Shah

Puja Shah SSCommitted Points: 1724 More actions · Answer 3

Hello Ravi,

Please check the attachement & let me know what am I doing wrong?

Puja Shah

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

Hi Puja,

Today i am fully engaged with some other work.So i did not see the problem.Once i get the solution i will let you know.

Regards

Ravi

gardenlady SSCommitted Points: 1974 More actions · Answer 5

If you want to look at my solution, perhaps I could add some detail. I created a table with all the transactions in the general ledger. Each row also has the beginning balance for the account (did that with a left outer join).

I've attached my solution so you can look at the report layout and see the beginning balance, the detail row and specifically the running balance that is at the far right of the detail row. It starts "=Code." and references textbox values to send as parameters to the VB code. If you go to Report, Report Properties, Code tab, you'll see the actual code. It accumulates a balance until either the account, sub account or accounting unit changes--your case may be much simpler. It's VB code and the variables have to be declared (DIM) above the code so that the scope is correct and the balance will reset appropriately. I hope seeing this, if you can open it, will be better than typed explanation from me. Good luck.

Puja Shah SSCommitted Points: 1724 More actions · Answer 6

Hi

Thanks for the help. I'll try the solution and let you know whether it works or not.

Regards,

Puja Shah

Puja Shah SSCommitted Points: 1724 More actions · Answer 7

Hi,

I tried as per your VR code. I don't have last three parameters(AcctUnit as string,Account as integer, Sub_Acct as Integer) in function.

So I passed " " in AcctUnit, 0 in Account and 0 in Sub_Acct.

I'm getting error for running balance, but report is generated successfully.

I've attached the .rdl file, please check it and let me know what is wrong.

Puja Shah

gardenlady SSCommitted Points: 1974 More actions · Answer 8

You have a lot less parameters. I'm assuming gldesc is what you need to break on--when the balance needs to start over. Also made the assumption it's a string. If either of those assumptions are wrong, you'll need to adjust the code accordingly. Take a look at the attachment (sorry for the messy fonts but trying to hurry) and see if it makes sense and works.

I couldn't test it (don't have data and it errored), but I'll keep my fingers crossed for you.

gardenlady SSCommitted Points: 1974 More actions · Answer 9

More thoughts: whatever field causes the Running Balance to start fresh at zero has to be a field in the report. I usually make it a field in the Detail row and under the Visibility Property, I hide it. Whatever the name of that textbox is, say "txtGLChg", that is what is sent in as the parameter in the textbox that has "=Code....". The paramater has to be the textbox name, not the field name unless they are the same.

The Report Properties code doesn't really require any changes (unless you just want to change the name of the parameter) unless the type of data in the textbox is not String. I didn't know what that field was, that resets the balance, and so did not know what to put in the Word doc I attached.

Good luck.

gardenlady SSCommitted Points: 1974 More actions · Answer 10

And one more thought (sorry--too much multitasking) that I may have mentioned earlier. The field that makes the running balance start over at zero, this field has to be in every row of your data. Otherwise it couldn't be in the hidden field in the detail row (Layout view) I discussed above. This field is going to trigger a balance change so it must be in each row and obviously the order of the data needs to have it near the top of the Order By clause.

So here's my final date for the report details. Notice the three things that can change my running balance are in the data and in the OrderBy. I created this with a LEFT JOIN.

SELECT company

,fiscal_year

,acct_unit

,account

,cast (account as varchar(8)) as acctsort

,sub_account

,TotBegDebits

,TotBegCredits

,TMONTH

,account_desc

,acct_unitname

,posting_date

,update_date

,debits

,credits

,reference

,TransDescr

,JE

,SystemCode

,StartDate

,EndDate

FROM @tmp_Details

ORDER BY company

,fiscal_year

,acct_unit

,account

,sub_account

,posting_date

Puja Shah SSCommitted Points: 1724 More actions · Answer 11

Hi,

I tried to get running balance as you have mentioned in the VB code, but I don't have unit value, account value & sub_acct values so I passed " " in unit, 0 in account & 0 in sub_acct.

The report is successfully generated, but in running balance column it is written "#error".

Please look at the attachment and let me know what changes should I make.

Thanks & regards,

Puja Shah

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

Hi Puja,

Did you check the query you used working or not in backend.

Regards

Ravi

Puja Shah SSCommitted Points: 1724 More actions · Answer 13

I checked in backend, its working properly.

The main problem is I'm not able to calculate & display running balance.

Any suggestions will be great help to me.

Puja Shah

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

Hi Puja,

When it is working in backend properly,when you pasted same query in the dataset, Running balance field will come directly.Without doing anything just drag the field to design area in table where u want to display and check.

Regards

Ravi

gardenlady SSCommitted Points: 1974 More actions · Answer 15

Okay, I'm looking at sl_2.rdl which may be the wrong rdl file. You have in the last textbox:

=Code.CalcSubTotal(ReportItems!txtGLCrDr.value,ReportItems!txtdebit.value,ReportItems!txtcredit.Value,"rs",1,CInt(23))

dim RAcctUnit as string

dim RAccount as integer

dim RSubacct as Short

dim Balance as double

dim Counter as integer

And in the code:

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

This is not the latest code I sent you and the code call (textbox) doesn't match the parameters of the code, so the textbox is going to say Error. Also, I don't see an Invisible textbox in the detail row with the field that determines when you start over with the balance.

Can you send the sql the populates the report AND tell me the field that changes the running balance?