Smart Pivot table generator Logo

Tutorial

Generating a quarterly sales report from a MySQL DB


In this tutorial, we will show you how to create a simple quarterly sales crosstab report from a MySQL DB using Smart Pivot table generator in just a few mouse clicks!.

The report should include the sales per quarter for each employee , as in the example shown in the following image:

MySQL sales Report


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 Two tables which are :

1) The “Orders” table from which we need to focus only on two columns:

  • Order ID
  • Order Date
Orders MySQL table

2) The "Employees" table from which we need to focus only on two columns

  • Employee ID
  • Employee Name
Employee MySQL table

Building this report 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 "Quarterly Sales Report"

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.

Now we will need to specify the column labels to define what exactly will be listed as the headers of the report. In our example, we want to display the quarters as the header. So, we will select the “Orders” table and the “OrderDate” field.

column labels

The moment we select the “order Date”, a new box should appear, in this box we will choose “Quarter” because we want to display the "sales per quarter".

column labels

Please be aware that if you want to get your report for a specific year, all you need to do is to check the "Exact year" box and add the year you wish to use for your report .

In a similar manner as we created our columns, we will display “Employees” in the “Row labels" area. We will select the “Employees” table and the “Employee Name” column .

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 the report

Finally the relationship step. in our example the join field is the Employee ID

Click generate, That’s all there is to it!