0

I am new to SSRS. I have a requirement to display top 3 products based on sales for each country where country is a column grouping, in a tablix.So the report should look like this ( the countries should appear as columns with products and total sales underneath.Unfortunately I am unable to type the below as expected)

UK

Prod1 100
Prod3 70
Prod4 50

Spain

Prod2 80
Prod3 55
Prod4 30

Italy

Prod3 45
Prod1 20
Prod4 15

Top N filter applied at the column group does not work as it seems to be ignoring it completely. Top N cannot be applied at the Table level as it will only display Top 3 across all countries. The data is sourced from a cube.

Any help is much appreciated

Sam K
  • 1
  • 3
  • Possible duplicate of [How to get total of top 10 sales in SSRS 2012](http://stackoverflow.com/questions/17275232/how-to-get-total-of-top-10-sales-in-ssrs-2012) – Chris Latta Feb 11 '16 at 00:30
  • you mean your report structure is look like UK SPAIN ITALY ..... like this? – bmsqldev Feb 11 '16 at 05:12

1 Answers1

0

This is something you should solve in your select query and that shouldn't be too hard.

I don't know your database layout so I'll try and explain it with a simple example:

Sales Table

  • SalesId
  • CustomerId
  • Date
  • Total

SalesDetails table

  • SalesDetailId
  • SalesId
  • Product
  • Quantity

CustomersTable

  • CustomerId
  • Country
  • Name
SELECT DISTINCT(c.Country), q.Product, q.Quantity
FROM Customers c
JOIN (SELECT c1.Country, d1.Product, SUM(d1.Quantity) as Quantity
    FROM SalesDetails d1
    JOIN Sales s1 ON s1.SalesId = d1.SalesId
    JOIN Customer c1 ON c1.CustomerId = s1.CustomerId
    GROUP BY d1.Product, c1.Country) q ON q.Country = c.Country
WHERE q.Product IN (SELECT TOP 3 d2.Product
    FROM SalesDetails d2
    JOIN Sales s2 ON s2.SalesId = d2.SalesId
    JOIN Customer c2 ON c2.CustomerId = s2.CustomerId
    WHERE c2.Country = c.Contry
    GROUP BY d2.Product, c2.Country
    ORDER BY SUM(d2.Quantity) DESC)
ORDER BY c.Country

I hope this is of use to you, if you share your actual database layout I'm willing to update my example based on yours.

Oceans
  • 3,445
  • 2
  • 17
  • 38