question

JMCalstone avatar image
JMCalstone asked

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][1] [1]: /storage/temp/3885-matrixexample1.png
report-buildersortingmatrix
matrixexample1.png (26.6 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
BigFatBeard avatar image
BigFatBeard answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.