Create Calculated Field

Add a new calculated field to the selected Data Store from the Design page. Calculated fields can be created as a Formula (expression) or a Range (bucketization) and then used like any other field in

Access

• Open a report’s Design page (Design canvas).

• On the right-hand side toolbar, click Create Calculated Field (calculator icon).

Scope: Calculated fields are saved into the selected Data Store and become available under Dimensions or Measures based on their data type.

Create a Formula Field

Use when you need a derived numeric or textual value computed from existing fields.

1. Click Create Calculated Field → choose Formula.

2. Name: Enter a unique field name (e.g., Net_Sales).

3. Expression: Build your formula using existing fields and operators.

   • Examples (illustrative):

     • Arithmetic: Sales - Discount

     • Percentage: (Revenue - Cost) / Revenue

     • Simple scaling: Quantity * Unit_Price

4. (Optional) Data Type: Select the expected output type (Number/Text/Date), if shown.

5. Validate the expression (if a Validate button is provided).

6. Click Save.

• The new field appears under Measures (numeric) or Dimensions (text/date). Drag it into a View like any other field.

Tip: Use clear names and avoid spaces (or use underscores) for easier reuse in other Views.

Create a Range Field (Bucketization)

The Range option in the Calculated Field editor allows you to group a numeric measure into defined ranges (e.g., Low / Medium / High). The resulting field is saved as a Dimension and can be used for grouping, filtering, and visualization.

Note: The Range option is not available for spaces where the MongoDB server is configured in Admin Settings.

Steps to Create a Range Field

1. On the Design page, click Create Calculated Field (calculator icon).

2. Navigate to the Range tab.

3. Configure the range:

   • Name – Provide a title for the formula field (e.g., Experience_Range).

   • Measure – Select a numeric measure field from the drop-down.

4. Click ADD ROW to define a range.

5. Enter the range details:

   • Name – Label for the range (e.g., Junior, Mid-level, Senior).

   • From – Minimum value for the range.

   • To – Maximum value for the range.

6. Click the Add icon to insert the range.

7. Repeat steps 4–6 to add multiple ranges as needed.

8. Click Save.

• A notification confirms the calculated field.

Notes & Constraints

• Range fields are saved as Dimensions by default, regardless of source measure type.

• Each record is assigned to the first matching range definition.

• Overlapping ranges may cause ambiguity; ensure that defined ranges are mutually exclusive.

• Availability: Requires Elastic Search configuration. Not available for MongoDB spaces.

Use the Calculated Field in a View

1. On the Design page, locate the new field under Dimensions/Measures.

2. Drag and drop it into the View (or select via Search in the New UI).

3. (Optional) Adjust Chart and Chart Properties (labels, formats, colors).

4. Click Save View.

Note: The Range calculated field displays records grouped by the defined ranges. Example: Records are categorized into Junior, Mid-level, or Senior buckets based on the Experience measure. Example: Ranges for Experience

Edit or Delete a Calculated Field

• Hover or right-click the calculated field (in the field list) and choose Edit or Delete (where available).

• Updating a calculated field affects all Views that use it; review impacted Views after changes.

Notes & Behavior

• Validation: If provided, use Validate to catch syntax or type errors before saving.

• Type inference: The system may infer the calculated field’s data type from the expression; override it only when necessary.

• Governance: Calculated fields saved to a shared Data Store become visible to other users with access to that store.

• Performance: Complex expressions or many range rules may impact rendering time on very large datasets—optimize where possible.

• Compatibility: Available operators/functions can vary by environment; use supported syntax (arithmetic, comparisons, basic functions) per your tenant’s configuration.

Quick Examples (Illustrative)

• Formula (numeric): Gross_Profit = Revenue - Cost

• Formula (percentage): Margin_Pct = (Revenue - Cost) / Revenue

• Range (bands): Amount_Band: 0–1000 = "Low", 1000–5000 = "Medium", >5000 = "High"