Skip to main content

Evaluating Formulas

The evaluator.evaluate() method lets you compute cell values on the server without needing a full export. This is useful for retrieving specific cell values, performing ad-hoc calculations, and building dynamic reports.

Basic Usage​

import * as farseer from 'farseer-client';

const client = new farseer.FarseerClient();

const cells = await client.evaluator.evaluate({
    filters: [],
    cellQueries: [
        { type: 'VARIABLE', formula: 'sum("Revenue","Plan","Y2024")' },
    ],
});

console.log(cells[0].value); // e.g. 1250000

Each cell query returns an EvaluateCell with the computed value, plus metadata like readonly, calculated, and frozen.

CellQuery Types​

Cell queries define what to compute. There are two types:

VARIABLE​

Evaluates a formula expression against variables:

const query: farseer.CellQuery = {
    type: 'VARIABLE',
    formula: 'sum("Revenue","Plan","Y2024")',
};

DTABLE​

Queries dimension table data directly:

const query: farseer.CellQuery = {
    type: 'DTABLE',
    tableGid: 42,           // Dimension table global ID
    columnIds: [1, 2, 3],   // Column IDs to retrieve
    formula: 'filter-expr',
};

Formula Syntax​

Formulas follow Farseer's expression language. You can reference tags by name or by ID:

// By name (human-readable)
{ type: 'VARIABLE', formula: 'sum("Revenue","Plan","Y2024")' }

// By ID (use $ prefix)
{ type: 'VARIABLE', formula: 'sum($42,$27,$100)' }

// Mixed
{ type: 'VARIABLE', formula: 'sum("Revenue",$27,$100)' }

Using Filters for Variable Substitution​

Filters let you define aliases that get substituted into formulas. This avoids repeating the same tag references across multiple queries:

const revenue = await client.getVariable('Revenue');
const plan = await client.getDimensionMember('Versions', 'Plan');
const y2024 = await client.getDimensionMember('Years', 'Y2024');

const cells = await client.evaluator.evaluate({
    filters: [
        ['RV', `$${revenue.id}`],   // RV → $42
        ['P', `$${plan.id}`],       // P  → $27
        ['Y', `$${y2024.id}`],      // Y  → $100
    ],
    cellQueries: [
        // "RV", "P", "Y" get replaced by filter values before evaluation
        { type: 'VARIABLE', formula: 'sum(RV,P,Y)' },
    ],
});

This is equivalent to:

{ type: 'VARIABLE', formula: `sum($${revenue.id},$${plan.id},$${y2024.id})` }

Evaluating Multiple Cells at Once​

You can pass multiple queries in a single request. Each query produces one EvaluateCell in the response array, in the same order:

const allMonths = await client.getDimensionMembersForTable('Months');

const monthQueries: farseer.CellQuery[] = allMonths.map(month => ({
    type: 'VARIABLE',
    formula: `sum(RV,P,Y,$${month.id})`,
}));

const cells = await client.evaluator.evaluate({
    filters: [
        ['RV', `$${revenue.id}`],
        ['P', `$${plan.id}`],
        ['Y', `$${y2024.id}`],
    ],
    cellQueries: [
        { type: 'VARIABLE', formula: 'sum(RV,P,Y)' },  // Total
        ...monthQueries,                                  // Per-month breakdown
    ],
});

const total = cells[0].value;
const monthValues = cells.slice(1).map((c, i) => ({
    month: allMonths[i].name,
    value: c.value,
}));

EvaluateRequest​

interface EvaluateRequest {
    filters: Array<[string, string]>;  // Alias-to-value pairs
    cellQueries: Array<CellQuery>;     // Queries to evaluate
}
FieldTypeDescription
filtersstring[][]Array of [alias, value] pairs for formula substitution
cellQueriesCellQuery[]Array of cell queries to evaluate

EvaluateCell (Response)​

Each query returns one EvaluateCell:

interface EvaluateCell {
    value: any | null;       // Computed value (number, string, etc.)
    readonly: boolean;       // Whether the cell is read-only
    calculated: boolean;     // Whether it's a calculated field
    frozen: boolean;         // Whether the cell is frozen
    goal?: number;           // Goal/target value if set
}

Calculate On-Demand Cells​

If your model has on-demand calculated fields, trigger calculation before evaluating or exporting:

// Simple
await client.cells.calculateOnDemandCells();

// With timeout (recommended for large models)
const calc = client.cells.calculateOnDemandCells();
const timeout = new Promise(resolve =>
    setTimeout(() => resolve('timeout'), 10 * 60 * 1000)
);

const result = await Promise.race([calc, timeout]);
if (result === 'timeout') {
    console.log('Calculation timed out, continuing...');
}
tip

Call calculateOnDemandCells() before evaluator.evaluate() or export.exportFormula() to ensure all calculated fields are up to date.

Complete Example​

A full workflow that evaluates Revenue for Plan across all months:

import * as farseer from 'farseer-client';

async function main() {
    const client = new farseer.FarseerClient();

    // Get tag references
    const revenue = await client.getVariable('Revenue');
    const plan = await client.getDimensionMember('Versions', 'Plan');
    const y2024 = await client.getDimensionMember('Years', 'Y2024');
    const allMonths = await client.getDimensionMembersForTable('Months');

    // Build queries for each month
    const monthQueries: farseer.CellQuery[] = allMonths.map(m => ({
        type: 'VARIABLE',
        formula: `sum(RV,P,Y,$${m.id})`,
    }));

    // Evaluate
    const cells = await client.evaluator.evaluate({
        filters: [
            ['RV', `$${revenue.id}`],
            ['P', `$${plan.id}`],
            ['Y', `$${y2024.id}`],
        ],
        cellQueries: [
            { type: 'VARIABLE', formula: 'sum(RV,P,Y)' },
            ...monthQueries,
        ],
    });

    // Process results
    console.log(`Total Revenue (Plan, 2024): ${cells[0].value}`);
    cells.slice(1).forEach((cell, i) => {
        console.log(`  ${allMonths[i].name}: ${cell.value}`);
    });
}

main().catch(farseer.handleUnknownError);