SSRS line chart, 2 series, many data points, 1 category

I am trying to create one SSRS report with SQL Server database at backend.

The chart is having Result Value on Y-axis and Result Date on X axis.

The result date needs to be shown like below, Day (1,2,3,4... etc) need not be continuous...

Sept, 2015 Oct, 2015 Nov, 2015

1 2 3 4 5 .... 1 2 3 4 5.... 1 2 3 4 5...

The report shows 2 series,

1. First test result value of Test point on a day. (single value per day)
2. Retest values for test point on a day (Multiple values per day)

Here's what I have done so far

1. inserted one table put up row grouping to test point name, so as to get a chart per Test Point
2. insert chart control in a cell, set up chart type as line chart.
3. Add 2 category groups. First group by Month (label as Monthname and Year) of Result Date and second group by Day (label as Day) of result date
4. Put up series value as First(Fields!ResultValue.Value) to get the first measurement for that day.

Everything looks okay till now, but now I am not able to plot the 2nd series i.e. retest values which are multiple values on same day

I have tried following

1. create series group on IsRetest field to have retest data as separate series but this returns only first value because of existing category group.
2. I tried creating separate dataset but when I try using LookUpSet method to return all retest result values for particular testpoint it doesn't render anything on the chart, although just LookUp method works well but again it return only the first value.

Any help or guidance in this query would be much appreciated. Thanks in advance.

Regards,
Sujay C

Try to add a line chart to your report and set up Category Groups as below :
- First Category Group: Group by Month(Fields!ResultDate.Value) and label it as MonthName(Fields!ResultDate.Value) & ", " & Year(Fields!ResultDate.Value)
- Second Category Group: Group by Day(Fields!ResultDate.Value)
Then set up Series Groups, you need to add a series group by Fields!IsRetest.Value to separate the first test and retest values.

Set the series value to First(Fields!ResultValue.Value) and use a filter on the series to include only where Fields!IsRetest.Value = False.
Second Series (Retest), set the series value to Fields!ResultValue.Value and use a filter on the series to include only where Fields!IsRetest.Value = True.

To handle multiple retest values per day, you need to ensure that the chart can plot multiple points for the same category (day).
Write a custom code in SSRS to aggregate or handle multiple values. Go to Report Properties -> Code and add a custom function to handle multiple values.

Public Function GetRetestValues(ByVal values As Object()) As String
    Dim retestValues As String = ""
    For Each value As Object In values
        retestValues &= value.ToString() & ", "
    Next
    Return retestValues
End Function

If you need to fetch multiple retest values from a different dataset, use LookupSet and then aggregate them using the custom function.

=Code.GetRetestValues(LookupSet(Fields!ResultDate.Value, Fields!ResultDate.Value, Fields!ResultValue.Value, "RetestDataset"))

The X-axis labels need to be formatted correctly to show the date as Day within each Month, Year.
Don't forget to adjust the chart properties to ensure clarity, such as setting the interval for the X-axis labels.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi1y ago
Accepted answer
Try to add a line chart to your report and set up Category Groups as below :
- First Category Group: Group by Month(Fields!ResultDate.Value) and label it as MonthName(Fields!ResultDate.Value) & ", " & Year(Fields!ResultDate.Value)
- Second Category Group: Group by Day(Fields!ResultDate.Value)
Then set up Series Groups, you need to add a series group by Fields!IsRetest.Value to separate the first test and retest values.

Set the series value to First(Fields!ResultValue.Value) and use a filter on the series to include only where Fields!IsRetest.Value = False.
Second Series (Retest), set the series value to Fields!ResultValue.Value and use a filter on the series to include only where Fields!IsRetest.Value = True.

To handle multiple retest values per day, you need to ensure that the chart can plot multiple points for the same category (day).
Write a custom code in SSRS to aggregate or handle multiple values. Go to Report Properties -> Code and add a custom function to handle multiple values.
```
Public Function GetRetestValues(ByVal values As Object()) As String
    Dim retestValues As String = ""
    For Each value As Object In values
        retestValues &= value.ToString() & ", "
    Next
    Return retestValues
End Function
```
If you need to fetch multiple retest values from a different dataset, use LookupSet and then aggregate them using the custom function.
```
=Code.GetRetestValues(LookupSet(Fields!ResultDate.Value, Fields!ResultDate.Value, Fields!ResultValue.Value, "RetestDataset"))
```
The X-axis labels need to be formatted correctly to show the date as Day within each Month, Year.
Don't forget to adjust the chart properties to ensure clarity, such as setting the interval for the X-axis labels.