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

0 answers: sort voted first
Be the first one to answer this question
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:

x17
x16
x4

asked: Jan 09 at 07:08 PM

Seen: 13 times

Last Updated: Jan 09 at 07:08 PM

Copyright 2016 Redgate Software. Privacy Policy