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
Interesting idea, but not exactly sure how your solution would be implemented with my current code.
with drugranking as
(
SELECT
Facility
,[Encounter Type]
,[Product Description]
,[Dispense Category Code]
,SUM(FullView.Doses) AS SUM_Doses
,dateadd(month, datediff(month, 0, FullView.[Service Date and Time]),0) AS Service_Month
,rank() over (partition by dateadd(month, datediff(month, 0, FullView.[Service Date and Time]),0) order by SUM(Doses) desc) as DrugRank
FROM
FullView
WHERE
[Service Date and Time] >= @ServiceDateandTime
AND [Service Date and Time] < @ServiceDateandTime2
AND Facility = @Facility
GROUP BY
Facility
,[Encounter Type]
,[Product Description]
,[Dispense Category Code]
,dateadd(month, datediff(month, 0, [Service Date and Time]),0)
)
select
drugranking.*
from
drugranking
where
drugrank <= 25
order by
drugrank
September 28th, 2011 1:37pm
Hi BSOD2600,
Thanks for your post.
You can replace rank() to row_number() to have try. As we know, for Rank, if two or more rows tie for a rank, each tied rows
receives the same rank. For example, if you data contain several product which have the same
sum(Doses) values, they would get the same
DrugRank, as a result, if you specify the condition drungrank<=25, you would get more count rows than you expected.
Thanks,
Bill Lu
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
September 29th, 2011 6:20am