Magento自定义报告：从数据库中获取数据

Question

I need some help.

I created a custom report in Magento. Now I want to list all products wich have been ordered in a month in my grid. In my report are the following columns: SKU, Name, Ordered Quantity and Base Cost.

In the column "Ordered Quantity" I want to show how often a product has been ordered. In the column "Base Cost" I want to show the total base costs (ordered quantity * base cost).

With the following code I get so correct product names and skus. The other columns are not correct.

Can someone help me?

$this->getSelect()->reset()
        ->from(
            array('order_items' => $this->getTable('sales/order_item')),
            array(
                'ordered_qty' => 'SUM(order_items.qty_ordered)',
                'order_items_name' => 'order_items.name',
                'base_cost' => 'SUM(order_items.base_cost)',
                'sku' => 'order_items.sku'
            ))
        ->where("created_at BETWEEN '".$from."' AND '".$to."'")
        ->where('parent_item_id IS NULL')
        ->group('order_items.product_id')
        ->having('SUM(order_items.qty_ordered) > ?', 0)
        ->order(
            array(
                'SUM(order_items.qty_ordered) DESC'
            ));

Answer 1

To outputting the raw SQL query see Output raw SQL query from Magento collection

Format field, you can use price or currency

see http://code007.wordpress.com/2012/07/16/grid-column-types-in-magento/

  $this->addColumn('some_column_id', array(
          'header' => Mage::helper('core')->__('Some column name'),
          'index' => 'some_column_index',
          'type' => '???',
  ));

Types

• action
• checkbox
• concat
• country
• currency
• date
• datetime
• input
• interface
• ip
• longtext
• massaction
• number
• options
• price
• radio
• select
• store
• text
• theme
• wrapline

See /app/code/core/Mage/Adminhtml/Block/Widget/Grid/Column/Renderer folder.

To make your own grid types see http://mydons.com/how-to-create-custom-column-renderer-in-magento-grid/.