Deep Dive: Report level formula

Stevie LaFortune

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 R00I7J3xs5fn assuming that is the report's ID.

The Old Report ID number is a unique number per report and can be used to identify specific reports as well. Every report has both an ID and an old ID - they're simply different ways of showing the same information in either base10 or base62.

• {report:oldID} would output R3513250790654885 assuming that is the report's old ID.

   The Reimbursement ID is the unique number for a report that's been reimbursed via ACH in Expensify. The reimbursement ID is searchable on the Reports page and is found on your bank statement in the line-item detail for the reimbursed amount.

• {report:reimbursementid} would output 123456789109876 assuming that is the ID on the line-item detail for the reimbursed amount in your business bank account.

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. This formula can be used for export templates, but not for report titles.

• {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. This formula can be used for export templates, but not for report titles.

• {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. This formula can be used for export templates, but not for report titles.

• {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 sride@email.com

                 Formula                                                               Export name

{report:submit:from:email}                                  sride@email.com
{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} would output alice@email.com if they are the current approver

• {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

• Deep Dive: Understanding custom export templates and formulas

• Deep Dive: Expense level formula

• Deep Dive: User level formula

• Deep Dive: GL Code formula

• Deep Dive: Date formating for formulas

• Deep Dive: Default report title best practices, which formula are eligible, formula that update based on report information (submission date, date of last expense)

• Deep Dive: Understanding math and substring formula for custom exports
  

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