Update February 5th, 2017: Alas, the TryAcumatica.com links don’t work anymore since that site has been taken down. So, I removed the links.
Last Week
Last week we took a look at how to build a simple Generic Inquiry screen in Acumatica.
Then, at the very end, we checked a box to enable it for OData.
Checking that box allows us to connect to the data in our Generic Inquiry screen using other applications, including the world’s most popular business intelligence application: Microsoft Excel.
Generic Inquiry screens are great inside of Acumatica.
But they are much more powerful when enabled for OData.
The OData connection still passes through Acumatica security so it’s just as safe as if you were accessing the data in a regular Acumatica screen.
This Week
This week we’re going to look at how to connect to an OData-enabled Generic Inquiry screen from Microsoft Excel.
All you need to do is add /OData to the end of the URL that you use to access Acumatica, like this:
If you have more than one company in Acumatica, then you need to add the company name after /OData. For example, if your company name is “Company” then it would look like this:
The OData connection point in Acumatica returns an XML file if you connect to it with your web-browser. But programs like Excel (that understand OData) know how to present this XML file in a meaningful way.
From within Excel, you just need to go to Data -> From Other Sources -> From OData Data Feed like this:
It’s significant to note that this is already available in Excel without the need to install any 3rd-party software.
Once you bring your Acumatica data into a program like Excel, then you can do lots of interesting things with it.
We will just display the data in an Excel table this week, but we will do more with it in future weeks.
The Excel table that we will create is an intelligent table because it can be refreshed with live data from Acumatica.
See the video below to learn how to do this.
Note: In the video I am using Excel 2013, but it should also work in Excel 2010 and Excel 2016 (coming soon). It just might look slightly different.