Master Your Data with SmartSuite Table: How to Use Pivot Tables for Dashboard Views

Pivot Tables in Dashboards

Welcome to another tutorial on our website. Today we'll walk through how to use a pivot table in a dashboard view. This is a new feature, available as a widget in dashboard views. Let's dive in!

Setting Up the Tables

We'll quickly walk through the solution. There are three tables:

Accounts Table: A list of accounts, from account 1 to account 21.
Products Table: A list of products, from product 1 to product 30, each with unique price points per unit.
Sales Table: Acts as a junction table linking accounts to products and recording the date of sale, quantity sold, and the total cost for each sale.

Accounts Table

This table has a list of accounts:

Products Table

This table contains products and their price points:

Sales Table

The Sales table links products to accounts and records sale data:

It focuses on the date, quantity sold, and a formula to calculate total cost.

Creating the Pivot Table

Now let's look at the pivot table in a dashboard view.

Opening the Dashboard View

Pivot tables are available in the SmartSuite Views section of the widget library. Select the Pivot Table option.

The configuration settings menu allows you to:

Title: Name the pivot table
Description: Add a description
Solution: Choose the related solution

Setup the Pivot Table

Let's use the sales table and determine rows and columns:

Setting Rows and Columns

Rows: Select accounts to see a list of accounts
Columns: Select products to see what was sold

Use ascending order for better readability:

Accounts: 1, 2, 3,...
Products: 1, 2, 3,...

Summary and Assessment

We want to assess the total cost, not just count the sales.

Determine Summary Options

Field to Assess: Total cost field
Summary Type: Sum to total the cost

There are other summary options like max, average, etc. But here we'll sum the total cost of products sold by each account.

"This dot notation allows us to dive into the linked record and extract a specific field value from the record that's linked. And you are seeing that automated output here via the formula for how much this sale was worth."