SSRS Combine two value based on two dates on one X axis LABEL

Question

Please can any one can help me? This is my question:

In my report I have:

Chart 1: in the axis I have 2012, 2013, 2014, 2015, 2016, 2017, 2018

The expression value is:

=Sum(fields!ao_amountparticipationValue.Value)*100/Sum(Fields!ao_amountparticipationValue.Value, "Chart1_CategoryGroup")

Now my problem is; in Chart 2 I want to have value WITH scrolling 2 years like this:

In one X label i want to have this:

2012-2013, 2013-2014, 2014-2015, 2015-2016, 2016-2017, 2017-2018

And the value should be the average of two years.

Thank you

Can you return the begin year and end year in your data and group by both columns in chart 2? — Ross Bush

niktrs niktrs · Accepted Answer · 2018-04-25T12:52:34

You can use custom code to create the average of each couple of years

Dim oldvalue As decimal

Public Function GetAverage(v AS decimal) As decimal

Dim avgvalue As decimal

If  oldvalue = Nothing Then
     avgvalue = 0
Else
    avgvalue = (v + oldvalue)/2
End If

oldvalue = v

Return avgvalue

End Function

Your value expression should call the custom code with an expression

=Code.GetAverage( SUM(Fields!val.Value))

For the label of years couple you can use an expression like below

= Iif(Fields!year.Value = 2012,Nothing, Cstr(Fields!year.Value-1) + "-" + Cstr(Fields!year.Value))

Notice that because there is no average for year 2012 you should check if it matches the first year and set label to nothing

SSRS Combine two value based on two dates on one X axis LABEL

2 Answers