Data model variables#

Early release. This feature is in early release, so its screens and behavior may change. Tell us what you think with Send feedback. See Early release features and Send feedback.

A variable lets a report supply a value to a data model view at run time: the fiscal year of a P&L, the region of a burst, the cost center of a budget submission. You write it as a {{Token}}, for example {{Year}}, and set how it behaves on the view's Variables tab. The values flow downstream to Preview, to reports, to bursting, and to the Excel add-in.

Where variables come from#

A view's Variables tab shows each variable the view uses as a card, headed by its caption (once you set one) and its {{Token}}, in up to three groups:

The sample value is not a default#

A view whose query has a {{Token}} needs a value to run while you build it, so the Design or Connection tab shows a Connection values sidebar for a sample value. Reportworq uses the sample to run the query, work out the view's fields, and fill in Preview while you author.

The sample is only for authoring. It never becomes the variable's default, and reports never see it. Set the default on the Variables tab. Preview starts from the default, and from the sample value when a view has no default. A joined view never asks for a sample value: see Variables of a joined view.

Set a default and whether a value is required#

1. Open the data model, select the view in the rail, and select the Variables tab.
2. In the variable's card, enter its Default: the value Preview and reports use when nothing overrides it, for example 2024. For an optional filter variable the box reads Optional: leave blank to skip the filter; otherwise it reads Default value.
3. For a filter variable, select or clear Required at the top right of the card. A connection variable always shows Required and cannot be made optional.
4. Select Save Changes.

A report, a burst, or the Excel add-in can override the default with its own value. In Preview, a required variable left blank brings a warning, "No value for required variable(s)", that names it.

Variables in Preview#

The Variable values pane in Preview starts with each variable's default. For a joined view that is the same value a run would use. Two rules then apply:

• A box you change keeps your value until you select Reset to defaults. That includes a box you clear. Editing the model elsewhere does not refill it.
• A cleared box runs blank. An optional filter variable with no value is dropped, so you see all rows. A required variable with no value is refused, with a warning that names it.

A box you have not touched follows the view's defaults as you change them.

Set how a report prompts for a variable#

Select Prompt at the top right of a variable's card to open its prompt settings. They decide what the person running the report sees when asked for the value.

• Caption and Help text name the variable and explain it in the prompt, for example Fiscal year and "The year of the actuals to report".

• Choices sets where the values to pick from come from:

  • Free text: the person types a value. This is the default.
  • A view of this data model: pick a view, then the field whose values become the list, for example a cost-center lookup view and its Cost Center field. Any other view of the same model can supply the list.
  • Parameter list: select Set the list… to open the same parameter editor the job editor uses, titled Choices for {{Name}}. Every parameter type your installation offers is available: a SQL query, a Planning Analytics subset or dimension, an Excel range, a list of values, a global parameter, and the rest. See Parameters reference. An Excel Range list can take its values By column (pick a worksheet and one of its column headers; the list follows the data as rows are added), or By range (a named range or an address, with My data has headers); see Excel Range lists. The card then reads List from: and the type, with Edit the list… beside the drop-down to change it.

• Also allow typing a value that isn't on the list appears once Choices is a view or a list. Leave it off to limit people to the list. Turn it on when the list is a convenience rather than the complete set of valid values.

  With it off, the variable is pick only. In this release the choices shape the variable's prompt in the data model's Preview: a drop-down of the list, or a box you can also type in when the option is on. Preview shows a plain message when a value isn't on the list, for example "East2 isn't one of the choices for Region. Pick a value from its list.", so you can check the rule before readers use it. A report built in the Excel add-in uses its own value as given, whether typed or read from a cell. A job is never refused: a value that a job supplies, from a parameter or a burst, is used as given, so bursting with any value works.

• In the query: Raw query text, pasted as is is the opt-in described in Use a value as query text. It appears for a connection variable on a view whose query text Reportworq fills: a SQL query, a Planning Analytics MDX query, a Power BI or Analysis Services MDX or DAX query, or a Pigment export view. In a Pigment export, values in the URL and the JSON body are otherwise encoded; the opt-in pastes the value unencoded.

Select Save Changes when you are done.

How a value is filled into a query#

People type values into prompts and Excel cells feed values into variables, so Reportworq makes each value safe where it is pasted into a SQL, MDX, or DAX query. A value can never change what the query does: it can only be a value. Reportworq looks at where each {{Token}} sits in the query text and fills it accordingly.

The rules by position are the following.

Two kinds of bare value are accepted as well as a number:

• A list, in SQL, when the token sits directly inside parentheses: IN ({{Regions}}) with the value 'East', 'West', or IN ({{Years}}) with 2023, 2024.
• Member names, in MDX, such as [Region].[East] or [Region].&[12], and comma lists of them, so bursting on members still works.

A value that breaks a rule fails the run, and nothing is sent to the source. The message names the variable and says what to do. For a bare token in SQL it reads:

The value for “Region” can't be used: it is pasted into the query without quotes, so it only takes a number (or, inside IN ( ), a list such as 1, 2, 3 or 'East', 'West'). For a text value, the data model's author should put quotes around it in the query: '{{Region}}'. Only as a last resort, for query text such as a column name, can the data model's author mark the variable as raw query text, which turns this protection off and pastes readers' values unchecked.

In MDX the message asks for brackets instead ([{{Region}}]), and in DAX for double quotes ("{{Region}}"). On a connection whose query uses syntax that databases read differently (see Database rules), the message says that the variable can only take a plain number unless the author marks it as raw query text, and suggests rewriting that part of the query.

What fails and how to fix it#

