Line graph at SSRS 2008 R2

Question

I have created SQL script PIVOT which generate data and I want to create line graph for it.

Year Jan Feb Mar Apr May -Dec
2012 00 00 00 00 50 20 
2013 116 113 182 144 50 435
2014 184 135 00 00 00 00

I am adding line chart with markers from SSRS charts, i am not able to create year as line graph, Y axis will show numbers, X axis will shows months. can somebody guide me in this regards?

thanks

To do this in a chart, realistically the data will need to be unpivoted, not pivoted; then you can just add Year and Month columns as Category Groups and your value column as the Series value. — Ian Preston
I agree with @Preston. Your dataset should be Year, Month, Value. — Ron Smith
@IanPreston thanks for your comment, when i export same data with same columns, i am able to do proper chart at MS Excel 2010, there it shows three series and it generate exact chart what i look for, it shows three lines with qty numbers for each year and at X Axis all months. Unfortunately i m not able to attache the image here..as this data come real time, i am finding it difficult to arrange with SSRS. In Dataset it will be one variable for year, and 12 variable for month, values for each is also available, but i can't sum months to make one variable, in that case it will not generate line — Saifee
@RonSmith query i use part 1 'code' SELECT [ISYear], isnull(Jan,0) as Jan, isnull(Feb,0) as Feb, isnull(Mar,0) as Mar, isnull(Apr,0) as Apr, isnull(May,0) as May, isnull(Jun,0) as Jun, isnull(Jul,0) as Jul, isnull(Aug,0) as Aug, isnull(Sep,0) as Sep, isnull(Oct,0) as Oct, isnull(Nov,0) as Nov, isnull(Dec,0) as Dec From ( SELECT iid_item_code ItemCode, iid_issue_qty as IssuedQty, YEAR(iid_created_date) as [ISYear], LEFT(datename(Month,iid_created_date),3) as [ISSmonth] From issue_inv_detail where iid_item_code = 'GEC0122' — Saifee
@RonSmith query part 2 - and YEAR(iid_created_date) >= '2012' group by iid_item_code, iid_issue_qty,iid_created_date )as INVISS PIVOT ( SUM(IssuedQty) FOR [ISSmonth] IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec) )as PVT — Saifee

Ron Smith Ron Smith · Accepted Answer · 2014-02-25T17:58:41

You should simply group your IssuedQty values by Year and Month like this:

-- load test data
    declare @issue_inv_detail table(iid_item_code int identity(1,1),iid_issue_qty int,iid_created_date datetime)
    while (select count(*) from @issue_inv_detail) < 1000
        begin
            insert into @issue_inv_detail(iid_issue_qty,iid_created_date)
                values
                    (round(rand()*10,0),dateadd(day,round(rand()*765,0),'20120101'))
        end

-- return @issue_inv_detail grouped by Year,Month
    select
        year(iid_created_date) ISYear,
        month(iid_created_date) ISMonth,
        sum(iid_issue_qty) ISQty
    from @issue_inv_detail
    group by
        year(iid_created_date),
        month(iid_created_date)
    order by
        year(iid_created_date),
        month(iid_created_date)

Then set your SSRS Chart values:

Value: ISQty
Category Group: ISMonth
Series Group: ISYear

Line graph at SSRS 2008 R2

1 Answers