A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Tuesday, January 29, 2013

Alternating Row Background Colors in a Tablix (aka the Green Bar effect)

UPDATE on 1/31/2013: Major overhaul of technique for formatting a matrix with the "green bar" effect. The new approach requires no pre-planning for the dataset query and uses scope designations that are agnostic to the specific names of the data region.

The "green bar" effect is a document is the alternating of one background color (usually white) with another background color (originally light green) on each printed line of a document in order to make it easier to read across each line of text. The term comes from the days of impact printers with continuous feed paper that was pre-printed with just such a scheme of alternating colors, and it remains a popular method of making long lists, particularly grids, easy to read. Here is an example:



The method of formatting SQL Server Reporting Services tables with a "green bar" effect is not difficult. Formatting a matrix is only a little more difficult.

Formatting a Table
  1. In design mode, highlight all the text boxes in the (Details) section. Use a click-and-drag method to select them all in one step or hold down the Ctrl key and click each one in turn.
  2. On the Properties pane click the down-arrow for the value of the BackgroundColor property, and then click Expression...
  3. Enter the following expression. If you want colors other than white and light green, substitute your own color choices.

    =IIf(RowNumber(Nothing) mod 2 = 0, "LightGreen", "White")
The Mod function applies "modulo division," which returns the remainder of the division. All even numbers have a remainder of zero when divided by 2, so the IIF() function will return "true" for even numbers and "false" for odd numbers, resulting in the two different colors being applied to the background of the text box. That's all there is to it for a table. A matrix is a little more involved.

Formatting a Matrix
  1. Create your matrix, and then right-click on the left-most column and select Insert Column > Inside Group - Left.
  2. On the bottom-left text box of the matrix, which was just created when you added the new column, right-click and select Text Box Properties...
  3. Change the Name property to RowNumber.
  4. Enter the following expression as the Value property:

     =RunningValue(Min(1), Sum, Nothing)
     
  5. Click OK to confirm the new settings for the text box.
  6. If you do not want the running count of rows to appear in your final matrix, click on the tablix column marker to select the entire column. Then use the Properties pane to change the Hidden property to True.
  7. Click on the text box that will show the aggregate data for your matrix (in an Excel PivotTable this would be called the "data region" cell).
  8. On the Properties pane click the drown-arrow for the value of the BackgroundColor property, and then click Expression...
  9. Enter the following expression.

    =IIf(ReportItems!RowNumber.Value mod 2 = 0, "LightGreen", "White")
If you want the row labels to alternate with the same colors, merely repeat the same formula for the background color of the row label text box(es) as well.

4 comments:

  1. I love this! Great tip and explained very well. This makes for an attractive and useful grid that transports well to excel or wherever.

    ReplyDelete
  2. Is there a way to apply the "green bar" effect to only the visible rows? I have a condition on my detail line that is filtering out report conditions (which can't be filtered in the source SQL query), but I want to use the green bar effect on the results after the detail visibility is set. How could I perform that task? If I remove my visibility condition, the above works perfectly...but when some rows are hidden, it's a little wacky. Thanks.

    ReplyDelete
    Replies
    1. The problem is that the hidden rows, though taking up no space in the output, still "exist" as far as the tablix context is concerned. So the values affect sum totals and other aggregate functions, including RowNumber.

      Can you remove the filter from the row visibility and add it to the tablix filter property? It might require some creative use of expressions, depending on the complexity of the filter condition, but it would then allow all the rows to be visible and render the green bar effect as desired.

      Delete
  3. I think I figured out a solution to filter the table using a very long iif condition so as not to use the report items. Thanks!!!

    ReplyDelete

Followers