Line chart in SSRS

Question

I am trying to create a KPI line chart in SSRS per [Employee] over the time - [Week 25], [Week 24].... [Week 0].

I have a table (dataset) like this (please ignore brackets):

[Employee] [Wk25] [Wk24]...[Wk0]

(John Doe) (0.95)   (0.75) ... (0.80)

(Dow Jones) (1,20)  (0.50) ... (1.10)

(Absalom Absalom) (NULL) (NULL) ... (2.50)

For Excel it is simple, but I gave up building a line chart in SSRS using the dataset as is.

How (if?) should I transform the source table to be able to make a line chart in SSRS? Any ideas will be really appreciated.

Ian Preston Ian Preston · Accepted Answer · 2013-08-29T23:52:11

To chart this data in SSRS you'll really need to unpivot the underlying data; it doesn't handle your type of table very well in charts.

I can't give an extra query without knowing your underlying tables, but it will be something like this:

SELECT Employee, Wk, Value
FROM 
   (SELECT Employee, Wk25, Wk24, Wk0
   FROM MyTable) t
UNPIVOT
   (Value FOR Wk IN 
      (Wk25, Wk24, Wk0)
)AS unpvt

SQL Fiddle with demo.

Which will give a Dataset that looks like this:

EMPLOYEE    WK  VALUE
John Doe    Wk25    0.95
John Doe    Wk24    0.75
John Doe    Wk0     0.8

I haven't added all 26 columns but this should give you the idea.

From here it will be straightforward to chart this in SSRS - just add Value as the Value data and WK as the Category group and you will get the desired results.

Line chart in SSRS

1 Answers