SSRS - sorting by a single tablix column result in a column group

Question

I'm using SSRS 2016. I have a dataset that looks like the following:

Year    Month   Supplier    Category    Type    Item    Sales   QTY
2010    02      Supplier1   Category2   Type1   Item1   200     10
2010    02      Supplier2   Category2   Type1   Item3   150     15
2011    02      Supplier1   Category1   Type1   Item4   250     10
2011    02      Supplier2   Category2   Type1   Item3   400     20

My tablix that I created in SSRS look as follows:

                [YEAR]
[Supplier]      sum(Sales)
    [Category]  sum(sales)
        [Item]  sum(sales)
Total           sum(sales)

The result looks like this:

                2010        2011
Supplier1       200         250
Supplier2       150         400

I need to sort the data according to the 2011 column desc for Supplier, then Category, then Item. When I use the tablix properties Sort or the Group Properties sort on SSRS, it sorts via the total of 2010+2011 and not just the values in 2011.

EDIT: The table contains sales for multiple years/suppliers/categories/items. the user limits it to the year/month they need and it provides a comparison with the year before. So if the month is 2 and year 2011, it will provide 2011-02 and 2010-02

Is there anything that I am missing?

Alan Schofield Alan Schofield · Accepted Answer · 2020-03-17T13:41:14

As you appear to be using a matrix (Column Group on Year) then you will need to use an expression to determine the sort order.

In your rowgroup, go to the sort options and use an expression something like ...

=SUM(IIF(Fields!Year.Value= 2011 , Fields!Sales.Value, nothing))

If you always want to sort by a dynamic year then you will have to use something like this (this example always uses the last year to sort by)

=SUM(IIF(Fields!Year.Value=MAX(Fields!Year.Value), Fields!Sales.Value, nothing))

SSRS - sorting by a single tablix column result in a column group

1 Answers