Ocerra is a modern accounts payable automation software that come with many perks out of the box. Easy integration with other systems was the main driver behind the Ocerra architecture. In this blog post, we will show how Ocerra integrates with Excel for ad-hoc reports and analytics.
Sometimes you just need a quick list of invoices per supplier or in a date range to match it with your invoice statements. For this reason, we’re going to be using the Excel to pull invoices per supplier for the last month.
Let’s start with the blank spreadsheet and connect to Excel using OData interface. The Ocerra supports multi-company scenarios, to make multi-company reports you can use different endpoints: https://app.ocerra.com/odata1, https://app.ocerra.com/odata2 and so on... Up to 6 companies.
On the next screen, you can choose to connect to https://app.ocerra.com/odata1 using the Basic authentication. When you select the level, pick the full path instead of a root domain only.
Now you can explore the OData interface for Ocerra, and we’re planning to use VoucherHeaders table to get the list of invoices:
To make our data look pretty, we’re going to click Transform Data and remove columns that we don’t need. Here is the list of columns we’re planning to use: Number, FcNet, FcTax, FcGross, Vendor.Name, Currency.Code. You can add more if you need it.
On the next step, we’re going to expand our other tables: Vendor, Purchase Order, Currency to see the actual value in our Excel result. Select the single “Code” field only and click OK.
I did the same for other fields, to see values instead of a generic ‘Record’ label on the table:
Now is the right time to rename some of these columns to make it more user friendly, apply sorting and filter invoices. You can use relative filters so it will be much easier to open this Excel in a month and just refresh the data to see up to date values:
Now, after applying filtering and sorting you can click “Close & Load” button from the left top corner to see your result in Excel:
This excel spreadsheet is updatable, and it will automatically read invoices form Ocerra next time you open it.
If you want to learn more about Ocerra, subscribe to our blog. Using the modern AP Automation tools, you can get more insight on the money spent in your company. This will improve your cash flow and put your company in a better place than your competitors.