Sorting and filtering on a line graph doesnt work ssrs

Question

I need to create a line graph which shows top 10 Products which have made the highest sales for the current month and their trends the previous months.

My query for the dataset is something like this :

**select [Year], [Month]  , ProductName, Sales from ABC;**

I inserted an ssrs Line Chart with

Values :  Sum(Sales)

Category Group : Month

Series Groups : ProductName 

I did a right click on the Series Groups and Changed the filtering and the sorting properties as below :

Filter :

Expression : Sales
Operator : Top N
Value = 10 

Sorting :

Sort by : Sales  Order by : Z to A

Logically , I expected to see the top 10 Products which have made the highest sales in the descending order , but I dont see that happenning.

Instead what I see is that the legend for the line graph is sorted in some random order and the first 10 products shown in the legend are only shown as part of the line graph. Can anyone tell me how I can resolve it ?

Let me know I can provide more details if needed.

Answer 1

Most of the time I find it is easier to do the aggregating, filtering, and ordering in SQL and just use SSRS to display the chart. You could change your dataset query to be something like this:

SELECT [MONTH], [YEAR], A.ProductName, Sales
FROM ABC as A
INNER JOIN (
  SELECT TOP 10 ProductName, SUM(Sales) as SalesRank
  FROM ABC
  WHERE [MONTH] = MONTH(GETDATE()) and [YEAR] = YEAR(GETDATE())
  GROUP BY ProductName
  ORDER BY SUM(Sales) DESC
) as B on B.ProductName = A.ProductName
ORDER BY SalesRank DESC, [YEAR], [MONTH]

This will make it easier to get the chart working the way you would expect it to in your report.