User guide

Help for users and administrators


Watch videos

Zetadocs Expenses Reporting Feed Reference

Print

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

Field name

Data type

Description

ExpenseId

Edm.Int32

Short ID for expense. This is the ID which is displayed in the client, and which is entered in the Search screen to view the expense. For itemised expenses, each item is included as a separate line with the same ID.

ExpenseDate

Edm.Date

The date when the payment or journey was made

ExpenseItem

Edm.Int32

Item number for itemised expenses starting from “1”. For expenses which are not itemised, the ItemNumber is set to “1”.

Category

Edm.String – see section 3.2

Category for expense – eg PaymentFuel. See section 4.1.

CategoryClass

Edm.String – see section 3.3

Class of expense – eg Payment. See section 4.1.

Submitter

Edm.String

User ID for the submitter of the expense. This is the User ID value entered in the user settings configuration screen, or the full name of the user if the user ID has not been specified.

SubmitterName

Edm.String

Full name for the user (first name + lastname).

ReportId

Edm.Int32

Short ID of primary report for the expense. For an expense originally submitted on a Payment Card Report this is the ID of the Payment Card Report if that was used to approve the expense; otherwise it is the ID of the Expense Report or Mileage Report used to approve the expense.

ApprovalTime

Edm.DateTimeOffset

The date and time when the expense was approved

ExportTime

Edm.DateTimeOffset

The date and time when the expense was exported to the finance system

Company

Edm.String

Short name of the company to which the expense is assigned. For accounts using multiple companies this is the name entered on the company configuration screen. For accounts using a single company this field can be ignored.

ExpenseType

Edm.String

Display name for the expense type, used to give a unique description of the expense type – eg “Rechargeable Train Travel”. See section 4.1.

ExpenseTypeGroup

Edm.String

Name of the group containing the expense type – eg “Rechargeable Travel”. See section 4.1.

Description

Edm.String

Short description entered by the submitter

Notes

Edm.String

Additional text entered by the submitter in the Notes field.

BaseCurrency

Edm.String

Base currency for expense, set to the base currency for the account. This is a 3 letter standard currency code (eg “USD”).

GrossAmount

Edm.Decimal

Gross expense amount in base currency. See section 4.4.

NetAmount

Edm.Decimal

Cost to company in base currency, after deducting reclaimable sales tax and any personal contribution by the submitter. See section 4.4.

SalesTaxAmount

Edm.Decimal

Amount of sales tax in base currency included in the GrossAmount, for accounts which are configured to allow sales tax reclaim. See section 4.3.

SalesTaxReclaimed

Edm.Decimal

Amount of sales tax in base currency which can be reclaimed by the company. See section 4.3.

SalesTaxRate

Edm.String

Name of the sales tax rate. See section 4.3.

VehicleType

Edm.String

Name of the vehicle type used for the journey.dep

Quantity

Edm.Decimal

Total distance travelled, number of nights, etc, depending on expense category. See section 4.5.

QuantityUnits

Edm.String - see section 3.3.

The units for the Quantity field. See section 4.5.

Department

Edm.String

Name of the department to which the expense is assigned.

This can be configured by setting the Analysis field to Department on the Custom Property Details.

Area

Edm.String

Name of the area or project to which the expense is assigned.

This can be configured by setting the Analysis field to Area on the Custom Property Details.

CustomProperty01

Edm.String

Value of custom property 1 – eg “Sales”. See section 4.6.

CustomProperty02

Edm.String

Value of custom property 2. See the description for CustomProperty01 above.

CustomProperty03

Edm.String

Value of custom property 3. See the description for CustomProperty01 above.

CustomProperty04

Edm.String

Value of custom property 4. See the description for CustomProperty01 above.

CustomProperty05

Edm.String

Value of custom property 5. See the description for CustomProperty01 above.

CustomProperty06

Edm.String

Value of custom property 6. See the description for CustomProperty01 above.

CustomProperty07

Edm.String

Value of custom property 7. See the description for CustomProperty01 above.

CustomProperty08

Edm.String

Value of custom property 8. See the description for CustomProperty01 above.

CustomProperty09

Edm.String

Value of custom property 9. See the description for CustomProperty01 above.

CustomProperty10

Edm.String

Value of custom property 10. See the description for CustomProperty01 above.

 

3.2  Category field enumerated values

Value

Description

PaymentGeneral

General payment expense.

PaymentFuel

Fuel expense, used to record fuel purchases for a vehicle including the vehicle type

PaymentHotel

Hotel expense, used to record hotel accommodation including the number of nights.

MileageGeneral

General mileage expense

See section 4.2.

3.3  CategoryClass field enumerated values

Value

Description

Payment

Payment expense, recording a payment by the submitter for an allowable business expense.

Mileage

Mileage expense, recording a journey made by the submitter in their personal vehicle or company car.

See section 4.2.

3.4  QuantityUnits field enumerated values

Value

Description

Miles

Used with mileage expenses (CategoryClass = Mileage) when the Quantity field specifies the distance travelled

Km

Used with mileage expenses (CategoryClass = Mileage) when the Quantity field specifies the distance travelled

Nights

Used with hotel payments (Category = PaymentHotel) when the Quantity field specifies the number of nights covered by the payment

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:

CategoryClass

Category

Quantity field

Mileage

(any)

Contains the distance for the journey. The QuantityUnits field specifies whether this distance is in miles or km.

Payment

PaymentHotel

Contains the duration of the hotel stay (number of nights)

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).