Lately I have seen the same question for couple of times in newsgroups and during my courses. The question is how to conditionally format the properties of text boxes in group lines; for example, let’s say we need alternating background color. It is quite simple to achieve this in detail lines, there is an example how to do it in Reporting Services Books OnLine and in MSDN Library in the “Adding Conditional formatting”. Here is an expansion of the mentioned topic that shows how to apply conditional formatting on group lines.Like in the simple Books OnLine example, we need an expression that evaluates to true or false, and then use the IIF function to decide which color to use for the true and which for the false result. As 1/0 works fine for true/false results, the Modulo operator that computes the remainder of the division between two operands can be used. For the second operand of the Modulo operator we can use value 2, and the possible results are 1 and 0. For the first numeric value, we can use group sequential number. We can get it if we count distinct number of groups. But as we need it for every group, not for the grand total only, we have to do a running aggregate. So, we have to use four functions: IIF and Mod VB.NET functions and Reporting Services aggregate functions CountDistinct and RuningValue.
For example, imagine we have a single grouping over the Country field, and we want to alternate the background color property between red and blue. In the BackgroundColor property of the textbox (or complete group line) we would use this expression:
=IIF(RunningValue( Fields!Country.Value , CountDistinct, Nothing) Mod 2 = 1, “Red”, “Blue”)
By using bigger number than 2 for the second operand of the Modulo operator and Select Case expression we could, of course, alternate between more than two colors.
Latest posts by Dejan Sarka (see all)