x

Sorting within a matrix column in Report Builder 3.0

Greetings!

I am working on a sales report in Report Builder 3.0 and need some help. I am including the code and the output it gives. The issue I'm having is trying to do a descending sort on the sales for the year 2016. Because the year column header is dynamic in the matrix I'm not sure how to get it to sort by just one of the years.

Thank you in advance for any help you can provide.

 SELECT 
        l.item_no AS Item, 
        SUM(l.sls_amt) AS Sales, 
        YEAR(h.inv_dt) AS Year,
        case
               when l.item_no like '%0805%' then 'Gray/Charcoal'
               when l.item_no like '%0813%' then 'Tan/Brown'
               when l.item_no like '%0815%' then 'Tan/Charcoal'
               when l.item_no like '%0825%' then 'Red/Charcoal'
               when l.item_no like '%0835%' then 'Brown/Charcoal'
               when l.item_no like '%0871%' then 'Cream/Tan'
               when l.item_no like '%0845%' then 'Gray Blend'
               when l.item_no like '%0855%' then 'Tan Blend'
               when l.item_no like '%0865%' then 'Tan/Gray'
               else 'n/a'
        end as Color
 FROM 
        oehdrhst_sql h
        JOIN oelinhst_sql l ON h.ord_type = l.ord_type and h.ord_no=l.ord_no and h.inv_no = l.inv_no
 WHERE 
        YEAR(h.inv_dt) BETWEEN '2012' AND '2016'
        AND l.item_no LIKE 'KS____CM%'
        AND l.item_no NOT LIKE 'KS____CMC'
 GROUP BY 
        l.item_no, YEAR(h.inv_dt)
 ORDER BY 
        l.item_no


alt text

matrixexample1.png (27.2 kB)
more ▼

asked Jan 09 at 07:08 PM in Default

avatar image

JMCalstone
0

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Would something like this work in the group expression?

=iif(Fields!Year.Value ="2016", Fields!Sales.Value,nothing)

essentially you'll be telling ssrs to sort on values only when the year equals 2016

more ▼

answered Jan 24 at 09:29 PM

avatar image

BigFatBeard
11 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x20
x19
x4

asked: Jan 09 at 07:08 PM

Seen: 64 times

Last Updated: Jan 24 at 09:29 PM

Copyright 2017 Redgate Software. Privacy Policy