Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    sales and data analytics
    How Data Analytics Improves Lead Management and Sales Results
    9 Min Read
    data analytics and truck accident claims
    How Data Analytics Reduces Truck Accidents and Speeds Up Claims
    7 Min Read
    predictive analytics for interior designers
    Interior Designers Boost Profits with Predictive Analytics
    8 Min Read
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
    big data and remote work
    Data Helps Speech-Language Pathologists Deliver Better Results
    6 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Would You Prefer Prettier Pivot Tables II?
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Uncategorized > Would You Prefer Prettier Pivot Tables II?
Uncategorized

Would You Prefer Prettier Pivot Tables II?

Editor SDC
Editor SDC
6 Min Read
SHARE

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.

More Read

The R Journal – A Refereed Journal for the R Project Launches
When It’s Sink or Swim
Google Apps: The Missing Manual
Essential Hardware And Software for Virtual Employees and Contractors
Posterous DOS attack. Someone should write the story
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.

Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

sales and data analytics
How Data Analytics Improves Lead Management and Sales Results
Analytics Big Data Exclusive
ai in marketing
How AI and Smart Platforms Improve Email Marketing
Artificial Intelligence Exclusive Marketing
AI Document Verification for Legal Firms: Importance & Top Tools
AI Document Verification for Legal Firms: Importance & Top Tools
Artificial Intelligence Exclusive
AI supply chain
AI Tools Are Strengthening Global Supply Chains
Artificial Intelligence Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

Media Cloud is a system that lets you see the flow of the media….

3 Min Read
Image
Uncategorized

Virtual Reality: A New Creative Medium Where the Default State Is Belief

5 Min Read

Social Media Meets Healthcare

19 Min Read

Start cooking: can you explain SOA or mashups in two minutes?

1 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

giveaway chatbots
How To Get An Award Winning Giveaway Bot
Big Data Chatbots Exclusive
ai is improving the safety of cars
From Bolts to Bots: How AI Is Fortifying the Automotive Industry
Artificial Intelligence

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?