Alternate row formatting - Conditional Formatting with row & column groups

Question

Is there a way to use conditional formatting to alternate the background color for a Matrix table that has both a column group and a row group?

Here are details of my table....
Row group name:  GroupbyEmployee
Column Group name:  GroupbyProjectNumber
Value:  Sum(HoursWorked)
Tablix name:  matrix1

Note, there is only one row and one column, but they are both 'groups' so I can properly get the sum of hours worked by each employee on each project. 

I have tried the following conditional formatting on the Value text box properties, but none of them give the desired result:  
=IIF(RowNumber("GroupbyEmployee") Mod 2, "White", "PaleGreen")
=IIF(RowNumber("GroupbyProjectNumber") Mod 2, "White", "PaleGreen")
=IIF(RowNumber("matrix1") Mod 2, "White", "PaleGreen")
=IIF(RowNumber(Nothing) Mod 2, "White", "PaleGreen")

None of the scopes above for RowNumber() seem to give the desired result.  I know this has something to do with the row and/or column groups.  Is there anything that will work for this specific example? 

Thanks,
Elizabeth

Answer 1

Hi Elizabeth,

 

As per details posted, you may not be able to apply alternate color option with row and column groups included in the design with Mod expression . May be you can use below alternate approach to get alternate background color even in the matrix control.

 

 

Please feel free to revert back if any issues still, thank you

 

Cheers,

Arun Gangumalla

Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 2

Unfortunately, this did not work for me.  The filler row is not alternating color, nor is the background color being passed through to the column group column.

Placeholder column name:  Color

Placeholder column BackgroundColor expression:

=IIF(RowNumber(Nothing) MOD 2, "Gray", "White")

Column group cell BackgroundColor expression:

=ReportItems!Color.value

Results:

(EDIT: Sorry, I wrote "row group" in the picture above where I meant "column group")

As you can see, the background color does not alternate properly, and the background color is not passed through to the column group cell\column properly, either.

Is this a difference between Report Builder 2.0 and 3.0?  I noticed your screenshots look slightly different than mine.

Answer 3

Hi Elizabeth,

I create a similar report in my test environment, and get the result as the following screen shot shows.

Based on your description, you want to apply the expression on both the rwo group and column group, for example, when the row number of the date cell is odd number and the column number of the cell is odd number, the background color of this cell should be "White”. However, including aggregates that specify both a row group and a column group in a single expression is not supported in Reporting Services. So, RowNumber aggregate functions within a Tablix can refer to row groupings or data column groupings, but not both.

To work around this issue, we can add new field call “RowNumInGroup”, for example, in dataset query,
SELECT   Year, Month, Amonut, ROW_NUMBER() OVER (PARTITION BY year ORDER BY year) AS RowNumInGroup
FROM    table  
In the report design surface, add the field as column parent into the Tablix and then specify the background color of the data cell with following expression.
=IIF((Fields!RowNumInGroup.Value mod 2)=1 and (rownumber("Month") mod 2)=1, "White","LimeGreen")
The following screen shot shows the preview of the test report, please take as reference.

Regards,
Fanny Liu

---

Fanny Liu
TechNet Community Support

Answer 4

Hello Elizabeth,

I have recreated design in Report Builder 3.0 with same design and same logic, i think you didn't follow the steps which i have illustrated in previous post.

you might have added the column inside the column group instead please add it before column group i.e outside. Follow the below steps again.

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 5

Hi,

one more thing noticed is seems like you have named the column as "Color", you should use the textbox as the source not the column so you should name the textbox shown in the above post as "Color" & assign the background color and font color of the textbox as "=value" and then use it as background color value (=ReportItems.Color.value) for other textbox controls.

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 6

This still isn't working for me.  I'll explain exactly what I did in clearer detail so maybe you can see why this isn't working.

Please note that I did make my placeholder cell outside the column group but inside the row group.  See the screenshot below:

The resulting report looks like this:

Fanny:  Just to clarify, I'm trying to get alternating row colors here, not columns.  Just an FYI for my setup.

