# Matrix rolling total

### Question

• Hi,

I nearly always find an answer or work out a solution myself before posting but on this occassion I'm completely stumped!

I'm using Report builder 3 / SQL Server Mgt Studio with SQL 2005 (although my company will be upgrading to 2008 R2 in the coming months, in case this is relevant).  I'm trying to add a rolling total to a cross table.  Much of the googling I've done so far has brought up plenty on cumulative totals using RunningValue and other sum functions but I'm actually after a 3 month rolling total.  My end goal would look as follows:

 Year: 2009 2010 Month Value Roll 3mths Value Roll 3mths Jan 2 4 8 (1+3+4) Feb 5 0 7 (3+4+0) Mar 7 14 (2+5+7) 8 12 (4+0+8) Apr 7 19 (5+7+7) 5 13 (etc...) May 6 20 (7+7+6) 4 17 Jun 4 17 (etc...) 5 14 Jul 0 10 7 16 Aug 9 13 1 13 Sep 4 13 0 8 Oct 7 20 6 7 Nov 1 12 3 9 Dec 3 11 3 12

Notes:

1.) I have one row group which is the month
2.) I have one column group which is the year
3.) I have no details row but i could easily add one
4.) the bits in brackets are to explain the calculation and wouldn't feature in the final report
5.) in jan & feb 2009 it does not matter if the values are blank, or if Jan = Jan and Feb = Jan+Feb
6.) jan and feb 2010 includes values from nov and dec 2009

I've looked at RunningValue, Sum, group variables (think these might only work with 2008R2?), embedded code and more.

I've considered ideas like creating a running total across the whole dataset and then subtracting one running total from an earlier one but I have not been able to work out how to do this (didn't even come close!).

With slightly more success, I've tried creating SUM() formulas where the SUM() scope is the entire dataset but it uses current scope level variables, i.e. the start and end dates of a 3 month period based on the current scope month and year.  I successfully managed to derive formulas that determined what the start and end dates would be, e.g. for Oct 2010, start date would be 1st Aug whilst end date would be 31st Oct.  However, as soon as I put these fomulas within a SUM() function using the dataset as scope, my start and dates changed to something else (not sure what) and i had the same massively inflated numbers across all rows and columns.  Is there a way of having a SUM() function with scope=dataset but variables that are an inner scope?

I'm new to SSRS so may have overlooked something, but I do come from a reporting/technical background.  I'm wondering if SQL 2008R2 is my solution...?  Perhaps the above is possible with SQL05?  Fingers crossed, someone can help!  It would be much appreciated.

Many thanks,

Gerard.

Monday, May 09, 2011 3:18 PM

• Hi GezC,

From your scenario, my understanding is that you are going to calculate a total value after a 3 month on report level.

I am afraid this cannot be achieved in reporting service at present, including SQL 2008 R2. Sum function does not have variables as inner scope . I suggest you writing T-SQL in your dataset to show the rolling total. You can post the thread to our Transact-SQL forum: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. There are many experts focus on it.

Additionally, I would suggest you submit a feedback at http://connect.microsoft.com. Connect site is a connection point between you and Microsoft, and ultimately the larger community. Your feedback enables Microsoft to make software and services the best that they can be, and you can learn about and contribute to exciting projects. Appreciate your understanding.

Thanks,
Eileen

• Marked as answer by Friday, May 13, 2011 11:47 AM
Friday, May 13, 2011 10:20 AM
• Thanks Eileen for taking the time to respond.  The absence of other forum posts on this led me to the conclusion that it wasn't currently possible.  I will indeed submit feedback via Connect site.

In the past few days I've succussfully gone down the T-SQL cursor route.  A few links for anyone else coming across this thread:

As mentioned previsouly I used the cursor approach and adapted it as follows:

USE my_database

-- create final output table
DECLARE @FinalTbl TABLE (YearMth varchar(7), SourceType varchar(10), PaxCount int, PaxRollTot int)

-- declare and set variables
-- PaxRollTot will store the 3 month rolling total
-- PaxMinus1/2/3 variables will store the previous 3 rows values
DECLARE @YearMth varchar(7),
@SourceType varchar(10),
@PaxCount int,
@PaxRollTot int,
@PaxMinus1 int,
@PaxMinus2 int,
@PaxMinus3 int

SET @PaxRollTot = 0
SET @PaxMinus1 = 0
SET @PaxMinus2 = 0
SET @PaxMinus3 = 0

-- declare cursor
DECLARE rt_cursor CURSOR
FOR

-- select data for use in cursor
-- SELECT statement was here – I removed as it was rather lengthy and wouldn't mean much to users!
-- It essentailly retrieved values for @YearMth, @SourceType and @PaxCount
<br/>
-- open cursor and fetch first record
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @YearMth, @SourceType, @PaxCount

-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN

-- add on current row value and subtract row value from 3 rows prior
SET @PaxRollTot = @PaxRollTot + @PaxCount - @PaxMinus3

-- insert row into final table
INSERT @FinalTbl VALUES (@YearMth, @SourceType, @PaxCount, @PaxRollTot)

