How to use data from other modules in reports
In general, extracting data from third-party modules is possible if the required data exists within the database. The Config Builder provides the ability to adjust relations between the native Magento database tables and tables from a 3rd-party or custom extension. Typically, you’ll need to establish a relation between a third-party table and a native Magento table.
A common example is linking a third-party table to the sales_order table. For instance, consider enhancing the 'Orders' report with additional data from the Reward Points extension. Specifically, we aim to add columns showing the points spent and earned for a given order. In the Reward Points extension, this data contains in the mst_rewards_purchase table.
In advanced mode, you can search for a column by its name in the database. However, if you attempt to add a column from a third-party table directly to the report, you will typically encounter an error indicating that the table is not related to the native Magento table.
To resolve this, you need to establish the relation between the tables. Additionally, you must define the table and its columns to make them accessible in the 'Columns' list.
Find the full config to include the "Earn Points" and "Spend Points" columns below:
<config>
<table name='mst_rewards_purchase' label='MST Rewards Purchase'>
<column name='earn_points' label='Earn Points' type='number'/>
<column name='spend_points' label='Spend Points' type='number' />
</table>
<relation name="sales_order-mst_rewards_purchase" type="1n">
<leftTable>mst_rewards_purchase</leftTable>
<rightTable>sales_order</rightTable>
<condition>%2.entity_id=%1.order_id</condition>
</relation>
</config>
Once the relation is set up, the columns from third-party tables can be utilized for custom columns.
Here is the result with columns added to the 'Orders' report:
