Instructions in this article require advanced Excel skills to achieve desired reporting results.

Alternatively, please get in touch, and we'll configure the connection for you as part of our hourly-based services.


In this article, we will provide you with an example of how Ocerra integrates with Excel for ad-hoc reports and analytics.By following the below instruction, you should create a connection of your Ocerra account with Excel and bring the list of invoices to Excel. You can then build a list of invoices per supplier or in a date range to match it with your invoice statements.


Let's get started

Let’s start with the blank spreadsheet and connect to Excel using the OData interface. 

Note: 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.


 Step 1:


Step 2

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. 


Step 3

Now you can explore the OData interface for Ocerra, and we’re planning to use VoucherHeaders table to get the list of invoices:  


Step 4

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.  


Step 5

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:  



Step 6

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:

  

Step 7

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 from Ocerra the next time you open it.