sql server - Dynamic table/output each month for report

Question

I have a table and report I need to create and I'm not sure how to wrap my head around how to make it display in the correct order each month for the output.

Using SQL Server 2012 and SSRS 2016 as the output, I need to create a rolling report that displays the last 12 months with their corresponding values. Each month the previous 12th month will drop off.

What's the best table design to approach something like this and how do you control the output to drop off the previous 12th month and keep it rolling?

Sample of desired output would be something like below but next month I need to drop off Dec - 15 and add Jan - 16 but have the columns sorted in a descending order so the previous month is always the last month in the report.

-- Desc     |   DEC - 15 |  Jan - 16 | Feb - 16 | restofmonths| Nov 16 | Dec 16|
********************************************************************************
-- Loss     |   1,000   |   2500    | 1700      | 123         |  4565  |  3433 | 
-- Expense  |   2,000   |   3200    | 900       | 456         |  1223  |  4445 |
-- Reserve  |   3,000   |   3300    | 400       | 789         |  4747  |  4444 |

Answer 1

You need to use a matrix.

In your dataset, add 2 columns (if not already present). The first is the column header and the second is the column sort order. Something like this

    [Header] = LEFT(DATENAME(MONTH, DateValue), 3) + ' - ' + RIGHT(YearNum, 2)
,   [HeaderSort] = CONVERT(VARCHAR, YEAR(DateValue)) + RIGHT('0' + CONVERT(VARCHAR, DATEPART(MONTH, DateValue)), 2)

Set the matrix column group to your Header value and set the sort order to your HeaderSort value.