How to get the previous value of a row in SSRS

Question

My SSRS report has two groups (Account, Month). Account is Parent and Month is child group. Now I want to show the ending balance of each Month as the Beginning balance of next month. A sample report is given bellow. Red font indicates the SUM of each group. Remember that i am trying to get the result by using SSRS Previous() function, but can't get the expected result.

Previous(Sum(Fields!NetAmt.Value),"Month")

Month => Month group name.

Can any one help me?

Thanks in advance.

Rashed

Sample SQL Data

CREATE TABLE [dbo].[Balances](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [Account] [nvarchar](50) NULL,
    [Month] [date] NULL,
    [BegBalance] [float] NULL,
    [Debit] [float] NULL,
    [Credit] [float] NULL,
    [EndBalance] [float] NULL,
 CONSTRAINT [PK_Balances] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Balances] ON
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (1, N'Cash', CAST(0xDB3A0B00 AS Date), 0, 100, 50, 50)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (2, N'Cash', CAST(0xDB3A0B00 AS Date), 0, 200, 50, 150)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (3, N'Cash', CAST(0xFA3A0B00 AS Date), 0, 200, 50, 150)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (4, N'Cash', CAST(0xFA3A0B00 AS Date), 0, 200, 100, 100)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (5, N'Mr.  Axxx', CAST(0xDB3A0B00 AS Date), 0, 200, 100, 100)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (6, N'Mr.  Axxx', CAST(0xDB3A0B00 AS Date), 0, 100, 50, 50)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (7, N'Mr.  Axxx', CAST(0xFA3A0B00 AS Date), 0, 100, 50, 50)
INSERT [dbo].[Balances] ([id], [Account], [Month], [BegBalance], [Debit], [Credit], [EndBalance]) VALUES (8, N'Mr.  Axxx', CAST(0xFA3A0B00 AS Date), 0, 100, 20, 80)
SET IDENTITY_INSERT [dbo].[Balances] OFF

After Using Previous() function

Could you add some sample data? I think it can be achieved. – alejandro zuleta Oct 06 '16 at 13:33 — alejandro zuleta, Oct 06 '16 at 13:33

p2k · Answer 1 · 2016-10-06T19:40:29.303

You can handle it in your dataset's SQL query, Try using LAG():

SELECT mt.*, beg.begbal FROM [dbo].[Balances] mt
JOIN
(
SELECT [Account]
      ,[Month]
      ,SUM([EndBalance]) EndBal
      ,LAG (SUM([EndBalance]), 1, 0) OVER (PARTITION BY [Account] ORDER BY [Month] ASC) begbal
  FROM [dbo].[Balances]
  GROUP BY [Account]
      ,[Month]
) beg
ON mt.Month = beg.Month
AND mt.Account = beg.Account

Update: without using LAG(), try the below code.

WITH CTE AS
(
SELECT [Account]
      ,[Month]
      ,SUM([EndBalance]) EndBal
      ,ROW_NUMBER() OVER (PARTITION BY [Account] ORDER BY [Month] ASC) RowVal
  FROM [dbo].[Balances]
  GROUP BY [Account]
      ,[Month]
)

SELECT mt.*, ISNULL(t2.EndBal, 0) as begbal FROM CTE t1
LEFT JOIN CTE t2
ON t1.Account = t2.Account AND t1.RowVal = t2.RowVal + 1
JOIN [dbo].[Balances] mt
ON t1.Month = mt.Month AND t1.Account = mt.Account

Also, below is the SSRS expression code you are looking for:

=IIF
(
(RunningValue(Fields!Month.Value, CountDistinct, "Account")) = 1,
0,
Previous(SUM(Fields!EndBalance.Value),"MonthGrp")
)

Updated the answer with SSRS Expression. Also T-SQL replacement using ROW_NUMBER(). — p2k, Oct 06 '16 at 19:41
Thanks for your SSRS expression code. I was actually looking for this. It is perfectly working for me. — Rashedul Alam, Oct 07 '16 at 03:24

score 1 · Accepted Answer · answered Oct 06 '16 at 20:12

You can use LOOKUPSET function and custom code to get the previous End Balance total.

Go to Report menu / Report properties... in the Code tab paste this VB function.

Public Function GetBegBal(values As Object) As String
    Dim total As Double = 0
    For Each value As Double In values
                      total = total + value
        Next
    Return total
End Function

In the Beg Balance use this expression:

=Code.GetBegBal(
LOOKUPSET(
Fields!Account.Value & "-" & MONTH(Fields!Month.Value)-1,
Fields!Account.Value & "-" & MONTH(Fields!Month.Value),
Fields!EndBalance.Value,
"DataSetName")
)

Replace DataSetName by the actual name of yours, then preview your report, it should look like this:

Note I am using MONTHNAME function in the Months column so it returns the name of the month based on the regional and language settings in my machine (Spanish).

Let me know if this helps.

How to get the previous value of a row in SSRS

2 Answers2

Linked