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 2 of 4
How to create custom sales report
 
marketingflotte
Member
 
Total Posts:  35
Joined:  2007-10-23
 

@jsu1972 and ilovemagento:

any news about how to implement this whole thing into the local folder? :l

 
Magento Community Magento Community
Magento Community
Magento Community
 
EYEMAGINE
Member
 
Avatar
Total Posts:  35
Joined:  2008-02-14
Irvine, CA
 
Finster - 07 May 2009 10:57 AM

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.

Hey Finster,

It’s only 3 weeks later smile but verify your local modules are being enabled by opening/creating app/etc/modules/<YourModule>_All.xml

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <
modules>
        <
[YourModule]_[ReportModuleName]>
            <
active>true</active>
            <
codePool>local</codePool>
        </
[YourModule]_[ReportModuleName]>
    </
modules>
</
config>
 
Magento Community Magento Community
Magento Community
Magento Community
 
Tingu
Jr. Member
 
Total Posts:  14
Joined:  2009-06-01
 

Hi! All,

I’m Tingu very new to magento.
Thanks lot for your contributions to us.
I have a issue regarding reports, I’m sure you are the right person to ask.
Please help me. The issue is…

Login into magento admin > System > Configuration > To the left side, under Sales > Shipping Methods > UPS > Gave values like this Calculate Handling Fee = Fixed, Handling Applied = Per Order & Handling fee = 5;

I am getting “Shipping Amount” = “Handling fee” + “Amount given by the UPS”.
Its working fine, having no issue with it at all.

Now Reports > Sales > Shipping, I can see the report with fields “Period”, “Carrier/Method”, “Number of Orders” & “Total Shipping”,
now also no issue.

But now I want to add a column to the existing columns with the name “Shipping Profit”, Under this for each row, value should be “Total Shipping” − “Amount given by the UPS”. So that I will get value to “Shipping Profit”. At last total row I should have sum of Shipping Profit.

And one more thing is if I change Calculate Handling Fee = Percent, Handling Applied = Per Package & Handling fee =10; Then also it should work.

Please help me. I will be very thankful to you.
I’m doing a small project using Magento for my academics. If I do it well in time I will get a good grade. Please help me in this issue.

I haven’t created custom reports.

With lots of hope. Please help me……….

Thanks lot for you time.

Truly,
Tingu.

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

Just wondering if anyone has gotten around to writing up a tutorial or has more information available on this?

 
Magento Community Magento Community
Magento Community
Magento Community
 
jaggsr
Member
 
Total Posts:  39
Joined:  2009-03-25
Ahmedabad, India
 

Hello Guys,
I would like to Thanks all to help here.
I was able to generate the custom report I was wanted.

I described here.
http://magentocoder.jigneshpatel.co.in/php/create-custom-reports-in-magento-admin/

Take a look at the attachment what I got.

Best,

Image Attachments
Artist_sold_works_report.png
 
Magento Community Magento Community
Magento Community
Magento Community
 
James Thompson
Jr. Member
 
Total Posts:  29
Joined:  2009-01-23
 
jaggsr - 07 September 2009 12:46 AM

Hello Guys,
I would like to Thanks all to help here.
I was able to generate the custom report I was wanted.

I described here.
http://magentocoder.jigneshpatel.co.in/php/create-custom-reports-in-magento-admin/

Take a look at the attachment what I got.

Best,

Thanks for the guide! Have you tried doing the same in the local codespace? As you have added your report in ‘core’ it may be overwritten when you upgrade Magento. I have been trying to create custom reports in ‘local’ for weeks with no success.

 
Magento Community Magento Community
Magento Community
Magento Community
 
jaggsr
Member
 
Total Posts:  39
Joined:  2009-03-25
Ahmedabad, India
 
James Thompson - 07 September 2009 03:18 AM

jaggsr - 07 September 2009 12:46 AM
Hello Guys,
I would like to Thanks all to help here.
I was able to generate the custom report I was wanted.

