Linking Database Tables in Advanced Reports

Linking Database Tables in Advanced Reports

This is only required if you are managing custom reports. Some fields though available to select from the attributes selection need to be linked together for the report to display properly (eg MCM attributes). In such cases, if you select a field that is not already linked, you will see this error message appear when you try to load the report (depending on the report you copied from already).



Understanding tables

Reports are generated based on a certain table in the database, taken from what report you copied it from originally. If you copied a sales report, it will use the sales_order table as the main table (the same will be if you copied a product or customer report). Some tables are linked together already (such as sales_order and sales_order_item). Each of these tables will have certain attributes. For example, sales_order will have the Order number and Grand Total, where as sales_order_item will have the data about each individual product, such as Product Name and Row Total.

You can see this below in this example how the advanced report shows these:


While searching, you will also be able to see the following using the advanced mode when searching for a column to add.



Here you can see that the table being referenced is the sales_order table, separated by a separator | which is then followed by the attribute (which as listed above is status, status__concat etc). Other tables may not be linked automatically, such as the Omnyfy MCM Vendor Fee Report Admin table.




Linking the tables via Config Builder

In the Advanced report, you can use the Config Builder to link a table's relationship by finding the attributes that link them together via XML code. For example, we need to make a link between the omnyfy_mcm_marketplace_fee_report_admin and the sales_order table.

1. Access the Advanced Reports Config Builder by navigating to Reports > Advanced Reports > Config Builder.



2. The page will load with a grid of any existing configurations / links you have added. Select Add New Config from the top right of the page.



3. This page will include 2 fields.

Field
Notes
Title
Input the title of this configuration.
XML Config
Input the configuration for the XML Configuration code. See further information in the next section of how to create the XML Configurations.

4. You can select Save from the top right, once you have completed this and check the report once again to see if it worked.




Creating the XML Config code

Here we will need to have a reference for the tables. Using the same example of the omnyfy_mcm_marketplace_fee_report_admin and the sales_order table linking - in this case we will be linking it with sales_order_item (since the omnyfy_mcm_marketplace_fee_report_admin attributes match the sales_order_item rather than sales_order - and sales_order_item already links to sales_order so it will still match fine).

XML Config example:

  2. <config>
  3. <table name='omnyfy_mcm_marketplace_fee_report_admin' label='Omnyfy Marketplace Fee' />
  4.         <relation name="omnyfy_mcm_marketplace_fee_report_admin-sales_order_item" type="number">
  5.                 <leftTable>omnyfy_mcm_marketplace_fee_report_admin</leftTable>
  6.                 <rightTable>sales_order_item</rightTable>
  7.                 <condition>%2.item_id=%1.item_id</condition>
  8.         </relation>
  9. </config>

The config must start and end with <config> start and </config> at the end. The other parameters will be included in between these. Some of these parameters are not required, however you can see below what it supports.

Parameter
Notes
config
This 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 like mage_ 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
This is the label on a table that is used in identifying a table.
relation
This determines how to link different tables to build reports that includes multiple tables. The connection between the two or more tables is called a table relationship.
column
Defines how a column should be calculated
-- name
Input the unique name of the column, MySQL-compatible.
-- label
Input 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
This is 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.
-- 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.

  1. In this case, we are referencing the omnyfy_mcm_marketplace_fee_report_admin table in <table name='omnyfy_mcm_marketplace_fee_report_admin' label='Omnyfy Marketplace Fee' />.
  2. The relationship name is based on the two tables we are linking omnyfy_mcm_marketplace_fee_report_admin-sales_order_item and the type of values in it (which in this case is a number). This is referenced in <relation name="omnyfy_mcm_marketplace_fee_report_admin-sales_order_item" type="number">.
  3. We are referencing that the left table is the omnyfy_mcm_marketplace_fee_report_admin table, and the right table is sales_order_item. This is referenced in <leftTable>omnyfy_mcm_marketplace_fee_report_admin</leftTable> and <rightTable>sales_order_item</rightTable>.
  4. The condition using the following expression is that based on the right table (%2), the attribute "item ID" (.item_id) matches the left table (%1) attribute "item ID" (.item_id). This is referenced in <condition>%2.item_id=%1.item_id</condition>.
Once this is saved, you will then be able to reference from the omnyfy_mcm_marketplace_fee_report_admin table in any sales report.
NOTE: Currently, some flat tables are not supported in the reports. You will be able to identify which is a flat table if it contains "flat" in the table name.

If you have any questions or issues using this, please contact Omnyfy Support.

    • Related Articles

    • Managing Filters and Attributes for Custom Reports

      With custom reports, you can change the dimensions, columns and filters that you'd like to use. This view will open up when you are editing a report, and select Manage on either Dimensions or Columns. You can also see this when you choose Edit ...

    • Reports - Overview

      Omnyfy comes with standard e-commerce reporting out of the box, for Marketplace Owners. Vendors currently do not have access to this, and can see their stats through the dashboard (depending what you have provided as available). Accessing Reports Log ...

    • Creating a custom report

      While there are a number of reports that are already available, you may also want to create your own custom reports that you can pull up with custom columns, filters and specific graphs. Making a copy of a report To create a new report, you will have ...

    • Customising your custom report

      You can only customise your copied reports. Pre-existing reports cannot be modified. On your selected report that you've copied, go to the far right side and click button with the 3 dots. Choose Edit report. On the left you will see a variety of ...