SSRS Sorting by Year group total

Question

I have a tablix that has Customer as the row group and Month and Year as Column Groups. Sales amount is in the data area. I would like to sort the customers in descending order by the Year total sales.

I tried the following (psuedo code)

SELECT Period (a CONCAT of YEAR(date) and MONTH(Date), SUM(Amount), Company

FROM [tables]

Group by Period and Company
ORDER BY Sum(Amount) Desc

I did it this way thinking that if I sorted in the query it would come through in the order I want, but obviously it's showing the customer with the highest single month sales first, not the highest year.

Thinking more about it, if I want the report to be able to span multiple years, then I have to figure out which Year to total on, but I'd be happy to restrict the report to a single Year (identified by a parameter).

When I try to sort the tablix or customer group on Sum(Fields!Amount.value, "xYear") I get the error that aggregates can include groups.

Answer 1

I switched from Tablix to Matrix and now sorting the Customer Group by SUM(Fields!Amount.Value) works.... kind of.

It sorts by the grand total as opposed to a given year, but I can live with that for now. Maybe I'll add a parameter that defaults to the current year and try to figure out how to use that to enforce the sort. I'm thinking I may have to get the total YTD sales by customer in a separate dataset (that doesn't display in the report).

Answer 2

You could do it two ways.. (not tested... it's midnight here...) assuming you have a parameter to select the sort year and the Period is a date - adjust to suit...

You could sort by an expression something like

=SUM(
     IIF(
         YEAR(Fields!Period.Value) = Parameters!pSortYear.Value,
         Fields!Amount.Value,
         0),
     "myDataSetName")

NOte The dataset name must match your dataset name exactly (case sensitive) and be enclosed in double quotes.

Or.. what I normally do is do it in SQL

SELECT Period, Company, SUM(Amount) AS Amount
    INTO #data
    FROM myTable 
    GROUP BY Period, Company

SELECT d.*, s.SortOrder
    FROM #data d
        JOIN (
              SELECT Company, ROW_NUMBER() OVER(ORDER BY Amount DESC) as SortOrder
                   FROM #data
                   WHERE Period = @pSortYear
             ) s on d.Company = s.Company

Then in your report you can simply sort by SortOrder

This is done off he top of my head so there could be some basic errors but hopefully close enough for you to follow.