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.

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.

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. If you have any question, please feel free to ask. Thanks, Eileen

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: Details different methods for doing T-SQL based running totals http://www.sqlteam.com/article/calculating-running-totals http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx http://www.pawlowski.cz/tag/running-total/ http://www.sqlservercentral.com/articles/T-SQL/68467/ 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 wont 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.

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