Thursday, March 10, 2016

Shading rows for readability in a PowerBuilder datawindow

Just a little almost-useless tech note for myself.

I really like when developers do little things that make my life easier. Sometimes you'll see reports with a lot of data get shading to help your eye track the row you're reading.

I figured out a way to do this when I was doing a lot of PowerBuilder work. Apply an expression to the color property of the detail band of the datawindow. It's fairly easy to do if you just want to alternate every other row between two colors, but it gets trickier if you want to do it with more than odd or even rows.

Here's the code to shade every alternating set of three rows between light grey and white:
if ( mod(
          if( mod( getRow(), 3) <> 0
            , int(getRow() / 3)
            , int(getRow() / 3) - 1
         , 2
         ) = 0
   , rgb(255, 255, 255)   //white
   , rgb(233, 233, 233)   //grey

What this code is doing is using the modulus function to analyze the remainders of the row number divided by 3 to reduce it into one of two categories, grey or white.  In a division by three, the remainder would be 0, 1, or 2. If it's 1 or 2, we know it's the first or second row in a block of three rows. If it's a zero, we know it's a last row in a block of three rows.

But we want that third row to be included in the batch of three rows of a single color, and to alternate with every other set. So here's where the PowerBuilder INT function helps. We divide rows 1 and 2 by 3, and get a fraction, but the INT function simplifies them to the largest integer below the given value, so for rows 1 and 2, they INT function returns 0. But for row three we don't want it to fall into the next grouping so we subtract 1. Then we MOD these results by 2 to get the binary result of either a 0 or 1.

This makes our formula generate these values for the first several rows:

Row  Nested_IF  Outer_MOD
 1         0       0
 2         0       0
 3         0       0
 4         1       1
 5         1       1
 6         1       1
 7         2       0
 8         2       0

See how that works? It's fairly simple for the computer to process, but easily breaks each row down into either a 1 or a 0, and at that point it's pretty easy to wrap the whole thing in an IF statement and just assign the color.

I really only wrote this so I wouldn't forget it if I needed it again, but you're welcome to use it. If you're feeling "old school" you can use a light green instead of grey for the colored rows. And if you're a brainiac that figured out a way to do this with even less code, please post in the comments, I'd be happy to find an easier way.