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

Question

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

Viewing 14 posts - 31 through 43 (of 43 total)

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

gardenlady SSCommitted Points: 1974 More actions · Answer 1

Let me try to sum up what you need.

1. The field that resets the running balance has to be in every row of the SQL that populates the report.

2. You have to create a hidden textbox on the detail row. It references the field in #1 above. You hide it because you don't want it on the report, it's just a ReportItem we need for referencing elsewhere.

3. In the running balance textbox, you write a call to Code (see above) that has parameters for beginning balance, and transactions (typically divided into Debits and Credits but may be one field), and the field that resets the Running Balance. These parameters typically refer to ReportItems which is why you need this hidden field to reset the report, as well as a balance, debit, credit, etc...).

4. In Report, Report Properties, Code tab, you put the code (similar to above posting) but only with parameters that match what you put in #3. Your parameters in 3 and 4 may not match my parameters above--make parameters that match your situation. Your mileage may vary. 🙂

5. The field that resets the balance may be a string or a different data type. Fix my code accordingly.

6. If you're getting an error, you probably don't have matching parameters in 3 and 4 or you have a bad reference/typo.

Sample Code:

dim RBalChanger 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,BalChanger as Integer)

If RBalChanger <> BalChanger

Balance = BegBal + Debits + Credits

Else

Balance = Balance + Debits + Credits

End if

Counter = Counter +1

RBalChanger = BalChanger

Return Balance

End Function

Sample Call from Textbox:

=Code.CalcSubTotal(ReportItems!txtBegBal.value,ReportItems!txtdebit.value,ReportItems!txtcredit.Value, ReportItems!txtBalChanger.value)

I hope that this has simplified things for you. I'm on vacation next week and out of town but may be able to look at code today.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

All of the methods used where a correlated sub-query uses the < or <= relationship is doomed to performance failure. If you want a very high speed (1 million rows in 7 seconds) method for doing this, I recommend you take a look at (study and try, actually) the following article...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

I've used the method with great success and it's never failed me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gardenlady SSCommitted Points: 1974 More actions · Answer 3

Well, I have to say you're right. I tested over 300,000 rows and the report is taking way longer to run than the pure SQL. The reason I did it this way is that my coding skills are stronger and I couldn't figure out a SQL Server solution AND because I'd used code in Reports before. Guess I need to look at your article closer (it's pretty complex) and apply it to my project. Thanks for pointing this out.

Puja Shah SSCommitted Points: 1724 More actions · Answer 4

Hi,

I'm getting this warning when I run the report:

"Warning1[rsRuntimeErrorInExpression] The Value expression for the textbox ‘Running_Balance’ contains an error: Input string was not in a correct format.e:\ad-hoc report generation\reporting services applications\banking reports\banking reports\SL_2.rdl00"

In running balance textbox, I'm writing following :

=Code.CalcSubTotal(ReportItems!GLCrDr.value,ReportItems!debit.value,ReportItems!credit.Value,"rs",1,CInt(23))

It is showing UnrecognizedIdentifier in CalcSubTotal.

Why is this error appearing? Please look at the image.

Puja Shah SSCommitted Points: 1724 More actions · Answer 5

Hi,

Can you please explain me what shall I pass in RBalChanger parameter?

I don't have such field.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

gardenlady (8/29/2008)
Well, I have to say you're right. I tested over 300,000 rows and the report is taking way longer to run than the pure SQL. The reason I did it this way is that my coding skills are stronger and I couldn't figure out a SQL Server solution AND because I'd used code in Reports before. Guess I need to look at your article closer (it's pretty complex) and apply it to my project. Thanks for pointing this out.

You bet... and thank you very much for the feedback. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 7

pujashah10985 (9/1/2008)
I'm getting this warning when I run the report:

If you create a stored procedure to return a result set using the methods in the following article (mentioned previously on this thread), no such problems will exists and you will have very high speed running totals, to boot... 😉

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Puja Shah SSCommitted Points: 1724 More actions · Answer 8

Hi Jeff,

I have visited the link. I've to look in to detail for your solution. I'll try and let you know.

Thanks for your help.

Regards,

Puja Shah

gardenlady SSCommitted Points: 1974 More actions · Answer 9

