Data models#

A data model is a reusable, governed layer between a source system and the people who build reports. It binds a datasource to one or more curated views, and those views are what report authors import when they build Excel Reports in the Excel add-in. A model lets you rename and reshape source columns into clean business fields once, so every report reads from a stable vocabulary that survives changes to the underlying source schema.

A model feeds two downstream workflows, not just reporting: Distribution Jobs (template-based output) and Contribution Campaigns (input forms). A contribution form binds to a model view the same way a report does, so curate a model as a shared vocabulary, not just a report source.

Modeling a single workbook or database that lives in this workspace? You do not need a datasource connection at all. See Build a data model from a workspace file - it is the quickest way to get from a file in your workspace to a working model, with nothing added to Integrations.

Before you begin#

• Either a datasource connection exists and tests successfully (see Connect a data source), or the model will read an Excel workbook or SQLite database that already lives in this workspace (see Build a data model from a workspace file).
• You have permission to author data models in the workspace.

The model editor at a glance#

A model is edited in the workspace. The rail on the left lists the model's Source and each of its views, with Add View below them. The Source entry's second line names the connection, or the workspace file, the model reads (No source until you choose one). Each view shows its name, a type, and small chips: its field count (for example 21 fields) and Draft while it is not published. The type reads Table or View for a view built on the designer from one table, Joined view with its table count (for example Joined view · 4 tables) once the designer canvas holds more than one table, and SQL Query for a view written as SQL. Select an entry to open it; your place on each screen is kept while you move between them, so you can check a table on Source and return to a half-built view without losing it.

• Source (model level). On the Source screen's Connection section, a Connection / Workspace file toggle under Bound datasource picks how the model reads its data: bind to a configured datasource under Connection, or point straight at an Excel workbook or SQLite database that already lives in this workspace under Workspace file (select Browse… - see Build a data model from a workspace file). Under Bound datasource, below that choice, the same section carries two model-wide settings, Direct queries and Field filtering, both covered below. On a SQL datasource - including a SQLite workspace file - the Source screen also has Tables and Relationships sections, so you can look at the datasource's rows and links before you build a view. See Explore the source.
• Views. A view is the model's unit of output: one source query plus a curated set of fields. Add View creates another view, and a model can hold several. Each view has four tabs, and every tab is always available:
  • Design (on SQL datasources) or Connection (on every other connector) defines what the view queries, whether the model is bound to a configured connection or a workspace file. On a SQL datasource you build the view on a canvas of tables and their relationships, or write it as SQL. See Build a view with the designer. On other connectors the tab holds that connector's own query settings, for example a cube data view for Planning Analytics; use Browse datasource to explore tables and columns, then Import Fields (or Update Fields on a re-import) to generate the field list from the source.
  • Fields is the curated reporting field list, auto-generated on import or on a designed view's first save. Each field decouples its reporting-facing Id and Name from the source column it maps to (the Mapped Field), so renaming or re-mapping a source column does not break downstream reports. Fields can be hidden, reordered, renamed, re-typed, filtered, added, or deleted.
  • Variables are model variables that parameterize the source query and flow downstream to reporting and bursting. A variable's default is what preview and reports resolve when nothing overrides it.
  • Preview runs the view and shows the resolved rows after variables and field projection are applied.

Fields follow the query. There is no button to import fields. When you leave the Design or Connection tab, and when you select Save Changes, Reportworq runs any query that changed and brings the view's fields up to date. While it runs, the view reads "Updating fields from the query". If the query cannot run yet, for example because a {{variable}} has no sample value, a warning explains why and the view reads "Fields may not match the query yet" until the next tab change or save. On the designer canvas, fields are added as you tick columns, so a designed view is always up to date.

Explore the source#

The Source screen has up to three sections, chosen from the strip under the source's name: Connection (where the model reads its data, plus Direct queries and Field filtering), and, on a SQL datasource, Tables and Relationships. Tables and Relationships share one table list, so the table you select stays selected when you switch between them. A model with no source yet opens on Connection; a connected SQL model opens on Tables.

Use Tables to find the table a view should start from, and Relationships to check how tables link before you build.

1. Select Source in the rail, then Tables.
2. Select a table in the list on the left. Use Search tables to narrow a long list.
3. Review the table's rows. The row count under the grid shows how many rows were read, and the grid shows the first 100 of them. Tables reads at most 1,000 rows; for a larger table the count reads first 1,000.
4. Optional: select the clipboard button to copy the rows with a header row, ready to paste into Excel, the Excel button to download them as a workbook, or the small copy icon beside the table name to copy the name.
5. Select Relationships to see which tables the selected table links to. See Table relationships.

The refresh button reads the table again. To see more than 1,000 rows, build a view and use its Preview, which can show all rows.

