Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't let the little things tie you down. Keep the wider perspective in your view
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

11
November

How Do I Show the 3 Largest Sales Figures per Group on One Row in SQL Server Reporting Service 2008 R2

During a recent SQL Server Reporting Services 2008 R2 consultancy, the delegate wanted to know how to display the 3 highest values per group as a single record.


Solution:

The solution is the modification of the blog post How Do I Calculate the Top N Sales by Group.

Using the SQL statement to create 3 derived tables thats allows one to calculate separately the 1st, 2nd and 3rd largest sales value per group.


SELECT     Employee.[Employee ID], Employee.[First Name] + ' ' + Employee.[Last Name] AS [Employee Name], [1st].[Order Amount] AS Highest,
                      [2nd].[Order Amount] AS [2nd Highest], [3rd].[Order Amount] AS [3rd Highest]
FROM         Employee INNER JOIN
 (SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
   FROM          Orders AS a INNER JOIN
  Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
GROUP BY a.[Employee ID], a.[Order Amount]
 HAVING      (COUNT(*) = 1)
   ORDER BY a.[Employee ID], COUNT(*)) AS [1st] ON

Employee.[Employee ID] = [1st].[Employee ID] INNER JOIN

(SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
  FROM          Orders AS a INNER JOIN
  Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
 GROUP BY a.[Employee ID], a.[Order Amount]
  HAVING      (COUNT(*) = 2)
    ORDER BY a.[Employee ID], COUNT(*)) AS [2nd] ON

Employee.[Employee ID] = [2nd].[Employee ID] INNER JOIN

  (SELECT     TOP 100 PERCENT a.[Employee ID], a.[Order Amount], COUNT(*) AS Rank_No
 FROM          Orders AS a INNER JOIN
 Orders AS b ON a.[Employee ID] = b.[Employee ID] AND a.[Order Amount] <= b.[Order Amount]
   GROUP BY a.[Employee ID], a.[Order Amount]
  HAVING      (COUNT(*) = 3)
  ORDER BY a.[Employee ID], COUNT(*)) AS [3rd] ON Employee.[Employee ID] = [3rd].[Employee ID]

This SQL can then be added to a tablix within SQL Server Reporting Services 2008 R2


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
                           
1

Julia

02
December
You're welcome glad it helped
2

Training

21
August
Testing if this works thanks

MAKE A COMMENT

Name *

Email Address *

Comment *