Get Max value from selected parameters Report Builder

Question

I have a report that has a year parameter where you can select multiple years at a time. for the subtotal row I need it to sum up a total for all the years selected in the parameter except for the Max year they selected. I tried the following 2 examples but they do not work.

First example I tried using the field which the parameter is based on \

=Sum(iif(Fields!YEAR.Value < Max(Fields!YEAR.Value), Fields!Year_Sold.Value, Nothing))

The second example I tried using the actual max parameter value

=Sum(iif(Fields!YEAR.Value < Max(Parameters!BLDYEAR.Value), Fields!Year_Sold.Value, Nothing))

so lets say when you run the report and select the bldyear parameter in the drop down it will have the years 2010,2011,2012,2013,2014,2015. Now lets say you select the years 2010 - 2013 I want to subtotal for all the years except 2013.

Answer 1

If your parameter is Integer type you can use the following approach.

Go to report properties

In Code tab / Custom Code put the following code:

Public Function GetMax(ByVal parameter as Parameter) as Integer
   Dim max as Integer
   max = 0
      For i as integer = 0 to parameter.Count-1
    If max < parameter.Value(i) Then
        max = parameter.Value(i)
    End If
      Next
   Return max
End Function

Then in your expression call the function passing your parameter.

=Sum(iif(Fields!YEAR.Value < Code.GetMax(Parameters!BLDYEAR),
Fields!Year_Sold.Value, 0))

Try it yourself and let me know if this could help you.