Best Practices: Reporting

Best practices for report scripting — the ReportStartFunction, ReportBuildFunction, and ReportEndFunction scripts that prepare report data. This page does not cover report rendering or template design.

Use the three scripting entry points for their intended purpose

A report can implement up to three scripting hooks. Each has a distinct role and runs at a different stage of the report pipeline.

Hook	When	Typical use
`ReportStartFunction`	Before queries execute	Set hidden parameters, apply status and filter values
`ReportBuildFunction`	After start, before queries	Populate UI controls (dropdowns, selections) from live data
`ReportEndFunction`	After all queries	Reshape, join, and aggregate `DataTable` results for the template

In practice: keep ReportStartFunction focused on parameter injection — status filters, employee scope, culture. Use ReportBuildFunction only when UI controls need dynamic data (e.g. employee list for a single-employee selector). Put all data transformation in ReportEndFunction and delegate to local methods to keep the Execute() method readable.

Declare queries in the report definition, not in script

Queries are named mappings from a DataTable name to an API endpoint. They are declared in the report definition's queries object and executed automatically before ReportEndFunction runs — no scripting required for the common case.

"queries": {
  "Employees": "QueryEmployees"
}

The key (Employees) becomes the DataTable name in the DataSet. The value is the API query name. The resulting table is accessible in ReportEndFunction via Tables["Employees"].

Declare all queries that return complete, unfiltered data sets statically. Use scripted ExecuteQuery calls only for data that requires dynamic parameters — for example, per-employee per-period slices inside a loop.

Filter queries with hidden OData parameters

Query results can be scoped without scripting by declaring a hidden parameter named <TableName>.Filter with an OData expression. The engine injects this filter into the query automatically.

{ "name": "Results.Filter",
  "hidden": true,
  "value": "jobName eq '$PayrunJobName$'" }

The $PayrunJobName$ placeholder is replaced at runtime by the value of the PayrunJobName parameter. This pattern covers single-job payslip reports without any scripting.

Common filterable parameters follow the same <TableName>.Filter naming convention. The OData expression supports eq, ne, lt, le, gt, ge, and, or.

Inject context parameters with `parameterType`

Reports always need TenantId, and often PayrollId or UserId. Declare these as hidden parameters with the appropriate parameterType — the engine fills them automatically and they never appear in the UI.

{ "name": "TenantId", "hidden": true, "parameterType": "TenantId" }

Available parameter types: TenantId, UserId, EmployeeId, PayrollId, RegulationId, PayrunId, PayrunJobId.

In ReportEndFunction, read the injected value with the corresponding typed property (TenantId, UserId, …) rather than GetParameter("TenantId") — the property is always an int and avoids a string-to-int conversion.

Use `ExecuteQuery` for dynamic per-employee, per-period data

The static query mechanism cannot loop. Use ExecuteQuery in ReportEndFunction when you need one query call per employee, per period, or per combination:

var slice = ExecuteQuery("EmployeeWageTypes",
    "GetConsolidatedWageTypeResults",
    new Dictionary<string, string>
    {
        { "TenantId",    TenantId.ToString() },
        { "EmployeeId",  employeeId.ToString() },
        { "PeriodStarts", $"[\"{periodStart}\"]" }
    });

The first argument is the scratch table name in the DataSet — it must be unique for each logical slot. The engine reuses the named slot on repeated calls with the same name inside the loop, so pre-create the slot with AddTable before entering the loop to avoid exceptions on the first iteration.

Common ExecuteQuery targets:

API query name	Returns
`QueryEmployees`	All employees visible to the report's tenant
`QueryPayrollResultValues`	Flat wage type + collector result rows (filter by job name)
`GetConsolidatedWageTypeResults`	Deduplicated wage type results for one or more period starts
`GetConsolidatedCollectorResults`	Deduplicated collector results for one or more period starts

Prepare data in `ReportEndFunction`, not in the template

FastReport template expressions are designed for display formatting, not for business logic. Complex calculations, sorting, grouping, and conditional column construction belong in the scripting layer where the full DataSet API is available.

What to do in the script:

