CODEWITHSUNDEEP
sqlreporting-servicesssrs-2008
IowaMatt
IowaMatt

Reputation: 57

Pick MAX from multiple fields in SSRS

I have a large SSRS report that I am trying to grab the max values from multiple fields.Right now I have IIF grabbing the MAX value between 2 dates but I want the MAX value from all the fields in the report. here is what I have to grab the MAX between 2 values

=IIF(((IIF(Fields!OnOrder1Day.Value<Fields!Sold1Day.Value, 
Fields!Sold1Day.Value, Fields!OnOrder1Day.Value) + 
IIF(Fields!OnOrder2Day.Value<Fields!Sold2Day.Value, Fields!Sold2Day.Value, 
Fields!OnOrder2Day.Value) + 
IIF(Fields!OnOrder3Day.Value<Fields!Sold3Day.Value, Fields!Sold3Day.Value, 
Fields!OnOrder3Day.Value)) - (Fields!lonhand.Value - 
Fields!OnOrderToday.Value)-Fields!onpo.Value)<0, 0, 
((IIF(Fields!OnOrder1Day.Value<Fields!Sold1Day.Value, Fields!Sold1Day.Value, 
Fields!OnOrder1Day.Value) + 
IIF(Fields!OnOrder2Day.Value<Fields!Sold2Day.Value, Fields!Sold2Day.Value, 
Fields!OnOrder2Day.Value) + 
IIF(Fields!OnOrder3Day.Value<Fields!Sold3Day.Value, Fields!Sold3Day.Value, 
Fields!OnOrder3Day.Value)) - (Fields!lonhand.Value - 
Fields!OnOrderToday.Value)-Fields!onpo.Value))

What I want to do is find the MAX from all of the following fields:

Fields!OnOrder1Day.Value
Fields!Sold1Day.Value
Fields!OnOrder2Day.Value
Fields!Sold2Day.Value
Fields!OnOrder3Day.Value
Fields!Sold3Day.Value
Fields!OnOrder4Day.Value
Fields!Sold4Day.Value
Fields!OnOrder5Day.Value
Fields!Sold5Day.Value
Fields!OnOrder6Day.Value
Fields!Sold6Day.Value

Is it possible to do something like 

MAX(Fields!OnOrder1Day.Value,Fields!Sold1Day.Value,Fields!OnOrder2Day.Value,Fields!Sold2Day.Value,Fields!OnOrder3Day.Value,Fields!Sold3Day.Value,Fields!OnOrder4Day.Value,Fields!Sold4Day.Value,Fields!OnOrder5Day.Value,Fields!Sold5Day.Value,Fields!OnOrder6Day.Value,Fields!Sold6Day.Value)

Upvotes: 0

Views: 3835

Answers (2)

TJS
TJS

Reputation: 81

You can add custom function, something like this

Public Function GetMaxDate(val1 as Date, val2 as Date) as Date

    Return iif(val1 > val2, val1, val2)

End Function

And use it in your expressions

=Code.GetMaxDate(Code.GetMaxDate(Code.GetMaxDate(Code.GetMaxDate(Max(Fields!OnOrder1Day.Value),
                                                                 Max(Fields!Sold1Day.Value)
                                                                ),
                                                 Max(Fields!OnOrder2Day.Value)
                                                ),
                                 Max(Fields!OnOrder2Day.Value)
                                ),
                 Max(Fields!OnOrder3Day.Value)
                )

etc.

Or define any other function arguments as you wish

Upvotes: 1

Lucky
Lucky

Reputation: 4493

If I understand correct you want the highest value of multiple fields in the dataset. Basically, you want the max of the max. It's doable using loads and loads of embedded IIFs, just a pain in the buns.

IIF(
    (MAX(Fields!OnOrder1Day.Value) > MAX(Fields!OnOrder2Day.Value)) AND
    (MAX(Fields!OnOrder1Day.Value) > MAX(Fields!OnOrder3Day.Value)) *ETC*,
    Fields!OnOrder1Day.Value,
    IIF(
         (MAX(Fields!OnOrder2Day.Value) > MAX(Fields!OnOrder1Day.Value)) AND
         (MAX(Fields!OnOrder2Day.Value) > MAX(Fields!OnOrder3Day.Value)) *ETC*,
         MAX(Fields!OnOrder2Day.Value,
         IIF( *ETC*),
    ),
)

Upvotes: 1

Related Questions