Hi i need to display something as in ssrs report DEBIT Credit Total 100 0 100 50 0 150 0 300 -150 0 100 -250 my dataset query is select case when (type='C' ) then tran_amt else 0 end as Debit , case when (type='T' ) then tran_amt else 0 end as Credit from Table 1 . How I can get this total in Report. i have tried something like decalre @sum float = 0.0 select case when (type='C' ) then @sum = @sum + tran_amt else @sum = @sum - tran_amt end from table 1 . But it is not working . help on this one kulbir

You need a running total: http://www.sqlteam.com/article/calculating-running-totals You will need to modify this so you are summing across two fields, perhaps by adding a third field which is the sum of debits plus credits, and doing the total on this.Bonediggler

Hi There Thanks for your posting. If you are using SSRS 2008 R2 then this is simple enough you can write expression something like this =(RunningValue ((Fields!Debit.Value-Fields!Credit.Value),sum,nothing)) =(RunningValue ((Fields!Debit.Value-Fields!Credit.Value),sum,nothing)) I am putting screnshot for you help If you would like to use query instead of builtin function please you can have a look on my other threads http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/619cf08f-db9c-47d8-9eb9-9c3716fa991a/ http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8bc6c817-562f-483b-be48-b924710c24f9 I hope this will help Many thanks Syed Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful

hI sYED yOUR SOLUTION WORKED but it resulted in another error My report has grouping. i have created a group based on Currency. My report should be like this. ( Running total from last group does not passes over to next group) ORDER Debit Credit Total A 100 0 100 B 0 200 -100 C 0 200 -300 Total By Currency (USD) 100 400 -300 ORDER Debit Credit Total A 100 0 100 Total By Currency (INR) 100 0 100 But I am geeting report like this ORDER Debit Credit Total A 100 0 100 B 0 200 -100 C 0 200 -300 Total By Currency (USD) 100 400 -300 ORDER Debit Credit Total A 100 0 -200 Total By Currency (INR) 100 0 100 the value -200 (highligted in BOLD and underlined ) is wrong. Accuaret value should be 100. -200 is coming because ( -300 value from USD group is getting caried over to INR group, -300 + 100 = -200 Please help on this one kulbir

Found the solution I had created a group (RunningValue ((Fields!Debit.Value-Fields!Credit.Value),sum,"GROUPNAME")) replace the nothing with groupname of group created.Thus running value is reset to 0 with each instance of groupkulbir