Magento comes with a number of built-in reports covering areas like sales, taxes, customers, products, abandoned carts, and reviews. However many merchants have reporting needs beyond Magento has to offer. For some merchants, a third-party extension like Aheadworks’ Advanced Reports module will do the trick. However, if an existing module doesn’t fit the bill, then you’ll need to build a custom report. Traditionally, a Magento developer would build a report into Magento following the same pattern of Magento’s native reports. However, this approach can take a while and will often be cost-prohibitive.
To make building custom reports an easier task, Kalen Jordan built a module called Clean_SqlReports that allows you to take a MySQL query and automatically turn it into a Magento report. During the Magento Imagine 2014 Hackathon, Kalen worked with a number of others to enhance the module to add support for the Google Charts API, which means you can build some really visual reports. In this blog post, I’m going to demonstrate how to build a few different types of reports using this extension (some examples inspired by the module readme):
- Top 500 Customers by Lifetime Volume (Plain Table)
- Order Status (Pie Chart)
- Order Status by Month (Stacked Column Chart)
Module Setup
First, you will need to install the module: https://github.com/kalenjordan/custom-reports You can install it via modman, Composer, or download a ZIP of the module and copy it into your Magento installation. Once you have the module installed, log into the admin. Since this module runs MySQL code that is entered via the admin, someone with access to add/edit reports could wreak havoc on the site, if they knew the proper MySQL to enter (think TRUNCATE or DROP). To overcome this, go to “System > Permissions > Roles”. You should have at least one role for users that you don’t want to have access to add/edit reports.
Types of Reports
The module has two primary forms of reports:
- Plain Table – this report type is similar to what you get with Magento’s native reports. You won’t be able to do things like filter by date range and change how the report is grouped, but a business user can export the report to CSV or Excel and do additional filtering in a spreadsheet application.
- Google Charts API – this report type allows you to use nearly any of the Google Chart types. Check out these examples. Be aware that you need to write your MySQL query to return the data in the format required by be Google chart.
Report #1 – Top 500 Customers by Lifetime Volume
Now that you’ve setup the permissions for this extension, let’s create our first report. For this example, we’re going to grab the top 500 customers and display their lifetime order value, the total number of orders, and the last date they ordered. Go to “Reports > Special Reports”. Click “Add Report”. For the title, enter “Top 500 Customers by Lifetime Volume”. For the “SQL”, enter this query:
SELECT
-- Round to two decimal places and prepend with $
CONCAT('
Now, save the report and run it by clicking on that row.
Report #2 – Order Status
Create a new report with the title of “Order Status”. Select “Pie Chart” from the “Output Type” field. Enter the following SQL:
SELECT
sales_order_status.label AS 'Status',
COUNT(sales_flat_order.entity_id) AS 'Orders'
FROM sales_flat_order
LEFT JOIN sales_order_status ON sales_flat_order.status = sales_order_status.status
GROUP BY sales_flat_order.status
ORDER BY COUNT(sales_flat_order.entity_id) DESC
The “Chart Configuration” field needs to be a JSON object and corresponds to the “Configuration Options” section of the Pie Chart page. Enter this value into the “Chart Configuration” field:
{
height: 900,
width: 1100,
title: 'Order Status',
}
Report #3 – Order Status by Month
Building a single MySQL query that groups data by months is a bit messy, as you have to “hard code” the columns you want have included in the report. In our example, we’re going to hard code the most common order statuses as columns in our select statement. While it is possible to dynamically create columns from statuses, that is outside the scope of this article. Create a new report with the title of “Average Products per Order by Month”, select the “Column Chart” Output Type and then enter this SQL:
SELECT
-- "Year - Month"
CONCAT(YEAR(sales_flat_order.created_at), ' - ', MONTHNAME(sales_flat_order.created_at)) AS 'Month',
SUM(IF(`status` = 'canceled', 1, 0)) AS 'Canceled',
SUM(IF(`status` = 'closed', 1, 0)) AS 'Closed',
SUM(IF(`status` = 'complete', 1, 0)) AS 'Complete',
-- Custom status
SUM(IF(`status` = 'complete_partially_shipped', 1, 0)) AS 'Partially Shipped',
SUM(IF(`status` = 'processing', 1, 0)) AS 'Processing',
-- Custom status
SUM(IF(`status` = 'shipped', 1, 0)) AS 'Shipped'
FROM sales_flat_order
GROUP BY MONTH(sales_flat_order.created_at)
Enter this value into the “Chart Configuration” field:
{
isStacked: true, // Important, as this turns the column into a stacked chart
height: 600,
width: 1400,
title: 'Order Status by Month',
vAxis: {
title: 'Number of orders'
}
}
NOTE: Magento saves all records in the database using UTC, so when you run reports based on date, it will be reporting based on UTC, not your local timezone. Magento’s native reports account for this adjustment, but a raw MySQL query does not. However, there is a way to overcome this: use the DATE_ADD function to adjust for your time zone. Assuming you are in central time (and it is daylight savings time), you are UTC – 5. So replace the GROUP BY statement in the above query with this, and voilà, your report now accounts for the timezone difference:
GROUP BY MONTH(DATE_ADD(sales_flat_order.created_at, INTERVAL -5 HOURS))
Conclusion
Now that you’ve seen some of the things that you can do with the Clean_SqlReports module, go build some reports!
, FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Lifetime Sales', COUNT(sales_flat_order.entity_id) AS 'Orders', customer_entity.email AS 'Email', MAX(sales_flat_order.created_at) AS 'Most Recent Order Date' FROM `customer_entity` LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id GROUP BY customer_entity.entity_id ORDER BY SUM(sales_flat_order.`grand_total`) DESC LIMIT 500
Now, save the report and run it by clicking on that row.
Report #2 – Order Status
Create a new report with the title of “Order Status”. Select “Pie Chart” from the “Output Type” field. Enter the following SQL:
The “Chart Configuration” field needs to be a JSON object and corresponds to the “Configuration Options” section of the Pie Chart page. Enter this value into the “Chart Configuration” field:
Report #3 – Order Status by Month
Building a single MySQL query that groups data by months is a bit messy, as you have to “hard code” the columns you want have included in the report. In our example, we’re going to hard code the most common order statuses as columns in our select statement. While it is possible to dynamically create columns from statuses, that is outside the scope of this article. Create a new report with the title of “Average Products per Order by Month”, select the “Column Chart” Output Type and then enter this SQL:
Enter this value into the “Chart Configuration” field:
NOTE: Magento saves all records in the database using UTC, so when you run reports based on date, it will be reporting based on UTC, not your local timezone. Magento’s native reports account for this adjustment, but a raw MySQL query does not. However, there is a way to overcome this: use the DATE_ADD function to adjust for your time zone. Assuming you are in central time (and it is daylight savings time), you are UTC – 5. So replace the GROUP BY statement in the above query with this, and voilà, your report now accounts for the timezone difference:
Conclusion
Now that you’ve seen some of the things that you can do with the Clean_SqlReports module, go build some reports!