SSRS Expression works as cell value expression, but not as background color value expression

Question

I have an SSRS report with a matrix in it, where I needed to display the Growth Percentage in a column group compared to the previous column value. I managed this by using custom code...

DIM PreviousColValue AS Decimal
Dim RowName AS String = ""

Public Function  GetPreviousColValue(byval Val as Decimal, byval rwName as string)  as Decimal

DIM Local_PreviousColValue AS Decimal

IF RowName <> rwName THEN
    RowName  = rwName 
    PreviousColValue  = val
    Local_PreviousColValue  = 0
ELSE
    Local_PreviousColValue =  (Val - PreviousColValue)/PreviousColValue
    PreviousColValue  = val
END IF

Return Local_PreviousColValue 
End Function

..and then using this as the value expression in the cell.. =Round(Code.GetPreviousColValue(ReportItems!Textbox8.Value,Fields!BusinessUnit.Value)*100,0,system.MidpointRounding.AwayFromZero)

So far so good, this produces the expected value. Now I need to use this expression in a background color expression to get a red/yellow/green but in that capacity it fails.

The background color expression looks like this: =IIF(ROUND(Code.GetPreviousColValue(ReportItems!Textbox9.Value,Fields!Salesperson.Value)*100,0,System.MidpointRounding.AwayFromZero)<=-5,"Red" ,IIF(ROUND(Code.GetPreviousColValue(ReportItems!Textbox9.Value,Fields!Salesperson.Value)*100,0,System.MidpointRounding.AwayFromZero) >=5,"Green" ,"Yellow"))

When I run the report the background color expression only ever returns yellow. As a test I pasted the background color expression in as the cell value and ran it again. Results in the image below

I get no build or run time errors so I'm not sure why this does not work.

Have you tried using the built-in Previous function? Also, you'd be better off referring to dataset fields in your expression rather than individual textboxes. — StevenWhite
Re: trying the built-in Previous function: This might be a case of internet searching returns stale info.. But when looking for a solution I kept running into statements that the "Previous function is not supported when you have row and column groupings" See Social.msdn Link here. — Voysinmyhead
@StevenWhite Re using textbox reference vs dataset field.. you're right of course but the value i'm getting by textbox value ref is itself a "IIF(Sum(),0)".. type expression, and the whole thing was just getting hard to read/follow — Voysinmyhead

Voysinmyhead Voysinmyhead · Accepted Answer · 2016-02-22T17:15:32

After some more searching I found a better Custom Code solution than what I was using to get the Growth Percentage in a column group compared to the previous column value. Besides being simpler to read this version has an added benefit: You can dynamically hide the growth percentage column for your first instance of the column group (because it will always be zero or null) and still get the right values in the 2nd/3rd/4th instance of the column group.

Public Function GetDeltaPercentage(ByVal PreviousValue, ByVal CurrentValue) As Object
If IsNothing(PreviousValue) OR IsNothing(CurrentValue) Then
    Return Nothing
Else if PreviousValue = 0 OR CurrentValue = 0 Then
    Return Nothing
Else 
    Return (CurrentValue - PreviousValue) /  PreviousValue
End If
End Function

The new function is called like so =Code.GetDeltaPercentage(Previous(Sum(<expression or dataset field>),"Group ByColumn"), Sum(<expression or dataset field>))

Re: the original question - why does my cell value expression not work when used as the background color expression - I took an easy out and just referenced the cell value.

=IIF(ROUND(Me.Value*100,0,System.MidpointRounding.AwayFromZero)<=-5,"Red"
,IIF(ROUND(Me.Value*100,0,System.MidpointRounding.AwayFromZero) >=5,"Green"
,"Yellow"))

SSRS Expression works as cell value expression, but not as background color value expression

1 Answers