Custom Reports in Magento with Clean_SqlReports
Custom Reports in Magento with Clean_SqlReports

Custom Reports in Magento with Clean_SqlReports

Published September 24, 2014 in Development
Avoiding Code Errors
Avoiding Coding Errors
August 25, 2014
Classy Llama Magento Translation Blog
Magento Translation, Step Zero: What Must Be Translated?
October 24, 2014

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):

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. Click on each of those roles and then uncheck the “Add/Edit Reports (DANGEROUS)” option:

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:

Now, save the report and run it by clicking on that row. You should see something like this:

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:

Now, when you run the report, you should see something like this (this example is using non-native status labels):

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:

Now, when you run the report, you should see something like this (this example is using non-native status labels):

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!

12 Comments

  1. Jay says:

    Just uploaded this module to our Magento ver. 1.9.2.2 and I see the reports but not able to edit or add any new ones? I went to System->Permissions->Roles and made sure my users roles have All selected in the drop down to give all access. Clicked save logged out and back in. Still when I move my mouse over Reports->Special Reports there is no add/edit opetion. I also looked at the vag/log system.log and exceptions.log which have no errors.
    Suggestions one where I can edit the code to just allow everyone?

    • Hi,

      How can you show both the report table and the chart below that? thanks so much.

      • Erik Hansen says:

        @Daniel – That is not possible with the current version of the extension, but feel free to fork the extension, add that functionality, and submit a pull request with that functionality added.

    • Brad says:

      Sweet module, only issue I get is that on most SQL I try and save I get the error – You don’t have permission to access /index.php/admin/adminhtml_report/save/key/dce6120d0975b630100a06733cdc5437/ on this server

      It isn’t every SQL statement and all run fine in PHP Admin or if I put them into the DB direct. Any ideas ?

      • Erik Hansen says:

        First, I would try to log out and then log back in. Second, check to make sure your admin user has permissions to save reports. Viewing reports and saving reports are two different permissions. Go to System > Permissions > Roles, click on the Role of your user, and then check to see whether your Role has “All” permissions OR has individually selected permissions *with* the report saving option selected.

        • Brad says:

          It is an Admin User it has resource Access to ALL.

          It is not on all SQL if I do something dumb like select * from table it will work but add a where clause no dice.

          If I try and use your sample again no dice. I can paste it into the DB table and get it to work but not through the UI.

          • Erik Hansen says:

            @Brad – Try adding a semicolon at the end of your MySQL statement.

          • Brad says:

            Nope made no difference at all.

            Here is the Query, it is on a module table but should give you the idea

            SELECT SUM( qty ) AS QTY, sku AS SKU, Product_name AS Product_Name, supplier_name AS Supplier_Name, OrderPlaced
            FROM mage_supplier_dropship_items
            WHERE OrderPlaced = ‘False’
            GROUP BY sku, Product_name, supplier_name;

          • Brad says:

            If I take out the where clause it works fine

          • Erik Hansen says:

            @Brad – Ok, well I’m glad that you figured out why the report was not working. I’m not sure how to get it to work with a WHERE clause, so you’ll have to dig into that if you need to get it to work.

    • Kenn says:

      You need to click on “Special Reports” menu link itself, this will take you to the grid where you will find your reports.

      If you add a new report, it won’t show up on the grid immediately, you need to log out and log in again to see it.

  2. OMG, this post is awesome, thanks for sharing!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related postsView all
March 27, 2018

How to Create a Payment Method in Magento 2

Sometimes you might need more than the standard out-of-the-box payment method. Perhaps you have an agreement with a credit card processor and a solution for their […]
March 6, 2018

How to Build a Theme in Magento 2

When building a custom theme for Magento, it’s important to follow best practices for how the system is designed to be extended. Magento 2’s frontend is […]
February 12, 2018

Display Configurable Product Price Ranges in Magento 2

Today we’re going to look at the advantages of using configurable products and how we can update how their prices display on Magento 2 product listing […]
Most Recent PostsView all
November 29, 2018

Llama in the Sunset: David Alger

His stature has him towering well above the other llamas that surround him at Classy Llama. And his worn, size 13 cowboy boots show he’s more […]
October 17, 2018

Llamas and the Mobile eCommerce Optimization Initiative

The Mobile eCommerce Optimization Initiative has kicked off and Classy Llama is participating! The Magento Community has come together to create and test multiple conversion optimization […]
June 7, 2018

From Brick-and-Mortar To Online: How to Connect with a Niche Audience

As an eCommerce business, every penny counts. If you want to be successful, you have to carefully balance spending money to generate business and the profits […]