SSRS report using tablix and grouping

Question

I have been working on SSRS report which has following structure:

"Actual Values" section is a Row Group which is used for aggregation purposes. "Problem" section is Detail group.

Report itself shows Actual and Expected values and if they are different associated value in "Actual Values" section should be colored to red (Value1, Value2, etc).

I have implemented SSRS report using this template, but I am struggling to color affected cells.

For Instance, for first aggregated row in "Actual Values" section - Value3 = 4 is present in "Problem" section as well so I would like to fill Value3 cell under "Actual Values" section using red color. Something like this which obviously won't work:

=IIF(Fields!Value3.Value = Fields!Actual Values.Value, "Tomato", "Transparent")

I have tried to use Lookup, but it works only for first match and covers only first aggregated row:

=IIF(Value3 = Lookup("Value3",Fields!BreakName.Value,Fields!AccountEntityId Values.Value,"DataSet"), "Tomato", "Transparent")

Basically I need to loop through every record in "Problem" section for each row in "Actual Values" section, compare values and change fill color to red if there is a match.

Could you please suggest if there is a way to achieve this?

Answer 1

You basically need the compare the expressions that you are currently using to display the data.

I'm assuming that the 'Problem' section is the detail section (not grouped) and that the 'Actual Values' section on the left is grouped and the group name is myRowGroup.

Next assumption for this answer is that the '4' in the last column has a simple expression of =Fields!myActualValue.Value. If this is a sum or anything else then you need to change the expression below accordingly.

Finally, I've assumed the expression for the '4' in the 3rd column is =SUM(Fields!myActualValue.Value)

Without seeing the design it's hard to give an exact answer but the expression should look something like .

=IIF(Fields!myActualValue.Value = SUM(Fields!myActualValue.Value, "myRowGroup"), "Tomato", Nothing)

This basically says "check the myActualValue in the current context (the detail row) and compare is to the sum of myActualValue within the context of of the current row group.

If this doesn't help, post your report design with the row groups shown and the results of the dataset that is used.