CODEWITHSUNDEEP
reporting-servicesreportbuilder
D4TAM4X
D4TAM4X

Reputation: 44

Get Max value from selected parameters Report Builder

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.

Upvotes: 0

Views: 3094

Answers (2)

Fenix
Fenix

Reputation: 2391

If your parameter values are sorted from highest to lowest, Parameters!Seasons.Value(0) should return the highest (max) selected value. Otherwise, Custom Code has to be used to calculate the highest selected value.

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

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

Go to report properties

enter image description here

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.

Upvotes: 1

Related Questions