Sales by Category in Magento?

We have a category called "Clearance" on our Magento website. Ideally it would be nice to be able to get a report of all orders sold in that category.

Is there anyway I can join the orders collection with the pr开发者_C百科oducts collection and filter by category id?

Here is a similar thread: http://www.magentocommerce.com/boards/viewthread/44296/

Any ideas, or am I crazy? It doesn't need to be pretty.

All order data in Magento is product-specific. It doesn't store anything about what category the products are in. So, as you have found out, there are no reports available in Magento for that sort of thing. It makes it challenging as well because of the fact that products can be in multiple categories. Let's say you have a product that is in 'Clearance' and another category. How can you know which category the product was purchased from? (You can't).

A dirty way to do it would be to sift through all of the orders, and on each product, check to see if it belongs to the 'Clearance' category, as you mentioned with the Order Collection.

Unfortunately, this gets complicated rather quickly, and the effort may really not be worth the time.

Here's how I got sales totals (i.e. revenue) by category. It may not suit you...

First off, I created a drop-down attribute called reportingcategory - and created a list of valid values for it. Then I assigned the correct value to each product in the store. Using an attribute in this way gets around the problem that a product can belong to more than one 'real' category. For reporting, you only ever want it to be linked to one category.

Then I used Aheadworks Advanced Reports extension with the Sales By Manufacturer unit added. I made a copy of this reporting unit and changed all references to manufacturer to be reportingcategory.

Bob's your Uncle, I now have a sales revenue by category report.

I'm still working on how to get an itemised list of products sold by reporting category....

:(

Hope the above helps someone

I'm building a system that overrides the Mage_Checkout_OnepageController's function saveOrderAction may get me what I need.