Config Builder

Predefined reports provided by the extension and Report Builder can cover most cases for analyzing sales in a simple Magento store. However, the real stores and types of items they sell may require more advanced and flexible tools to analyze different parts of their sales. Also, real stores can have a lot of 3rd-party extensions with their own database tables that contain important data for sales/marketing/inventory analytics.

The Config Builder is exactly the tool for such purposes. It is located in Reports -> Advanced Reports -> Config Builder.

The Config Builder provides the ability to create Synthetic columns with complex data calculation/aggregation and to adjust relations between the native Magento database tables and tables from a 3rd-party or custom extension so the data in reports will be as accurate as possible.

Config Builder Overview

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.

The Structure of the XML Config

config

This is the root element, e.g. all other tags should be contained inside it.
There are only 3 possible nodes allowed inside the config element - <table />, <eavTable />, <relation />.

table

This element must be used for registering tables so the extension will be able to retrieve data from them. This element also can be used for already defined tables to assign custom synthetic columns to them.
Allowed child elements: <pk />, <fk />, <column />.
Attributes:

Attribute	Description	Required	Default
name	name of the table. The name should be specified without the table prefix. This means that if you have in your database a prefix like `mage_` and real table name `mage_sales_order`, you need to enter here just `sales_order`	required	none
group	name of a group, under which this table appears in the Report Builder. Default values: `Sales`, `Products`, `Customers`, `Categories`, `Other`.	optional	`Other`
label	the label of a table that is used in identifying a table.	optional	the value of the name attribute

eavTable

This element must be used for registering EAV tables so the extension will be able to retrieve data from corresponding EAV entities including their attributes. This element SHOULD NOT be used for native EAV tables like catalog_product_entity, catalog_category_entity, customer_entity, etc as these EAV entities are already registered in the extension.
Allowed child elements: <pk />, <fk />, <column />.
Attributes:

Attribute	Description	Required	Default
name	name of the table. The name should be specified without the table prefix. This means that if you have in your database a prefix like `mage_` and real table name `mage_sales_order`, you need to enter here just `sales_order`	required	none
group	name of a group, under which this table appears in the Report Builder. Default values: `Sales`, `Products`, `Customers`, `Categories`, `Other`.	optional	`Other`
label	the label of a table that is used in identifying a table.	optional	the value of the name attribute
type	the type code of the EAV entity. The code can be found in the `entity_type_code` column in the `eav_entity_type` table	required	none

pk

The primary key of the table. This element is optional and SHOULD NOT be used on native tables.
Attributes:

Attribute	Description	Required	Default
name	the name of the primary key column	required	none
label	the labels for the column	optional	the value of the name attribute

fk

The foreign key is in the table. This element is optional and SHOULD NOT be used on native tables.
Attributes:

Attribute	Description	Required	Default
name	the name of the foreign key column	required	none
label	the labels for the column	optional	the value of the name attribute
table	the relation table name without prefix	required	none

column

This element is responsible for defining the column. This element is mostly used for synthetic columns with custom calculations.
Attributes:

Attribute	Description	Required	Default
name	the name of the primary key column	required	none
label	the labels for the column	optional	the value of the name attribute
type	data type of the column's value. The type defines how the value of the column will be displayed and possible aggregations for the column. The extension will automatically add columns with aggregations to the Column Chooser. Supported types: `money`, `country`, `date`, `qty`, `number`, `select`, `percent`, `store`, `html`. More information about data types you can check here	optional	`html`
fields	comma-separated list of fields, which will be used in calculations	optional	if omitted the value from the name field will be used
expr	this attribute contains MySQL expression to calculate field value. You can use placeholders, where `%1` corresponds to the first field in the previous attribute, `%2` - to the second, and so on.	optional	`%1`
options	non-mandatory attribute where the data type `select` or `country` is used. Here you need to enter the full name or the class that implements the method `toOptionArray` for this column.	optional	none
tables	comma-separated list of table names (without prefix) for the ability to include columns from other tables inside the expr field	optional	none

Column types:

Type	Description	Aggregators
html	Simple string, Output values as it is	`JOIN`
number	Number rounded to 2 decimal points	`SUM`, `AVG`
qty	Integer value, mostly used for ID fields	`COUNT`
percent	Value formatted as a percentage with `%` symbol, rounded to 2 decimal points	`AVG`
money	Currency format, displays numbers with currency symbol, rounded to 2 decimal points. If the report is filtered by one particular store the value will be displayed in the default currency for that store. Otherwise, the default store currency will be used	`SUM`, `AVG`
date	Date format, Depending on the aggregation type the output may vary	`HOUR`, `DAY`, `DAY OF WEEK`, `MONTH`, `QUARTER`, `YEAR`
select	The type for the field with predefined variations for values. With this type, the `options` attribute SHOULD be used in the `column` element	`JOIN`
country	Similar to the `select` type. If the value of the column is in the ISO 3166-1 alpha-2 format then class `Mirasvit\Report\Config\Source\Directory\Country` can be used as the value of the `options` attribute in the `column` element	`JOIN`
store	Similar to the `select` type but store labels will be retrieved by the extension so the `options` attribute SHOULD NOT be used along with this data type	none

relation

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.
The < relation /> element is responsible for defining relations between tables.
Required child elements: <leftTable />, <rightTable />, <condition />.
Attributes:

Attribute	Description	Required	Default
name	the name of the relation. the name can be any string but it is recommended to use the following format: `leftTable-rightTable`	required	none
type	the type of the relation between tables. Possible values: `11` - one to one; `1n` - one to many.	required	none

leftTable, rightTable

This element is responsible for defining tables in the relation in the next format: <leftTable>sales_order_item</leftTable>. The table name should be specified without the table prefix. These elements don't have attributes.

condition

This element is responsible for defining JOIN conditions between leftTable and rightTable using MySQL syntax. You can use placeholders %1 and %2 where %1 is a placeholder for the letTable and %2 - for the rightTable

Config Examples

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>