Skip to main content
Gainsight Inc.

Tutorial: Create a Report to Determine the Length of Time between Two Milestones

This tutorial explains how an administrator can create a report to determine the length of time between two milestones. In this tutorial, we will explain the process of determining the length of time between the original contract date and the launched date milestones. We will create a report on the Milestone object and use two custom fields in the report. One of the custom fields will be used to create a formula field (Number) to calculate the difference between the two milestone dates, and the other will be used to convert the Milestone Date field from Date-Time type to Date type.

Create Custom Fields

Two custom fields - MilestoneLaunchedDate and CustomFormulaNumberField must be created, which will later be used in building the report.

  • MilestoneLaunchedDate: This field will be mapped to the Date field in the Milestone object using the Rules Engine.

Note: Milestone Date is a Date/Time type field, whereas Original Contract date is a Date type field. Hence you have to convert the Date/Time field to a Date field, so that subtraction between these two fields is possible. For this purpose, this custom field is created.

  • CustomFormulaNumberField: This field will be used to calculate the difference between the two milestones.

Create MilestoneLaunchedDate

  1. Navigate to the Setup > Objects page.
  2. Select the Customer Info object.
  3. Click New.
  4. Select the Field Type as Date. Click Next.

New Custom Field.png

  1. Enter MilestoneLaunchedDate in the Field Label/Field Name fields. Click Next.

New Custom Field_Image 2.png

  1. Select the required profiles for which you want to grant edit access to this field via field-level security.

New Custom Field_Image 3.png

  1. Click Next. Select the required layout where you want to include this field.

Note: Page layouts control the layout and organization of buttons, fields, s-controls, Visualforce, custom links, and related lists on object record pages in Salesforce. They also help determine which fields are visible, read only, and required.

New Custom Field_Image 4.png

  1. Click Save.

Create CustomFormulaNumberField

  1. Navigate to the Setup > Objects page.
  2. Select the Customer Info object.
  3. Click New.
  4. Select the Field Type as Formula. Click Next.

New Custom Formula_Image 1.png

  1. Enter CustomFormulaNumberField in the Field Label/Field Name fields. Click Next.
  2. From the Formula Return Type menu, select Number. Click Next.
  3. Enter the following formula in the Advanced Formula tab.

MilestoneLaunchedDate__c - JBCXM__OriginalContractDate__c

New Custom Formula_Image 2.png

  1. Click Next. Select the required profiles for which you want to grant edit access to this field via field-level security.

New Custom Formula_Image 3.png

  1. Click Next. Select the required page layout that should include this field.

Note: Page layouts control the layout and organization of buttons, fields, s-controls, Visualforce, custom links, and related lists on object record pages in Salesforce. They also help determine which fields are visible, read only, and required.

New Custom Formula_Image 4.png

  1. Click Save.

Convert Date/Time to Date field

Note: Since Milestone Date is a Date/Time field, whereas Original Contract date is a Date field, you have to convert the Milestone Date field to a Date field, so that subtraction between these two fields is possible. This conversion can be achieved by running a rule through Rules Engine, which would map the custom MilestoneLaunchedDate field that we created in the Create MilestoneLaunchedDate section to the Milestone Date field.

Perform the following steps to create the rule.

  1. Navigate to Administration > Rules Engine.
  2. Click +RULE.
  3. Select Custom from the Rule Type drop-down list.
  4. Select Account in the Rule For field.
  5. Enter the required rule name and description. Click Next.

Date Field Image 1.png

  1. Select the source object as Milestone.
  2. In the Show fields section, select Date, Milestone Name, and Account Id.
  3. Apply the “Milestone Name = Launched” filter to the rule.

Date Field Image 2.png

  1. Click +ACTION.  Select Load to Customers in the Action Type menu.
  2. In the Field mappings section, map the selected fields as specified in the screenshot below:

Date Field Image 3.png

Note: The Date field is mapped to the custom MilestoneLaunchedDate field in the above screen.

  1. Run the rule by clicking the RUN NOW button.

Create the Report

  1. Navigate to Administration > Report Builder.
  2. Click +REPORT. Select the data source as Milestone.
  3. Add the following fields in the Show me section.
  • Account Name
  • Milestone Name
  • Original Contract Date
  • MilestoneLaunchedDate (custom)
  • CustomFormulaNumberField (custom)

Report Image 1.png

  1. Click RUN.

Report Image 2.png

The required report is generated, where CustomFormulaNumberField displays the number of days between the original contract date and the launched date.

 

  • Was this article helpful?