Synthetic Columns Config Builder

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.

Quick Summary:

How to create Synthetic Field Column Config

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:

Config

It is very simple, and contains the following fields:

  • Title - sensical name of your new synthetic field (can be named as you wish).
  • XML Config - column definition as XML document.

Example

Quick Example of Synthetic Field Config It defines new synthetic field, which on-the-fly calculates Gross Profit, based in actually 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>

Here is how this config built:

  • config - is the document element, e. q. all other tags should be contained inside it.
  • table - defines a table, where new synthetic field should be added
    • name - name of that table, which should be given without your table prefix. So, if you have in your database prefix mage_ and real table name mage_sales_order, you need to enter here just sales_order.
    • group - name of a group, under which this table appears in the Report Builder. Default values: Sales, Products, Customers, Categories, Other. Tables without explicitly set group - fall into the Other group.
    • label - label of a table, used to identify a table.
  • column - defines, how column should be calculated
    • name - unique name of the column, MySQL-compatible.
    • label - sensical name, which will be displayed in Report.
    • type - data type for displayed value. Supported are: money, country, date, qty, number, select, percent, store, str.
    • fields - comma-separated list of fields, which will be used in calculations.
    • expr - this attribute contains MySQL expression to calculate field value. You can use placeholders, where %1 corresponds to the first field in previous attribute, %2 - to second and so on.
    • options - non-mandatory attribute, when data type select or country is used. Here you need to enter full name of a class, which implements method toOptionArray for 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.

How to adjust relations between tables

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.

Example

    <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>