SSRS 2016 - Line Graph will not chart multiple series

Question

Using SSRS 2016 and SQL 2017 Report Builder

I have a dataset like the following. It has event dates and attendance:

    Event_Date  Attendance
    1/1/2016    1
    2/1/2016    2
    3/1/2016    3
    4/1/2016    4
    5/1/2016    5
    6/1/2016    6
    7/1/2016    7
    8/1/2016    8
    9/1/2016    9
    10/1/2016   10
    11/1/2016   11
    12/1/2016   12
    1/1/2017    5
    2/1/2017    6
    3/1/2017    7
    4/1/2017    8
    5/1/2017    9
    6/1/2017    10
    7/1/2017    11
    8/1/2017    12
    9/1/2017    13
    10/1/2017   14
    11/1/2017   15
    12/1/2017   16

I am attempting to create a line chart in an SSRS report where there is a datapoint (Attendance) for each element in the series. I am specifying two series -- Series 1 is the attendance for 2016 and Series 2 is the attendance for 2017. Each series will be on a separate line in the chart. The purpose is to visually compare attendance in 2016 vs 2017.

The date periods are determined dynamically by report parameters when the report is run for one series and by variables calculated from the report parameters for the other series.

In the line chart I have defined:

• Values are Attendance
• Category Groups are Event_Date (with a filter to select events in 2017)
• Series Groups are Event_Date (with a filter to select events in 2016)

PROBLEM

When I run the report, the graph renders but has no data (lines).

If I remove the Series Group (events in 2016), the graph renders correctly with only the data line for 2017.

I've been working on this for quite a while and I am stalled trying to figure out what is going on.

Does anyone have any ideas as to why both series are not being graphed and how to fix it?

Many Thanks!

Answer 1

Thanks so much for the help. It pushed me in the right direction so that I could make things work for the situation.

I made some changes to the query so that I could align all the data on top of each other by a yearly event number.

Here is the query:

    select
        ROW_NUMBER() over (PARTITION BY year([dbo].[#temp_table_4].[event_start_date]) ORDER BY [dbo].[#temp_table_4].[event_start_date]) as yearly_event_number
        ,format ([dbo].[#temp_table_4].[event_start_date],'d') as event_start_date
        ,format([dbo].[#temp_table_4].[event_start_date],'MM/dd') as MonthDay
        ,year([dbo].[#temp_table_4].[event_start_date]) as Year
        , Count (*) as [Total Attended]
    from 
        [dbo].[#temp_table_4]

    Group by [event_start_date]

    order by 
            [dbo].[#temp_table_4].[event_start_date]

The main change was that I needed to number each event that happened each year beginning with 1 and ending with 'n' (the last event of the year). I needed to do that because the events don't happen on the same date each year (they actually happen each Wednesday which is not the same date each year).

To account for this, I added the following SQL clause:

ROW_NUMBER() over (PARTITION BY year([dbo].[#temp_table_4].[event_start_date]) ORDER BY [dbo].[#temp_table_4].[event_start_date]) as yearly_event_number

In the chart, I assigned Total_Attendance to Values, yearly_event_number to Category Groups and Year to Series Groups. I also added a tool tip that showed the event date so that you can hover over any point and show the event date.

Answer 2

Note: The solution is simple, don't be put off by the length of the reply ! :)

You'll need to make a few simple changes to your dataset query..

Here's a sample query I used to replicate you data.

DECLARE @t TABLE (Event_Date date, Attendance int)

INSERT INTO @t
VALUES 
('1/1/2016', 1),
('2/1/2016', 2),
    ('3/1/2016', 3),
    ('4/1/2016', 4),
    ('5/1/2016', 5),
    ('6/1/2016', 6),
    ('7/1/2016', 7),
    ('8/1/2016', 8),
    ('9/1/2016', 9),
    ('10/1/2016', 10),
    ('11/1/2016', 11),
    ('12/1/2016', 12),
    ('1/1/2017', 5),
    ('2/1/2017', 6),
    ('3/1/2017', 7),
    ('4/1/2017', 8),
    ('5/1/2017', 9),
    ('6/1/2017', 10),
    ('7/1/2017', 11),
    ('8/1/2017', 12),
    ('9/1/2017', 13),
    ('10/1/2017', 14),
    ('11/1/2017', 15),
    ('12/1/2017', 16)

select Event_Date, format(Event_Date, 'dd/MM') as DayMonth,  year(Event_date) as Yr, Attendance from @t

Notice in the final select (the bit you actually need) I've added a year column Yr and a day/month column DayMonth. With your dataset as it was, even if you split by year the x axis would still represent the date so the you two lines would have been next to each other rather than one on top of the other which is what I expect you want....

The result of the dataset gives us something like...

Event_Date   DayMonth   Yr     Attendance
2016-01-01   01/01      2016   1
2016-02-01   02/01      2016   2
.....
.....
2017-01-01   01/01      2017   5
.....
.....

And so on..

Now, add a new line chart and drop Attendance into Values; DayMonth into Category Groups and Yr into Series Groups.

Your chart design should look something like this...

That's it!

When we run the report we get this...