Hi all SQL 2008 R2 Report Builder I'll keep this simple. Imagine I have a dataset that relates to sales. The data set at row level has a year and month. In the report I group by year and month. At the year level I have an expression which shows the average sales per month for the year. An expression which sums up total sales for the year divided by distinct count of amount of months there are the in current dataset SUM(sales)/DISTINCT COUNT of Months. This works fine. I've now been asked at year level for an average of the last 6 months. So if we had say 11 months details at month level Jan - Nov then at Year level there would be an average of Jan - Jun sales. Any idea on how to do this? The requesters come from Excel and they say it is easy lol, select Jan - Jun date range and choose AVG in the sum area. Thanks, derrysql

I'd be inclined to try to do this in your database query. Set up a subquery that calculates the last 6 months sales for every month, then join to it in your current query on year and month. Josh Ash

Hi Derry, In order to meet your requirement, please try to the following steps: 1. Redesign the dataset query and add a new field RowNumberInYear: For example: SELECT Year, Month,Sales, ROW_NUMBER() OVER (PARTITION BY Year ORDER BY Year) AS RowNumberInYear FROM table 2. Add a new row inside the Year group and specify the value of the textbox with following expression. =Runningvalue(IIF(fields!RowNumberInYear.Value<=6,fields!Sales.Value,0),SUM,"Year") For more information, please see: ROW_NUMBER (Transact-SQL) RunningValue Function (Reporting Services) If I have any misunderstanding, please let me know. Regards, Fanny Liu Fanny Liu TechNet Community Support

I've now been asked at year level for an average of the last 6 months. So if we had say 11 months details at month level Jan - Nov then at Year level there would be an average of Jan - Jun sales. That would be the FIRST six months. Would not the last 6 months in that case be June - November? Partitioning over the year and doing a running value won't work - you will just lose most of your data as it will just SUM the first 6 rows in every year it finds. My first answer is wrong as well as you need a rolling total at the year level for the last 6 months that exist. I am going to assume you need the LAST 6 months you have data for each year, which is June - November in the example given. The components of the query required are :- [1] A SUM for the year and a distinct count of the months (which you already have). [2] The MAXIMUM month that data exists for in each [3] A SUM for each year that sums between the maximum month number, and the month 6 months preceding. Sorry about the date code.. I'm sure you can shorten that Example on AdventureWorks Sales.SalesDetail table. /* [1] A SUM for the year and a distinct count of the months (which you already have). [2] The MAXIMUM month that data exists for in each and the date to sum to and from [3] A SUM for each year that sums between the dates in two for each row in [1] */ /* [1] and [2] are as follows */ WITH Set1_SumYearAndDistinctMonths AS ( SELECT YEAR(ModifiedDate) AS SalesYear, SUM(UnitPrice) as SumSales, COUNT(DISTINCT MONTH(ModifiedDate)) AS MonthCount, MAX(MONTH(ModifiedDate)) AS MaxMonth, CONVERT(datetime,(CONVERT(varchar(4),YEAR(ModifiedDate)) + '-' + CONVERT(varchar(2), MAX(MONTH(ModifiedDate))) + '-31')) AS DateToSumTo, DATEADD(month, -5, CONVERT(datetime,(CONVERT(varchar(4),YEAR(ModifiedDate)) + '-' + CONVERT(varchar(2), MAX(MONTH(ModifiedDate))) + '-01'))) AS DateToSumFrom FROM [Sales].[SalesOrderDetail] GROUP BY YEAR(ModifiedDate) ), FinalQuery AS ( SELECT * /* [3] is here */ ,(SELECT SUM(UnitPrice) AS Last6MonthsSales FROM [Sales].[SalesOrderDetail] s2 WHERE s2.ModifiedDate BETWEEN DateToSumFrom AND DateToSumTo ) as asdf FROM Set1_SumYearAndDistinctMonths s1 ) SELECT * FROM FinalQuery Josh Ash

I've now been asked at year level for an average of the last 6 months. So if we had say 11 months details at month level Jan - Nov then at Year level there would be an average of Jan - Jun sales. That would be the FIRST six months. Would not the last 6 months in that case be June - November? Partitioning over the year and doing a running value won't work - you will just lose most of your data as it will just SUM the first 6 rows in every year it finds. My first answer is wrong as well as you need a rolling total at the year level for the last 6 months that exist. I am going to assume you need the LAST 6 months you have data for each year, which is June - November in the example given. The components of the query required are :- [1] A SUM for the year and a distinct count of the months (which you already have). [2] The MAXIMUM month that data exists for in each [3] A SUM for each year that sums between the maximum month number, and the month 6 months preceding. Sorry about the date code.. you have to calculate the last day of the month... i'm using a hardcoded 31 because it fits my data.. but you'll need to change that you'll get conversion errors... but you get the idea.. I have my own problems :D http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/ Example on AdventureWorks Sales.SalesDetail table. /* [1] A SUM for the year and a distinct count of the months (which you already have). [2] The MAXIMUM month that data exists for in each and the date to sum to and from [3] A SUM for each year that sums between the dates in two for each row in [1] */ /* [1] and [2] are as follows */ WITH Set1_SumYearAndDistinctMonths AS ( SELECT YEAR(ModifiedDate) AS SalesYear, SUM(UnitPrice) as SumSales, COUNT(DISTINCT MONTH(ModifiedDate)) AS MonthCount, MAX(MONTH(ModifiedDate)) AS MaxMonth, CONVERT(datetime,(CONVERT(varchar(4),YEAR(ModifiedDate)) + '-' + CONVERT(varchar(2), MAX(MONTH(ModifiedDate))) + '-31')) AS DateToSumTo, DATEADD(month, -5, CONVERT(datetime,(CONVERT(varchar(4),YEAR(ModifiedDate)) + '-' + CONVERT(varchar(2), MAX(MONTH(ModifiedDate))) + '-01'))) AS DateToSumFrom FROM [Sales].[SalesOrderDetail] GROUP BY YEAR(ModifiedDate) ), FinalQuery AS ( SELECT * ,(SELECT SUM(UnitPrice) AS Last6MonthsSales FROM [Sales].[SalesOrderDetail] s2 WHERE s2.ModifiedDate BETWEEN DateToSumFrom AND DateToSumTo ) as asdf FROM Set1_SumYearAndDistinctMonths s1 ) SELECT * FROM FinalQuery Josh Ash