How to create Magento custom reports

Partial of config.xml

The contents of Super_Awesome_Adminhtml_Report_ExampleController:

The line that says: “->_addContent($this->getLayout()->createBlock(‘awesome/adminhtml_report_simple’))” really is the important one here. This is telling Magento which block to use to drive the WHOLE report. Oh, and look at the exportSimpleCsvAction() – that all you need to export a grid into CSV.

So let’s take a look at the block Super_Awesome_Block_Adminhtml_Report_Simple –

There isn’t much to it. It will use the parent class to auto generate the name of the “grid” block (aka the block with all of the report data):

Here is the content of that block (Super_Awesome_Block_Adminhtml_Report_Simple_Grid) –

The important parts:

1. The fact that it extends Mage_Adminhtml_Block_Report_Grid – This will require you to do things specific ways and will also automatically put the filter piece at the top of your report so you can query by date and period.
2. The collection that it is using – By default it uses the Mage::getResourceModel(‘reports/report_collection’) collection, but you need to call the initReport() function on that collection with the collection class that will handle YOUR data. Ours is: initReport(‘awesome/report_simple_collection’)
3. The columns – Like I said earlier, you really should be running these reports on “already aggregated” data, but you still might want to sum some things up for totals. Any column that has a ‘total’ => ‘sum” attribute will be included in the total section correctly.
4. The export types – One thing that I discovered while doing this is that every grid has the ability to export itself as CSV or XML file. Cool huh?

We mentioned the collection in #2, so let’s take a look at that. The contents of the collection –

There really isn’t anything special about this collection. It is a normal collection that inits the resource model (so you will need a normal one of those). The only hitch here is that the Mage_Adminhtml_Block_Report_Grid is expecting specific methods to be in this collection. Those methods are:

• public function setStoreIds($storeIds)
• public function setDateRange($from, $to)

I am not running the query by store, so I don’t care about that. There are examples in other classes if you need to see how to do it, but basically it is adding the store_id to the where clause.

I do however want to add some date range stuff, so I simply call a protected method in my collection to set up the SQL.

If you are having trouble with your SQL, you can debug it easily by changing your load to: parent::load(true, true). This will print out the SQL to the screen and the system.log. Also something that stumped me for a while was that my data was not necessarily showing up on the correct date in my report. This has to do with some code in Mage_Reports_Model_Mysql4_Report_Collection class.

It does some “time shifting” based on locale, so BE AWARE.

The only other things I haven’t talked about are the (empty) Data.php (helper) –

…and the normal model class that you should already know how to create. All that has is the init of the resource model in the construct –

Complex Report

With this, you will see a lot of duplication, but I want to be thorough.

The table structure (setup script):

The types of classes needed:

• Report Controller
• Report Block
• Report Grid
• Configs (Menu and Table Definitions)
• Model, Resource Model, Collection Model
• The layout.

The Folder Structure (can be different if you want it to be):

First we create the menu item –
Contents of adminhtml.xml –

Then we create the necessary config.xml settings:

Now we create our controller –

So this is where the “complex” report is a little more complex. The layout that is loaded at first is from the awesome.xml file:

So with the layout and the controller combined we know that we have to worry about 3 different blocks:

1. awesome.xml: awesome/adminhtml_report_complex (Super_Awesome_Block_Adminhtml_Report_Complex)
2. controller: adminhtml_report_complex.grid (Super_Awesome_Block_Adminhtml_Report_Complex_Grid)
3. awesome.xml and controller: grid.filter.form (Mage_Adminhtml_Block_Report_Filter_Form) – This is a Mage class and we don’t need to touch this.

The first block is the Grid container. Here is his contents –

Every line is important, so pay close attention to this file. In the parent of this class, it has the function …

… which auto generates the block “awesome/adminhtml_report_complex_grid” with the name adminhtml_report_complex.grid. This is #2 in the “blocks we care about” list.

Here is the contents of Super_Awesome_Block_Adminhtml_Report_Complex_Grid –

Notice that we are extending Mage_Adminhtml_Block_Report_Grid_Abstract (this is different than the “simple” report example).

In here, the part that we don’t want to screw up is the getResourceCollectionName() function. It contains the Collection model that is the bane of my existance. Ready to see it?

BAAAAAAAAAAAAAAAAAM –

So remember the part about “I’m not sure how it all works”? Yeah. So I will talk about snippets that I found are important, but might not have a lot of info on why:
1. When I didn’t have this, it complained about “period” in the where clause:

2. Because I am not already aggregating the sums in my table (BAD ME!), I had to introduce the following code:

I found that if I wanted to group my period by a date that was formated, I had to include the SAME formatted date in the group clause.
3. I wanted the SUM of the value column, so you will see this in lots of places: