Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Excellence can be obtained if you care more than others think is wise, risk more than others think is safe, dream more than others think is practical, expect more than others think is possible
 
Unknown
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

MAKE A COMMENT

Name *

Email Address *

Comment *