Visualize hierarchical data using the hierarchy layout for pivot tables in Amazon QuickSight

Are you finding it cumbersome to scroll horizontally in pivot tables to see all your row fields? Are you dealing with data that has a hierarchical structure, like cost center or product hierarchies? If you are, then there’s exciting news for you! Amazon QuickSight now offers a new layout option called hierarchy layout for pivot tables. This feature allows you to organize your data in a hierarchical outline format, displaying each level indented under the level above it. In this post, we delve into the advantages of using the hierarchy layout and explore its various configuration options.

Hierarchy layout for pivot tables

A new layout option known as hierarchy layout has been introduced in pivot tables. Currently, each field in the Rows field well adds a new column to the pivot table, which can be cumbersome for customers with numerous row fields because they have to scroll horizontally to view all fields. The hierarchy layout addresses this challenge by presenting all row fields in a single column in a hierarchical format. This format uses indentations to differentiate items from different fields, resulting in a more readable table. Additionally, this layout is a more condensed display, allowing for more room to display numeric data.

In the words of one of our customers, Best Western,

“With the recent addition of the hierarchy layout to the pivot tables visuals, the Best Western Hotels team can modify one of our most used dashboards. This dashboard allows the hoteliers to select the content of the first four columns and to omit up to three columns. This feature will allow the hoteliers to view more information without having to scroll right and left. The benefit to everyone is that more information is communicated more quickly.”

Choosing between the hierarchy and tabular layout in pivot tables depends on the specific dashboarding and reporting needs. The hierarchy layout is ideal for displaying vast amounts of data while conserving space, making it an excellent choice for presenting hierarchical data in a clear and easy-to-read format. One of the main benefits of the hierarchy layout is that you don’t need to display individual row field headings. On the other hand, if you prefer a more traditional table format, the tabular layout is an excellent option. This layout presents each row field in its own column, making it perfect for presenting detailed and granular data, such as survey responses with multiple answer options.

Field configurations for Hierarchy layout

Hierarchy layout offers different field configurations. For example, the following screenshot shows the hierarchy layout with values in rows.

The following screenshot shows the hierarchy layout with values in columns.

In the following sections, we look at the different features and functionality of hierarchy layouts, along with the process of configuring them.

Convert to hierarchy layout

All pivot tables created after launch will start with hierarchy layout as the new default experience without affecting existing pivot table visuals. You can toggle to hierarchy layout by navigating to Pivot options on the Format visual menu and selecting Hierarchy.

Sorting

Similar to tabular layout, you can sort row, column, and value fields using the refreshed menu options. You can view singular field menu options for column and value fields as compared to combined row field menu options for all row fields together, allowing you to choose your field to sort by and order in which you would like to sort.

The following screenshot illustrates sorting using the combined row header menu.

The following screenshot shows sorting on columns.

The following screenshot shows sorting on values.

Expand or collapse

To reveal detailed information in pivot tables, you have the option to expand row and column fields either through the menu or by choosing the plus sign. For more information, refer to Expanding and collapsing pivot table clusters. Although the plus and minus signs are ideal for expanding or collapsing individual nodes, the context menu offers a range of navigation options, including expanding or collapsing all nodes and expanding or collapsing up to a specific field level.

As shown in the following screenshot, you can expand or collapse using the row header menu (choose the row header to display the menu).

You can also expand or collapse a single node using the plus and minus signs or use the menu option to collapse the node or expand all the nested nodes.

As shown in the following screenshot, you can also expand or collapse individual columns.

Row header styling

The header styling options between the tabular and hierarchy layout remain largely unchanged except for the row header, which has new options like the ability to hide or rename the row label to something meaningful and apply the same row header styling to row labels.

Subtotals Styling

We have enhanced the subtotal options in pivot tables with a new control called Apply styling to. This allows authors to choose their preferred styling for subtotals, including value only (new option), entire row (header and value), and header only, providing greater flexibility. It’s worth noting that this option is also available for tabular layouts, ensuring a seamless transition between different layouts.

Export

When exporting to Excel, there will be differences in the output between the hierarchy layout and tabular layout in pivot tables. The exported Excel file will reflect the specific layout chosen, providing accurate representation of the data based on the selected layout. The following screenshot shows an example output in hierarchy layout.

Other updates

The pivot table update also includes redesigned context menus, and it now displays renamed columns in the sorting menu.

Redesigned context menus

As part of the update, we have redesigned the context menu options for pivot tables, replacing the super menu with a more condensed layout. This change ensures a unified experience for both authors and readers. Although both authors and readers can access field-level configurations through the context menu, authors have the additional capability to access further configurations directly from the field wells. The context menu for row, column, and value fields has been revamped to provide a consistent experience across both layouts. The only distinction is the inclusion of a combined menu option specifically for rows in the hierarchy layout. The following screenshots compare the old and new experience.

Display renamed column names in the sorting menu

Previously, when renaming a column using the format pane, only the header display name was updated, causing confusion because the sort menu still showed the original column name. However, this issue has been addressed. Renamed columns now reflect the updated name in both the column headers and sort menus. For instance, if we rename Gross Margin to GM%, the new name will be consistently displayed in both the column header and sorting menu, as demonstrated in the following screenshot.

Conclusion

This post explored the hierarchy layout feature available in pivot tables, highlighting its various functions such as interaction, styling, and export capabilities. The hierarchy layout can help condense multiple row levels into a more simplified and easily readable format, offering a practical solution for working with complex data. Start using hierarchy layout to enable hierarchical navigation and provide a drill-down experience on pivot tables today!

If you have any questions or feedback, please leave a comment.

For additional discussions and help getting answers to your questions, check out the QuickSight Community.

About the author

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.

Jose Banos is a Sr. WW Go-To-Market QuickSight Specialist focused on creating QuickSight awareness worldwide. He has over 17 years of experience in fields like Telco, cyber-security and data analytics. Jose started his career as presales, moving into business development and customer success working with customers in 4 continents and living in 3 different countries. He leads go-to-market plans across marketing, sales, and customer success and he enjoy helping customers to increase their revenue and reduce cost using QuickSight and other AWS services.