Config builder
This section provides an overview of the Config Builder and explains the structure of the XML configuration.
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:
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 |
| 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