Crystal Reports & SQL Reporting Services Training Consultants

Account Log in:

Username:

Password:

*Free advice generally costs more than any other kind*

Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers

Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog

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