Search code examples
reporting-servicesssrs-2008

Is it possible to limit RunningValue to look back for a certain number of rows?

Im trying to average the past 5 rows in my table i created in SSRS grouped by date(Monday of every week). Ive tried runningValue however it looks back at all the past rows for each group. Is there a way to limit the scope to just the past 5 rows or weeks for each Date group.

Thanks

Solution

  • I would accomplish this with grouping. I don't know what your dataset is like but I assume it is a SQL query you can modify. The easiest solution would be to add a week number column to your query. For example:

    SELECT datepart(week, YOURDATE) as WeekNumber

    More info on datepart: https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017

    Once you have a week number, use the table creation wizard in Report Builder and add WeekNumber as a row group. This will group your values by week number and give you a total under each week. You can change the total by double clicking and making it AVG() instead of SUM().

    Note: If you already have each 5 day period in a group, you should be able to right click that and add total. At which point you can just change the SUM to AVG there.