Arun:  I don't understand what you meant by your last comment.

Hopefully the screenshots above help clarify my situation...

Answer 7

Hello Elizabeth,

From the screenshot you have posted seems like you trying to assign background color of the "Color" textbox control as =iif(Rownumber(nothing) mod 2,"hot Pink","white")

but as i said before it should be assign with "=Value" and font color of the textbox as "=Value" under text box control properties.. for your reference please refer the screenshot again.

Please feel free to revert back if any issues still, thank you..

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 8

I think I understand what you mean now, but it's still not working. 

"Color" text box value & properties:

Resulting report:

All of the rows are showing White.  What am I missing now?

Answer 9

I did a bit of testing - it seems there is something off about the row.  If I simply put =RowValue(Nothing) for my placeholder column, I get the following result:

As you can see, the RowValue(Nothing) is returning an even number every time, which is why (after I fixed the setup to follow your directions) every row was showing up White.

Answer 10

Hi Eli

Do you have similar kind of design as below or you have more than one row grouping ?

 

> try avoiding the placeholder instead directly place the expression in textbox expression.

> Or else try creating a small sample report like above one with some basic data like fields required for row grouping , column grouping with direct expression under text boxes but not in a placeholder

> if possible please provide some sample data and the design needed so can work out the same in a test environment.

 

please try the above ones and revert back if any help needed further, lets fix this issue :) , thank you...

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 11

There is only one row group.

I did place the expression directly in the textbox expression.  You couldn't see it in my screenshot because both the font and the background color were white.

I think the problem is my data...

The original Dataset looks like this:

WorkedDate		HourWorked		CeUser		ProjectNumber
10/29/2010		3				Elizabeth 		2948.005
10/29/2010		4				Elizabeth 		2948.001
10/29/2010		8				Bob 			2948.001
10/28/2010		1				Elizabeth	 	2948.011
10/28/2010		3				Elizabeth 		2948.007
10/28/2010		4				Elizabeth	 	2948.001
10/28/2010		4				Bob 			2948.001
10/28/2010		4				Bob 			2948.007
10/27/2010		0.75				Elizabeth 		2948.007
10/27/2010		2.25				Elizabeth 		2948.005
10/27/2010		8				Bob 			2948.005
10/27/2010		4.75				Elizabeth 		2948.001

In my report, this should show up as such (see below), with single Row Group for Project Number and single Column Group for CeUser.  It sums up all hours worked on a single Project per CeUser, and has totals for both each row and each column.

(There is a filter parameter for Start Date and End Date, though this shouldn't matter here)

Project Number		Elizabeth		Bob		TOTALS
2948.001				12.75		12		24.75
2948.005				5.25			8		13.25
2948.007				3.75			4		7.75
2948.011				1			0		1
TOTALS:				22.75		24		46.75

Answer 12

Hello Elizabeth,

 

Thanks for posting sample data.I was able to create the output with out any issues.

 

Please follow the steps as shown below without any changes even the color hashcodes as few colors are not working like baby pink etc so use the same hash code etc.

 

Please revert back if any issues still, thank you....

 

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 13

It still doesn't work.  I'm ready to give up.  I'm sure it has something to do with the Dataset I'm using.

After the step where I "Run" - it's still not alternating rows colors as it should.  I followed your directions exactly.

No need to reply, marking this issue closed.

Answer 14

Hi Elizabeth,

 

There is another expression also for achieving the alternate color option under matrix... please try this once

=IIf(RunningValue(Fields!NAME.Value,CountDistinct,Nothing) Mod 2, "#FF99CC", "White")

 

you can make use of runningvalue function with distinct count of the row values... so obviously it should work as it'll give unique values for each row under matrix output.

so please verify this expression on your side once.

hope it'll help you ... thank you

 

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.

Answer 15

Your last suggestion worked great - just what I needed!

Thanks

Answer 16

Hello,

Great ...finally it worked for you :)

 

---

- Arun Gangumalla, Please mark as helpful or answered if it resolves your issue to help others in finding solutions easily.