Switching the series and category in an SSRS line chart without a PIVOT

Question

I have a table of data in Excel, which shows percentage changes over a 4-year period and produces a straight-forward line chart with minimal fuss.

However, reproducing this in SSRS is proving more of a problem.

I have re-created the table as a dataset in SSRS 2016, which involves some SUM aggregations and a few UNIONs to get the table. However, I only seem to be able to produce the following line-chart:

SSRS Line Chart

I need the series to show the 3 Departments and the x-axis to show the years 2014, 2015, 2016 and 2017.

Is there a way to do this within SSRS, withough having to PIVOT the data in the dataset? No amount of switching things around in the chart properties seems to do the trick.

Many thanks PG

Alan Schofield Alan Schofield · Accepted Answer · 2018-02-19T18:05:56

If your dataset looks like the table at the top of your screenshot then that's going to make life difficult. You said you did some unions and aggregations to achieve this so you're probably doing too much at this point.

What you need to pass to SSRS is a simple list of the values. I've recreated (more or less) your dataset but as a simple normalised table. You can reproduce this step-by-step like this.

Create a new report.
Add a new datasource (point it to anything we won't be getting real data)
Add a new data set and use the following query to emulate your data

..

DECLARE @t TABLE(Dept varchar(10), Yr int, Amount int)

INSERT INTO @t VALUES
('Dept. A', 2014, 42),
('Dept. A', 2015, 49),
('Dept. A', 2016, 40),
('Dept. A', 2017, 47),
('Dept. B', 2014, 33),
('Dept. B', 2015, 32),
('Dept. B', 2016, 33),
('Dept. B', 2017, 34),
('Dept. C', 2014, 30),
('Dept. C', 2015, 32),
('Dept. C', 2016, 34),
('Dept. C', 2017, 36)

SELECT * FROM @t

The dataset will return a simple table as three columns, Dept; Yr; Amount

Insert a new line chart on your report
Click the chart area so you see the Chart Data dialogue
Drag the Amount field to `Values'
Drag the Yr field to Category Groups
Drag the Dept to Series Groups

That's it, done! The chart will look like this (I thickened the lines and added markers for clarify)

In my example, I also added a matrix to replicate the table you had in your screenshot. If you need to do this then...

Insert a matrix onto your report
Drag Dept onto Rows
Drag Yr onto Columns
Drag Amount onto Data

The final design looks like this.

The final output looks like this.

Note that this process will work even if you have lots of data in your dataset for each dept and year, SSRS will aggregate this for you so often you will not need to do simple aggregations in your dataset (although it can perform better when you do)

Switching the series and category in an SSRS line chart without a PIVOT

1 Answers