Smart Pivot table generator Logo


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
  • sales MySQL table

    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:

    Smart Pivot table Example

    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

    Connect to MySQL

    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"

    Title of Smart Pivot table

    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

    Numeric pivot table

    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".

    column labels

    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.

    MySQL Table Relations

    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.

    Values of Pivot table

    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!

    Buy Now → Other Reporting Tools For MYSQL →