Computed Fields

Computed fields are user-defined fields that expand analytic capabilities in Views and Reports. Without modifying the object model, users can build ad hoc fields that support complex data modeling and analysis. These fields do not reside in the database, but are created when needed for presentation in Views or Reports.

When included in Views or Reports, Computed Fields provide powerful building blocks for analytical models and metrics to monitor business operations in easy-to-read dashboards, tables or charts.

Eligible Field Types

These field types can be used in Computed Fields formulas:

• Text string
• Numbers (integers and numbers with decimals)
• Rollup Summary Fields
• Boolean
• Percent (%)
• Currency

How it Works

Let's consider a real-life example: an Inventory system where simple details about a product are specified and Computed Fields are used to analyze complex Pricing and Packaging schemes.

Pricing Information

Assume an inventory item, a 6-pack of soda, which is defined as follows:

Field Name: Value: Part Number Soda-20457 Description 6 cans, shrink-wrapped, 12 oz each Price $4.50 Number of Units per Pack 6 Unit Price Price / Number of Units per Pack (this is a [[Formula Fields|Formula Field]])

In this record, the unit price is calculated as $.75 per can of soda ($4.50/6 cans of soda = $.75)

Analysis

To setup the pricing analysis, create the following Computed Fields, one for each of the available packaging options. Each package option calculates the Unit Price of the 6-pack of soda, based on packaging.

This Expression is used to calculate the Unit Price for each package option: Unit Price = (Package Price / (Quantity * 6 cans each))

Computed Fields Package Option: Quantity: Package Price: Unit Price: Case 4 $15. $.625 Carton 8 $20. $.416 Bulk 12 $35. $.486

With this Analysis, it's easy to compare the unit price of Package Option to the Unit Price of a single 6-pack ($.75).

Add a Computed Field

In this example, the Revenue field in the Accounts object is used to create the computed field. The ranges are defined as follows:

1. From the Reports tab, add a new report, or choose a report to modify
2. From the Fields tab, confirm that an appropriate field is in the list of Selected Fields (in this case, Revenue)
3. Click the New Computed Field link

   File:Newcomputedfield.gif

4. Add the following code to create a computed field

   Column Title

   Name of the computed field

   Return Type

   Select Text

   Formula

   Add the following code:

   IF((REVENUE <= 10000),'<$10K',
   

   IF((REVENUE >= 10000 && REVENUE < 100000),'$10-100K',
   

   IF((REVENUE >=100000 && REVENUE < 500000),'$100K-500K',
   

   IF((REVENUE >=500000 && REVENUE <1000000),'$500K - 1M',
   

   '>= $1M'))))

   File:Computedrevenuerange.gif

5. From the Group tab, choose the computed field in the Group Information by field
6. From the Charts tab, choose Pie
7. Click the [Preview] button to display this chart

   File:Revenuepie.gif

   In this example, the computed field allows records to be grouped by range, and displayed in a pie chart

Using Computed Fields

Computed fields offer different functionality, depending on whether the computed fields are used in a View or a Report.

Computed fields must be created separately for Views and Reports. A computed field created in a View is not available in a Report, just as a computed field created in a Report is not available in a View.

In Reports, computed fields can be used to:

• Filter a Report
• Add Color Coding to a Tabular Report
• Group Records to create Matrix Reports
• Compute Metrics in Matrixed Reports
• Enhance Chart Metrics

In Views, computed fields can be used to:

• Filter a View
• Add Color Coding to a View

Formula Functions used in Computed Fields

The following Formula Functions can be used in Computed Fields:

Date Functions	Logical Functions
DATE DATEADD DATECOMP DATESUB DATEVALUE DAY HOUR MINUTE MONTH NOW TODAY WEEKDAY YEAR Not included: DATEPART, NULLDATE, NULLTIME, SECOND, TIME, TIMEPART, TIMEVALUE	AND IF NOT OR Not included: ISNEW, ISCHANGED, ISNULL
Math Functions	Text Functions
ABS CEIL EXP FLOOR LN MAX MIN MOD POW ROUND SQRT	CONCAT CONTAINS LEN LOWER REPLACE SUBSTRING TEXT TRIM UPPER VALUE Not included: ENDSWITH, FIND, STARTSWITH
Not included: PRIORVALUE

For details on how to use these functions, see Formula Functions.