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