Graph line from multiple fields in Microsoft Report Builder

Question

I am using Microsoft SQL Server Report Builder and am trying to graph a line based on multiple fields within a Dataset. Basically I have a Dataset containing a lot of elements, each of which has a category field, and twenty fields associated with previous year values. I want to be able to group by category, then graph a line for each category that looks up the summed value for each of the twenty years.

So I want the X values for the line to be years, and the Y values to be whatever the summed total is for that category, in that year.

Answer 1

I'm going to assume you have multiple rows for each category with a field for the year, category, and value. If it is a single row which contains all of the values for each year then things will be a bit tougher.

On your line chart you should make the category group the year, make the series group the category, and then make the value the SUM() of the value field.