Balance CalculationRSS

• • Reply
  jsshivalik

  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

  
  
  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	

• • Reply
  Yongqing Yu

  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.

  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.