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
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
110 Points
389 Posts
Balance Calculation
Nov 29, 2019 07:55 AM|jsshivalik|LINK
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
Contributor
2820 Points
803 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.
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.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.