-- prior to moving to next row of cursor, the values are ‘shuffled’ along, i.e. current row becomes minus 1, minus1 becomes minus2 and minus2 becomes minus3.
-- however, note the order is important, i.e. you must set 3 then 2 then 1, as 1 then 2 then 3 won’t work!
SET @PaxMinus3 = @PaxMinus2
SET @PaxMinus2 = @PaxMinus1
SET @PaxMinus1 = @PaxCount

-- select next record in cursor
FETCH NEXT FROM rt_cursor INTO @YearMth, @SourceType, @PaxCount

-- end of while loop
END

-- close out...
CLOSE rt_cursor
DEALLOCATE rt_cursor

-- final select statement
SELECT * FROM @FinalTbl

The SQL output looked like this:

YearMth     SourceType     PaxCount     PaxRollTot
2010-01     Agents           5                 5
2010-02     Agents           7                 12
2010-03     Agents           4                 16
2010-04     Agents           9                 20
2010-05     Agents           3                 16

...which is fairly simple to use in an SSRS / Report Builder 3 matrix (or tablix) table...

I hope this helps others.

Gerard.

• Marked as answer by Friday, May 13, 2011 11:47 AM
Friday, May 13, 2011 11:47 AM

### All replies

• Is anyone able to help with this?  Even if it's a reponse to say it's not possible, I will at least know to move on.

Cheers,

Gerard.

Wednesday, May 11, 2011 7:53 PM
• Hi GezC,

From your scenario, my understanding is that you are going to calculate a total value after a 3 month on report level.

I am afraid this cannot be achieved in reporting service at present, including SQL 2008 R2. Sum function does not have variables as inner scope . I suggest you writing T-SQL in your dataset to show the rolling total. You can post the thread to our Transact-SQL forum: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. There are many experts focus on it.

Additionally, I would suggest you submit a feedback at http://connect.microsoft.com. Connect site is a connection point between you and Microsoft, and ultimately the larger community. Your feedback enables Microsoft to make software and services the best that they can be, and you can learn about and contribute to exciting projects. Appreciate your understanding.

Thanks,
Eileen

• Marked as answer by Friday, May 13, 2011 11:47 AM
Friday, May 13, 2011 10:20 AM
• Thanks Eileen for taking the time to respond.  The absence of other forum posts on this led me to the conclusion that it wasn't currently possible.  I will indeed submit feedback via Connect site.

In the past few days I've succussfully gone down the T-SQL cursor route.  A few links for anyone else coming across this thread:

As mentioned previsouly I used the cursor approach and adapted it as follows:

USE my_database

-- create final output table
DECLARE @FinalTbl TABLE (YearMth varchar(7), SourceType varchar(10), PaxCount int, PaxRollTot int)

-- declare and set variables
-- PaxRollTot will store the 3 month rolling total
-- PaxMinus1/2/3 variables will store the previous 3 rows values
DECLARE @YearMth varchar(7),
@SourceType varchar(10),
@PaxCount int,
@PaxRollTot int,
@PaxMinus1 int,
@PaxMinus2 int,
@PaxMinus3 int

SET @PaxRollTot = 0
SET @PaxMinus1 = 0
SET @PaxMinus2 = 0
SET @PaxMinus3 = 0

-- declare cursor
DECLARE rt_cursor CURSOR
FOR

-- select data for use in cursor
-- SELECT statement was here – I removed as it was rather lengthy and wouldn't mean much to users!
-- It essentailly retrieved values for @YearMth, @SourceType and @PaxCount
<br/>
-- open cursor and fetch first record
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @YearMth, @SourceType, @PaxCount

-- start of while loop
WHILE @@FETCH_STATUS = 0
BEGIN

-- add on current row value and subtract row value from 3 rows prior
SET @PaxRollTot = @PaxRollTot + @PaxCount - @PaxMinus3

-- insert row into final table
INSERT @FinalTbl VALUES (@YearMth, @SourceType, @PaxCount, @PaxRollTot)

-- prior to moving to next row of cursor, the values are ‘shuffled’ along, i.e. current row becomes minus 1, minus1 becomes minus2 and minus2 becomes minus3.
-- however, note the order is important, i.e. you must set 3 then 2 then 1, as 1 then 2 then 3 won’t work!
SET @PaxMinus3 = @PaxMinus2
SET @PaxMinus2 = @PaxMinus1
SET @PaxMinus1 = @PaxCount

-- select next record in cursor
FETCH NEXT FROM rt_cursor INTO @YearMth, @SourceType, @PaxCount

-- end of while loop
END

-- close out...
CLOSE rt_cursor
DEALLOCATE rt_cursor

-- final select statement
SELECT * FROM @FinalTbl

The SQL output looked like this:

YearMth     SourceType     PaxCount     PaxRollTot
2010-01     Agents           5                 5
2010-02     Agents           7                 12
2010-03     Agents           4                 16
2010-04     Agents           9                 20
2010-05     Agents           3                 16

...which is fairly simple to use in an SSRS / Report Builder 3 matrix (or tablix) table...

I hope this helps others.

Gerard.

• Marked as answer by Friday, May 13, 2011 11:47 AM
Friday, May 13, 2011 11:47 AM
• Hi Gezc,

Thank you for sharing your solutions and experience here. It will be very beneficial for other community members who have similar questions.

Thanks,
Eileen

Friday, May 13, 2011 2:37 PM