none
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

Answers

  • 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

    • Marked as answer by GezC 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:

    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 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 GezC 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.

    If you have any question, please feel free to ask.

    Thanks,
    Eileen

    • Marked as answer by GezC 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:

    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 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 GezC 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