I'm back from vacation and working on this code again. I'm hung up on creating (or recreating?) the indexes. Here's a piece of my code. The error I get is "Incorrect syntax near ')' ". The cursor is on the 2nd line of the 2nd index but the line number it's referring to is in the CREATE TABLE statement on the ",credits decimal (18,2) NOT NULL " line. Any thoughts? This table and indexes will have to be recreated each time the report runs. Thanks.

-- ===== Create the test table

CREATE TABLE [dbo].TransHist

( RowNum INT IDENTITY (1,1) NOT NULL

,company int NOT NULL

,fiscal_year int NOT NULL

,acct_unit nvarchar(15) NOT NULL

,account int NOT NULL

,sub_account int NOT NULL

,posting_date datetime NOT NULL

,reference nvarchar(150) NOT NULL

,SystemCode nvarchar(2) NOT NULL

,TotBegDebits decimal(24,2) NOT NULL

,TotBegCredits decimal(24,2) NOT NULL

,TMONTH int NOT NULL

,account_desc nvarchar(150) NOT NULL

,acct_unitname nvarchar(50) NOT NULL

,update_date datetime NOT NULL

,debits decimal (18,2) NOT NULL

,credits decimal (18,2) NOT NULL

,BegBal MONEY NOT NULL

,RunBal MONEY NOT NULL

,TransDescr nvarchar(150) NOT NULL

,JE int NOT NULL

,StartDate datetime NOT NULL

,EndDate datetime NOT NULL )

ALTER TABLE [dbo].[TransHist] ADD PRIMARY KEY CLUSTERED (RowNum)

CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date ON [dbo].TransHist

(Account, Acct_unit, sub_account,posting_date)

gardenlady SSCommitted Points: 1974 More actions · Answer 10

This almost works:

ALTER TABLE [dbo].TransHist

ADD PRIMARY KEY CLUSTERED (RowNum)

CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date

on [dbo].TransHist (Account, Acct_unit, sub_account,posting_date)

But when I execute the procedure, it says:

"Msg 308, Level 16, State 1, Procedure Trans_History3_pr, Line 1177

Index 'IX_TransHist_AccountID_Date' on table 'dbo.TransHist' (specified in the FROM clause) does not exist."

It's like the 2nd index is not getting added. I've tried various syntax changes but no luck.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 11

gardenlady (9/8/2008)
I'm back from vacation and working on this code again. I'm hung up on creating (or recreating?) the indexes. Here's a piece of my code. The error I get is "Incorrect syntax near ')' ". The cursor is on the 2nd line of the 2nd index but the line number it's referring to is in the CREATE TABLE statement on the ",credits decimal (18,2) NOT NULL " line. Any thoughts? This table and indexes will have to be recreated each time the report runs. Thanks.
-- ===== Create the test table
CREATE TABLE [dbo].TransHist
( RowNum INT IDENTITY (1,1) NOT NULL
,company int NOT NULL
,fiscal_year int NOT NULL
,acct_unit nvarchar(15) NOT NULL
,account int NOT NULL
,sub_account int NOT NULL
,posting_date datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,update_date datetime NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,JE int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime NOT NULL )
ALTER TABLE [dbo].[TransHist] ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date ON [dbo].TransHist
(Account, Acct_unit, sub_account,posting_date)

Dunno what's wrong... the code above ran just fine on my box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gardenlady SSCommitted Points: 1974 More actions · Answer 12

Strange, I'm not getting that error this morning. However, it's part of a larger piece of code. First time I run it, fine. Second time it says the table already exists. It is not an object in tempdb..sysobjects, so this stops it the 2nd time.

If EXISTS (SELECT * FROM tempdb..sysobjects WHERE name = 'TransHist' AND type = 'U')

BEGIN

DROP TABLE TransHist

END

gardenlady SSCommitted Points: 1974 More actions · Answer 13

Okay, I found code to get around that. Now it says my nonclustered index does not exist. But when I run my code again, it doesn't add either index and so I get an error:

Msg 308, Level 16, State 1, Procedure Trans_History3_pr, Line 1185

Index 'IX_TransHist_AccountID_Date' on table 'dbo.TransHist' (specified in the FROM clause) does not exist.

gardenlady SSCommitted Points: 1974 More actions · Answer 14

Never mind. I got the syntax fixed, it runs and tests correctly. Thanks for the great referral.