Improve pivot table space utilization using the hide collapsed columns and default column width features in Amazon QuickSight

Amazon QuickSight is a scalable, serverless, machine learning (ML)-powered business intelligence (BI) solution that makes it simple to connect to your data. You can create interactive dashboards, get access to ML-enabled insights, enable natural language querying of your data, and share visuals and dashboards with tens of thousands of internal and external users, either within QuickSight itself or embedded into any application.

Recently, we launched a range of new features for Tables and Pivot Tables in QuickSight centered around interactivity and performance, allowing you to alter field visibility, load tables faster, and build consistency across different interactions. Building on this momentum, we are excited to unveil two additional capabilities, this time with a focus on improving readability, presentation, and space optimization.

In this post, we delve into the following features:

• Default column width – This makes it easier to set a precise and uniform column width for all columns, including dynamically added columns
• Hide collapsed column – This helps you improve space efficiency and the user experience by making pivot tables look more compact and organized, eliminating the need for unnecessary horizontal scrolling

Default column width

Bid farewell to the laborious task of manually adjusting individual column widths using the column drag handler. Now, authors can effortlessly establish a consistent column width for all value columns within their pivot table by utilizing the new column width setting. Just like row height, authors can define a default column width in the Format visual pane, which applies the same width to all value columns, eliminating the need to adjust them one by one. This streamlines the process, allowing authors to precisely set column widths in pixels, granting them greater control over presentation and ensuring an aesthetically pleasing layout. Even with data refreshes, your column widths will remain uniform, because new columns will automatically conform to this default value.

Note that the column width attribute is applicable only to the metric fields of the pivot table visual.

Let’s look at an example of applying a new default column width to a pivot table. The following example pivot table shows that the width of metric columns isn’t uniform, which isn’t visually appealing.

To apply a default column width for all the metric fields in the pivot table, complete the following steps:

1. Choose the pencil icon to open the Format visual pane.
   
2. Expand the Cells section and for Column width (pixels), enter your preferred width. In this example, column width is set to 150 pixels.
   

The default column width of 150 pixels gets applied to all the metric fields in this pivot table.

Any new metric columns added to the pivot table after setting the default width will automatically have the same width. The following screenshot shows that the newly added column discount is automatically set to the default width of 150 pixels.

Hide collapsed columns in Tabular layout

Pivot tables in QuickSight offer two layout options, each with a key distinction in how they present row fields. The recently introduced hierarchy layout consolidates all row fields into a single column in a hierarchical structure. In contrast, the tabular layout, which is more traditional, displays detailed and granular data by presenting each row field as a separate column, making it less space-efficient. Additionally, in the tabular layout, when row dimension fields are expanded and collapsed, the collapsed dimensions appear as empty columns, taking up unnecessary space. This can be inconvenient, particularly for users with numerous row fields, because it requires horizontal scrolling to view all the columns.

To enhance space utilization and user experience, we are introducing a new feature to hide collapsed columns, accessible in the pivot table options within the format settings. We are updating the default behavior of the tabular layout pivot table, where the Hide collapsed columns option will be enabled by default, automatically concealing collapsed fields and only displaying the expanded ones. This update conserves valuable screen real estate, reduces the need for unwanted scrolling, and ensures that users can concentrate solely on the expanded columns. The result is a more efficient and visually pleasing user experience.

To illustrate the effectiveness of the newly introduced feature, let’s consider the following pivot table with city and postcode columns collapsed, leaving them displayed as blank columns.

Let’s explore how to enable the option to automatically hide these columns when they are collapsed.

1. In the pivot table visual, choose the pencil icon to open the Format visual pane.
   
2. Under Pivot options, select Hide collapsed columns.

Before enabling Hide collapsed columns, make sure to collapse at the parent level for all intended columns to be hidden.

As shown in the following screenshot, the city and postcodes columns are now hidden.

The following screenshots illustrate the view before and after adding this new feature.

Other minor updates

We’ve also made a few minor enhancements to improve tabular data presentation. One of these changes pertains to displaying values as rows in pivot tables with no dimension fields in row and columns. This is especially useful when you need to showcase multiple key performance indicators (KPIs) within a confined space. In the past, we used to include an empty header cell for the values column. With our latest update, we’ve eliminated the header, resulting in a cleaner look. The following screenshots compare the view before and after this enhancement.

Conclusion

In this post, we explored two new features and the new user experience using QuickSight pivot tables, which can help you create more elegant and efficient pivot tables. Setting a default column width streamlines the process of setting a consistent pixel width for all value columns, simplifying your workflow. Hiding collapsed columns automatically conceals empty columns when row fields are collapsed in tabular layouts, enhancing space utilization and overall user experience. These user-friendly features can improve the presentation and readability of your pivot tables. Give them a try today and let us know your feedback in the comments section.

About the authors

Bhupinder Chadha is a senior product manager for Amazon QuickSight focused on visualization and front end experiences. He is passionate about BI, data visualization and low-code/no-code experiences. Prior to QuickSight he was the lead product manager for Inforiver, responsible for building a enterprise BI product from ground up. Bhupinder started his career in presales, followed by a small gig in consulting and then PM for xViz, an add on visualization product.

Ashok Dasineni is a Solutions Architect for Amazon QuickSight. Before joining AWS, Ashok worked with clients and organizations in Banking and financial domain, focusing on fraud research and prevention. He designed and implemented innovative solutions to improve business process, reduce cost and increase revenue, enabling companies around the world to achieve their highest potential through data.

Srikanth Baheti is a Specialized World Wide Principal Solution Architect for Amazon QuickSight. He started his career as a consultant and worked for multiple private and government organizations. Later he worked for PerkinElmer Health and Sciences & eResearch Technology Inc, where he was responsible for designing and developing high traffic web applications, highly scalable and maintainable data pipelines for reporting platforms using AWS services and Serverless computing.

Raji Sivasubramaniam is a Sr. Solutions Architect at AWS, focusing on Analytics. Raji is specialized in architecting end-to-end Enterprise Data Management, Business Intelligence and Analytics solutions for Fortune 500 and Fortune 100 companies across the globe. She has in-depth experience in integrated healthcare data and analytics with wide variety of healthcare datasets including managed market, physician targeting and patient analytics.