Advanced parameters: variables and cascading#

Parameters are usually independent: each supplies its own value. Their real power, though, comes from letting one parameter reference another parameter's value as a variable, so a query runs differently, or repeats, based on what a second parameter holds. This is how a report fans out one slice per item, one report per element, or a whole set of nested results, from a single template.

This page explains the mental model and the tokens behind it. For the task steps, see Parameterize a report; for the type catalog and replication modes, see the Parameters reference.

The core idea: a parameter inside a parameter#

Some parameter types carry a query, SQL Query, Planning Analytics MDX Query, and Cube Value. Inside that query text you can embed a token that stands for another parameter's value. At runtime Reportworq substitutes the token before running the query. Because the query's results become the referencing parameter's own values, a single parameter can be driven by another, and can repeat.

Only query-bearing types scan for these tokens. Plain Text Value and Text List parameters do not.

The three tokens#

%param:Name%, another parameter's value#

Write %param:Name% (or the equivalent %param_Name%) anywhere in a SQL, MDX, or Cube query, and Reportworq replaces it with the value of the parameter named Name before the query runs. This works in all query-bearing types, SQL Query, MDX Query, and Cube Value, not only SQL.

Example: a Departments SQL parameter whose query is SELECT dept_code FROM departments WHERE region = '%param:Region%' returns the departments for whichever region the Region parameter currently holds.

%global:Name%, a workspace global's value#

%global:Name% (or %global_Name%) substitutes the value of a workspace or global parameter the same way. Use it for values that are set once at the workspace level, such as a current fiscal year or a default currency, and shared across many reports.

Repeating and fan-out#

Embedding a variable is what makes a query repeat. There are two ways this shows up.

Cascading, one report per item#

Put %param:Region% in a SQL or MDX parameter's query, then set that parameter's replication to One report per item (see Parameters reference). The query returns one element per item for the referenced region, and Reportworq produces one output file per element. This is the cascading, or dependent, parameter workflow: pick a region, and get a separate report for each department in that region.

Multi-value concatenation (Cartesian)#

When the referenced parameter itself holds multiple values, the referencing query is run once per referenced value, and the result lists are concatenated. Reportworq builds the Cartesian product of the referenced values, runs one query per combination, then unions the rows.

Example: if Region holds North, South, and West, a Departments query that references %param:Region% runs three times, once per region, and the returned departments are combined into one list. Add a second referenced parameter and the combinations multiply accordingly.

When to use each#

• %global:Name%, for a value set once for the whole workspace (fiscal year, reporting currency, company code) that many reports should share without re-entering it.
• %param:Name% in a query, when a parameter's allowed values depend on another parameter. Choosing a region should narrow the department list to that region.
• Cascading, one report per item, when you want a separate, self-contained output file per element that a dependent query returns, for example one department report per department in the chosen region.

Notes and limits#

• Tokens are literal substitution. Reportworq replaces the exact token string before the query runs, so the surrounding query must remain valid after substitution (mind the quoting).
• Both token forms are accepted: %param:Name% and %param_Name% are equivalent, as are %global:Name% and %global_Name%.
• Cross-parameter tokens work in SQL Query, MDX Query, and Cube Value. They do not work in Text Value or Text List parameters.
• A referenced parameter with many values multiplies the work: N referenced values means the query runs N times and the results concatenate.

Related topics#

• Parameterize a report
• Parameters reference
• Worksheet output options