Last post Dec 02, 2019 02:48 AM by Yongqing Yu

• jsshivalik

Member

140 Points

527 Posts

Balance Calculation

Hi

How to calculate balances thru SSRS.

I want in Document No 11 it should show balance as 9187 Cr
In Document No 12 it should show balance 13187 Cr

In Document No 14 balance should be 35999 Cr

```CREATE TABLE [#Gl] (IdInt int PRIMARY KEY IDENTITY,        LineType int,		AcctCode  nvarchar(15),		AcctName  nvarchar(100),				TransType  nvarchar(4),		DocumentNo  int, 		DocumentDate  datetime,  		Narration  nvarchar(max),		LineMemo  nvarchar(max),		Debit  numeric(19,6),		Credit  numeric(19,6),		Balance  numeric(19,6),		Dr_Cr nvarchar(2)	)Account	Name	DocumentNo	DocumentDate	Narration	Debit	Credit	Balance
1	Loan 	                               Opg. Balance		0	15200	Cr
1		11	        01-10-2019	By Loan - 011	6013
1		12	        14-10-2019	By Loan - 210	0	4000

2	Cash			               Opg. Balance		0	0	Cr
2		14	        14-10-2019	By Cash	0	        35999	```
• Yongqing Yu

Contributor

3690 Points

1043 Posts

Re: Balance Calculation

Dec 02, 2019 02:48 AM|Yongqing Yu|LINK

Hi jsshivalik,

In your previous thread : https://forums.asp.net/t/2161896.aspx , @Yang Shen has found a way to calculate and fill in the Balance column value through the SQL statement.

You can put the SQL statement provided by him into the data source of SSRS when binding data.

```create table #Gl
(
AcctCode nvarchar(15),
Debit numeric(19,6),
Credit numeric(19,6),
Balance numeric(19,6)
)
insert into #Gl values(1,null,0,15200),(1,6013,null,null),(1,0,4000,null),(2,null,0,0),(2,null,35999,null)

IF OBJECT_ID('tempdb..#G2') IS NOT NULL DROP TABLE #G2
select *,ROW_NUMBER() over(partition by AcctCode order by AcctCode asc) rank into #G2 from #Gl

declare @i int = 1;
declare @j int;
declare @code nvarchar(15);
while @i<=(select count(distinct(AcctCode)) from #Gl)
begin
set @code=(select top 1 a.AcctCode from( select distinct(AcctCode) from #Gl)a where a.AcctCode not in (select top (@i-1) a.AcctCode from( select distinct(AcctCode) from #Gl)a))
set @j=1
while @j<=(select COUNT(*) from #Gl where AcctCode=@code)
begin
update #G2 set Balance=isnull((select Balance from #G2 where rank=(@j-1) and AcctCode=@code),0)+isnull(Balance,0)+isnull(Credit,0)-ISNULL(Debit,0) where AcctCode=@code and rank=@j
set @j+=1
end
set @i+=1
end

truncate table #Gl
insert into #Gl select AcctCode,Debit,Credit,Balance from #G2
select * from #Gl```

Here is the image you can refer to bind your data in SSRS :

Here is the whole process of operation. You can refer to the following diagram：

Best Regards,

YongQing.

MSDN Community Support