Skip to main content

Examples of configs

On this page you will find examples of configurations created with the Config builder. Use these common config examples as a guide for setting up your custom columns and table relationships.

Simple synthetic field config​

A synthetic field that calculates the Gross Profit on the fly based on the actual invoiced orders.

<config>
  <table name="sales_order" group="Sales" label="Orders">
      <column name="custom_gross_profit" 
              label="Custom Gross Profit"
              fields="base_subtotal_invoiced,base_total_invoiced_cost,base_total_refunded,base_to_global_rate" 
              type="money"
              expr="(%1 - IFNULL(%2, 0) - IFNULL(%3, 0)) * %4" />
  </table>
</config>

Multi-table synthetic field​

A synthetic field that calculates the Total (row total) for items in the cart. This config is a part of the already defined config for the table qoute_item and is given here only as an example.

<config>
    <table name="quote_item" label="Quote Items" group="Sales">
        <column name="row_total" 
                label="Total" 
                fields="base_row_total" 
                type="money" 
                expr="%1 * IF(quote.base_to_global_rate > 0, quote.base_to_global_rate, 1)" 
                tables="quote"/>
    </table>
</config>

Simple relation config​

The example of building a relationship between sales_order and sales_order_item tables.

<config>
    <relation name="sales_order-sales_order_item" type="1n">
        <leftTable>sales_order_item</leftTable>
        <rightTable>sales_order</rightTable>
        <condition>%2.entity_id=%1.order_id AND %1.parent_item_id IS NULL</condition>
    </relation>
</config>

Config for shipment date per item​

The config with relations and synthetic column to display the shipment date for each item in the order even if items from the order were shipped separately. At first, the sales_shipment_item table needs to be registered in the extension. Then necessary relations are adjusted to properly connect items in the cart with shipment tables. After the Shipment Date column is defined in the sales_shipment table (to give a self-explanatory label for the column, this part is optional).

<config>
    <table name="sales_shipment_item" group="Sales" label="Shipment Item">
        <pk name="entity_id"/>
        <column name="parent_id" label="Shipment Id"/>
        <column name="order_item_id" label="Order Item Id"/>
    </table>
  
    <relation name="sales_order_item-sales_shipment_item" type="11">
        <leftTable>sales_order_item</leftTable>
        <rightTable>sales_shipment_item</rightTable>
        <condition>%1.item_id=%2.order_item_id</condition>
    </relation>
  
    <relation name="sales_shipment-sales_shipment_item" type="11">
        <leftTable>sales_shipment</leftTable>
        <rightTable>sales_shipment_item</rightTable>
        <condition>%1.entity_id=%2.parent_id</condition>
    </relation>
  
    <table name="sales_shipment" group="Sales" label="Shipment">
        <column name="shipped_at" label="Shipment Date" fields="created_at" type="date"/>
    </table>
</config>

EavTable config example​

This config defines the EAV entity table and automatically adds columns for all attributes related to this entity. This example includes the primary key field, the foreign key to connect customer_entity table with customer_address_entity table (in this case, another EAV entity), and the column of the type select for Customer Group.
This config is already defined in the extension and given here only as an example.

<config>
    <eavTable name="customer_entity" label="Customers" group="Customers" type="customer">
        <pk name="entity_id" label="Customer ID"/>
        <fk name="default_billing" table="customer_address_entity"/>
    
        <column name="email" label="Email"/>
        <column name="group_id" 
                label="Group" 
                type="select" 
                options="Mirasvit\Report\Config\Source\Customer\Group"/>
    </eavTable>
</config>

Example for active special price column​

This config creates a column that displays active product special prices for the current date. This config can help build the report that displays all products with active special prices.

<config>
    <table name="catalog_product_entity">
        <column name="active_special_price" 
                label="Active Special Price" 
                fields="special_price,special_from_date,special_to_date" 
                type="money" 
                expr="IF(DATE(IFNULL(%2, 0)) &lt; DATE(NOW()) AND DATE(IFNULL(%3, NOW())) &gt;= DATE(NOW()), IFNULL(%1, 0), 0)" />
    </table>
</config>