Design captivating Amazon QuickSight dashboards with new Table and Pivot Table features

Post Syndicated from Rushabh Vora original https://aws.amazon.com/blogs/big-data/design-captivating-amazon-quicksight-dashboards-with-new-table-and-pivot-table-features/

Amazon QuickSight is a fast and cloud-powered business intelligence (BI) service that makes it easy to create and deliver insights to everyone in your organization without any servers or infrastructure. QuickSight dashboards can also be embedded into applications and portals to deliver insights to external stakeholders. And QuickSight Q lets end-users simply ask questions in natural language to get machine learning (ML)-powered visual responses to their questions.

Within QuickSight, tables and pivot tables are among the most-used visuals and almost always present in dashboards. These visuals let data be presented in a clear and easy-to-read format with filtering, conditional formatting, and cross visual actions, while scaling to millions of rows of data. This can be either directly queried from a data warehouse, such as Amazon Redshift, Snowflake, or QuickSight’s SPICE data store. When used with QuickSight’s new Free form layout, this lets you present pixel-perfect views of your data to your stakeholders. We recently introduced eight new features that let tables and pivot tables in QuickSight be extensively customized to meet your presentation needs:

Row height customization
Vertical alignment in cells
Text wrapping in cells
Background and font colors
Borders and gridline customization
Images in table cells
Hyperlinks in table cells
Custom styling for totals and subtotals

In this post, we explore how, as authors of QuickSight dashboards, you can now supercharge your table and pivot tables using these new features.

Use styling options to present your data better

These new changes let you create tables that follow a distinct design pattern or apply a corporate identity to your tabular visuals. For example, you might want to create a table visual about a set of videos, with views and votes information for each, along with links to the video. It is important to follow the color pallete of the organization, choose an appropriate line height, use a specific grid style, and display thumbnail images of a specific size. Let’s see how you can achieve all of this using the newly launched table styling options. You can access these settings from the format visual panel of the visual.

To customize the background color, row-height, and text color on table headers:

In the Format visual pane, choose Headers.
For Background, choose the background color icon, and then choose a color. You can choose one of the provided colors, reset the header text color to the default color, or create a custom color.
For Row height, enter a number in pixels. You can enter a whole number between 8 and 500.
To change the font color, choose the Abc color icon, and then choose a color. You can choose one of the provided colors, reset the header text color to the default color, or create a custom color.

To customize the background color, row-height and text color, and wrap-text on table cells:

In the Format visual pane, choose Cells. The Cells section expands to show options for customizing cells.
For Row height, enter a number in pixels. You can enter a whole number between 8 and 500.
For Background, select alternate background colors between rows, and then choose a color for Odd rows and a color for Even rows by choosing the background color icon for each and selecting a color. You can choose one of the provided colors, reset the background color to the default color, or create a custom color.
For Text, to change the font color, choose the Abc color icon, and then choose a color.
To wrap text in cells, select Wrap text and increase the Row-height.

To show URLs as images:

In the Format visual menu, choose Field styling.
In the Field styling pane, choose the field you want to style from the dropdown menu.
In the Url options section of the Field styling menu, choose Show URLs as images.
After rendering images in a table, you can choose how to size the images in the Image sizing section of the Field style pane. You can fit images to their cell’s height or width, or you can choose not to scale the image. Images will fit to a cell’s height by default.

Steps to create a table visual with Hyperlinks and Images

Custom styling options for Totals and Sub-totals

In some use cases (most commonly with financial data), it might be important to style your totals and sub-totals independently. You can access these settings from the format visual panel of the visual.

Customizing the background color, font color, and borders for totals and subtotals:

In the Format visual pane, choose Total or Subtotal.
For Background, choose the background color icon, and then choose a color. You can choose one of the provided colors, reset the background color to the default color, or create a custom color. In pivot tables, you can also add background colors for column totals and subtotals. To do so, choose the background color icon for Background in the Columns section.
To change the font color, choose the Abc color icon, and then choose a color. You can choose one of the provided colors, reset the cell text color to the default color, or create a custom color.
For Borders, do one or more of the following:
- To customize the type of border that you want, choose a border type icon. You can choose no borders, horizontal borders only, vertical borders only, or all borders.
- To customize the border thickness, choose a border thickness.
- To customize the border color, choose the border color icon, and then choose a color. You can choose one of the provided colors, reset the border color to the default color, or create a custom color.
To apply totals and subtotals styling to cells, choose Apply styling to cells.

Here is an example of a pivot table that has been formatted with some of the above techniques:

Steps to create pivot table visual in a form of financial report by using custom styling for Totals and Subtotals

Sorting and custom actions

Combining the new formatting features with some of QuickSight’s existing capabilities lets you unlock a range of possibilities in your dashboards.

Custom sorting

Sometimes you must organize the fields beyond the conventional methods of ascending/descending by the dimension or a measure. For example, you cannot organize the days of the week or month names by sorting them alphabetically or by any measure. With the sort options on Table and Pivot table (launched in early 2021), you can now sort a field by any other field in your dataset or a calculated field. This is done regardless of whether it is being used in the visual or is off the visual. The following steps sort a field using an off-visual field:

Add the required fields.
Add a new calculated field using the rank function to rank days of the week in the required order.
Choose the field that has days of the week, and then choose Sort options:
- For Sort by, choose the new calculated field from the drop-down list.
- Since this field is based on rank function, it uses custom aggregation.
- Select Ascending.
- Click Apply.
You will see that now the days of the week are arranged as we wanted.
Note: It is not necessary to use a calculated field using rank function. You can also use one of the fields in your data-set that may be used for maintaining the rank. For example, I want to order the list of players on the team based on a specific display order that should not be visible on the visual.

Custom Actions

What if you don’t want your readers to go away from your dashboard to watch the training video? You can do this with help of Table, custom actions, and custom visual content. Let’s take a closer look.

Step 1: Add video link to the visual.

Add the field that has the Video URL to the Table (hide the field if you do not want to show that on the dashboard).

Step 2: Add the parameter for video URL.

Add a new parameter by choosing the add icon (+) near the top of the pane.
For Name, enter an alphanumeric value for the parameter.
For Data type, choose String.
For Values, choose Single value, as this parameter will contain only one value.

Step 3: Add custom action.

Set the action name.
For Activation, choose Select so that the action is activated when a specific row of the table is selected.
For Action type, choose Navigation Action.
For Target sheet, choose the same sheet where the Table visual is created.
For Parameters, select the Parameter created in Step 2.
Save your selections, and then create the custom action.

Steps 4 and 5: Add Custom visual content and Link the Video URL parameter.

In the Visual types pane, choose the custom visual content and then choose Customize visual.
Add the parameter to the custom visual content by by choosing the add icon (+) near the URL section of the format visual pane.

Below is the example of Video Player on the dashboard using the Table visual linked to custom visual content using the custom action:

Get started today!

We hope this blog has sparked new and creative ideas for your next Amazon QuickSight dashboard! Also, don’t forget to learn more about recent features in our what’s new.

About the Authors

Rushabh Vora is a Senior Technical Product Manager for Amazon QuickSight, Amazon Web Service’s cloud-native, fully managed BI service. He is passionate about Data Visualization. Prior to QuickSight, he was working with Amazon Business as a Product Manager.

Jose Kunnackal, is a principal product manager for Amazon QuickSight, AWS’ cloud-native, fully managed BI service. Jose started his career with Motorola, writing software for telecom and first responder systems. Later he was Director of Engineering at Trilibis Mobile, where he built a SaaS mobile web platform using AWS services. Jose is excited by the potential of cloud technologies and looks forward to helping customers with their transition to the cloud.

Noise