Limit aggregated top N rows displayed?
SQL2008 R2 and using Report Builder 3.0. The end goal is to display the top 25 products sold per month along with displaying the top 25 products over the past 3 months. I've already solved the top 25 per month by using RANK and PARTITION over the month field in a CTE. The problem I'm running into is that when 3 months are displayed in the SSRS report, there are more than 25 rows displayed. This makes sense, since each month the same product isn't always in the same ranking (i.e. July WidgetA was #1, Aug WidgetA was #3, Sept WidgetA was #2). So while not all months have the same top 25 products, many do overlap and the report does show this, but the 'stragglers' make the report have additional rows. I've already tried playing around with filters on the dataset and various groups, but it's not achieve the desired end result of only 25 rows in the final report. Am I must be missing something simple in the T-SQL query or filter/setting in Report Builder?
September 27th, 2011 6:22pm

It sounds to me like you need to change your query a bit (if I understand your requirement). It sounds like you need to limit the query to returning the top 25 products over the last three months, and then have columns which display their rank for each of the last three months. This will require that you make use of some sub-queries or CTE's to get the aggregates. Here is an example using AdventureWorks2008R2. This will need to be modified to work with your schema, but should help getting the concepts ;with MonthTotals as ( select prod.Name [ProductName], dateadd(month,datediff(m,0,head.OrderDate),0) [TranMonth], SUM(sod.LineTotal) [Total], RANK() over(partition by dateadd(month,datediff(m,0,head.OrderDate),0) order by SUM(sod.LineTotal) desc) [Rank] from Sales.SalesOrderDetail sod join sales.SalesOrderHeader head on head.SalesOrderID = sod.SalesOrderID join Production.Product prod on prod.ProductID = sod.ProductID where head.OrderDate >= '1 Jan 2007' and head.OrderDate < '1 Apr 2007' group by prod.Name, dateadd(month,datediff(m,0,head.OrderDate),0) ), OverallRank as ( select ProductName, SUM(Total) [Total], RANK() over (order by sum(Total) desc) [Rank] from MonthTotals group by ProductName ) select top (25) OverallRank.ProductName, OverallRank.Total [ThreeMonthTotal], OverallRank.[Rank] [3MonthRank], m1.[Rank] [Month1Rank], m2.[Rank] [Month2Rank], m3.[Rank] [Month3Rank] from OverallRank left join MonthTotals m1 on m1.ProductName = OverallRank.ProductName and m1.TranMonth = '1 Jan 2007' left join MonthTotals m2 on m2.ProductName = OverallRank.ProductName and m2.TranMonth = '1 Feb 2007' left join MonthTotals m3 on m3.ProductName = OverallRank.ProductName and m3.TranMonth = '1 Mar 2007' order by OverallRank.[Rank] HTH Craig Bryden - Please mark correct and/or helpful answers
Free Windows Admin Tool Kit Click here and download it now
September 27th, 2011 8:42pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics