none
Simple Charting Question (at least I hope so)

    Question

  • I have a dataset generated in SQL Server that already returns tabular data.  It simply list the year and number of units sold each month.  I am having much more difficulty than I anticipated getting the correct line graph.  I have tried multiple variations using the category and group settings in the chart data.  If I take the data to Excel, I can get the result with no issues.  A sample of the dataset being returned from SQL and the chart I am trying to create are shown below.  Any help is greatly appreciated.

    Wednesday, September 25, 2013 3:52 PM

Answers

  • Hi Todd,

    From your screenshot, it seem the month are static column on your database, in this case we need use unpivot function to conver the month column to row. The query looke liek:

    SELECT [Year],[Month],[Sold]
    FROM
    (SELECT [Year],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
      FROM TableName
    ) p
     UNPIVOT
    ([Sold] FOR [Month] IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
     ) AS Unpvt

    Since the month name are string value, then it be sorted as Apr, Aug, Dec, Feb.... We need to create a calculated to get the MonthNumber to sort the tablix and chart (Right the dataset>Add Calculated Field)
    Field Name:MonthtNumber                            
    Fiel Source:=switch(Fields!Month.Value="Jan",1,
    Fields!Month.Value="Feb",2,
    Fields!Month.Value="Mar",3,
    Fields!Month.Value="Apr",4,
    Fields!Month.Value="May",5,
    Fields!Month.Value="Jun",6,
    Fields!Month.Value="Jul",7,
    Fields!Month.Value="Aug",8,
    Fields!Month.Value="Sep",9,
    Fields!Month.Value="Oct",10,
    Fields!Month.Value="Nov",11,
    Fields!Month.Value="Dec",12)

    Then add a chart control to the design surface, drag [Sold] field to Values, drag [Month] to Category Group and drag [Year] to Series Groups.

    The report looks like below.

    If you have any questions, please feel free to ask.

    Regards,


    Charlie Liao
    TechNet Community Support

    Friday, September 27, 2013 8:42 AM