Custom Row Banding
While working on client site recently I had an ask that seemed like it would be simple but turned out to be a bit of a complex and convoluted little problem. The ask was simple the client had a chart with two different categories on the row shelf. The inner categories were repeated across the outer category. The client wanted to highlight one particular row out of the inner category. Now the first thought I had was to use row banding, however row banding effects alternating groups of rows, and couldn’t pick a specific row from a subset.
The next thought was to use annotations and float them over the row with a block transparent colour. These were effective but they are prone to breaking when the data changes and don’t include the header, which was not what the client wanted.
The eventual solution was a mix of row banding and calculated fields. The first step is creating a calculated field that selects only the category you want to highlight and puts the other elements of the dimension into an ‘other’ field (you could also do this with grouping).
Once the grouping has been achieved then you place it on the rows shelf between the two categories (the one being grouped should be to the right of the group/calculation)
Now if we were to add banding in we can see it at every other row, now we change the level of the banding and now we can see the inverse of what we want to highlight is highlighted, now we could simply change the order of the groups and put the category we want to highlight below the one we do not, but in this case the client wanted it to appear at the top of the list.
So now this is where the trick really comes into play, first we make sure that the banding is in the correct place, and then we change the banding colour to white.
Next we go to the row header for the rightmost category that we are performing the group on and we select format, here we change the shading, now we should be seeing that the header of the category that we want to highlight appears to be highlighted, we do the same with the pane here we right click in the view and select format, and we fill the pane (not the worksheet) with the colour we have just used for the header, and voila we have the banding the client wanted, now we just hide the header for the middle category and we are good to go!
There are a couple of situations where the system will break down. The first is in the rightmost category, the one which we will be grouping or not this one should not be in the middle or at the top of a hierarchy, else if the user tries to drill down the banding that we’ve so carefully set up will be broken. The left dimension can be part of a hierarchy though.
This may not be the absolute best practice, but in this case the client had a specific idea of how they wanted the view to look and in these circumstances this was one of the better options for this view.