1

I want to find the second minimum number in my row. I can complete Min and Max by the inbuilt expression.

More information about the SSRS, it is loaded from SSAS datasource.

In the SSRS design, I have followed Chris steps

1.) Put the custom code in to report properties

2.) Input the expression into two separate columns (setMinMaxReset and setMinMax): =Code.setMinMaxReset(Fields!ID_AverageChangeRevenue_Value.Value) =Code.setMinMax(Fields!ID_AverageChangeRevenue_Value.Value)

SSRS design

3.) The =code.min2 was also input into the column MIN2

same for MAX2

but it turn out the result is incorrect. Thank you for your help

SSRS Review

Platalea Minor
  • 877
  • 2
  • 9
  • 22

1 Answers1

1

You can use custom code in your report to store min2 and max2 in variables (to avoid confusion with multiple get/set functions I declared variables as public)

Public Dim max1 As Integer
Public Dim max2 As Integer
Public Dim min1 As Integer
Public Dim min2 As Integer

Public Function setMinMax(ByVal v As Integer)  As Integer

If max1=0 Then
max1 = v
ElseIf v>max1 Then
max2 = max1
max1 = v
ElseIf v<max1 And v>max2 Then
max2 = v
End If

If min1 = 0 Then
min1 = v
ElseIf v < min1 Then
min2 = min1
min1 = v
Elseif min2=0
min2 = v
ElseIf v<min2
min2 = v
End If

Return v

End Function


Public Function resetMinMax(ByVal s As String) As String 
    max1 = 0
    max2 = 0
    min1 = 0
    min2 = 0

Return s

End Function

For each row group(green color) you will use resetMinMax passing the group string as a parameter. The function will initialize the values for each group row and display the group name

=Code.resetMinMax(Fields!r.Value)

For each value cell (blue color) you will use the setMinMax passing the value as parameter. The function will do the min/max calculations and display the parameter value

=Code.setMinMax(Sum(Fields!v.Value))

For min & max values just call each variable

= Code.max1
= Code.max2
= Code.min1
= Code.min2

enter image description here

enter image description here

niktrs
  • 9,858
  • 1
  • 30
  • 30
  • +1 Good answer, but it needs to calculate min/max within the row rather than down the rows. I've edited to allow it to do that but feel free to roll back if you feel my edits don't reflect your intentions. Also added `>=' in case to the code the max1 and max2 are the same number (duplication of numbers in result set). – Chris Latta Jun 26 '18 at 07:53
  • Chris thank you very much for any corrections you have made to my answer! – niktrs Jun 26 '18 at 08:45
  • Yes, just right click and select expressions. Use the sample expressions, just change the field names to match your own. – niktrs Jun 27 '18 at 03:42
  • The colors is used to highlight where to use each expression and refers to the sample image – niktrs Jun 27 '18 at 05:16
  • @niktrs your code works like a charm, but I think you mistyped the blue cell should be setMinMax and green should be resetMinMax – Platalea Minor Jun 28 '18 at 04:09
  • You are right, where blue I wanted to write green and vice versa. – niktrs Jun 28 '18 at 05:03