How to extract Merchanter data into Excel
OData extraction to Excel allows data to be for reporting Sales, Purchases, Stock and more.
Written by Ian Oldrey
Updated at January 3rd, 2023
Table of Contents
OData extract into Excel
Odata is a common data standard that enables trading data from Merchanter to be used in other systems, especially reporting and analysis software such as Excel.
Available to User types
| Ops | Basic | Pro | Super | Prime |
|
|
 |
|
 |
How to import OData into Excel*
* Other applications also use OData
OData is commonly available in other reporting, analytics and workflow tools. These instructions are for Excel, but similar steps can be used with other applications.
- Go to the Data tab in Excel
Have you imported OData from Merchanter to Excel before? (from this PC/device)
Click on ‘Recent Sources’ button in the ribbon menu
Select the Merchanter system url from the list and proceed to selecting the OData tables
In the Data ribbon menu click:
- ‘Get Data’
- ‘From Other Sources’
- ‘From OData Feed’
- Enter your system url and type on the end /odata/Analysis for example – https://unitrade400-<mysystem>.mendixcloud.com/odata/Analysis
- When asked for security protocols select ‘Basic’ from the left-hand menu
- Enter your Username and Password for Merchanter
- This will take you to the OData table list
Selecting OData tables
There are a number of OData reporting tables available from Merchanter. Select the table(s) that you require for your reporting/analysis
Decoded reporting tables
The OData tables from Merchanter have been designed that the majority of reporting requirements can be created from a single table. The associated entries relating to that table's key information has already been decoded to speed up accessing that information. For example the Sales Order table shows sales order lines, with the customer name already decoded, so there is no need to also bring in the Customer table. If you need more detailed information about the customer, like sales orders for customers who are over their credit limit, then you can bring in sales orders and customers and link the tables together.
- Load - use to bring the entire table(s) into Excel
- Transform Data - opens the tables in Power Query editor so you can transform, filter and add custom columns prior to importing into Excel.
Once the data is loaded into Excel you can analyse the data as a table, or by creating Pivot Tables, Pivot Charts and more