Rename raw Value columns to semantic names (M1..M12, Value, YtdValue, RetroValue)
Add computed columns via AddColumn<T>(name, expression) using DataColumn expressions
Remove irrelevant rows via DeleteRows(filter) before handing data to the template
Sort with DataView and re-import with ImportRow to preserve the table reference

What the template handles:

Number formatting (Format="Number", Format.UseLocale="true")
Conditional visibility and colour (Highlight / Condition)
Band repetition and grouping via DataBand / DataSource hierarchy

Guard against empty results explicitly

When no payrun data exists for the requested period, the result tables are empty but the employee table may still contain rows. An unguarded template iterates all employees and renders empty grids. Clear the employee table when there are no results to force the template to produce a genuinely empty report.

if (!hasWageTypeResults && !hasCollectorResults)
{
    employees.Clear();
}

Also guard early if the employee table itself is null or empty — exit Execute() with return null rather than proceeding through the loop.

Use `AddTable` / `RemoveTables` for temporary working tables

The report context maintains a named table dictionary. Temporary accumulation tables created with AddTable must be removed with RemoveTables after their results have been merged into the final output table — otherwise they appear as unused data sources and may interfere with template rendering.

DataTable employeeRows = AddTable("EmployeeRows");
// ... populate and merge into master table ...
RemoveTables("EmployeeRows");

Call AddTable before ExecuteQuery with the same name. If the query returns no results the table still exists in the DataSet, so a subsequent RemoveTables call will never fail.

Only tables that the template actually references should remain in the context after ReportEndFunction returns.

Use `SetPrimaryKey` / `RemovePrimaryKey` around `Merge` operations

DataTable Merge with MissingSchemaAction.AddWithKey uses the primary key to decide whether to update an existing row or insert a new one. The primary key must be set on the source table before merging, and removed from the target table before appending multiple employee slices (otherwise rows from different employees with the same key overwrite each other).

// source: merge by wage type number — updates existing row or inserts new one
employeeSlice.SetPrimaryKey("WageTypeNumber");
masterTable.Merge(employeeSlice, false, MissingSchemaAction.AddWithKey);

// target: remove key before appending next employee's rows
masterTable.RemovePrimaryKey();
nextEmployeeSlice.RemovePrimaryKey();
masterTable.Merge(nextEmployeeSlice);

Declare `AddRelation` after both tables have content

The AddRelation call registers a parent-child relation between two tables so the template can use nested DataBand elements. The relation must reference tables that already exist and are populated — declare it after the per-employee loop, not inside it.

// after all employees are processed
AddRelation("EmployeeWageTypeResults",
    employees.TableName, wageTypeResults.TableName, "EmployeeId");

The relation name in AddRelation must match the Relation Name attribute in the FRX template's <Dictionary> section exactly.

Never compute YTD or retro values during payrun execution

Period totals (YTD) and retro corrections are reporting concerns — they must be computed in ReportEndFunction, not inside wage type or collector scripts.

Why this is a hard rule:

GetConsolidatedWageTypeResults must be called once per prior month per employee. Executing this inside a payrun script means one or more SQL queries per wage type per employee per period — at scale this destroys payrun throughput.
A payrun result must be a pure function of the current period's inputs. Reading results from other periods inside a wage type expression creates hidden temporal dependencies that break retro recalculation correctness.
YTD values derived during the payrun become stale the moment a retro correction changes a prior period — they cannot self-update.

The only valid exception is a wage type that structurally requires a prior period value — for example, a delta wage type that collects the accumulated retro correction to pay it out in the current period. Even then, use the dedicated RetroSum accessor (GetRetroWageTypeValueSum) rather than querying consolidated results directly.

Wrong — never do this in a wage type expression:

// WRONG: queries consolidated results inside payrun execution
var ytd = GetConsolidatedWageTypeResults(...); // don't do this

See the dedicated sections below for the correct patterns: Computing period totals in a report and Computing retro values in a report.

Computing period totals in a report

The standard pattern in ReportEndFunction queries each prior month once, renames the result column, merges into an accumulation table, then adds a computed YtdValue column after the loop.

