Hey,
I have a matrix that gives me company down the left and year & month along the top. It shows how many orders we have had for each company in each month. Simple so far.
I've then added a column to the end of this which gives the difference between 2018 and 2017. I've done this with
,CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END AS [2018v2017]
in the select statement, with
WHERE (YEAR(Appointments.StartDateTime) = YEAR(getdate()) OR YEAR(Appointments.StartDateTime) = YEAR(getdate())-1)
to make sure we are only looking at this year and last year. This all works fine. However, what's the best way (if possible) to create interactive sorting on this final column, so that we can see either all the customers who have order a lot less this year, or a lot more this year. I've tried it in the query first using
ORDER BY CASE WHEN YEAR(Appointments.StartDateTime) = YEAR(getdate()) THEN 1 ELSE -1 END DESC
but this didn't work, and it's obviously not interactive. So then I tried right clicking the header cell in the table, selecting Text Box Properties > Interactive Sorting > ticked 'Enable interactive sorting on this text box' > selected 'Detail rows' > Sort by: '[2018v2017]. This gives me the sorting option, but when I try to use it nothing changes.
Any help appreciated, thanks!