Columns from multiple tables in visual report builder

[jamesuniversal]

Using visual report building I'm wanting to pull in information from both sales_order and sales_order_payment. 

To do this I'm selecting the metrics of base_total_paid and base_total_refunded, and then the 'group by' dimensions of
status, increment_id, ........ etc from sales_order

To finish the report I want to pull in method from sales_order_payment but it does not appear under the Group By options and I can't figure out how to effectively make this join.

I have the report I want in SQL, however it needs to be date filterable. I have tried creating Column Paths from sales_order_payment to sales_order but that didn't seem to work.

Any help appreciated. 

[glo11486ad4f3e]

Hi James,

Although I don't have details of your MBI CID (or merchant name) or the specific report you are trying to create, here is a generic list of check-points.

1. Ensure the necessary calculated columns are being created in Data Warehouse with required column path (between sales_order & sales_order_payment in this case).
   https://docs.magento.com/mbi/data-analyst/data-warehouse-mgr/create-paths-calc-columns.html
2. Use these calculated columns during your new metric creation. Ensure the expected dimension (on the same page) has 'Groupable' as checked so that it starts showing in your Visual Report Builder.
   https://docs.magento.com/mbi/data-user/reports/ess-manage-data-metrics.html
3. Use this Metric now in your Visual Report Builder and check whether the 'Group By' (or Metric Filter based on your requirement) is working
   https://docs.magento.com/mbi/data-user/reports/ess-rpt-build-visual.html

Hope this helps.

Note: Please refer to our Recorded Webinars for more information.

Regards,
MBI Team