SSRS Charts Query and Data Series

Question

I'm new to this forum but Id like to ask about Charting in Reporting Services which I am completley new to too.

I want to create a chart data series that is called Months displaying the data April to March. However, the data in the tables and query is set us as follows not having a monthname; returns data fields; Ref, Year, Month1datavalue, Month2datavalue, Month3datavalue.... Month12datavalue.

Query as below:

SELECT     ChartData.Year, ChartData.Month1, ChartData.Month2, ChartData.Month3, ChartData.Month4, ChartData.Month5, ChartData.Month6, ChartData.Month7, 
                      ChartData.Month8, ChartData.Month9, ChartData.Month11, ChartData.Month10, ChartData.Month12, ChartData.SeriesName, ChartData.ColorName, ChartData.Style, 
                      Performance.ReferenceNo, Performance.ShortName, Performance.FullName, Performance.Description, Performance.Active, Performance.Deleted
FROM         ChartData INNER JOIN
                      Performance ON ChartData.PerformanceID = Performance.PerformanceID
WHERE     (ChartData.Year = 2015)

I create the chart but the month1data appears as a separate series to month2data. Ideally I would like the month1data,month2data,month3data fields to be as one series being able to show the month names in the horizontal axis title.

Any ideas for such as novice?

Thanks

Answer 1

Based on your query and your requeriments posted in the question, I recreated your scenario with sample data.

Update: I've updated the query to return the month of the year number. This let you put the name of the months in the x-axis.

Try this:

With pivot_cte as
(
SELECT 
    ChartData.[Year],ChartData.Month1, ChartData.Month2, ChartData.Month3, ChartData.Month4,
    ChartData.Month5, ChartData.Month6, ChartData.Month7, ChartData.Month8, ChartData.Month9,
    ChartData.Month11, ChartData.Month10, ChartData.Month12, ChartData.SeriesName,
    ChartData.ColorName, ChartData.Style, Performance.ReferenceNo, Performance.ShortName,
    Performance.FullName, Performance.Description, Performance.Active, Performance.Deleted
FROM 
    ChartData INNER JOIN Performance ON ChartData.PerformanceID = Performance.PerformanceID
WHERE
    (ChartData.Year = 2015)
)
select
    [Year],
    SeriesName, 
    ColorName,
    Style,
    ReferenceNo,
    ShortName,
    FullName,
    [Description],
    Active,
    Deleted,
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)) [Month], --New line added in UPDATE
    Value
from
    (select * from pivot_cte) p
UNPIVOT
    (Value FOR [Values] in
    (Month1,Month2,Month3,Month4,Month5,Month6,Month7,Month8,Month9,Month10,Month11,Month12)
    ) as unpivot_dt;

This will give you columns as rows and let you create only one data serie. Also it will return a Month column in order to put the name of the month on the x-axis.

Run the updated fiddle with data example only with demostration purposes and note the data structure required for your chart.

With the data exposed in the fiddle I created this chart. with Value field in the Serie and Month in Category Groups.

In the Category Gropus right click the Month field, go to Category Groups properties and put the following expression for the Label.

=Choose(Fields!Month.Value,
"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"
)

This will preview the following chart.

If you want to show only Apr and May filter the dataset, right click on the dataset and go to properties, in the properties go to filter tab, and click add button.

In the expression 1 put this expression:

=CStr(Fields!Month.Value)

For the expression 2 put this one:

=Split("4,5",",")

Note I put 4 for Apr and 5 for May, so if you need to show Oct,Nov and Dec you should put =Split("10,11,12",",")

With the filter the chart will show Apr and May.

Let me know if this could help you.