0

I have created a report, where users can select the company from parameter grid and it will compare the result with the max, min, 2nd max and 2nd min(the values generated by custom code)

If match, it will return a weighted score and I have created a subtotal for the every row and I would like to use the color expression to represent the subtotal on the leftest cell.(If Subtotal is larger than 70, the cell will turn red, below 49 turn green, in between 50 to 70 turn yellow).

For the report, the column company and searchcompany is dynamic, the rows are static

Report Design

enter image description here

Report Review

enter image description here

However, when I implement the expression, my custom code (All max min functions) doesn't work properly.

Report Design View(Putting all in the same row)

enter image description here

Report Review(Putting all in the same row)

enter image description here

I have figured out a solution by implementing the color expression under the the subtotal column.

The temporary solution

enter image description here

I am just wondering is there any solution for me to put everything in the same column like this?

Expected Result

enter image description here

For the custom code, it is below

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

the reset function(I have not shown in the screen caps)

=Code.resetMinMax(Fields!r.Value)

the minmax function(I have input in the company column)

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

the min and max function 

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

The custom code is provided by niktrs for more details

SSRS 2016 find 2nd minimum/ 2nd maximum

Thank you in advance

Naresh
  • 16,698
  • 6
  • 112
  • 113
Platalea Minor
  • 877
  • 2
  • 9
  • 22
  • Which are the expressions you are using for the subtotal row and the score column? I am unable to understand how they are calculated – niktrs Jul 05 '18 at 07:29
  • The score column is calculated by using the 1,2,3 times a weight which different row has a different weight. For the subtotal expression, it is calculated by adding all the score textbox reportitem value. – Platalea Minor Jul 05 '18 at 08:30
  • I emulated your problem by creating a row and tested color by using a static and a dynamic expression, both work fine. The image cannot help us understand the expression used in each cell – niktrs Jul 05 '18 at 08:34
  • I see, perhaps is my problem. thanks anyway – Platalea Minor Jul 05 '18 at 08:57
  • Sorry, but it's hard for anyone to help without knowing the expressions. Don't forget things are already complicate since custom code is used for calculations – niktrs Jul 05 '18 at 09:02

0 Answers0