Sometimes for Report you need a special column, that can be calculated from existing fields.
Our extension provides you with Config Builder. It is a tool, which allows you to define so-called Synthetic Fields. They will be added to Database View section and will act as a normal column, except of actually being calculated on-the-fly.
It is located at Reports -> Config Builder, and has similar menu interface, as in Report Builder - divided into two columns. Here is also displayed Config Grid, which also lists all defined here synthetic fields.
Open either Report Builder or Config Builder menu, and select at right menu section Add New Config action. You will be brought to the Config Workspace:
It is very simple, and contains the following fields:
<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>
Here is how this config built:
mage_and real table name
mage_sales_order, you need to enter here just
%1corresponds to the first field in previous attribute,
%2- to second and so on.
toOptionArrayfor this column.
After config is created, your column custom_gross_profit will appear in the field Columns in corresponding section of Database View Pane of Report Builder.
In order to build a report based on multiple tables the module should determine how to connect different tables together.
This connection between two or more tables is called table relationship.
Using config builder you can explicitly set what type of relation the module should use to connect two tables.
Below is an example of building the relationship between sales_order and sales_order_item tables.
<config> <relation name="sales_order-sales_order_item" type="n1"> <leftTable>sales_order</leftTable> <rightTable>sales_order_item</rightTable> <condition>%1.entity_id=%2.order_id AND %2.parent_item_id IS NULL</condition> </relation> </config>