Virtual Entities — Reusable SQL Building Blocks for Reports & Dashboards

You've probably been here before: the same complex SQL query keeps showing up in five different report-wizard records and three dashboard widgets. Every time you tweak it, you have to remember every place that copy lives. And the wizard's table picker only ever lists real entities like SalesInvoice or CostInTransLine — so anything that needs a UNION, a hand-rolled join, or a per-row sign flip has to keep being re-written by hand inside each widget's SQL editor.

Virtual Entities solve that. You write one SELECT (as fancy as you want — UNION, JOIN, expressions, aggregates, the lot), give it a code and a name, and the system turns it into a first-class entity. From that moment on, it shows up in the Report Wizard's main-table picker and in the Dashboard Widget Wizard exactly like a real table — same field selector, same auto-joins on reference fields, same translations, same parameters panel.

The Big Idea in One Example

Suppose you want to analyze stock movements that come from two different transactions: in-transit cost arriving (CostInTransLine, positive quantities) and in-transit cost leaving (CostOutTransLine, negative quantities). There's no single physical table that gives you both with a unified sign convention. So you write:

select l.totalCost, l.totalQty, l.item_id, l.legalEntity_id, l.valueDate
from CostInTransLine l
union all
select l.totalCost * -1, l.totalQty * -1, l.item_id, l.legalEntity_id, l.valueDate
from CostOutTransLine l

You save this as a Virtual Entity called InTransitMovements. Behind the scenes, the system creates a SQL Server view named vw_InTransitMovements, registers a new entity definition pointing at that view, and adds it to the model the wizards consult.

Now in the Report Wizard, you pick "Main Table → InTransitMovements", and you get:

• A field called item typed as Reference → InvItem (because item_id on the source table mapped to that property).
• A field called legalEntity typed as Reference → LegalEntity.
• A field called valueDate typed as Date.
• Numeric fields totalCost and totalQty.

You can drop them into a report, group by item, sum the cost and quantity, and you're done. No raw SQL anywhere in the report. The same Virtual Entity is also pickable from the Dashboard Widget Wizard — same auto-join behavior, same translations, same drill-down support.

Where to Find It

The Virtual Entity screen lives in the Basic module's menu. Open it like you would any other master file: click New, fill in the header, and you're in business.

The header has just a handful of fields:

• Code — short identifier, letters/digits/underscore only, must start with a letter. The system uses this to name the underlying view (vw_<code>) and as the entity's type name. Once you save, the code can't be changed (renaming would mean dropping and re-creating the view, which Phase 1 doesn't do — see What's Coming Later).
• Arabic Name / English Name — what users will see in the wizard's table picker, in field labels, and in screen titles.
• SQL Query — the SELECT statement that defines the entity. Multi-line; use UNION, JOIN, expressions, aggregates — whatever you need.
• Materialization — for now, only View is allowed. The Table option is reserved for a future phase that will materialize the data into a refreshable physical table.

After filling these in, click Save. Then, to map the SELECT columns to entity properties, click the Edit Mappings button at the top of the screen. That opens the editor where the real magic happens.

The Edit Mappings Dialog

The dialog has two halves stacked vertically.

Top — The SQL Editor

A Monaco editor (the same one that powers VS Code) with SQL syntax highlighting, autocomplete, and full-screen height. Edit your query freely; the system parses it as you type so it can spot which columns each property is mapped to.

Above the editor, the Bootstrap button does the heavy lifting: it parses the entire SELECT, walks every column in the result set, figures out which physical table and column each one came from, looks up the matching property on that table's entity definition, and proposes a complete property list. Click it and the bottom half fills up with suggested mappings.

If your SQL doesn't parse, the editor shows a parse error in red just above it. The Bootstrap button still works in that case, but the proposals will mostly come back as Unresolved — you'll need to fill in the property details by hand.

Bottom — The Property List

This is the table that defines what fields the wizard will see. Each row is one property, with these columns:

Two ways to add rows:

1. Click "Add Column". An empty row appears. The Column Name control on that row is a picker over the columns the parser found in your SQL — unmapped ones first (highlighted), then already-mapped ones marked "pick to overwrite". Picking a column runs single-column bootstrap and fills the rest of the row.
2. Use Bootstrap. As described above — replaces the whole list with the parser's proposal.

If you bootstrap and rows already exist, the system asks for confirmation first. Same for picking a column on a row that already has a bootstrap-derived mapping — it asks before clobbering your work.

Orphan Flagging

If you edit your SQL after mapping properties, and a property's Column Name no longer matches any column in the new SELECT list, the row gets badged with a warning: "This column is no longer in the current SQL query." The system doesn't auto-remove anything — you decide whether to re-bootstrap, pick a replacement column, or delete the row.

The re-parse runs ~2.5 seconds after you stop typing, so the badge updates as you work.

How Bootstrap Resolves Each Column

Understanding what the parser does makes it much easier to predict its output.

For a plain column reference like l.item_id from CostInTransLine l:

1. The parser sees item_id belongs to alias l, which is CostInTransLine.
2. It looks up CostInTransLine in the data model and finds the property whose underlying column is item_id — that's item.
3. It copies that property's full name (item), its type (Reference), its target entity (InvItem), and its Arabic/English labels into the proposal.
4. It overrides the Column Name on the proposal to whatever alias you used in the SELECT list, so the view's column name lines up.

Result: a fully-resolved row that needs no manual editing.

For an aggregate like SUM(l.totalCost):

The parser tags this as Aggregate Numeric. The proposal gets Decimal as the field type, no reference target, and uses the SELECT alias as the column name. You may want to fill in a friendlier label.

