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:
The data made available to you is:
- Line items
- Orders
- Invoices
- Budgets
- Vendors
- Projects
- Currencies
- Exchange Rates
- Divisions
- Nominal
- Codes
Cost
- Vendors
Pre Requisites
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.
Your tenancy ID – that’s usually hidden from you but we will provide it to you
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.
Using the data
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. 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
Data Limits
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
Connecting to your data in Excel
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:
- The URL
- Your tenancyID
- Your api key
We will provide you with all of these once we have agreed a trial and pricing plan.
What to expect
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.
Budget Data
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.
Order Invoice Balance by Line Item
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
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.
Joins & Good Practice
Orders, Invoices & LineItems datasets have a void field. It's 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 out 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 Cost Codes; or Nominal Codes on the Budgets.BudgetLineItemID