Zetadocs Expenses Reporting Feed Reference

1.  Introduction

The Zetadocs Expenses Reporting data feed enables users to retrieve details of expenses on their account.  It is designed for displaying reports in Microsoft Power BI, or for further analysis of expenditure in Microsoft Excel, complementing the reporting available in Dynamics NAV and Business Central.

The data feed uses the OData standard, and is secured using the Zetadocs user account credentials (username and password).  If the specified user has been configured with the Zetadocs Expenses analyst role the feed will return approved expenses for all users in the account; otherwise the user will only see their own expenses.

This page defines the Reporting data feed, detailing the tables and fields which are available.  It is intended to help finance teams and others using the data to understand which fields to use, and any special case behaviour which they should take into account.

2.  Endpoints and metadata

The OData interface is accessed via the following URL:

https://expenses-odata.zetadocs.com/odatav4/v1

The metadata definition of the service can be accessed via the following URL:

https://expenses-odata.zetadocs.com/odatav4/v1/$metadata

The endpoint includes the following table:

Expenses

NOTE: For a Custom Property's data to appear in the feed, it must have an appropriate Analysis field defined against it. This can be done when configuring the Custom Property.

3.  Expenses table

The Expenses table lists each approved expense.  Expenses which are itemised are listed with one line for each expense item.  Expenses which are not itemised are listed with a single line for each expense.

3.1  Expenses table fields

3.2  Category field enumerated values

See section 4.2.

3.3  CategoryClass field enumerated values

See section 4.2.

3.4  QuantityUnits field enumerated values

See section 4.5.

4.  Data use and system behaviour

This section gives additional information about the use of selected data fields, and of the system behaviour which is relevant to those fields.

4.1  Expense types

The expense type is used to specify what the expense is for.  Each expense type is configured with an export code which specifies which GL account in the finance system to use when exporting expenses of that type. 

You can configure the Zetadocs Expense system to export multiple expense types to the same GL account.  For example, you could configure separate expense types for “Trains” and “Hotels”, then export both to a single “Staff Travel and Subsistence” GL account.

Expense Types are grouped together to make it simpler for submitters to choose the correct expense type.  The same name can be included in multiple groups – for example:

Rechargeable Travel

Train
Taxi

Non-Rechargeable Travel

Train
Taxi
Bicycle

The Zetadocs Expenses client app shows the group names when selecting the expense type.  However when displaying the expense type it uses a single text description.  This display name is set to the name of the expense type by default (“Train” or “Taxi” in the example above), but can be changed by the administrator if this results in duplicate names – eg changing it from “Train” to “Rechargeable Train Travel”.

The OData tables include the expense type group and names as follows:

• ExpenseType – this is the unique display name for the expense type (eg “Rechargeable Train Travel”)
• ExpenseTypeGroup – this is the group which contains the expense type (eg “Rechargeable Travel”).  This may be used to display high level totals for multiple related types of expense, for example in a simplified pie chart display.

4.2  Expense categories and category classes

When a user creates a new expense in the Zetadocs Expenses client, they start by specifying the category class – currently payment or mileage.  The client then displays the expense edit form for that class.

When editing an expense the submitter chooses the expense type – see section 4.1.  Each expense type is assigned a category which determines the edit form it can be used with and any additional properties which must be specified for that expense – eg for expense types with category PaymentFuel, the client displays a field to specify the vehicle type.

Many fields in the OData Expenses table are common to all expenses – eg the expense date and gross amount.  However some fields are only used with specific categories (eg VehicleType, which is used for mileage records and fuel expenses), and some fields may be used for different purposes depending on the category (eg Quantity, which contains the distance for mileage records and the number of nights for hotel payment expenses).

The OData tables include the expense category as follows:

• Category – this specifies the exact category of the expense – eg PaymentFuel.  It can be used by specialist applications which need to filter out specific expense variants – eg to analyse fuel expenses for each vehicle type.
• CategoryClass – this specifies whether the expense is a payment or mileage expense.  It can be used to protect applications using the data against new categories being added in future – eg a report of all payments can filter using this value without risk of omitting any future payment variants.

Note that new categories and category classes will be added in future, so filtering on these fields should be avoided unless necessary, so as to avoid the risk of omitting future expense variants.

4.3  Sales tax

