Posts Tagged SSRS

SSRS Alternating Background Issue

There is a very common issue with SSRS and background expressions that frequently stumps developers. The issue occurs when using a matrix, and has to do with how the RunningValue function works.

As you all know, when it comes to a matrix you can’t use the simple expression below (exmaple 1) to perform alternating backgrounds. This is because a matrix is a pivot of your dataset and thus the columns in the column groups represent rows in the dataset. So when SSRS evaluates background expressions for textboxes in the columns of a column group, those textboxes occur on different rows and thus your each column in the column group has a different alternating background. Experienced developers will recognize this quickly and switch to using the RunningValue function to solve this problem (example 2).

Example 1:

=IIf(RowNumber() Mod 2 = 0, “Alternate Color”, “Color”)

Example2:

=IIf(RunningValue(Field!RowGroup.Value, CountDistinct, “Table Scope”) Mod 2 = 0, “Alternate Color”, “Color”)

Normally this should do the trick, but occasionally an additional issue crops up. Sometimes the background color will span more than the row it should, continuing on into the next row. The root cause of this problem is that RunningValue ignores Nothing (Null) values. Why would we have Nothing (Null) values though? We have Nothing (Null) values when our dataset is a Ragged Matrix (Irregular Matrix) which creates Nothing (Null) values when its pivoted to the SSRS Rigid Matrix (Regular Matrix). A ragged matrix is one where each group does not have the same number of items.

Example Ragged Matrix:

Group A Item 1

Group B Item 1

Group B Item 2

When the Ragged Matrix is pivoted to a Rigid Matrix (a matrix where ever group is expected to have the same number of items), you end up with nulls for the missing items.

Rigid Matrix Example from Pivotted Ragged Matrix:

Group A      Item 1      Null

Group B      Item 1       Item 2

So when SSRS evaluates the background expression for a textbox in the matrix that has a Nothing (Null) value from the pivoting, it evaluates to the same background expression as the previously evaluated textbox. This is because the RunningValue function ignores the Nothing (Null) value. The RunningValue function does not treat the Nothing (Null) value as a change from the previous value it is currently holding. Thus if the previous value was on the previous group (row) then the background expression for the textbox on row 2 is the color of row 1. The solution to all this is a custom report function that does a running value but treats Nothing (Null) values as a change in the value from the previous value. Below is the code to do this along with an example expression of its use.

Example Background Expression Using Custom Code:

=IIf(Code.CountDistinctRunningValue(Fields!RowGroupItem.Value) Mod 2 = 0, “Alternate Color”, “Color”)

NOTE: The below code contains a <> on line 11 that may be pasted as &lt ; &gt ; which will need to be changed back to <> after pasting.

Custom Report Code:

Public Dim intRowNumber As Int32 = 0
Public Dim varPreviousValue As Object

Public Function CountDistinctRunningValue(ByVal o As Object) As Int32

If intRowNumber = 0 Then
intRowNumber = 1
varPreviousValue = o

Return intRowNumber
End If

If (IsNothing(varPreviousValue) And Not IsNothing(o)) _
Or (Not IsNothing(varPreviousValue) And IsNothing(o)) _
Or varPreviousValue <> o Then

intRowNumber = intRowNumber + 1
varPreviousValue = o

Return intRowNumber

End If

Return intRowNumber

End Function

Advertisements

, , , , , , ,

Leave a comment

%d bloggers like this: