Are you receiving SNAP/EBT benefits? The Expensify.org/SNAP-VAX campaign is live and accepting new members. Learn more about receiving \$50 for submitting a SNAP receipt and \$50 for getting your COVID-19 vaccine here and join today!

# Deep Dive: Report level formula

Expensify Team Posts: 234 Expensify Team
edited July 26

### When creating custom exports, you'll need to decide which details of the report are important for you to capture, these formulas are related to areas and actions taken on a report.

Title is the title of the report.
• {report:title} would output Example Co Lunch Meeting assuming that is the report's title.

The Report ID number is a unique number per report and can be used to identify specific reports.
• {report:id} would output 88432 assuming that is the report's ID.

Total is the total amount of the expense report.
• {report:total} would output \$325.34 assuming that is the report's total.

Type is the type of report (either Expense Report, Invoice or Bill)
• {report:type} would output Expense Report assuming that is the report's type

Reimbursable is the total amount that is reimbursable on the report.
• {report:reimbursable} would output \$143.43 assuming the report's reimbursable total was 143.43 US Dollars.

Currency is the currency to which all expenses on the report are being converted.
• {report:currency}  would output USD  assuming that the report total was calculated in US Dollars

Note - Currency accepts an optional three character currency code or NONE. If you want to do any math operations on the report total, you should use {report:total:nosymbol} to avoid an error. Please see Expense:Amount for more information on currencies.

Report Field formula will output the value for a given Report Field which is created in the policy settings.
• {field:Employee ID}  would output 12456 , assuming "Employee ID" is the name of the Report Field and "123456" is the value of that field on the report.

Created date the expense report was originally created by the user.
• {report:created} would output 2010-09-15 12:00:00 assuming the expense report was created on September 15th, 2010 at noon.
• {report:created:yyyy-MM-dd} would output 2010-09-15 assuming the expense report was created on September 15, 2010.

Note -  All Date Formulas accept an optional format string. The default if one is not provided is yyyy-MM-dd hh:mm:ss.  For a full breakdown, check out the Date Formatting here.
StartDate is the date of the earliest expense on the report.
• {report:startdate} would output 2010-09-15 assuming that is the date of the earliest expense on the report.

EndDate is the date of the last expense on the report.
• {report:enddate} would output 2010-09-26 assuming that is the date of the last expense on the report.

Scheduled Submit Dates are the start and end dates of the Scheduled Submit reporting cycle.
• {report:autoReporting:start} would output 2010-09-15 assuming that is the start date of the automatic reporting cycle, when the automatic reporting frequency is not set to daily.
• {report:autoReporting:end} would output 2010-09-26 assuming that is the end date of the automatic reporting cycle, when the automatic reporting frequency is not set to daily.

Submission Date is the date that the report was submitted.
• {report:submit:date} would output 1986-09-15 12:00:00 assuming that the report was submitted on September 15, 1986, at noon.
• {report:submit:date:yyyy-MM-dd} would output 1986-09-15 assuming that the report was submitted on September 15, 1986.

Note -  All Date Formulas accept an optional format string. The default if one is not provided is yyyy-MM-dd hh:mm:ss.  For a full breakdown, check out the Date Formatting here.

Approval Date is the date the report was approved.
• {report:approve:date} would output 2011-09-25 12:00:00 assuming that the report was approved on September 25, 2011, at noon.
• {report:approve:date:yyyy-MM-dd} would output 2011-09-25 assuming that the report was approved on September 25, 2011.

Note -  All Date Formulas accept an optional format string. The default if one is not provided is yyyy-MM-dd hh:mm:ss.  For a full breakdown, check out the Date Formatting here.

Reimbursement Date is the date an expense report was reimbursed.
• {report:achreimburse} would output 2011-09-25 assuming that is the date the report was reimbursed via ACH Direct Deposit.
• {report:manualreimburse} would output 2011-09-25 assuming that is the date the report was marked as reimbursed.

Export Date is the date when the report is exported.
• {report:dateexported} would output 2013-09-15 12:00 assuming that the report was exported on September 15, 2013, at noon.
• {report:dateexported:yyyy-MM-dd} would output 2013-09-15 assuming that the report was exported on September 15, 2013.

Note -  All Date Formulas accept an optional format string. The default if one is not provided is yyyy-MM-dd hh:mm:ss.  For a full breakdown, check out the Date Formatting here.

Expenses Count is the number of total expenses on the report of this specific expense.
• {report:expensescount} would output 10 assuming that there were 10 expenses on the given report for this expense.

Expense Number is the ordinal number of the expense on its expense report.
• {report:expense:number} would output 2 assuming that the given expense was the second expense on its report.

Policy Name is the name of the policy applied to the report.
• {report:policyname} would output Sales assuming that the given report was under a policy named Sales.

Status is the current state of the report when it was exported.
• {report:status} would output Approved assuming that the report has been approved and not yet reimbursed.

From can be the email address, name, or custom field entry of the report submitter.

Using this example: User's name is filled in her Account Settings as Sally Ride and her email is [email protected]

Formula                                                               Export name
{report:submit:from:email}                                  [email protected]
{report:submit:from:email|frontPart}                   sride
{report:submit:from:email|domain}                     email.com
{report:submit:from}                                            Sally Ride
{report:submit:from:firstname}                            Sally
{report:submit:from:lastname}                            Ride
{report:submit:from:fullname}                             Sally Ride
Note - If user's name is blank, then {report:submit:from} and {report:submit:from:email|frontPart} will print the user's whole email.

{report:submit:from:customfield1} would output the custom field entry for the user who submitted the report. If John Smith’s custom field 1 entry is 13456 this would output simply 13456. This is available only to Control Policy customers.

To is the email address of the last person who the report was submitted to.
• {report:submit:to:email|frontPart} would output alice.

## Formatting exported reports

Your spreadsheet program might format some of the exported data in an exponential form (1.7976931348623157e+308). If that happens, you can correct it by changing the data to Plain Text or a Number in your spreadsheet program.

Related articles

Have a question or want to know more? Start a discussion here!
Tagged: