I want to group ( display ) a set of row(s) based on a calculation on one of row's column data.
Lets say I have 3 columns in my tablix. 1. Description 2. Amount 3. Debit/Credit
based on the D/C value I would like to sum up the amount ( for debit its -ve and credit is +ve) until the total gets to zero and then group those rows into a different color or a line space between other set of rows.
Example output:
Thanks
Karthik
I've recreated your scenario using the dataset you have provided in the question.
I am using the Amount cell background-color
property to group the sums.
This is the tablix I've created. The selected Cell background-property is set to a expression (see below):
In Report
menu, Report Properties...
/ Code
tab put this function in the text area.
Dim prevColor As String = "Red"
Dim accumulator As Double = 0
Public Function GetSumColor(ByVal value as Double) as String
Dim color As String
accumulator = accumulator + value
color = prevColor
If accumulator = 0 Then
If prevColor = "Red" Then
prevColor = "Yellow"
Else
prevColor = "Red"
End If
End If
Return color
End Function
This function will change the cell background color between Red
or Yellow
based on sums equals to zero (you can use the whatever color you want).
In the Amount cell background-color property use this expression:
=Code.GetSumColor(
IIF(Fields!D_C.Value="C",-Fields!Amount.Value,Fields!Amount.Value)
)
It will produce the following result:
Let me know if this helps you.