Curate the fields#

The Fields tab is where a raw source becomes a governed model. Each field shows a green mapped chip or an amber unmapped chip, recomputed automatically as you edit.

• Unmapped fields are excluded until you remap them. A field that is unmapped, whether you added it without a mapping or it lost its mapping after a source change, is left out of the model, the preview, and every report until you point it back at a source column. Unmapped does not mean "author fills it in later", there is no calculated or literal field type; unmapped simply means excluded.
• Field names must be unique within a view. A duplicate display name is rejected and the edit reverts.
• The Fields list is not paged. Field order is set by dragging rows, and a drag cannot cross a page boundary, so every field is listed on one scrolling list however many there are. Other long lists in Reportworq page at 100 rows, this one deliberately does not.
• Hidden fields stay queryable. Hiding a field removes it from the report author's field list but keeps it usable as a filter reference, so you can filter on a key column without exposing it. Use Hide all or Show all to toggle visibility in bulk.
• Rename many fields at once. The Rename menu offers Tidy names, which turns source names such as Account_FullyQualifiedName into Account Fully Qualified Name and Transaction_Type_Id into Transaction Type ID, and Edit names as text, which lists every name one per line in field order so you can edit them in place or paste them back from a spreadsheet. Line 1 renames the first field, line 2 the second, and so on, so Apply names stays unavailable until there is exactly one non-empty, unique name per field. Both change display names only; reports bind to field ids and keep working, and Discard undoes a rename you do not want.
• Quote a text value only when it lands in a SQL string literal. A {{variable}} is substituted into the source query as a raw text replace, with no automatic quoting. When a variable feeds a SQL query on a relational source (SQL Server, OLE DB, ODBC, SQLite, or a CData source), a text value that must become a SQL string literal has to carry its own quotes in the query, for example '{{state}}' resolving to 'Maine'; numeric values are bare. This is a SQL-syntax requirement of the query, not a property of the variable's default. Sources whose query text is not SQL, such as Planning Analytics and the planning-tool APIs, do not need the quoting.

Choose where filters run (filter mode)#

The Field filtering setting on the Source screen's Connection section decides where a model's field filters are evaluated. Choose Database-side or Client-side; a note beside it reads "Filters run in the source query (faster on large tables)." or "Filters run in memory after the data is fetched."

• Database-side (pushdown) translates the filter into the source query, so the source engine returns only matching rows. Choose this for large sources and selective filters: it minimizes data transfer and leans on the source's indexes.
• Client-side (in-memory) retrieves the rows first and filters them inside Reportworq. Choose this when the result set is already small.

Important: pushdown is SQL-only. Non-SQL sources (Planning Analytics, the planning-tool APIs, and other non-relational connectors) always filter in memory regardless of this setting. So for a TM1 or Workday Adaptive model, filters are applied client-side whatever the mode says.

Direct queries: modeled view or direct query#

Direct queries is a checkbox on the Source screen's Connection section, labeled "Report authors can query this datasource directly", and it defaults off. Leaving it off keeps the model view-only: authors consume the curated, published views. Turning it on lets a report author query the datasource directly and bind to the query result's own columns, skipping the model's curated fields entirely.

When to use each:

• Use a published view for governed, reusable, change-resilient reporting. The field-mapping abstraction survives source schema changes, and the view is reusable across many reports and forms.
• Use a direct query for a one-off, well-understood, or exploratory pull, for example running a known SQL extract or inspecting a source before deciding whether to invest in a curated view. Direct-query results do not carry the field-mapping abstraction, so they do not survive source changes and are not centrally reusable.

Publish a view#

Each view carries a Draft or Published state, tracked per view, so one model can hold a mix of both.

1. Build and preview the view in Draft while you are still shaping it. Draft views are hidden from report authors.
2. Switch the view to Published when it is ready. Only published views appear in the Excel add-in's Connect picker for report authors to import.

When to use the two states together: publishing gates the picker, not existing bindings. A report already bound to a view keeps resolving even after you pull that view back to Draft, so you can safely revise a published view in Draft while the live reports keep running, then re-publish once the revision is validated.

Notes and limits#

• The Preview tab runs the view fresh each time, with no cache. Report display and formatting (grouping, aggregates, sort priority, format modes) are authored in the Excel add-in's list-report builder, not in the model editor.
• Opening a model in View mode redirects to Edit (for Authors) or Properties (for Users); a model has no separate read-only surface.

Going deeper. To build a view on a SQL datasource, see Build a view with the designer and Table relationships. For the field labels and query types of a specific source, see Connect IBM Planning Analytics, Connect SQL Server, OLE DB, and ODBC, and the Connector catalog. To bind a model to an Excel workbook or SQLite database in this workspace with no Integrations connection, see Build a data model from a workspace file.