A simple guide to Odata in MYOB Advanced

OData, short for Open Data Protocol, is an open protocol to allow the creation and consumption of RESTful APIs. What this means is that you can output Generic Inquiries (GI) from MYOB Advanced into Excel and view/query the results there.

Why use Odata?

Odata allows users to extract data to Excel or other reporting tools via a direct link to the database. The Odata link maintains a live feed of information through to your spreadsheet, so you can always work with the most up-to-date figures. This data can also be easily formatted and manipulated. This form of reporting replaces traditional methods of data extraction where users export data to Excel to form the basis of reports.

How to expose a GI to Odata in MYOB Advanced

As an example, let us explore how to expose Suppliers to Odata and view the data in Excel.

On the Suppliers screen, select Customisation and then Edit Generic Inquiry. This option is only available if you have the Customisation Role attached to your user. From here you can expose this specific GI to Odata.

Once the GI is available to Odata you can access it by setting up a connection from within Excel.

Establishing OData connections in Excel:

To create the connection back to MYOB Advanced via Odata, navigate to the Data menu within Excel and then select “From Odata Data Feed” from the “From Other Sources” dropdown.

Fill out the Data Connection Wizard with the appropriate details, such as a URL so MYOB Advanced and your login credentials. Excel will then show a list of GIs that have been exposed to Odata. You can make your selection of the GI you are after by ticking the box and then clicking Next.

The next time you open the document in Excel, it will refresh automatically. If it does not, then you may need to re-enter your credentials. You can manually refresh the data when you wish by right-clicking and selecting refresh.

Customising your Odata properties

To edit the properties of the query, select Properties from the Data tab on the Excel ribbon.

By enabling the different options, you can customise how your data will appear in Excel.

• Include Row Numbers – add a column to the left of the data that counts the rows,
• Adjust Column Width – automatically adjust column width to autofit based on data within each column,
• Preserve column sort/filter/layout – preserve any formatting applied to the data each time a refresh occurs.

More advanced Query Properties can be edited by selecting the extra menu next to the Name field.

Further customisations can be enabled by selecting any of the below options:

• Enable background refresh (Recommended) – Enable this option to run the query in the background. Running a query in the background enables you to use Excel while the query runs.
• Refresh every X minutes – Enable this option to enable automatic data refresh at regular time intervals.
• Refresh data when opening the file – Enable this option to automatically refresh external data when you open the workbook.
• Remove data from the external data range before saving the workbook – Enable this option to save the workbook with the query definition but without the external data.

The remaining options are not available for these kinds of queries.  

Power BI

Odata can also be accessed by other Microsoft Suite products, such as Power BI. Power BI is a data visualisation and analytics service. It allows you to create your own reports and dashboards with a simple interface.

Similar to opening a data source in Excel, you can open an Odata source in Power BI and use the data to build dashboard widgets or graphs.

From the Power BI Desktop version, select Get Data, Odata Feed, enter the URL, provide the username and password and then select which Odata feed you wish:

Enter URL:

Select the table you wish:

Do you need even more power in your reporting?

Taking further advantage of the data in your system can create a lasting impact for your team. Various add-on solutions can be integrated into MYOB Advanced to further improve the power of its reporting.

• Velixo is one such add-on that can be seamlessly integrated into MYOB Advanced to increase the reporting power of Excel. Velixo will also maintain a live data flow between MYOB Advanced and Excel. It also adds over 45 purpose-built functions and easy exporting of your reports to email.
• Phocas Software is another add-on solution that provides a cloud-based platform. It can also be integrated into MYOB Advanced to give you a single source of truth for your data. Phocas combines adaptive, ad-hoc reporting with best-of-breed business intelligence to offer predictions and analysis in real time.

If you need to increase the capability of your native MYOB Advanced system, consider integrating either of these connected services.

Use Odata connections to unleash the full potential of Excel by working with your live data directly from MYOB Advanced. If you wish to learn more about Odata or need assistance connecting MYOB Advanced to Microsoft Excel or Power BI, email us at samacs@kilimanjaro-consulting.com or call us at 1300 857 464 (AU) or 0800 436 774 (NZ).