Overview

  • In this article you will find an example of report building using Excel
  • Bringing the list of invoices from Ocerra to Excel


In this article, we will show how Ocerra integrates with Excel for ad-hoc reports and analytics.


Step-by-step, you will see how to quickly build a list of invoices per supplier or in a date range to match it with your invoice statements.


Let’s start with the blank spreadsheet and connect to Excel using 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 form Ocerra next time you open it.

If you need help building reports like this, please get in touch.