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

3
votes

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.

1
reporting-servicesssrs-2008-r2
I can answer the following question. But since I have bellow 50 reputation I have no right to answer. This is frustrating. What can I do to answer questions ? stackoverflow.com/questions/29606814/… – Jahangir Alam
You can change the color of a textfield this way: Right click on the textfield. In the context menu click Textfield Properties. Then goto "Fill" or "Background". Click the Fx Button. There you can add a formula for your background color. To access your custom function use =code.GetCellColor() to access your datasets use Fields!currentValue.Value. By the way, you could also solve this by using the IIF Function instead of the vb script. – Koryu
You should be able to answer a question but not comment. Were you clicking below the question or in the Your Answer section? – Hannover Fist
When a Cell is going to be colored I want to calculate max, min sales from the column values of a row and pass to the function GetCellColor @Koryu – Jahangir Alam
I didn't try using Your Answer section. Just right now I have answered that question. Thanks @Hannover Fist – Jahangir Alam

1 Answers

0
votes

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"))