CODEWITHSUNDEEP
reporting-servicesssrs-2016
PhineasGage
PhineasGage

Reputation: 3

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

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

Upvotes: 0

Views: 493

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

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)

enter image description here

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. enter image description here

The final output looks like this.

enter image description here

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)

Upvotes: 1

Related Questions