Simple Line Graph in SSRS

Question

I'm having issues using the line chart control in SSRS. If I was in excel this would be easy but for some reason I can't wrap my head around how to do it in SSRS

I'm returning 5 rows of data from a database and need to chart 3 of the rows.

    Name             J  F   M   A   M   J   J   A   S
1   Requested        13 19  4   20  2   0   0   0   0   
2   Completed        1  0   0   4   1   0   0   0   0   
3   % Completed      .7 0   0   0.2 0.5 0   0   0   0
4   Monthly Ba       12 19  4   16  1   0   0   0   0   
5   YTD Backlog      12 31  35  51  52  52  52  52  52

The rows consist of the name and the value for that month.

I'm trying to display a chart for rows 1, 2, and 5 that would look like a normal line graph with each of the Months being on the horizontal column and the value being the data point.

However, when I start adding things to my chart I'm getting 36 different series, one for each Month for each series and nothing seems to be working right. It's also splitting into Group 1 and 2 at the bottom which makes no sense to me. I feel like I'm missing something simple, most likely a grouping of some kind.

EDIT:

I ended up taking Nathan's suggestion and added an unpivot on the table to rotate the data into an acceptable format for SSRS

Thanks

Assuming your data is coming from a SQLServer query, can you add the query to the question? — user359040
So the requests are held in Table.Value - are the Completed and YTD backlog fields held on the same table? If so, in what fields? — user359040
The Table columns are Value1, Value2, Value2 SubmittedMonth, Submitted Year. The Table above is a Union of various different things to get that format. — Jabsy

Nathan Griffiths Nathan Griffiths · Accepted Answer · 2012-05-04T11:09:15

Can you alter the query that is returning the results for the dataset? If so try changing it so that the dataset looks more like this:

monthnumber month Requested Completed YTD Backlog
1           J     13        1         12
2           F     19        0         31
3           M     4         0         35
4           A     20        4         51

etc.

You should be able to add "Requested", "Completed" and "YTD Backlog" as Values and "month" as a Category to produce the chart you want.

I added a "monthnumber" column, as you will probably want something like this so that you can set the category (month) sorting expression to this, otherwise they will appear in alphabetical order.

Simple Line Graph in SSRS

1 Answers