Would You Prefer Prettier Pivot Tables II?

In a previous post, I discussed the SPSSINC MODIFY TABLES command and dialog that can easily automate pivot table formatting. I skipped over things like making totals bold, because they are a piece of cake. (But just in case you were wondering, here’s an example for totals in rows of a Custom Table. […]

In a previous post, I discussed the SPSSINC MODIFY TABLES command and dialog that can easily automate pivot table formatting. I skipped over things like making totals bold, because they are a piece of cake. (But just in case you were wondering, here’s an example for totals in rows of a Custom Table. It also makes the background yellow.)

SPSSINC MODIFY TABLES SUBTYPE="Custom Table"
SELECT = "Total" DIMENSION=ROWS
/STYLES BACKGROUNDCOLOR=255 255 88 TEXTSTYLE = BOLD

But there are some things that are a little harder.

For example, if you are old enough to remember the mainframe, you might want every other row green (or maybe to switch between green and white after every three rows). And a user recently requested a way to suppress all the sections except the last of each Regression output table when doing stepping. For the first request, you could just list all the odd-numbered rows in the SELECT statement. But that’s tedious, error prone, and you might encounter a table with more rows than you listed. For the second request, you need to know which output block is the last in order to hide the earlier rows.

SPSSINC MODIFY TABLES, and the companion command SPSSINC MODIFY OUTPUT accommodate this sort of thing with custom plug-in functions. Prior to Version 17, you could have written a fairly long program to do a task like this, but now you need only a few lines of Python code. A custom plug-in function gets called for all the selected rows or columns in the command, and all it has to do is to apply its logic to the cells in question. The customstylefunctions.py file installed with MODIFY TABLES has a number of little functions like this that are both examples and sometimes useful along with details on how to write such functions. Here’s the code for striping odd-numbered rows. Blue is the new green, so it actually sets a blue background.

def stripeOddRows(obj, i, j, numrows, numcols, section, more):
    """Color background of odd number rows for data and labels"""
    if i % 2 == 1:
        obj.SetBackgroundColorAt(i, j, RGB((0,0,200)))

The logic of this code is simply, is this an odd-numbered row? If so, set its background to blue. “%” is the modulus operator in Python, so its value is 1 for every other row. obj is the pivot table object. i and j are the row and column numbers.

To use it with a Custom Tables table, you could write

SPSSINC MODIFY TABLES SUBTYPE="Custom Table"
SELECT = "<<ALL>>" DIMENSION=ROWS
/STYLES CUSTOMFUNCTION="customstylefunctions.stripeOddRows".

Okay, some of you think the new green is red. You could change the color specification above to RGB((200,0,0)), but then you’ve got another function to maintain, and soon someone is going to ask for yellow. The solution to this is to give the function some color parameters, and let the user choose when he or she writes the syntax. Less work for the author, and more flexibility for the user.

Here’s the code for that function. It’s only a little more complicated.

def stripeOddRows2(obj, i, j, numrows, numcols, section, more, custom):
    """stripe odd rows with color parameters

    extra parameters are r, g, b"""

    if i % 2 == 1:
    # retrieve three parameters with defaults and calculate color value
    # the first time and add to dictionary
    if custom["_first"]:
        custom["_color"] = RGB((custom.get('r',0), custom.get('g', 0),
            custom.get('b', 200)))
        custom["_first"] = False
    obj.SetBackgroundColorAt(i, j, custom["_color"])

Notice the extra parameter named custom. It gets passed a Python dictionary with the user-specified parameters in it. The defaults, set with get, are 0,0,200 – blue, in case the user didn’t supply them.

This time the user specifies these in the CUSTOMFUNCTION keyword. For example,

CUSTOMFUNCTION="customstylefunctions.stripeOddRows2(r=255, g=255, b=88)"

The function code calculates and store the RGB value the first time it is called in the command. In this case, doing the calculation every time would not take any noticeable time, but there might be a saving with a more complicated example.

The point of this is that MODIFY TABLES, by providing this plug-in capability, can greatly extend its functionality while eliminating most of the code you would have had to write before. And it doesn’t have to be formatting code. You could do anything you want with the table contents.