Generating a Pivot table (crosstab) using Smart Pivot table Generator
In this tutorial, we will show you how to create a simple pivot table (crosstab) using Smart Pivot table generator in just a few mouse clicks!.
Let's assume we have a database for storing sales data of a company In a real life scenario, many tables and columns will be included. But in this example, we will focus only in One table which is the “Orders” table and 3 columns.
- Order ID
- Order Date
- Ship Country
If you need a pivot table that shows the total number of orders that have been made from each country in each year, you would see something like the following:
The headers of the pivot table will be generated from the years which are stored in the order date column, in other words, you will be converting rows to columns.
Countries will be listed in the "Row Labels" area And the number of orders should be listed in the is the body of the table. For example the number of orders in Brazil in the year 1995 was 38
Building this pivot table using Smart Pivot table generator couldn’t be easier!! The first step is to connect to your MySQL database
Then once a connection is established you will define the settings of the pivot table
First the General settings:
The title: we should add "Annual sales per country"
Smart Pivot table generator gives you the ability to password protect your tables so only the software administrator can view it. This is recommended if your table includes confidential information. In this example, we will enable password protection.
We will also need to set the "Is numeric" to “true” since we are listing the count of orders per each country which is “numeric” data that can be processed by mathematical calculation functions, which is exactly what we want to demonstrate in this example
Now we will need to specify the column labels to define what exactly will be listed as the headers of the pivot table. In our example, we want to display the years as the header. So, we will select the “Orders” table and the “OrderDate” field.
The moment we select the “order Date”, a new box should appear, in this box we will choose “Year ”because we want to display the "Annual sales". We interested in the "Year" part of the stored “Order dates".
In a similar manner as we created our columns, we will display “Countries” in the “Row labels" area. We will select the “Orders” table and the “Ship Country” column and, the “Country” field .
Note that once if we choose the “customers” table instead of the “orders”, table a new section for the relationship settings appeared. Now, we can define the relationship between these two tables. This step won't be needed if all data is stored in a single table, but we just want you to know that if your data is in multiple tables, that won’t be a problem.
Values should be listed in the main body of the generated table. In this example, we want to display the total number of orders, so we will select the “Orders” table, the “OrderID” field and the “Count” function.
If your pivot table includes a large number of columns, headers, or rows, you have the “pagination” option which enables you to display the table in several pages and navigate between them, but we don't need this option for this example.
Click generate, That’s all there is to it!