Report Data Editor Guide

The Report Data Editor is the visual query builder where your report’s foundational dataset is assembled. It serves as Step 1 of the execution pipeline, compiling high-performance SQL to fetch and shape data at the database level before passing it to the formula executor.

How It Works: The Pipeline Architecture

Building a dataset in the Data Editor follows a modular structure where data sources, known as Parts, are combined sequentially.

1. Adding Parts (Data Tables)

You begin by adding one or more Parts (tables, views, or custom queries) to your data pipeline.

2. Anchor & Joining Strategy

• The Anchor (Part 1): The first Part added acts as the Anchor table for the dataset pipeline.

• Secondary Parts: Every subsequent Part is joined to the Anchor or to preceding Parts using standard join types (e.g., LEFT, INNER, RIGHT, FULL) and explicit key matching conditions.

3. Output Generation (global vs. Direct joined)

Once all Parts are joined, you choose how the final Data Frame (DF) is generated:

• Direct Output (`df = joined`): Exposes the combined table directly to the formula executor.

• Global Query Output (`global`): Wraps the combined table in a master query, allowing top-level column selections, database-native aggregations, and metal-level window functions across the joined data.

Best Practices for High Performance

To ensure reports execute quickly at the database level, keep these optimization rules in mind:

• Filter Early and Aggressively: Always apply as many filters as possible directly inside individual Parts to reduce the data volume before join operations occur.

• Partition Pruning (Delta Tables): When querying Delta tables or partitioned data lakes, always filter on partition keys (e.g., year, month, region). This allows the query engine to skip unneeded files, drastically cutting down query execution time and resource usage.

Advanced Capabilities

Beyond standard table joins, the Data Editor includes several powerful features:

Dynamic Filters & Dimension Pipelines

You can populate dynamic report filters using special Dimension Pipelines. These pipelines run lightweight background queries to fetch distinct lists of values (e.g., active regions, current fiscal months) for user-facing dropdown selectors, driving responsive filtering without hardcoding inputs.

Global Limits & Sorts

Set top-level row limits (e.g. TOP 1000) and ordering rules at the database level to ensure snappy execution when inspecting large datasets during development.

Built-In Pivot Views

Transform long, transactional datasets into cross-tabulated matrix structures before passing them to the formula engine, letting the database handle matrix pivoting over heavy row volumes.

Complete Layout & Data Separation

The visual display layout is fully independent of the underlying database schema:

• Reorder & Hide: Freely rearrange column sequences, change display titles, or hide utility join keys without altering the underlying SQL output.

• Formatting Controls: Set unit formatting (currencies, percentages, decimal precision, and date patterns) directly in the UI visual layer without risking dataset corruption.

Custom SQL Fields (Calculated Fields)

The Data Editor allows you to create Calculated Fields that leverage native, arbitrary SQL expressions evaluated directly by the underlying database engine.

Custom fields extend the data model at Step 1 (metal level), making calculated output available across joins, filters, and subsequent formula operations.

Custom Formula Example                                                                              

COALESCE(stock, 0)
- COALESCE(out, 0)
+ COALESCE(in, 0) 

Common Use Cases

• Data Sanitization & Fallbacks:
  Use functions like COALESCE(column, 0) or NULLIF() to handle missing or NULL values cleanly before calculations take place.

• Custom Filter Expressions:
  Derive specialized flags or calculated dimensions (e.g., CASE WHEN sales > 1000 THEN 'High' ELSE 'Low' END) to simplify downstream visual filtering.

• Composite Join Keys (Advanced):
  Concatenate or format multiple attributes into a single composite string (e.g., CONCAT(store_id, '_', sku_id)) to enable multi-column key matching when joining Parts.

Performance Warning: Calculated Filters on Parquet/Delta Datasources

CRITICAL PERFORMANCE TIP:
When working with Parquet, Delta Lake, or file-backed datasources, NEVER apply filter conditions directly on calculated/transformed fields (i.e., WHERE f(column) = value).
Why?
File-based engines rely on metadata indexing (e.g., min/max stats per file or partition pruning) to skip unneeded files instantly. Applying a function f() wrapped around a column forces the engine to perform a full scan and evaluate every single row in the dataset, effectively killing query performance.

• ❌ Poor Performance: WHERE COALESCE(status, 'N/A') = 'Active' (Triggers full file scan)

• ✅ High Performance: WHERE status = 'Active' (Uses metadata & partition pruning)