7. Exporting to Excel vs. OData - Summit 2019 Session Examples
This post shows you how to create one of the examples from my presentation at Acumatica Summit 2019 in Houston, entitled C103: Getting Actionable Information with Self-Service Reports & Dashboards (click here for all of the examples).
In this Example #7, I'd like to illustrate the difference between Exporting to Excel from a Generic Inquiry and connecting a Generic Inquiry directly to Excel using OData.
I'm a HUGE Excel fan and I've written a number of posts about Excel in the past, including when Acumatica first connected to Excel with OData back in April 2015 (click here) and Excel Online, including a pretty nerdy cool YouTube video about Excel (click here).
What I like about Excel is that everyone already has it and it does a great job of handing data.
In this example I'm using Excel 2016 which has some BIG improvements over previous versions of Excel, namely that it includes Power BI out-of-the-box (and for free), but more on Power BI in Example #8. If you are on a version of Excel prior to Excel 2016, you can install some free add-ins from Microsoft to follow the steps in this post, but I won't be able to cover those add-ins here. Hopefully you are running Excel 2016. If you are, then you are good to go.
First lets talk about exporting to Excel. Yes, you can export the data from any Generic Inquiry to Excel. Just click the Export to Excel button on the top of the screen:
Acumatica will create an Excel file for you to download. And it's pretty clean with all the data on one Worksheet, then the Parameters on another Worksheet:
It used to be that Acumatica put a bunch of stuff in the rows above the data which was annoying and you would have to delete the rows manually. When you find something annoying in Acumatica, it's time to create an idea on https://feedback.acumatica.com/ which I did in this case by creating an idea called Generic Inquiry - Export To Excel - Header Fields and Parameters (click here). Thankfully, Vladimir Panchenko (the mastermind behind much of the Acumatica reporting strategy), got it implemented quickly and that's why the two screenshots above look so clean.
You can also prevent a user from exporting to Excel thanks to this feature request (click here) which is useful for extra sensitive data like customer lists.
But there is a darkside to exporting to Excel. The trouble is that 99% of the time you wind up doing something to the data. You might apply some filters, remove some columns, add some calculated columns, etc. And this is all manual work.
So, every time you want updated data, you have to manually export the data and manually make all of your changes. What a pain. It's time-consuming and prone to error.
The other trouble with this method is that you store the data in the spreadsheet, making your Pivot Table options limited. More on that when we get to Power Bi in Example #8.
So let's use OData instead of manually exporting to Excel.
In this Example #7, I'm going to bring the data into Excel from Acumatica using OData. And I'm going to bring the data into what is called the Data Model in Excel (which is actually just Power BI behind-the-scenes).
1. The main difference is that we don't start with Acumatica and push the data into Excel. We start in Excel and pull the data from Acumatica. First though, we have to make the Generic Inquiries that we are going to use available via OData. I'm using two in this example (SO-SalesOrder and AR-Customers). Make sure to check the Expose via OData box for both of them:
2. Then open Excel and go to Data -> Get Data -> From Other Sources -> From OData Feed:
3. Excel knows how to talk to Acumatica via OData out-of-the-box. You don't have to install anything special. Just put in the OData URL which is your regular Acumatica URL with /odata at the end. Or, if you have multiple Tenants in your Acumatica Instance, /odata/TenantName. My Acumatica Instance is at http://localhost/AcumaticaDemo and my Tenant is Summit2019 so my OData URL is:
4. Excel then needs to know how to authenticate to Acumatica so you click Basic on the left-hand side and put in your Acumatica Username and Password. Note that I'm using my local computer over http, but you would want to make sure that you are using https in a Production environment. Excel is authenticating the same way that you authenticate when you login to Acumatica using your web browser. It's just as secure and you are communicating over the same https protocol as you use with your web browser. This is a huge advantage over traditional direct database access methods where you had to install a VPN or open a port on the Firewall. With Acumatica, as long as you can get to Acumatica in your web browser, you know that you can get to the data using Excel.
Note: Excel will remember your credentials on your computer, but if you take the Excel file to another computer, Excel will ask you for your credentials again. Which makes sense doesn't it?
5. Once you click Connect in the screenshot above, Excel will authenticate and then show you the Generic Inquiries that have been exposed via OData and that your user has access to. We can click Select multiple items and choose our two Generic Inquiries: AR-Customers and SO-SalesOrder. Then click Edit because we aren't quite ready to load the data yet.
6. What opens next is a separate window called Power Query. Remember all that data manipulation that you used to have to do manually 99% of the time? Well, now you can do it all using Power Query. You can delete columns, apply filters, remove rows, pivot the data, etc. You can get really advanced with this. Power Query is really nice because everything you do gets added as a breadcrumb to the APPLIED STEPS area on the right-hand side. You can click a previous breadcrumb to see the data as it looked at that step, move breadcrumbs around, or even delete a breadcrumb to delete that data transformation step. It's very sophisticated, yet still very intuitive.
7. Everything that you do gets created in code in a language called M. Yet, that's right, the letter M. M Language. If you want to get really advanced, beyond what you can do with the graphical ribbon, you can edit the M code directly by going to View -> Advanced Editor. I like to think of this as modern VBA code.
8. If you want to learn more about Power Query, just go to Amazon and search for "Power Query". There are a lot of great books out there. Also, I won't explain the data transformation steps that I took because I'm going to provide a link to my Excel file at the bottom of this post so you can download it. For now, let's just load the data into Excel by going to Home -> Close & Load -> Close & Load To... (make sure you hit the down arrow, not the Close & Load button)
9. Traditionally, you would just load the data into the spreadsheet. Here is where we're going to use the Data Model (aka Power BI). Choose Only Create Connection and check Add this data to the Data Model:
10. You will see Excel doing some thinking and then it will pop open the two queries that we just created. You can always go back in and change them later.
11. Notice that the data is not in the spreadsheet. That's because it's hidden in the Data Model. Here is where you version of Excel 2016 does matter. You may or may not have access to the Power Pivot ribbon. Clicking Power Pivot -> Manage will allow you to view the Data Model. If you don't have this, fear not. You can still interact with the Data Model. I'm going to assume that you don't have this for this example.
12. We can now start creating Pivot Tables and Pivot Charts on our Data Model. Note that if you want to choose data from multiple data sources (SO-SalesOrder and AR-Customers in this case), you have to create relationships between them. Assuming you don't have the Power Pivot ribbon, you can do this by clicking into a Pivot Table, then going to Analyze -> Relationships on the Ribbon:
13. I created the following relationship. Now I can choose fields from both Generic Inquiries and put them together in one Pivot Table.
14. Before I include a link to my Excel file, I will explain each one of the objects. Skip the 1st object for now, the 2nd and 3rd objects are basically the same thing we did in Example #2, but now we're doing it in Excel. Both a Pivot Table and a Pivot Chart:
15. The next object puts Customer Class on the columns which comes from the AR-Customers Generic Inquiry (that's why I needed to create the Relationship):
16. The next object puts Customer Class underneath Order Size on the rows. And you get the +/- Expand/Collapse buttons like Excel has always had but that are now available in Acumatica 2019 R1 Pivot Tables:
17. Now, back to the first object which is something called a Slicer. The Slicer is basically a filter. There are two types of Slicers: Slicer and Timeline. Timeline can be used for data composed of Dates. I created a Timeline Slicer by going to Insert -> Filters -> Timeline:
18. Once you create a Slicer, you can decide which objects it will filter by right-clicking and choosing Report Connections...
19. Just check the objects that you want the Slicer to filter:
Ok, even though I'm a HUGE Excel fan, I'm getting tired of typing. Here is the Excel file that I used in this Example #7. Feel free to download it:
When open the Excel file, you need to connect to your Acumatica Instance. Go to Data -> Queries & Connections, right-click on one of the Queries on the right-hand side, and choose Edit:
For each of the 2 Queries on the left-hand side, click the Source breadcrumb and modify the URL in the formula bar on the top to match your Acumatica Instance URL. Then click the Close & Load button (no down arrow this time).
Note: If you don't see the formula bar above, just check the View -> Formula Bar checkbox:
Another BIG advantage of using OData is that now the entire report with everything that you built (even across multiple Worksheets) can be refreshed simply by clicking the Data -> Refresh All button. Think about that. What might have taken you hours to refresh manually with the Export to Excel option can now be accomplished with the click of a button.
Want to make it even better? What about automatically refreshing all of your Excel files automatically at night which you're sleeping so they are ready to by analyzed when you get in the next morning. There is a tool called Power Update which is free to try on one Excel file or a one-time $500 fee for refreshing unlimited Excel files. To learn about Power Update:
Lastly, if you automatically refresh your reports and have Power Update push them to a OneDrive or SharePoint folder, then people can access them using their web browser and Excel Online. You can even embed Excel Online back inside Acumatica. For more on that idea, check this out:
Want to join the conversation? Feel free to add a Post below to this Topic to continue the conversation.
Interested in joining a Local Acumatica User Group? Click here for more info