For more details see how the Custom Reports Builder extension works.
Build an unlimited variety of custom reports based on any available store data. Analyze each aspect of your store performance and build an efficient sales strategy. Arrange all needed columns in the most convenient order. Apply filters, set the time intervals, and visualize the data with charts.
When the extension is installed, please navigate to Amasty → Custom Reports Builder → Amasty Custom Report Builder. Here you can see the Custom Reports grid with the 5 prebuilt reports that we have created for your convenience. Use these reports as they are or flexibly modify any of them according to your needs. Also, here you can create and manage your new custom reports.
To create a new report, please hit the Add New Report button.
Name - Specify the name for your new report, that will be visible in the reports grid.
Main Entity - It is a core entity, on the basis of which the report data will be aggregated. For now, you may choose one of 6 main entities:
Before choosing the main entity, think about the main objectives of your report. For example, if you need some product breakdown and their performance by specific criteria - you should choose the Catalog Product main entity thus building your report from the product. Or if you need to analyze particular sales aspects - you should choose the Order Entity thus building your report from the order.
After you choose the main entity, you will see the option (column) block available for this entity. Each main entity has a specific block of options. For example, the Catalog Product entity has the following options block:
The main block of options (of the same name as the main entity) is highlighted. Also, each main entity has its own main option, which automatically appears in the right column, after you choose the main entity. The main option can't be removed from the report as it is the basic option relatively which the report is being built. But you can use time periods as the main column. Please, check the Use Periods section of this guide to know how to do it.
Also, when you choose the Main Entity, you can see that several Secondary Entities become available.
Secondary Entities are connected with the Main Entity (and other Secondary Entities) with relations. You can see the relations available for each entity at the top of the entity options block.
This way, you can add to your report the options (columns) as from the Main Entity so from the Secondary ones.
To add any of the Available options to the report, just expand the options blocks and drag and drop the options from the required blocks to the Chosen options tab.
When you drop the option to the right column, it becomes disabled at the left one.
Please keep in mind, that you can also link blocks that relate to other entities. As it was mentioned above, you can see the relations available for each entity at the top of the entity options block. This way, you can always understand with which other entities the current entity can be directly related.
For instance: if you choose Catalog Product as the main entity, you can also expand the Order Item block and see Order Entity in the list of connections.
But, you won’t see the Order Entity block of options among other blocks, if you select nothing besides the main entity Catalog Product. To see the Order Entity block of options, you should add at least one option from the Order Item block.
This way, the Order Entity option block appears and its options become available for adding to the report, depending on whether the option is added from the Order Item block (i.e., the Order Item option block is a kind of parent section for the Order Entity option block).
Also note, that if you add several options from a child block and delete the parent block, all child options will be deleted as well.
For example, we’ve added a child option from the Order Item block. We see that the Order Entity options block becomes available. Then we add the option from this Order Entity block.
If we try to delete the option dropped from the Order Item block, we will see the following warning:
We confirm the deletion and see that the option from the Order Entity child block was removed as well.
The block itself also disappeared from the Available options section.
Use periods setting lets you group several report lines into one, if this data relates to the same time period. If you turn on/off the setting, you will see the pop-up:
For example: We have several orders that were placed in different days/weeks/months. If we try to form a report based on periods, it will be displayed in the following way:
This way, we can see the list of products ordered by days/weeks/months/years. We can change the period using the options at the top of the grid.
For example, if you are grouping the data by months, in the Creation Time column you will see the values like:
And if you are grouping the data by years, in the Creation Time column you will see the values like:
Also, you can adjust the time period for your report in the filters toolbar on the report view page.
If you don't want to use periods you may add options with the timestamp type to your report and then set filtering for them separately.
This feature is available for the main entities with the store view scope.
You can also switch between store views on the report view page.
To visualize reports, you can enable the Display Linear Chart option. You will see 2 additional settings: X and Y axes, for which you can add the values from the report data.
After adding several options to the report, you can apply various actions to options, like sorting, filtering, hiding, etc.
This feature is available for the options with the Date (timestamp) type.
After enabling, filtering by dates becomes available for the chosen column with the from/to fields, which are displayed above the report grid on the report view page.
Sorting can be enabled for a single option only. The data can be sorted in ascending and descending order. By default, it is enabled for the main column.
See how it looks on the report view page:
You can activate the feature for any other option and pick the required sorting order (ascending or descending).
For this example, it will have the following look on the report view page:
According to the types of chosen options, different filtering features become available for your report.
Enable the filtering for particular options by hitting the filter icon next to the option name. Then, specify the filtering parameters for these options and click the Apply Filters button. If you want to remove a filter from an option, just click the Cancel.
All applied filters will be displayed on the report view page.
By the way, you can delete applied filters or add new ones on the report view page, but if you reopen the same report, it will be displayed again the way it was saved on the edit report page.
With this functionality, you can add various options to establish a connection between entities while not displaying these options in the report. To hide an option, just click an eye icon next to the option name. The option will not be displayed on the report view page, while the connection between entities will remain.
You can easily delete any option using the appropriate icon. Moreover, you can click Clear All to remove all the options except the main one.
In general, aggregation is the concatenation of multiple lines into one. Our Custom Reports Builder extension automatically aggregates data for more convenient and compact presentation in reports.
For example: We need to display the Order ID and Product Name options. In this case, the Order ID will always have one value, but there can be several Product Names in one order. Thus, we need to aggregate the Product Name column under the Order ID. In this case, we also added the Item ID option for more representativity.
Here we can see, that the highlighted order contains 4 products. Product Names from the same order are aggregated under one Order ID.
Currently, there are 4 types of aggregation available: count (adds the values of several rows), group_concat (concatenates several values and lists them using a comma), sum (sums up numeric values), max (keeps track and returns the maximum value among the numeric values). The type of aggregation for the column is indicated in () next to the column name.
The Custom Reports Builder is capable of analyzing the data from third-party extensions. To use this functionality, you should add a new entity to the module. Let's walk through the process of adding a new entity.
The use of entities in report generating is determined by special configuration files of entities. These files contain all information about entities. This way, the module perceives an entity as a container storing a set of data of the entity itself, its data, and its relationships with other entities.
A column is a data storage for an entity of a certain type. Later, this data can be visualized as a grid column on the view report page.
Relation - defines the linking rules for various entities with each other.
To add an entity, you need to create a configuration file in the module, the entity of which you want to add to the report.
You need to create an XML file in the following directory:<module_root>/etc/ambuilder_entity_scheme. The file name should match the name of the entity (e.g. catalog_product.xml).
The file should describe the entity (name, title, main table), the columns of the entity (name, title, data type, etc.), and its relations.
Please, keep in mind, that it is not necessary to describe all the columns in the XML file. It will be enough to describe only the control ones. Other entity columns will be formed by the Db and Eav adapters.
The file is represented by the amasty_report_builder_entities section, which contains the configuration of one or more entities.
Entity section has a required string parameter “name”. This parameter defines the unique entity key by which the entity data object will be created. The section can also contain two optional boolean parameters “primary” and “eav”.
The “primary” parameter can be true or false. All entities described in XML are divided into two types:
Primary entities can be used as the main report entity. Secondary ones serve for additional filling the report with data. If the primary parameter is not specified explicitly, the entity is considered secondary.
This division is intended to simplify the formation of a request for report data. If it is not needed to define an entity as primary for the report, it is better to define it as secondary.
The “eav” parameter can be true or false. This parameter is used to specify eav entities. For eav entities, the configuration object is additionally filled with data from eav tables.
The “entity” section should contain the following subsections: title, main_table (name of the main table without a prefix), columns, relations. For expandability purposes, finding these sections is not validated in the XML. However, the entity should have a title, its own table, and at least one column.
The “columns” section contains the description of the columns (entity data). The section should include at least one “column” subsection.
Section “relations” contains a description of the relations between entities. The links between entities can be of two types: simple (link through a column) and complex (link through a junction table). The section should have at least one “relation” subsection.
Section “column” contains the required string parameter “name”, which is a unique column key in the entity configuration. Also, the section can have four optional parameters: primary, useForPeriod, frontendModel, eavAttribute.
The primary parameter for the column can be true or false. Defines the main column of the entity. The main column of an entity is always presented in the report. The entity must have the main column. If several columns are specified as main - the first one will be selected as the main one.
UseForPeriod parameter can be true or false. This parameter determines whether the column can be used to generate an entity report using grouping by date periods. This parameter should be specified only for columns of types: date, datetime, and timestamp
The frontendModel parameter accepts a string. This parameter determines the type of HTML element when adding a filter to a column. Possible options: text, select, multiselect, textRange, dateRange
The eavAttribute parameter can be true or false. This parameter indicates that the column is an attribute of the eav entity.
This section can have the following subsections: title, type, aggregation_type, source_model, hidden, options.
“Relation” section should have two required attributes: name and type. The “name” parameter is a unique link key in the entity object. It is also a pointer to the name of the entity with which this entity is connected.
Parameter “type” defines the type of connection (column or table).
A “relation” section of the “column” type must include the following subsections:
A “relation” section of the “table” type should include the following subsections:
After determining the relationships of the new entity with other entities, it is also needed to determine the inverse relationship of these entities with the newly added one. Thus, it is required to add one more (or several) extension files of the existing entities and define the relations between them and the newly added entity. These files should contain the entity and relations sections and describe the connection with the newly added entity. See the example of such a file below.
<?xml version="1.0"?> <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:amasty:module:Amasty_ReportBuilder:etc/entity_scheme.xsd"> <amasty_report_builder_entities> <entity name="(?)" primary="(?)" eav ="(?)"> <title>(?)</title> <main_table>(?)</main_table> <columns> <column name="(?)" primary="(?)" /> <column name="(?)" useForPeriod="(?)"/> <column name="(?)" frontendModel="(?)"/> <column name="(?)" eavAttribute="(?)"/> <column name="(?)"> <hidden>(?)</hidden> <title>(?)</title> <type>(?)</type> <aggregation_type>(?)</aggregation_type> <source_model>(?)</source_model> <options>(?)</options> </column> </columns> <relations> <relation name="(?)" type="(?)"> <column>(?)</column> <reference_column>(?)</reference_column> <relationship_type>(?)</relationship_type> <relation_column>(?)</relation_column> <relation_table>(?)</relation_table> <relation_reference_column>(?)</relation_reference_column> </relation> </relations> </entity> </amasty_report_builder_entities> </config>
<?xml version="1.0"?> <config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:amasty:module:Amasty_ReportBuilder:etc/entity_scheme.xsd"> <amasty_report_builder_entities> <entity name="(?)" eav="(?)" primary="(?)"> <relations> <relation name="(?)" type="(?)"> <column>(?)</column> <reference_column>(?)</reference_column> <relationship_type>(?)</relationship_type> </relation> </relations> </entity> </amasty_report_builder_entities> </config>
Find out how to install the Custom Reports Builder extension for Magento 2 via Composer.