Hi I Want a Sql Server Report I Have Table like this Main Table --------------- Trid OpnBal 1 10000 2 20000 Second Table ------------------- TrDetID Fk_TrID Type Amt 1 1 Credit 100 2 1 Debit 200 3 2 Credit 100 --------------------------------------------------- My Report should like this TrId OpnBal Cr Db CloseBal 1 10000 100 0 9900 2 9900 0 200 10100 3 20000 100 0 19900 Thanks in advance

Hi Devsurya, In your example above, considering the first table name to be 'Table1' and the second table name to be 'Table2', please find below the SQL script which would give you the desired output - DECLARE @Table2 TABLE ( [RowNum] INT , [TrDetID] INT , [FK_TrID] INT , [TYPE] VARCHAR(10) , [Amt] MONEY , [RunningOpeningBalance] MONEY ) DECLARE @RunningOpeningBalance MONEY SET @RunningOpeningBalance = 0 INSERT INTO @Table2 SELECT ROW_NUMBER() OVER (PARTITION BY [FK_TrID] ORDER BY [TrDetID]) - 1 , [TrDetID] , [FK_TrID] , [Type] , [Amt] , null FROM [Table2] ORDER BY [TrDetID] UPDATE T2 SET @RunningOpeningBalance = RunningOpeningBalance = CASE WHEN [Type] = 'Credit' AND T2.[RowNum] = 0 THEN [T1].[OpenBal] - [T2].[Amt] WHEN [Type] = 'Debit' AND [T2].[RowNum] = 0 THEN [T1].[OpenBal] + [T2].[Amt] WHEN [Type] = 'Credit' THEN @RunningOpeningBalance - [T2].[Amt] ELSE @RunningOpeningBalance + [T2].[Amt] END FROM @Table2 [T2] INNER JOIN Table1 [T1] ON [T1].[TrID] = [T2].[FK_TrID] SELECT [Query].[TrDetID] , ISNULL([Query].[ClosingBal], 0) + ISNULL([Query].[Credit], 0) - ISNULL([Query].[Debit], 0) AS [OpeningBalance] , ISNULL([Query].[Credit], 0) AS [Credit] , ISNULL([Query].[Debit], 0) AS [Debit] , ISNULL([Query].[ClosingBal], 0) AS [ClosingBal] FROM ( SELECT TrDetID , FK_TrID , [Credit] , [Debit] , [RunningOpeningBalance] AS [ClosingBal] FROM @Table2 PIVOT ( SUM(Amt) FOR [Type] IN (Credit, Debit) ) P ) [Query] ORDER BY [Query].[TrDetID] I have tried the above code in my machine with the sample data given in your post above; this piece of code works fine for me (as in I get the output that is required as per your post above); however, I haven't done any extensive testing. You would need to do an extensive testing of the code and once you are happy with it, you can create a stored procedure using this logic. Once you create this stored procedure, you just need to call it from your SSRS report and then directly display the data in your report; no calculations are to be done in your report here since everything is being taken care of in the stored procedure. Please let me know in case it doesn't work for you. Cheers, ShalinShalin P. Kapadia