This tutorial will demonstrate how to create a basic pivot table from an MYSQL database using Smart Pivot Table in just a few clicks! For the purpose of this example, we will use a database that stores a company’s sales data. In real-life scenarios, numerous tables and columns would be involved, but we will concentrate solely on the “Orders” table and three columns “Order ID”, “Order Date” and “Ship Country”

For this tutorial, the required pivot table should illustrate the total number of orders made from each country in each year. The resulting table should resemble the following image.

To create the pivot table, the years stored in the order date column will serve as the headers. This means that rows will be converted to columns. The countries will be listed in the “Row Labels” area, while the number of orders will be listed in the body of the table. For instance, the number of orders in Brazil in the year 1995 was 38.

Using Smart Pivot Table to build this pivot table is incredibly simple! The first step is to connect to your MySQL database.

After establishing the connection, you will need to define the settings for the pivot table, starting with the general settings. These settings include the title of the pivot table, which should be set as “Annual sales per country”.

Smart Pivot Table offers the option to password-protect your tables to ensure that only the administrator can access them. This is particularly recommended if your table contains confidential data. In this tutorial, we will demonstrate how to enable password protection.

In addition, we need to set the “Is numeric” option to “true” since we will be displaying the count of orders for each country, which is a numeric value that can be processed by mathematical calculation functions. This is precisely what we aim to showcase in this tutorial.

Next, we will specify the column labels to determine what will be displayed as headers in the pivot table. For our example, we want to show the years as the headers. Therefore, we will select the “Orders” table and the “Order Date” field.

As soon as we select the “Order Date” field, a new box will appear where we will choose “Year” because we want to display the “Annual sales” and we are interested in the “Year” part of the stored “Order dates”.

We will follow a similar approach to create the rows by adding “Countries” to the “Row Labels” section. To achieve this, we will select the “Orders” table and the “Ship Country” column, along with the “Country” field.

It is worth noting that if we were to select the “Customers” table instead of the “Orders” table, a new section for relationship settings would appear. This step is necessary only if the data is stored across multiple tables. However, it is essential to know that Smart Pivot Table can handle data from multiple tables seamlessly.

The main body of the pivot table should contain the values. For the purpose of this tutorial, we need to display the total number of orders, which can be achieved by selecting the “Orders” table, the “OrderID” field, and the “Count” function.

For this tutorial, the “pagination” option is unnecessary since there are not many columns, headers, or rows. Therefore, we will skip this option.