During my reporting workshops I sometimes get the question, is it possible to create a GreenBar report and how do you do that in a matrix?
A GreenBar report is actually a report where you apply shading to every other row or column.
So let’s start with a simple example and let’s create a simple list report using a table.
First I create a simple dataset that fetches ItemLedgers by Item:
Then I add a layout:
and let’s have a look at the result:
Not really an amazing layout. Let’s apply some grouping in the layout, on Location:
So far so good, nothing really difficult. Let’s now apply green-bar formatting:
Voila, ready. Our detail rows now have a green-bar effect.
Now, let’s try to do the same thing, in a matrix.
Hmmm, not really what we expected…
As you can see, the effect is not alternating green bars but it is more like a strange checker-board. The reason for this is that the RowNumber() function for this text-box at run-time is not continuous because it depends on the row and column groups. In order to create a green-bar effect, you need to use another expression.
We will solve this problem by using a custom function, in the code tab of the report:
Now we set the expression in the BackgroundColor property of the text-boxes as follows:
And voila the result:
If you change the expression for the BackgroundColor property for both textboxes to the following:
=Code.AlternateColor(“PaleGreen”, “White”, True)
Then you get this result:
You can download the example reports here: