Synthetic Columns Config Builder

Sometimes for a Report, you need a special column that can be calculated from existing fields.

Our extension provides you with a Config Builder. It is a tool which allows you to define the so-called Synthetic Fields. They will be added to the Database View section and act like a normal column while actually being calculated on-the-fly.

It is located at Reports -> Config Builder and has a similar menu interface, as that in the Report Builder - divided into two columns. Config Grid is displayed here, which also lists all the defined synthetic fields.

Quick Summary:

How to create a Synthetic Field Column Config

Open either the Report Builder or Config Builder menu, and select the Add New Config action on the right menu section. You will be brought to the Config Workspace:

Config

It is very simple and contains the following fields:

  • Title - the new synthetic field's name (named whatever you wish).
  • XML Config - column definition as XML document.

Example

Quick Example of Synthetic Field Config It defines a new synthetic field which 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>

Here is how this config is built:

  • config - is the document element, e. g. all other tags should be contained inside it.
  • table - defines a table in which the new synthetic field should be added
    • name - name of that table, which should be given without your table prefix. This means that if you have in your database a prefix likemage_ 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 an explicitly set group will fall into the Other group.
    • label - the label on a table that is used in identifying a table.
  • column - defines how a column should be calculated
    • name - the unique name of the column, MySQL-compatible.
    • label - the name that will be displayed on the Report.
    • type - data type that will display its value. The supported types are: money, country, date, qty, number, select, percent, store, str.
    • fields - a 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 where the data type select or country is used. Here you need to enter either the full name or the class which implements method toOptionArray for this column.

After config is created, your column custom_gross_profit will appear in the field Columns in the corresponding section of Database View Pane of Report Builder.

How to adjust relations between tables

The module should determine how to link different tables to build reports from multiple tables.
The connection between two or more tables is called a table relationship.
Using the config builder, you can explicitly set the type of relation the module should use in order to connect two tables.
Below is an example of building a 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>