In countries like the UK, sales tax (VAT) on expenses incurred by their employees can be reclaimed reducing the cost to the company.  Reclaiming sales tax typically requires the submitter to include a receipt showing the tax amount paid, though some companies may agree exceptions to the regulations (eg allowing reclaim of amounts under a given limit without a receipt), and reclaim is prohibited for some types of expense.

For payment expenses, when the submitter enters the expense they can choose the sales tax rate to use.  This specifes where sales tax amount is stored when it is exported to the finance system, and may also specify the rate percentage which is used by the Zetadocs Expenses client to calculate the sales tax amount automatically.

For accounts and expense types which allow sales tax to be reclaimed, the submitter or finance processor specifies whether to reclaim tax for that expense – ie whether they have fulfilled the statutory requirements.

The Expenses table include three fields giving information about sales tax:

• SalesTaxAmount – this is the amount entered by the user.  This will be zero for accounts which do not reclaim sales tax
• SalesTaxReclaimed – the is the amount of tax which the company can reclaim on this expense.  For expense with the “Reclaim Sales Tax” option selected it is set to the SalesTaxAmount; otherwise it is set to zero.
• SalesTaxRate – this is the name of the sales tax rate selected for that expense, enabling analysis of sales tax amounts for each rate if needed.

4.4  Gross, net and personal amounts

Each Zetadocs Expenses account has a base currency which is used for all expenses created on that account.  Expenses which are entered in a different currency are converted into the base currency using the exchange rate entered by the submitter, and all amounts given in the OData Expenses table are in base currency.

(i) Payment expenses

For a payment expense, the payment amount in base currency is given in the GrossAmount field.  This is the expense amount which is shown in the Zetadocs Expenses client, and for expenses paid by cash or a personal credit card it will usually be the amount which will be reimbursed to the submitter.

If the payment is not an allowable business expense, then some or all of the payment amount will not be reimbursed.  For example, in the UK a user with a company car may be required to submit a fuel expense showing the VAT which they have paid, but this will not be paid by the company.  The non-claimable portion is referred to as a personal expense.

The cost to the company will also be reduced in countries like the UK if the company can reclaim sales tax (VAT) on the expense.

The cost to the company is given in the NetAmount field.  This is the GrossAmount, less reclaimable sales tax and any personal expense amount.  This net amount is typically used for cost analysis.

(ii) Mileage expenses

The total expense amount for each mileage expense is calculated automatically based on the distance travelled, the mileage rate effective on the journey date for the vehicle type, and (for companies using dual mileage rates) the total distance travelled by that user in the current year.  This amount is given in the GrossAmount field.

The NetAmount field gives the cost to the company, as for payment expenses.  This will be less than the gross amount if sales tax can be reclaimed by the company.

In countries like the UK, companies can reclaim sales tax (VAT) on business mileage even if they are not reimbursing the user using a fixed mileage rate.  They do this by configuring a vehicle type where business mileage will not be reimbursed, then requiring users to submit mileage expenses for journeys they make.  In this case the NetAmount value will be negative, since each mileage expense is only used to reclaim sales tax for company car users.

4.5  Quantities

The Quantity field is used for different purposes depending on the category and category class of the expense, as shown in the following table:

For other category values the Quantity field is undefined and should not be used, in order to avoid unexpected behaviour with future software updates.

The QuantityUnits field gives the units, and may be used when displaying the values – eg to add the mileage units to a displayed chart.

4.6  Custom properties

Custom properties are configured for a Zetadocs Expense account.  They are used to allow expenses to be analysed by different groupings – eg by department or region – and for selecting customers, jobs and tasks for expenses on a specific project or job.  Each custom property has a list of allowed values, and each expense may have single value (or blank) specified for each of these properties.

Two custom properties are treated as special cases and exported to the Department and Area fields.  The standard reporting templates supplied with the product include reports analysing expenditure by Department or Area which use these fields.  

Other custom properties are exported in fields CustomProperty01 to CustomProperty10.  These can be used for producing custom reports as required.  With current software these give the values of the first ten custom properties, based on the configured display order. 

The Custom Properties 'Analysis Field' option is used to specify which field in the OData interface it maps to (Department, Area, CustomProperty01, etc), so that the display order can be changed without affecting existing reports.

The OData tables include the custom properties as follows:

• Department – this is typically used for the name of the department to which the expense is assigned (eg “Sales”).
• Area – this is typically used for the project or area to which the expense is assigned (eg “New Business”).
• CustomProperty01 to CustomProperty10 - this is the text value for up to 10 custom properties (one property per field).