CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
Jahangir Alam
Jahangir Alam

Reputation: 801

how to get max of column values in a row in ssrs matrix report

I have created a SSRS report which shows Hours as column names and dates as row names. Cell values represents the Sales of a specific hour of a specific date.

the table in my report is as follows:

Date |Hour 1|Hour 2|Hour 3|max sales|min sales

4/10/2015| 5 | 10 | 15 | 15 | 5 4/11/2015| 30 | 10 | 20 | 30 | 10

I want Green color background in cell with max sales and Red color background in cell with min sales. the required output will be as follows:

Date |Hour 1 |Hour 2 |Hour 3 |max sales|min sales

4/10/2015|5(Red) | 10 |15(Green)| 15 | 5 4/11/2015|30(Green) |10(Red)| 20 | 30 | 10

I have written a custom code for GetColor as follows:

Function GetCellColor(ByVal minValue As Integer,ByVal maxValue As Integer, ByVal currentValue As Integer) As String
    If currentValue = maxValue Then
        return "Green"
    Else If currentValue = minValue Then
        return "Red"
	Else
		return "WhiteSmoke"
    End If
End Function

which returns color based on cell value. I can not pass the maxValue, minValue of a row.

Thanks in advance.

Upvotes: 3

Views: 4785

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10870

You should be able to accomplish this the way you are doing it but you'll need to use the same field or expression that you use to populate your Min and Max columns in your Code call. If you use an expression to calculate it, you would use the same formula.

=Code.GetCellColor(Fields!Max_Sales.value, Fields!Min_Sales.value, Fields!YourField.value)

As Koryu mentioned, you can accomplish this without using code by putting the logic in the Expression for the background color property:

=IIf(Fields!YourField.value = Fields!Max_Sales.value, "Green",
 IIf(Fields!YourField.value = Fields!Min_Sales.value, "Red", 
  "WhiteSmoke"))

Upvotes: 0

Related Questions