Graphing "Excel-ready" (i.e. PIVOTED) data in SSRS

Question

We have a collection of long and convoluted SQL procedures that currently output data into Excel ready for Graphs without any modification, here is an example result set:

SELECT 'WE:27/12/2015' AS ID, 63 AS [Brought Forward],  16 AS [New Calls],  18 AS [Closed Calls],   57 AS [Carried Forward] UNION
SELECT 'WE:03/01/2016', 57, 13, 14, 55 UNION
SELECT 'WE:10/01/2016', 55, 39, 31, 55 UNION
SELECT 'WE:17/01/2016', 55, 45, 42, 50

Creating this graph in Excel is literally two-clicks:

We're trying to implement this in SSRS using BIDS & Report Builder, i.e. "software tools commonly used by programmers" (So hoping this question is on topic! i.e. this meta post crosses fingers)

However, when we attempt to create a graph in BIDS, we're presented with the following interface that seems to expect "UnPivoted" data:

Because our data is already ready for Excel (i.e. We already have the calls per week per metric) - we cant see how this will work.

Is SSRS capable of outputting the above graph with the given dataset or are we going to have to "unpivot" our data for SSRS?

We've tried all sorts of combinations & cant seem to get it right, an example of our epic fail:

SSRS is forcing us to put something as a value with aggregate, yet we don't have a value "field" because the data is already pivot'ed?

Answer 1

I played with it but couldn't match your desired output without UNPIVOTing it. :sad face:

I don't see how you would be able to do it without UNPIVOTing it.

SELECT        ID, calls, CallTypes
FROM            (SELECT        'WE:27/12/2015' AS ID, 63 AS [Brought Forward], 16 AS [New Calls], 18 AS [Closed Calls], 57 AS [Carried Forward]
                          UNION
                          SELECT        'WE:03/01/2016', 57, 13, 14, 55
                          UNION
                          SELECT        'WE:10/01/2016', 55, 39, 31, 55
                          UNION
                          SELECT        'WE:17/01/2016', 55, 45, 42, 50) AS t1 
UNPIVOT (calls FOR CallTypes IN ([Brought Forward], [New Calls], [Closed Calls], [Carried Forward])) AS ct