SSRS - How do I format SQL data to generate line chart of time series?

I'm sure there are a million more elegant ways to do this but here is how I might approach it...

The following is based on the Microsoft supplied NorthWind database so you can recreate it if you really want to...

I've set the actual start and end date values in here but you can comment out the first few lines and then your SSRS parameters will be applied.

So to start with a took the orders table as it had some dates in and joined to some basic order data just so I could see the results looked OK, most of the columns are not used.

My dataset looks like this...

SET DATEFORMAT YMD

DECLARE @startDate date = '1998/04/01'
DECLARE @endDate date = '1998/07/30'

SELECT 
       e.EmployeeID, e.FirstName, e.LastName, e.Title
       , o.OrderID, o.OrderDate
       , c.CustomerID, c.CompanyName
       , CASE 
          WHEN (OrderDate between @startDate and @endDate ) THEN 'This Year' 
          WHEN (OrderDate between dateadd(YYYY,-1,@startDate) and dateadd(YYYY,-1,@endDate )) THEN 'Last Year' 
          WHEN (OrderDate between dateadd(YYYY,-2,@startDate) and dateadd(YYYY,-2,@endDate )) THEN '2 Years ago' 
         END as YearGroup
       , MONTH(OrderDate) AS OrderMonth
       , Day(OrderDate) AS OrderDay
    FROM Employees e 
       join Orders o on e.EmployeeID = o.EmployeeID
        join Customers c on o.CustomerID = c.CustomerID
    WHERE 
       (OrderDate between @startDate and @endDate )  OR
       (OrderDate between dateadd(YYYY,-1,@startDate) and dateadd(YYYY,-1,@endDate )) OR
       (OrderDate between dateadd(YYYY,-2,@startDate) and dateadd(YYYY,-2,@endDate ))

The Case statement in the SELECT clause checks to see if the dates fall into one of three groups, either

1. between the start and end dates supplied
2. between the same date range but minus a year
3. between the same date range but minus two years

The computed OrderMonth and OrderDay are there as I assume you will want to 'stack' the lines so say, 1 June, across all three groups is in the same horizontal position on the chart. ** See notes later for changing this.

The WHERE clause does similar tests to make sure we only return data from the ranges we need.

All I did them was simply add a line chart and set

• the Series Groups to the [YearGroup] field
• the [OrderMonth] and [OrderDay] to the CategoryGroup
• and (for no other reason than I didn't have much else to display) I used the sum of OrderID as the values.

** if you want to represent the time range as one continuous time, then remove the OrderMonth and OrderDay from the category groups and replace with OrderDate

The resulting chart looked awful but that was just down to the data.