// loop m = 1..payslipMonth
for (var m = 1; m <= month; m++)
{
    var periodStart = Date.MonthStart(year, m).ToUtcString();
    var queryParams = new Dictionary<string, string>
    {
        { "TenantId", TenantId.ToString() },
        { "EmployeeId", employeeId.ToString() },
        { "PeriodStarts", $"[\"{periodStart}\"]" }
    };

    var slice = ExecuteQuery("MonthSlot",
        "GetConsolidatedWageTypeResults", queryParams);

    slice.RenameColumn("Value", m < month ? $"M{m}" : "CurrentValue");
    slice.SetPrimaryKey("WageTypeNumber");
    accumTable.Merge(slice, false, MissingSchemaAction.AddWithKey);
}

// add YTD computed column — one expression, evaluated by DataColumn
var ytdExpr = string.Join(" + ",
    Enumerable.Range(1, month - 1)
        .Select(m => $"IIF(M{m} IS NULL, 0, M{m})")
        .Append("IIF(CurrentValue IS NULL, 0, CurrentValue)"));
accumTable.AddColumn<decimal>("YtdValue", ytdExpr);

The same pattern applies to GetConsolidatedCollectorResults.

Computing retro values in a report

In ReportEndFunction, two calls to GetConsolidatedWageTypeResults are needed for each prior month: one without EvaluationDate (consolidated) and one with EvaluationDate = start of m+1 (original):

for (var m = 1; m < month; m++)
{
    var periodStart  = Date.MonthStart(year, m).ToUtcString();
    var nextStart    = Date.MonthStart(year, m < 12 ? m + 1 : 1).ToUtcString();

    // consolidated: includes retro corrections
    var consolidated = ExecuteQuery("EmployeeWageTypes",
        "GetConsolidatedWageTypeResults",
        new Dictionary<string, string>
        {
            { "TenantId",    TenantId.ToString() },
            { "EmployeeId",  employeeId.ToString() },
            { "PeriodStarts", $"[\"{periodStart}\"]" }
        });

    // original: value as of end of that month — before later corrections
    var original = ExecuteQuery("EmployeeWageTypesOriginal",
        "GetConsolidatedWageTypeResults",
        new Dictionary<string, string>
        {
            { "TenantId",       TenantId.ToString() },
            { "EmployeeId",     employeeId.ToString() },
            { "PeriodStarts",   $"[\"{periodStart}\"]" },
            { "EvaluationDate", nextStart }
        });

    // accumulate delta per WageTypeNumber
    var originalLookup = original?.AsEnumerable()
        .ToDictionary(r => (decimal)r["WageTypeNumber"],
                      r => r["Value"] is decimal v ? v : 0m)
        ?? new Dictionary<decimal, decimal>();

    if (consolidated != null)
    {
        foreach (DataRow row in consolidated.Rows)
        {
            if (row["WageTypeNumber"] is not decimal wtn) continue;
            var consVal = row[$"M{m}"] is decimal cv ? cv : 0m;
            originalLookup.TryGetValue(wtn, out var origVal);
            var delta = consVal - origVal;
            if (delta != 0m)
            {
                retroAccum.TryGetValue(wtn, out var existing);
                retroAccum[wtn] = existing + delta;
            }
        }
    }
}

// inject RetroValue column from accumulator
table.Columns.Add("RetroValue", typeof(decimal));
foreach (DataRow row in table.Rows)
{
    row["RetroValue"] = row["WageTypeNumber"] is decimal wtn &&
                        retroAccum.TryGetValue(wtn, out var retro) ? retro : 0m;
}

Emit a report log entry at the end of `Execute()`

AddReportLog writes a diagnostic entry visible in the Payroll Console and the web application after the report runs. Include the key dimensions (period, record counts) so problems can be diagnosed without re-running the report.

AddReportLog($"Payslip {year}-{month:D2}: " +
             $"{wageTypeResults?.Rows.Count} wage types, " +
             $"{collectorResults?.Rows.Count} collectors");

Best Practices: Reporting

Use the three scripting entry points for their intended purpose

Declare queries in the report definition, not in script

Filter queries with hidden OData parameters

Inject context parameters with parameterType

Use ExecuteQuery for dynamic per-employee, per-period data

Prepare data in ReportEndFunction, not in the template

Guard against empty results explicitly

Use AddTable / RemoveTables for temporary working tables

Use SetPrimaryKey / RemovePrimaryKey around Merge operations

Declare AddRelation after both tables have content