For an expression like l.totalCost * -1:

If the expression wraps a single underlying column, the parser still resolves to that column's property metadata — so l.totalCost * -1 resolves the same way l.totalCost would. If the expression mixes multiple columns or uses a CASE statement, the parser falls back to a Text type with no reference, marking the proposal as Expression Partial. Fill in the rest by hand.

For a UNION ALL (or any set operation):

The parser walks each branch and resolves columns position-by-position. For position N, it walks the branches in order and takes the first branch that produces a fully-resolved proposal. That's why this works:

select 0 as x from a
union all
select x from b

Branch 1 has a constant at position 0 (no source column). Branch 2 has b.x. The merger picks branch 2's resolution.

What Happens When You Save

Saving runs three things:

1. Validation — Code shape, no name collision with an existing entity, materialization is View, SQL is non-empty.
2. Data-model XML rebuild — The mapping JSON is converted into a DMEntity definition (the same shape every other entity has) and stored on the record.
3. View DDL — The system runs DROP VIEW IF EXISTS dbo.vw_<code> followed by CREATE VIEW dbo.vw_<code> AS <your SELECT> inside the same database transaction as the entity save. If the view DDL fails — bad syntax, missing column, illegal ORDER BY without TOP, permission issue — the whole transaction rolls back and you see the SQL Server error inline. Nothing partial gets persisted.

After the save commits, the data-model cache is rebuilt so the next call to the Report Wizard or the Dashboard Widget Wizard sees the new entity in their main-table picker. No restart, no manual cache clear.

Using Virtual Entities in the Report Wizard

Open the Report Wizard, click Main Table, and your virtual entity appears alongside the real ones. The picker shows them under the table type Virtual Entity (parallel to Entity, Detail Line, and System Table).

From there, everything works as if you were on a real table:

• Field selection — Click Select Fields and you'll see the property list you defined: item, legalEntity, valueDate, totalCost, totalQty. Reference fields auto-expand to give you item.code, item.name1, item.name2, etc.
• Parameters — Add valueDate as a parameter with Between filter type and you get the standard date-range prompt.
• Aggregations — Drop totalCost and totalQty in as measures, group by item, get a totaled report.
• Translations — The Arabic/English names you set on each property show up as the column headers.

See the Report Wizard Guide for the rest of the wizard mechanics — they all apply unchanged.

Using Virtual Entities in the Dashboard Widget Wizard

In the Dashboard module, when you create a widget backed by a wizard data source, the same main-table picker appears with your virtual entity listed. Once you pick it, every BI feature that wizard-backed widgets get — dimension drill-by, auto-inferred cross-filter columns, field-ID pickers — works against the virtual entity exactly as it would against a real one.

This is where Virtual Entities pay off the most: you build one once, and from then on you can spin up new dashboard widgets visually without touching SQL again.

For details on how wizard-backed widgets behave, see the BI Module Guide and the Wizard Mode reference.

Worked Example — In-Transit Cost Movements Report

Let's walk the user-facing flow end to end with the SQL from the opening section.

Step 1 — Create the Virtual Entity

Open Virtual Entity from the Basic module menu, click New, and fill in:

• Code: InTransitMovements
• Arabic Name: حركات البضاعة في الطريق
• English Name: In-Transit Movements
• Materialization: View
• SQL Query:

select l.totalCost, l.totalQty, l.item_id, l.legalEntity_id, l.valueDate
from CostInTransLine l
union all
select l.totalCost * -1, l.totalQty * -1, l.item_id, l.legalEntity_id, l.valueDate
from CostOutTransLine l

Save the header.

Step 2 — Map the Columns

Click Edit Mappings. The dialog opens with the SQL already loaded. Click Bootstrap. The property list fills in:

Click OK. The dialog closes; on the entity screen, click Save. The view vw_InTransitMovements is created and the entity registers itself in the data model.

Step 3 — Build a Report

Open the Report Wizard, create a new wizard, and pick In-Transit Movements as the Main Table. Add fields:

• this — the entity reference (so you can drill down)
• item — auto-joined to InvItem because the property is a reference
• valueDate
• totalQty (with Sum aggregation)
• totalCost (with Sum aggregation)

Add valueDate as a parameter with filter type Between. Save and run. You get a per-item summary of net in-transit movement over your selected date range, with no raw SQL anywhere in the wizard.

Edge Cases & Common Pitfalls

Trust Model — Who Should Have Access

A Virtual Entity definition runs SQL inside the application's database connection. There's no sandbox: whatever the application user can do, the Virtual Entity's SELECT can do. That means:

• Restrict the Virtual Entity menu and entity-edit permissions to power users / admins.
• Don't expose the entity-creation screen to roles you wouldn't trust to write arbitrary read-only SQL against the production database.
• The view created from the SELECT is a normal SQL view — anyone who can read its rows can query it freely once it exists, but creating/editing the definition is what needs gating.

What's Coming Later

Phase 1 ships Virtual Entities as views only. The JSON config already carries forward-compatible fields for the next phase, so anything you save now will round-trip cleanly when these features land:

• Materialization → Table — physical pre-aggregated tables for queries that are too expensive to run on every dashboard load.
• Refresh policies — manual, scheduled (cron), or on-write refresh of materialized tables.
• Index management — define indexes on the materialized table for query performance.
• Code rename — drop the old view and create a new one in a single transaction.
• Dependency tracking — see which reports, dashboards, and other virtual entities depend on a given virtual entity before you change or delete it.

For now, treat materialization as a roadmap signal: the dropdown shows Table disabled with a "Coming later" tooltip.