Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 4
How to create custom sales report
 
hernandobrazil
Jr. Member
 
Total Posts:  12
Joined:  2008-09-17
 

I’d like to create custom sales reports.

If someone have an example to how to do that, I will apreciate that!

This kind of thing, made a new report, could be posted by Magento team, to the community.
I don’t uderstand why don’t have a rich documentation to help us.

 
Magento Community Magento Community
Magento Community
Magento Community
 
MageParts
Guru
 
Avatar
Total Posts:  415
Joined:  2007-11-18
 

For starters a lot of the documentation available here is written by community members, not always by the core team. Second off It’s a bit rude to demand a complete documentation of an open source project which still is in its early stages. Although I do understand that people would like some more documentation regarding certain issues. Personally I think the community and the core team have done a exceptional job helping people with guides and answering forum posts. But agreed, things are still missing. I helped a dude with a similar issue a while back. He wanted to create a custom report as well so I pointed him in the right direction, here is the link: http://www.magentocommerce.com/boards/viewthread/27928/. It isn’t much help, but it should get you on the right path I hope.

Good luck, if you have any questions post them here. I’ll bookmark the thread for a il’ while.

 
Magento Community Magento Community
Magento Community
Magento Community
 
hernandobrazil
Jr. Member
 
Total Posts:  12
Joined:  2008-09-17
 

Bock, thanks a lot!!

My Steps to Copy the Sales Report:

1) Copy the file app/code/core/Mage/Adminhtml/Block/Report/Sales/Sales.php to app/code/core/Mage/Adminhtml/Block/Report/Sales/Vendas.php
2) Copy the directory
app/code/core/Mage/Adminhtml/Block/Report/Sales/Sales/ to
app/code/core/Mage/Adminhtml/Block/Report/Sales/Vendas/
3) Alter the source to reflect Report_Sales_Vendas
4) Add in file app/code/core/Mage/Reports/etc/config.xml

<vendas translate="title" module="reports">
                                <
title>Vendas Report</title>
                                <
action>adminhtml/report_sales/vendas</action>
                            </
vendas>
5) Add in file app/code/core/Mage/Adminhtml/controllers/Report/SalesController.php
public function vendasAction()
    
{
        $this
->_initAction()
            ->
_setActiveMenu('report/sales/vendas')
            ->
_addBreadcrumb(Mage::helper('adminhtml')->__('Vendas Report'), Mage::helper('adminhtml')->__('Vendas Report'))
            ->
_addContent($this->getLayout()->createBlock('adminhtml/report_sales_vendas'))
            ->
renderLayout();
    
}

It works fine!!

But I’d like to know How to change the data source.
Where and How to create a new SQL to finally create a new report?
When this first custom report become functional, I will write a tutorial
to help the community.

I create a new column in the Table sales_order called vendor_id
and my Report has to show only 3 columns, the name of the vendor (the user witch create the order),
the total sales made and the total without shipment.

Thanks!!

 
Magento Community Magento Community
Magento Community
Magento Community
 
MageParts
Guru
 
Avatar
Total Posts:  415
Joined:  2007-11-18
 

Here comes the tricky part of creating a report. Seeing as I haven’t really created one myself yet I can still only point you in the right direction. And this one will take some detective work I fear. Have a look at app/code/core/Mage/Reports/Model. The block which you copied from the Adminhtml directory might hold some clues as to which Model files are being used. I imagine that those Blocks calls a model, which then calls a Mysql (or resource if you will) model which gather data from your database, the model sorts out that data and returns a result to the block which display these results. With me so far?

The problem here is that I have no idea which Model files you are looking for in the Reports module, there are quite a few. But try having a look at these, they might help you out.

