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
    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
    data driven insights
    How Data-Driven Insights Are Addressing Gaps in Patient Communication and Equity
    8 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Would You Prefer Prettier Pivot Tables?
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?
Uncategorized

Would You Prefer Prettier Pivot Tables?

Editor SDC
Editor SDC
4 Min Read
SHARE

I have heard from users that they often need to enhance the formatting of SPSS Statistics pivot tables beyond what can be done with tableLooks. Until recently there have been three ways to do this: tedious manual formatting, exporting to Excel and (tediously) formatting there, and writing a fairly complicated Basic or – starting […]

I have heard from users that they often need to enhance the formatting of SPSS Statistics pivot tables beyond what can be done with tableLooks. Until recently there have been three ways to do this: tedious manual formatting, exporting to Excel and (tediously) formatting there, and writing a fairly complicated Basic or – starting with version 16 – Python script. Now there’s a fourth way that is very powerful and much easier to use. And it doesn’t require any programming knowledge.

SPSSINC MODIFY TABLES is an extension command with a custom dialog box interface that is available from Developer Central for Version 17. I’ll explain some of its features with an example.

More Read

SQLCruise – The “Social-ism” Factor
Yes, Virginia, Google Does Devalue Everything It Touches
There’s Money in Them Thar Companies – BI & MDM Funding!
TunkRank and Retweet Rank
SAP Embraces Hadoop in the Enterprise

Here is a crosstab table (from the Crosstabs case study) showing customer satisfaction with different stores controlling by whether they had contact with a store employee or not.

The original crosstab with a tableLook applied

The original crosstab with a tableLook applied

TableLooks are good for static formatting and can be applied automatically, but they apply to whole areas of tables and are static.

The table cells contain counts and residuals. There is a story in this table that takes time to find. To bring out that story, we could highlight the large residuals. You can do that manually cell by cell, but that is tedious and error prone. The SPSSINC MODIFY TABLES command can automate this.

SPSSINC MODIFY TABLES can select cells based on the row or column label text, the indexes, and/or the cell values, and it can apply most of the formatting devices that you could use interactively, even hiding unwanted rows or columns. Here is an example that looks for the cell residuals and creates a red background if the residual is large.

SPSSINC MODIFY TABLES SUBTYPE='Crosstabulation'
DIMENSION=ROWS SELECT='Std. Residual'
/STYLES TEXTSTYLE=BOLD BACKGROUNDCOLOR=255 0 0
APPLYTO='abs(x) >2'.

I would run this right after the CROSSTAB syntax. It selects tables of OMS type “Crosstabulation” in that output; looks in the rows for the label “Std. Residual”, and makes the text bold and the background red (colors are RedGreenBlue numbers between 0 and 255). The APPLYTO keyword specifies that this only happen if the cell value is greater than 2 in absolute value.

Here’s the result.

The large residuals are bold and red

The large residuals are bold and red

The report has been turned into a message: there are issues with Store 2.

We can go further with this command. I’ll discuss that in another post, but before I go, I want to mention that this extension command has a custom dialog box available on the Utilities menu after you install it.

You can get the command and dialog interface from the Downloads section of SPSS Developer Central (linked at the top of this site).

I would be interested in hearing about particular formatting requirements you have for tables. I’m betting that most of them can be handled by this command.

Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

data analytics and truck accident claims
How Data Analytics Reduces Truck Accidents and Speeds Up Claims
Analytics Big Data Exclusive
predictive analytics for interior designers
Interior Designers Boost Profits with Predictive Analytics
Analytics Exclusive Predictive Analytics
big data and cybercrime
Stopping Lateral Movement in a Data-Heavy, Edge-First World
Big Data Exclusive
AI and data mining
What the Rise of AI Web Scrapers Means for Data Teams
Artificial Intelligence Big Data Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

A CTO’s favorite list of lessons from the Miracle of the 1980 Olympics

9 Min Read

Social Media: The Tension between Collaboration and Ownership

5 Min Read
Image
Uncategorized

5 Reasons Amazon Is Taking Over SEO

5 Min Read

Can You Digg It?

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.

AI chatbots
AI Chatbots Can Help Retailers Convert Live Broadcast Viewers into Sales!
Chatbots
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-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?