By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData Collective
  • Analytics
    AnalyticsShow More
    data-driven image seo
    Data Analytics Helps Marketers Substantially Boost Image SEO
    8 Min Read
    construction analytics
    5 Benefits of Analytics to Manage Commercial Construction
    5 Min Read
    benefits of data analytics for financial industry
    Fascinating Changes Data Analytics Brings to Finance
    7 Min Read
    analyzing big data for its quality and value
    Use this Strategic Approach to Maximize Your Data’s Value
    6 Min Read
    data-driven seo for product pages
    6 Tips for Using Data Analytics for Product Page SEO
    11 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-23 SmartData Collective. All Rights Reserved.
Reading: Would You Prefer Prettier Pivot Tables II?
Share
Notification Show More
Latest News
anti-spoofing tips
Anti-Spoofing is Crucial for Data-Driven Businesses
Security
ai in software development
3 AI-Based Strategies to Develop Software in Uncertain Times
Software
ai in ppc advertising
5 Proven Tips for Utilizing AI with PPC Advertising in 2023
Artificial Intelligence
data-driven image seo
Data Analytics Helps Marketers Substantially Boost Image SEO
Analytics
ai in web design
5 Ways AI Technology Has Disrupted Website Development
Artificial Intelligence
Aa
SmartData Collective
Aa
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
Last updated: 2009/04/22 at 11:26 AM
Editor SDC
6 Min Read
SHARE
- Advertisement -

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. […]

- Advertisement -

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

big data improves

3 Ways Big Data Improves Leadership Within Companies

IT Is Not Analytics. Here’s Why.
Romney Invokes Analytics in Rebuke of Trump
WEF Davos 2016: Top 100 CEO bloggers
In Memoriam: Robin Fray Carey
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.

- Advertisement -
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.

Editor SDC April 22, 2009
Share this Article
Facebook Twitter Pinterest LinkedIn
Share
- Advertisement -

Follow us on Facebook

Latest News

anti-spoofing tips
Anti-Spoofing is Crucial for Data-Driven Businesses
Security
ai in software development
3 AI-Based Strategies to Develop Software in Uncertain Times
Software
ai in ppc advertising
5 Proven Tips for Utilizing AI with PPC Advertising in 2023
Artificial Intelligence
data-driven image seo
Data Analytics Helps Marketers Substantially Boost Image SEO
Analytics

Stay Connected

1.2k Followers Like
33.7k Followers Follow
222 Followers Pin

You Might also Like

big data improves
Big DataJobsKnowledge ManagementUncategorized

3 Ways Big Data Improves Leadership Within Companies

6 Min Read
Image
Uncategorized

IT Is Not Analytics. Here’s Why.

7 Min Read

Romney Invokes Analytics in Rebuke of Trump

4 Min Read

WEF Davos 2016: Top 100 CEO bloggers

14 Min Read

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

ai is improving the safety of cars
From Bolts to Bots: How AI Is Fortifying the Automotive Industry
Artificial Intelligence
AI and chatbots
Chatbots and SEO: How Can Chatbots Improve Your SEO Ranking?
Artificial Intelligence Chatbots Exclusive

Quick Link

  • About
  • Contact
  • Privacy
Follow US

© 2008-23 SmartData Collective. All Rights Reserved.

Removed from reading list

Undo
Go to mobile version
Welcome Back!

Sign in to your account

Lost your password?