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
• Currencies
• Exchange Rates
• Divisions
• Nominal Codes
• Cost Codes
• Suppliers

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. 

Error Messages when Connecting 

If you receive any error messages when connecting to your data, and you have the exact tenancy ID and api key, check there isn’t an overriding settings around privacy. The access to the data should be Annonymous & public :

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. 

The line items dataset is the key to your reporting data. They contain both Order line items and invoice line items. Order Line are Type=1 and Invoice Lines are Type=2. You can achieve a line-by-line balance by joining the data together. This is our recommended method:

• Transform your line items in any way you need – adjusting dates/field types etc. 
• Duplicate your line items table, and filter to Type =2 (you now have a clone but Lines-Invoices only) 
• These two Line Items tables can be joined as shown below 

Now in yor report you can have the Order Lines and their values, together with the corresponding invoice data, such as Invoice Net, tax and Invoice Gross. You can then create calculated columns in your Dimension tables for the balances.

Custom fields are created in the Orders or Invoices table and grouped together with their labels and values. Using Split Columns, these can be transformed using PowerQuery to be split on a delimiter. 

Orders, Invoices & LineItems datasets have a void field, its good practice to set this to False early on so that you don’t have deleted records.

Closed Orders are often not required in reporting, filtering our Closed orders can be done in the Orders table, using the Status field 

Orders &  Invoices join to the Line Items tables on The DocumentID

The OrderNumber field is also consistent in the 3 key datasets 

Nominal, Cost Codes & Divisions can all be joined to the Line Items table on the corresponding ID field, ie LineItems.ProjectID = Projects.ProjectID. 

InvoiceLines (see above) join to the OrderLines on the LineItems.InvoiceLineID

Budgets join to either Cist Codes, or Nominal Codes on the Budgets.BudgetLineItemID