Aggregate values and display in chart

Question

I am struggling with this problem and I can't figure out how to solve it.

This is my dataset detail data:

Order  Item    StartDay      EndDay     EndYear  StartEndDiff
 1      1     01.01.2018   02.01.2018    2018         1
 1      2     03.01.2018   05.01.2018    2018         2
 2      1     05.01.2019   06.01.2019    2019         1
 2      2     06.01.2019   06.01.2019    2019         0
 2      3     08.01.2019   10.01.2019    2019         1
 3      1     07.01.2019   08.01.2019    2019         1

I now group them by Order (this works fine in a tablix). Group name is OrderGroup:

Order   MinStartDay   MaxEndDay    EndYear  MinStartMaxEndDiff
 1      01.01.2018    05.01.2018    2018        4
 2      05.01.2019    10.01.2019    2019        5
 3      07.01.2019    08.01.2019    2019        1

For MinStartMaxEndDiff I got the following expression:

=DateDiff("d", Min(Fields!StartDay.Value, "OrderGroup"), Max(Fields!EndDay.Value, "OrderGroup"))

Now I added another group in the tablix and displayed the Avg(MinStartMaxEndDiff) per EndYear. This still works fine in the tablix with all the groupings. The result:

Avg(MinStartMaxEnd)    Year
       4               2018
       3               2019

But I can't figure out how to display only the data from the result, based on the calculations, in a chart. I normally do these calculations on the SQL side, but this time I can't do them SQL side.

The problem is when I try to add the OrderGroup in the chart it always shows me the details. But I need this group to get the right values. Hide or set the category label to Nothing didn't work. Also setting the DataElementOutputto NoOutput didn't help (real look):

I would just need on the x-axis the Years and as values the Avg(MinStartMaxEnd). This would be my desired output based on the sample data from above:

EDIT AFTER A SOLUTION WAS FOUND:

What can I do if a row data looks like this:

Order  Item    StartDay      EndDay     EndYear  StartEndDiff
 1      1     29.12.2018   02.01.2019    2019         4
 1      2     28.12.2018   30.12.2018    2018         2

This one will get divided into two separate datarows in the tablix, because of the EndYear grouping. The expected result should be look like this:

Order   MinStartDay   MaxEndDay    EndYear  MinStartMaxEndDiff
 1      28.12.2018    02.01.2019    2019        5

But a grouping of the year with the expression

=Max(CDate(Fields!EndYear.Value), "OrderGroup")

is not possible.

Answer 1

You can use custom code inside your tablix to calculate the average per year, and call the calculated values in the chart.

Add the following custom code to your report

Public Dim YearAvg As New System.Collections.Generic.Dictionary(Of String , Decimal )

Public Function SetYearAverage( ByVal s As String,  ByVal d AS Decimal) As Decimal
YearAvg.Add(s,d)
Return d

End Function

In the textbox you calculate the year average change the expression to the following (make the appropriate changes to field names)

= Code.SetYearAverage(Cstr(Fields!EndYear.Value), Avg ( DateDiff("d", Min(Fields!StartDay.Value,"OrderId"), Max(Fields!EndDay.Value,"OrderId"))))

Then set your chart expression to

=   Code.YearAvg( Cstr( Fields!EndYear.Value))