# Creating Custom Reporting Metrics with Formula Fields

Some important questions about your documents and processes cannot be answered simply by reporting on the values of document fields. Your organization may need to measure a value that can only be derived from a combination of multiple fields or by analyzing the history of field values.

You can do this by creating special formula-type fields in reports. Formula fields use an Excel-like formula language to calculate a result from other field values, a set of built-in functions, and standard math operators.

<div class="note-border alert-info">
  <div class="alert alert-info" role="alert">
    <div><i class="far fa-info-circle"></i></div>
    <div class="alert-text">
      <p><strong>Note</strong>: Formula fields on documents can only be used in reports.</p>
    </div>
  </div>
</div>



For information about supported functions and operators, see <a href="/en/gr/52324/#">Vault Formula Reference Guide</a>.


### Process Reporting (Cycle Time Calculations) {#process-reporting-cyce-time-calculations}


This video demonstrates how to use cycle time metrics to report on object lifecycles and improve the overall business processes:
<video controls width=860 height =504 poster="https://platform.veevavault.help/assets/images/posters/2025-cycle-time-calculations.png" preload="metadata">
    <source src="https://platform.veevavault.help/108e9b1d-559c-4d48-918b-1e4c5b5a533c/c8051941-ea72-4be1-855f-a7c9370ff9fc/c8051941-ea72-4be1-855f-a7c9370ff9fc_source__v.mp4" type="video/mp4" >
    
    <track
    label="English"
    kind="subtitles"
    srclang="en"
    src="/en/gr/assets/captions/25r3-cycle-time-calculations.vtt"
    default />
    </video>

<a href="/en/gr/676797/">Details</a>


## Creating Formula Fields {#create}

Admins can <a href="/en/gr/3606/#formula-fields">create document report formula fields</a> directly within the report configuration. 

Admins can no longer create or edit document formula fields from **Admin > Configuration > Document Fields**. With 25R2, any document formula fields created in **Admin > Configuration > Document Fields** that do not use _Document Status_ or _State Type_ return blank values. Document formula fields that use _Document Status_ and _State Type_ continue to function as expected.

## Blank Value Handling {#considerations}

When creating a formula field for a report, the _Blank Field Handling_ setting allows you to select how Vault treats blank fields when evaluating an expression in a report.

When the _Blank Field Handling_ option is set to _Treat blank values as blanks_, one blank field value causes the entire expression to return a null/blank value. For example, `Concat("Hello",documents.title__v)` returns a blank value when the _Document Title_ field is blank.

When the _Blank Field Handling_ option is set to _Treat blank values as zeroes and empty strings_, Vault treats a blank field value as a zero or an empty string when evaluating the expression. For example, `Concat("Hello",documents.title__v)` returns _Hello_ when the _Document Title_ field is blank.

See the example below for the formula expression `Document.days_in_draft__c + Document.days_pending_qc__c`:

| Document | Days in Draft | Days Pending QC | Result: Treat Blank Values as Blanks | Result: Treat Blank Values as Zeroes or Empty Strings |
| --- | --- | --- | --- | --- |
| Document A | 12 | 5 | 17 | 17 |
| Document B| 5 | 0 | 5 | 5 |
| Document C| 9 | blank | blank | 9 |

Functions return a blank value if the field evaluated has never had the value specified, regardless of the _Blank Field Handling_ setting. For example, the function `firstTimeinState(Document.status__v, "Pending QC")` returns a blank for Document C because the document has never been in the _Pending QC_ status.

The <a href="/en/gr/52324/#isblank">`isBlank()`</a> function always returns `false` if _Blank Field Handling_ is set to _Treat blank values as zeroes and empty strings_.

## Time Zone Handling with Date Fields {#timezone}

Vault stores all values for _Date_ and _DateTime_ type fields in the database in UTC. When calculating a _dateDiff_ value, we use the following rules for time zone conversions:

  * For calculations involving two _Date_ fields, we do not perform a time zone conversion. _Date_ fields are considered without a time.
  * For calculations involving two _DateTime_ fields, we do not perform a time zone conversion. Both field values are stored in UTC, so the function is not affected by time zone. We perform a date-to-date calculation and round values to the nearest integer. For reports referencing these fields, values can include decimal places when defined to accurately reflect date differences.
  * For calculations involving a _Date_ field and a _DateTime_ field, we convert the _DateTime_ value from UTC to the Vault's **Default Time Zone** setting and truncate the time portion of the value. We then perform a date-to-date calculation. Vault does not account for daylight savings time.

## Formula Examples {#examples}

These common formula fields may be helpful for analyzing documents and processes in your Vault.  You can copy the formula expression directly into the **Formula** text field.

Approval Time (Total)
: `firstTimeInValue(Document.status__v, "Approved for Production") - firstTimeInValue(Document.status__v, "Pending QC")`

Approval Time (Production Time)
: `firstTimeInValue(Document.status__v, "Approved for Distribution") - firstTimeInValue(Document.status__v, "Approved for Production")`

Retired: Withdrawn Prior to Expiration
: `Document.expiration_date__c - firstTimeInValue(Document.status__v,"Withdrawn")`
: Use with **Filter: greater than 1**

Expired: Withdrawn On Expiration
: `Document.expiration_date__c - firstTimeInValue(Document.status__v, "Withdrawn")`
: Use with **Filter: equals 0**

Review Cycles
: `numTimesInValue(Document.status__v, "In MLR Review")`

 [3]: #considerations