Reporting API

You can connect to your Zahara data in a spreadsheet or your favourite BI tool like PowerBI. The data is updated daily, around 4am GMT,  and allows you to create reports or pivots of your data saving you from logging into Zahara and running off reports. You can access your Zahara data in the reporting API. This might sound daunting but it’s actually very straight forward and Excel makes it relatively easy to access your data, transform it in anyway you choose and then use it easily from that point onwards. 

The link below will take you to the API page where you can see all of the fields that are made available. The data guys will know this as a swagger file: 

https://data-api.myzahara.net/swagger/index.html

The data made available to you is: 

• Line items 
• Orders
• Invoices 
• Budgets
• Suppliers
• Projects
• Dimension tables like Divisions, Nominal codes, Cost Codes, Currencies. 

To use the data api, you need Zahara to enable your tenancy. You can discuss this with customer success or sales, as the functionality is premium and chargeable based on how many line items you have stored.  The starting price is  £40 per month for 6000 line items. Once enabled your data will be made available and updated once a day. There are two variables you need. 

1. Your tenancy ID – that’s usually hidden from you but we will provide it to you 
2. Your tenancy api key – this is available from the Admin menu of Zahara and then choosing Settings. 

Even with these two pieces of information, the data will not appear until we have enabled your tenancy and the overnight process has run. 

The data is updated daily so is designed for reporting where real-time data isn’t required. 

The line items, orders and invoice data can all be consumed in Excel using PowerQuery. Here is a video which shows you how to connect and access the data. 

Using the same methodology you can use Microsoft PowerBI to model your data. With PowerBI (or indeed any other BI tool), you can use the Dimension tables to model budget spend as well. 

The api call can determine how much data is brought back. If left blank, all of the data will be returned. This could be useful for the very first call. Once you have the core of your data you can then filter down to:

• A year 
• A month and year 
• A number of days

Example: 

https://data-api.myzahara.net/Export/LineItems?TenancyId=XXX&Year=2022&month=7

or 

https://data-api.myzahara.net/Export/LineItems?TenancyId=XXX&year=2022

or

https://data-api.myzahara.net/Export/LineItems?TenancyId=XXX&days=60

Create a new spreadsheet in Excel (Our experience shows that this initial staging needs to be done in Windows Excel. We havent found the functionality in Mac Excel)

Click on the Data menu, and select From web

Click Advanced to reveal this screen 

You will now need three elements as we state above: 

1. The URL 
2. Your tenancyID 
3. Your api key

We will provide you with all of these once we have agreed a trial and pricing plan. 

Once you connect up above, you can create a separate tab in Excel per table of data. Excel allows you to transform the data. You can add conditional fields, clean-up dates, and calculated columns such as fixed currency rates. 

The above is a spreadsheet with a connection to Zahara, using Filters on line-items of purchase orders to show a GRNI value. 

To Create a budget report in PowerBI (or similar), you will need to join multiple tables. Typically this will involve: 

• Budgets table
• Nominal or Cost Codes
• Division or Project
• Line Items 
• Calendar

Below is a screen shot of typical BI project with the data links: 

The key budget field is BudgetLineItemID – this should be linked to either NominalCodeID or CostCodeID in the relevant table. Here are our recommended joins:

In the above example, we have created a dim table of BudgetCal which is a calendar table. This links to the Budgets.CurrentFinancialYearEnd field (for an annual budget) and then also links to the relevant date field on the LineItems table. 

In effect, the Budgets table budgets.LineValue field hold the amount available to spend. We then need a date / period in our report  and we need to know how much has been spent by using the line items table (TotalNet or TotalGross) and filtering that accordingly.