I described here.
http://magentocoder.jigneshpatel.co.in/php/create-custom-reports-in-magento-admin/

Take a look at the attachment what I got.

Best,

Thanks for the guide! Have you tried doing the same in the local codespace? As you have added your report in ‘core’ it may be overwritten when you upgrade Magento. I have been trying to create custom reports in ‘local’ for weeks with no success.

Well I did not tried that. Will try that someday.

 
Magento Community Magento Community
Magento Community
Magento Community
 
James Thompson
Jr. Member
 
Total Posts:  29
Joined:  2009-01-23
 

How did you add the columns you wanted in the collection.php I have been trying to add the Customer Group to a copy of the ‘Customers by total’ report. I can add a column no problem but it shows up blank because it isn’t being included in the collection.

Currently the collection.php for my copy of the report is as below:

class Mage_Reports_Model_Mysql4_Customer_Customergroups_Collection extends Mage_Reports_Model_Mysql4_Order_Collection
{
    
protected function _joinFields($from ''$to '')
    
{
        $this
->joinCustomerName()
            ->
groupByCustomer()
            ->
addOrdersCount()
            ->
addAttributeToFilter('created_at', array('from' => $from'to' => $to'datetime' => true));
            
        return 
$this;
    
}
    
    
public function setDateRange($from$to)
    
{
        $this
->_reset()
            ->
_joinFields($from$to)
        return 
$this;
    
}
    

    
public function setStoreIds($storeIds)
    
{
        $vals 
array_values($storeIds);
        if (
count($storeIds) >= && $vals[0] != ''{
            $this
->addAttributeToFilter('store_id', array('in' => (array)$storeIds));
            
$this->addSumAvgTotals(1)
                ->
orderByTotalAmount();
        
else {
            $this
->addSumAvgTotals()
                ->
orderByTotalAmount();
        
}

        
return $this;
    
}
}

I have tried adding

->addAttributeToSelect('group_id')
but it doesn’t seem to do anything.
 
Magento Community Magento Community
Magento Community
Magento Community
 
Alexis Svenn
Jr. Member
 
Total Posts:  12
Joined:  2009-01-19
Brighton
 

OOh, the reports are horrible, all very well to see sales by period but merchants need sales by manufacturer etc.

What would be the nicest would be if someone could write a backend plugin/ extension so that one could then create reports from the back office by a combination of dropdowns/ checkboxes. In fact this would be so sweet for the merchant.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ForgetHangovers
Sr. Member
 
Total Posts:  83
Joined:  2009-04-12
Maryland, USA
 

I am also interested in creating a custom report, however I want to create a report of “Custom Options” selected on a specific product.
You see I have a FREE sample that I am distributing using Magento: http://store.forgethangovers.com/patches/free-sample.html
And in order to checkout the customer has to fill out a quick survey, which is simply just a set of drop down boxes I created using the product’s “Custom Option” feature w/in Magento.

I figured it would be pretty easy to later extract this information from the MySQL DB, but I was wrong.
The data is stored in
table=sales_flat_order_item
field=product_options

And here is a sample of the value of one of those fields:

a:1:{s:15:"info_buyRequest";a:3:{s:4:"uenc";s:88:"aHR0cDovL3N0b3JlLmZvcmdldGhhbmdvdmVycy5jb20vcGF0Y2hlcy9pbmRpdmlkdWFsLXBhdGNoZXMuaHRtbA,,";s:7:"product";s:1:"1";s:3:"qty";s:6:"1.0000";}}
or
'a:2:{s:15:\"info_buyRequest\";a:5:{s:4:\"uenc\";s:116:\"aHR0cDovL3N0b3JlLmZvcmdldGhhbmdvdmVycy5jb20vcGF0Y2hlcy9pbmRpdmlkdWFsLXBhdGNoZXMvZnJlZS1zYW1wbGUuaHRtbD9fX19TSUQ9VQ,,\";s:7:\"product\";s:1:\"1\";s:15:\"related_product\";s:0:\"\";s:7:\"options\";a:12:{i:16;s:2:\"50\";i:15;s:2:\"49\";i:14;s:2:\"46\";i:13;s:2:\"41\";i:12;s:2:\"33\";i:11;s:2:\"24\";i:10;s:2:\"23\";i:9;s:2:\"21\";i:8;s:2:\"18\";i:7;s:2:\"13\";i:6;s:2:\"10\";i:5;s:1:\"4\";}s:3:\"qty\";i:1;}s:7:\"options\";a:12:{i:0;a:7:{s:5:\"label\";s:3:\"Age\";s:5:\"value\";s:5:\"21-24\";s:11:\"print_value\";s:5:\"21-24\";s:9:\"option_id\";s:2:\"16\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"50\";s:11:\"custom_view\";b:0;}i:1;a:7:{s:5:\"label\";s:15:\"Marrital Status\";s:5:\"value\";s:9:\"UnMarried\";s:11:\"print_value\";s:9:\"UnMarried\";s:9:\"option_id\";s:2:\"15\";s:11:\"option_type\";s:5:\"radio\";s:12:\"option_value\";s:2:\"49\";s:11:\"custom_view\";b:0;}i:2;a:7:{s:5:\"label\";s:3:\"Sex\";s:5:\"value\";s:4:\"Male\";s:11:\"print_value\";s:4:\"Male\";s:9:\"option_id\";s:2:\"14\";s:11:\"option_type\";s:5:\"radio\";s:12:\"option_value\";s:2:\"46\";s:11:\"custom_view\";b:0;}i:3;a:7:{s:5:\"label\";s:10:\"Occupation\";s:5:\"value\";s:7:\"Student\";s:11:\"print_value\";s:7:\"Student\";s:9:\"option_id\";s:2:\"13\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"41\";s:11:\"custom_view\";b:0;}i:4;a:7:{s:5:\"label\";s:9:\"Education\";s:5:\"value\";s:12:\"Some College\";s:11:\"print_value\";s:12:\"Some College\";s:9:\"option_id\";s:2:\"12\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"33\";s:11:\"custom_view\";b:0;}i:5;a:7:{s:5:\"label\";s:16:\"Household Income\";s:5:\"value\";s:13:\"Less then 30K\";s:11:\"print_value\";s:13:\"Less then 30K\";s:9:\"option_id\";s:2:\"11\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"24\";s:11:\"custom_view\";b:0;}i:6;a:7:{s:5:\"label\";s:23:\"Do You Take Supplements\";s:5:\"value\";s:2:\"No\";s:11:\"print_value\";s:2:\"No\";s:9:\"option_id\";s:2:\"10\";s:11:\"option_type\";s:5:\"radio\";s:12:\"option_value\";s:2:\"23\";s:11:\"custom_view\";b:0;}i:7;a:7:{s:5:\"label\";s:40:\"How would you rank your typical hangover\";s:5:\"value\";s:6:\"Severe\";s:11:\"print_value\";s:6:\"Severe\";s:9:\"option_id\";s:1:\"9\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"21\";s:11:\"custom_view\";b:0;}i:8;a:7:{s:5:\"label\";s:51:\"What type of establishments do you typically prefer\";s:5:\"value\";s:13:\"House Parties\";s:11:\"print_value\";s:13:\"House Parties\";s:9:\"option_id\";s:1:\"8\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"18\";s:11:\"custom_view\";b:0;}i:9;a:7:{s:5:\"label\";s:40:\"How often do you usually go out per week\";s:5:\"value\";s:3:\"3-5\";s:11:\"print_value\";s:3:\"3-5\";s:9:\"option_id\";s:1:\"7\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"13\";s:11:\"custom_view\";b:0;}i:10;a:7:{s:5:\"label\";s:49:\"How many drinks do you typically consume per week\";s:5:\"value\";s:2:\"9+\";s:11:\"print_value\";s:2:\"9+\";s:9:\"option_id\";s:1:\"6\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:2:\"10\";s:11:\"custom_view\";b:0;}i:11;a:7:{s:5:\"label\";s:53:\"How would you prefer to buy Forget Hangovers Products\";s:5:\"value\";s:17:\"Convenience Store\";s:11:\"print_value\";s:17:\"Convenience Store\";s:9:\"option_id\";s:1:\"5\";s:11:\"option_type\";s:9:\"drop_down\";s:12:\"option_value\";s:1:\"4\";s:11:\"custom_view\";b:0;}}}'

Yeah, pretty sloppy… So what would the best way to extract this data from all my orders?

 
Magento Community Magento Community
Magento Community
Magento Community
 
bobbrodie
Sr. Member
 
Avatar
Total Posts:  132
Joined:  2008-04-02
Philadelphia, PA
 

Has anyone had the custom reports work in the local app pool? If so, could you post a zip? I’d love to see how to get it to work.

 
Magento Community Magento Community
Magento Community
Magento Community
 
revelnick
Jr. Member
 
Total Posts:  11
Joined:  2008-09-02
 
ForgetHangovers - 16 September 2009 01:53 PM
I am also interested in creating a custom report, however I want to create a report of “Custom Options” selected on a specific product.
You see I have a FREE sample that I am distributing using Magento: http://store.forgethangovers.com/patches/free-sample.html
And in order to checkout the customer has to fill out a quick survey, which is simply just a set of drop down boxes I created using the product’s “Custom Option” feature w/in Magento.

I figured it would be pretty easy to later extract this information from the MySQL DB, but I was wrong.
The data is stored in
table=sales_flat_order_item
field=product_options

And here is a sample of the value of one of those fields:

a:1:{s:15:"info_buyRequest";a:3:{s:4:"uenc";s:88:"aHR0cDovL3N0b3JlLmZvcmdldGhhbmdvdmVycy5jb20vcGF0Y2hlcy9pbmRpdmlkdWFsLXBhdGNoZXMuaHRtbA,,";s:7:"product";s:1:"1";s:3:"qty";s:6:"1.0000";}}

you could try unserialize()

http://www.php.net/manual/en/function.unserialize.php

that would restore the data to an object that you could work with.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Dimace
Jr. Member
 
Total Posts:  28
Joined:  2009-11-22
 

If any of you guys are interested in producing a neat profit report or need some slightly more advanced sales stats it might pay to check out Advanced Sales Statistics.

It utilizes the cost field in the product back end and compiles the data to produce a detailed profit report for your selected date range.

Does a load of other useful things as well, individual product performance, profit margins, conversion rates etc

Small investment makes checking financials a breeze, saves hours and hours each month…

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

As of right now, ‘cost’ data is not saved as part of an order (there exists a field for it, but it is not filled in - the Magento team has indicated to me that this is fixed in the next release, but I don’t see an indication of that in the Issue Roadmap). So, are you always looking at the CURRENT cost when running a report? This will cause (potentially major) fluctuations for store owners who have products which have a variable product cost.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Dimace
Jr. Member
 
Total Posts:  28
Joined:  2009-11-22
 

Jonathan is absolutely correct, at present Magento does not store ‘Cost’ data on the order level. This is noted in the product description and is recommended that you run the report frequently and save or print each report for more accurate referencing to past sales data… this constraint will obviously be lifted once Magento utilizes the ‘Cost’ field in their new release, but as mentioned there is no indication of that in the Issue Roadmap so who knows when it will actually be released, could be many months or even longer.

In the mean time many people, certainly myself, find it very useful to produce some form of profit report or advanced sales data even if it is done on a regular basis and then saved for future reference. It literally takes seconds to produce these reports as apposed to hours exporting data into sheets, matching fields and data over various date ranges.

If anybody has any other questions about this tool direct them to this thread, we’ll definitely try and help out any way we can.

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