Write tokens so they fill cleanly#

The following patterns cover most queries.

Database rules#

How a database reads quotes and comments differs, so Reportworq applies the rules of the view's connection:

• SQL Server: a backslash in a string is an ordinary character, and block comments can nest.
• SQLite: a backslash is an ordinary character, and block comments do not nest.
• ODBC, OLE DB, and CData connections can reach any database (MySQL, PostgreSQL, Oracle, Snowflake, and others), so they get the strictest rules. A backslash inside quotes is refused. And if the query itself uses syntax that some of those databases read differently, every token in it takes only a plain number. That syntax is a backslash inside a literal, # or // comments, $ (dollar) quoting, [brackets], triple quotes, Oracle q' quoting, E' strings, /*! comments, nested /* comments, or -- with no space after it. Rewrite that part of the query, or connect with a SQL Server or SQLite connection where one applies.
• SQL that runs a string as SQL, such as EXEC('…'), EXECUTE, sp_executesql, PREPARE, or EXECUTE IMMEDIATE: a value that is safe as text becomes code once the string runs, so every token in such a query takes only a plain number. Pass values to dynamic SQL as sp_executesql parameters instead.
• Any connection: a lone carriage return in the query text (an old Mac line ending) makes every token take a plain number. Save the query with normal line endings.

Planning Analytics, Analysis Services, and Power BI#

MDX and DAX follow the same idea:

• A bare MDX token takes only a number or member names. A value such as East in {[Region].{{X}}}, before .Children, in a range, or as a set expression is refused. Put the token inside the brackets instead: [Region].[{{X}}].
• An ordinary MDX string is a plain text value: in a comparison such as .Name = "{{Region}}" the quote is doubled and any text works. But a string that Planning Analytics and Analysis Services read again as MDX (the argument of StrToMember, StrToSet, StrToTuple, or StrToValue, or the body of WITH MEMBER or WITH SET … AS '…') gets the bare-token rule, outside a [name].
• A bare DAX token takes only a number. Put it inside "…" for text.
• Names that Reportworq passes to Planning Analytics as names rather than MDX refuse [ ] { } ( ), quotes, ;, and line breaks. That covers the dimension, hierarchy, and subset of an Elements query, and the cube and view of a Data view query.

Field filters#

A value that reaches a field filter, and is pushed down to the source as part of its query, is written by its field's type: a number is checked and written as a number; text, dates, and true/false values are quoted with their quotes doubled; and each item of a list is written the same way. Nothing in the filter needs quoting by hand.

Adaptive Planning names#

A value placed in an Adaptive Planning sheet, version, period, level, or dimension name is not checked by position: it is filled in as given. Give such a variable a list of Choices with Also allow typing a value that isn't on the list turned off.

Use a value as query text#

Sometimes a variable is meant to change the query itself: a column to sort by (ORDER BY {{SortColumn}}), a table or schema name (FROM {{Schema}}.ledger), or an MDX set expression. A value like that is not a number, so it is refused unless you opt in.

1. On the view's Variables tab, select Prompt on the variable's card.
2. Under In the query, select Raw query text, pasted as is. A note under it reminds you that readers' values are then pasted into the query unchecked, and that quoting the token is the better choice for text.
3. Select Save Changes.

The value is then pasted into the query exactly as given, with no checking. The setting is stored on the view's own query, so only the data model's author can turn it on: nothing a report or a caller sends can set it.

Warning: A raw query text variable can change what the query does. Whoever can supply its value can rewrite that part of the query against the source, with the connection's permissions. Turn it on only for a variable that genuinely holds query text, and give it a list of Choices with Also allow typing a value that isn't on the list turned off, so people pick from known values.

If you are upgrading#

Earlier releases pasted a value into the query as raw text. Queries that relied on that can stop working after the upgrade. Check views that match any of these patterns:

• IN ('{{List}}') with a value like East','West. The value used to expand into several items. It is now one string, East','West, which matches nothing, without an error. Write IN ({{List}}) with the value 'East', 'West' instead.
• A token used as SQL text, such as ORDER BY {{Col}}, FROM {{Table}}, or {{Schema}}.x, now fails until you select Raw query text, pasted as is (see Use a value as query text).
• A bare date, such as WHERE posted_on >= {{Day}} with 2024-01-01, now fails. Quote it in the SQL, '{{Day}}', or opt in as above.
• On ODBC, OLE DB, and CData connections, a value with a backslash (DOMAIN\user, a file path) is refused inside quotes, and a query that uses [brackets], #temp tables, $ quoting, // comments, triple quotes, Oracle q' quoting, or -- with no space after it takes numbers only unless you opt in. Use a SQL Server or SQLite connection where one applies.
• SQL that runs a string as SQL (EXEC('…'), sp_executesql, PREPARE) takes numbers only in every token, unless you opt in. Pass values to dynamic SQL as sp_executesql parameters instead.
• In MDX and DAX, a bare token takes only a number (or member names, in MDX), and so does a token inside a StrToMember-style string or a WITH … AS '…' body. Move it inside brackets in MDX ([Region].[{{X}}]) or inside "…" in DAX, or opt in.
• Planning Analytics names refuse the characters listed above: the dimension, hierarchy, and subset of an Elements query, and the cube and view of a Data view query.
• A sample value is no longer copied into an empty default. A view saved by an earlier release may already carry a default that was copied from its sample value. Check the Variables tab and clear any default you did not mean to set.

The run's error names the variable and the reason, so the failing view is easy to find.

Related topics#

• Data models
• Join views across sources
• Build a view with the designer
• Reportworq 6 release notes