app/code/core/Mage/Reports/Model/Report.php
app/code/core/Mage/Reports/Model/Mysql4/Event.php
app/code/core/Mage/Reports/Model/Mysql4/Product/*

Also, as you will need to set up your own table you might wanna have a look at the modules installation files. These can be found in app/code/core/Mage/Reports/sql/repoerts_setup/*. It can be quite confusing building a new module so you might wanna have a look at this
http://www.magentocommerce.com/wiki/custom_module_with_custom_database_table. It helped me out.

And I’m truly happy to hear that you are considering posting a tutorial on the subject once you succeed smile. I’d be happy to help out more, so don’t be shy to ask wink

 
Magento Community Magento Community
Magento Community
Magento Community
 
hernandobrazil
Jr. Member
 
Total Posts:  12
Joined:  2008-09-17
 

I finally have a new Sales report in magento.

My Steps to change the Collection

1) Copy the directory
app/code/core/Mage/Reports/Model/Mysql4/Order/ to
app/code/core/Mage/Reports/Model/Mysql4/Pedido/
2) Alter the source (Collection.php) to reflect Pedido_Collection
3) Change the file app/code/core/Mage/Adminhtml/Block/Report/Sales/Vendas/Grid.php
to call your new collection. In function _prepareCollection change order_collection to pedido_collection

protected function _prepareCollection()
    
{
        parent
::_prepareCollection();
        
$this->getCollection()->initReport('reports/pedido_collection');
    
}
4) At this point the new report will function like the original sales report
5) Open the source app/code/core/Mage/Reports/Model/Mysql4/Pedido/Collection.php
You will need just only 4 functions to your new report, they are:
protected function _getRange[removed]$range)
public function getDateRange($range, $customStart, $customEnd, $returnObjects = false)
public function setStoreIds($storeIds)
public function setDateRange($from, $to)
(The functions getDateRange, _getRangeExpression and setStoreIds, you dont have to change)
6) In setDateRange, you can change to reflect a new Sql, example:
public function setDateRange($from$to)
    
{
        $this
->_reset()
            ->
addAttributeToSelect('*')
            ->
addAttributeToFilter('created_at', array('from' => $from'to' => $to))
            ->
addExpressionAttributeToSelect('orders''COUNT(DISTINCT({{entity_id}}))', array('entity_id'));
        
$this->getSelect()->joinRight(array('v' => $this->getTable('salesordervendor')),'v.entity_id = e.entity_id',array('user_id'));
        
$this->getSelect()->joinLeft(array('u' => $this->getTable('admin/user')),'u.user_id = v.user_id',array('username' => 'username' ))
                          ->
group('v.user_id');
        
//Mage::log('SQL [' . $this->getSelect()->__toString() . ']');
        
return $this;
    
}
7) Uncomment the Mage:log call and run the report to get in file var\log\system.log the SQL
(this help-me a lot)
8) Change the file app/code/core/Mage/Adminhtml/Block/Report/Sales/Vendas/Grid.php
the functions _prepareColumns() to show new columns, if you want.

I’d like to know how to add a new filter, than the current (inicial an final date)

If someone knows how to do that, please post here.

I create the table salesordervendor to save the user id, that made the order.
I took the idea, reading http://www.magentocommerce.com/boards/errors.php/viewthread/10763/P15/

 
Magento Community Magento Community
Magento Community
Magento Community
 
Peasy
Jr. Member
 
Avatar
Total Posts:  1
Joined:  2009-01-04
Paris
 

That’s not a good idea to add something in a file in “app/code/core/*” if you want to update magento a day.

You must to create a new Module “Ventas” and add file in this module.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ilovemagento
Member
 
Total Posts:  40
Joined:  2009-02-04
 

just change the paths mentioned above from ‘app/code/core/*’ to ‘app/code/local/*’ (replaced core by local)

and your changes will never be overwritten,

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jonathan123
Sr. Member
 
Total Posts:  215
Joined:  2008-09-14
 

I just tried the combination of the two above posts, but doing everything within the “local” directory instead of “core”. Changes made to current reports seem to work, but my new report will not show up. Any ideas why this may be the case?

 
Magento Community Magento Community
Magento Community
Magento Community
 
ilovemagento
Member
 
Total Posts:  40
Joined:  2009-02-04
 

Are you able to see the links of this new report in your Admin panel? (under Reports)

 
Magento Community Magento Community
Magento Community
Magento Community
 
Jonathan123
Sr. Member
 
Total Posts:  215
Joined:  2008-09-14
 

No, the new links don’t show up.
I’m wondering if the “config.xml” file can not be under the local directory?

I just tested this actually, and changes I make to config.xml do not seem to impact anything. I have cache disabled and I logged back in to be sure it would reload everything. I removed some reports from the config.xml file, but they still show up in admin.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ilovemagento
Member
 
Total Posts:  40
Joined:  2009-02-04
 

well, sorry for late reply,

i am preparing a tutorial in which I’ll enhance the hernandobrazil’s work and will place the code in proper files so Magento upgrade will never hurt these changes.

Though hernandobrazil did great help to give core idea for ‘Custom report creation in Magento’ but it will get vanish after your Magento upgrade.
I’ll paste the code on my Blog and will also update the link in this thread so could be helpful for you.

I’ll definitely publish it soon,

 
Magento Community Magento Community
Magento Community
Magento Community
 
l781
Sr. Member
 
Total Posts:  109
Joined:  2008-11-09
 

@ilovemagento - i will wayt your tutorial… smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
jdavisbacon
Jr. Member
 
Total Posts:  2
Joined:  2009-02-16
 

@ilovemagento - Any luck with publishing a write up?  Thanks in advance!

 
Magento Community Magento Community
Magento Community
Magento Community
 
jsu1972
Jr. Member
 
Total Posts:  11
Joined:  2009-02-19
 

so i’m getting a little closer to being able to have a custom report. when i’m done i’ll post a tutorial on how i did it.

I’m using a local directory so that I don’t have to modify or change anything in core.  I’ve gotten to the point where I have my new files in the local directory and I can get the new report to show up in the reports menu.  My problem is that I don’t understand the structure of the config.xml file well enough.  I can’t get the <action> to point to my new block of code.

For example:
in the original config.xml file for reports there is an entry

<sales translate="title" module="reports">
   <
title>Sales Report</title>
   <
action>adminhtml/report_sales/sales</action>
</
sales>

In my new config.xml file i have the following

<profit translate="title" module="reports">
   <
title>Profit Report</title>
   <
action>adminhtml/report_sales/profit</action>
</
profit>
but this is still looking in the core section of the code for the profit.php file.

how do i get that action statement to point to my modules code?

 
Magento Community Magento Community
Magento Community
Magento Community
 
miked2004
Sr. Member
 
Avatar
Total Posts:  214
Joined:  2007-12-13
Atlanta, Georgia
 

<profit translate="title" module="reports">
<title>Profit Report</title>
<action>adminhtml/report_sales/profit</action>
</profit>

Hope this helps allittle.

the <action> is the controller action that will be run. you probably need to change the action to match what you set up for your adminhtml controllers frontName in the config.xml.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Finster
Jr. Member
 
Total Posts:  2
Joined:  2009-05-07
 

I am having the same issue with my Magento installation not recognizing any of the files in my app/code/local especially if I have new files. If anyone knows a way to get these custom reports going, as it looks like ilovemagento has fallen off the internet, I would love to hear what people have done.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 4