Parameters reference#

This page is the exhaustive reference for parameters on the Parameters step of the Job Editor: every parameter type, how each parameter is mapped to a worksheet, how list-valued parameters replicate output and cross-join, and how parameter sets work. For the task walkthrough, see Parameterize a report. For variables, cascading, and repeaters, see Advanced parameters.

Bursting, replication, and burst sets: the words first#

These three words get used interchangeably and they are not the same thing. Getting them straight makes the rest of this page, and the Bursting step itself, much easier to read.

Bursting is the outcome: taking one report and producing many versions of it from one set of parameter values. What gets multiplied can be a single worksheet, a group of worksheets copied together, or an entire report output.

There are three ways to achieve it, in increasing order of control:

The distinction that trips people up: you do not have to flag a parameter Burstable to get many outputs. If a parameter already has multiple values, from a Planning Analytics subset, an MDX query, a SQL query, or a typed Text List, and you set a replication mode, it is already fanning the output out. That is replication, and it needs nothing from the Bursting step.

Flag a parameter Burstable only when you actually want the Bursting step, because you need burst sets, per-file parameter control, or recipient-driven delivery. Throughout this guide, output fanned out by a parameter's own values is called replication; burst set means specifically the Bursting-step feature.

See What bursting is and when you need it.

Parameter types#

The Type dropdown is plugin-driven: the five Native types are always present, and each connector contributes its own types when a connection of that kind exists. The default type is None.

Connector types appear only when a connection of the matching kind exists: a Planning Analytics connection for the Planning Analytics types, a SQL connection for SQL Query, and so on.

Per-worksheet mapping (Location)#

Parameter definitions are held at the report level, where each name is unique. Each parameter is then mapped per worksheet on the Reports step, and its Location determines where its value is read. A parameter's value is set once, at the report level, not per worksheet. You can map the same parameter onto many worksheets, and often will, but every one of those worksheets receives the same value. There is no way to give one worksheet a different value for the same parameter name. If two worksheets genuinely need different values, they need two parameters.

Replication modes#

Every parameter has a Replication mode that decides whether the parameter fans the output out, and how. You set it in the parameter's edit panel, under Parameter Options. The mode is one of:

No replication#

Choose No replication when the parameter is a single value that should never fan the output out (a reporting period, one legal entity, a scenario). How you set it depends on the parameter type:

• A Text Value parameter is always a single value, so the replication area is hidden and the mode is fixed to No replication. The panel shows the note "A text value is a single value, so no replication applies," and there is nothing to choose.

• For any other type (a Text List, a SQL or MDX query, a dimension selection), No replication is one of the choices in the mode list. Because a dynamic query can return zero, one, or several values, you also declare what Reportworq should do if the parameter does not resolve to exactly one value:

The single-value check runs when the job builds its run, before any worksheets are produced, so a Fail choice stops the run early rather than partway through.

Related controls:

• Collate worksheets orders "one page per item" replications by parameter across worksheets (for example Income Statement then Balance Sheet for US, then the same pair for UK, then Canada), rather than grouping all copies of one sheet together. See Job options.
• Burstable flags a parameter for bursting, which reveals the otherwise-disabled Bursting step and uses the same replication machinery to fan output out per recipient.

Multi-parameter cross-join (Cartesian)#

When more than one replicating parameter is present, the runtime takes the Cartesian product of their value lists. For example, 3 business units cross 4 departments produces 12 worksheets or reports.

Only One page per item and One report per item parameters take part in the cross-join. A No replication parameter is a single value, not a replication axis, so it contributes its one value to every combination without multiplying the count.

Parameter order controls the sequence#

The order of the parameters decides the order of the output, because the first parameter is the outer loop. With 3 business units and 4 departments:

Business Unit first, then Department, gives you BU1 with departments A, B, C, D; then BU2 with A, B, C, D; then BU3 with A, B, C, D. The output is grouped by business unit.

Flip the parameters so Department is first, and you get department A with BU1, BU2, BU3; then department B with BU1, BU2, BU3, and so on. Same 12 outputs, grouped by department instead.

Choose the order that matches how the reader wants to read the pack.

When you do not want every combination#

A cross-join is exhaustive, and often you do not need every cell of it. Not every department exists in every business unit. You have two options, and they have very different performance profiles.

Page suppression discards the pages you do not want after they are produced. Set it up on the Reports step. It is the simpler route and it is the right one when most of the combinations are wanted and you are trimming a few. Be aware of what it costs: the data for a suppressed page is still fetched and the page still generated, then thrown away. With 12 pages and 2 suppressed, nobody notices. With 5,000 combinations of which you keep 200, you have paid for 5,000.

Parameter sets avoid producing the unwanted combinations at all, because you state the combinations you want rather than deriving them. When the product is large and the retained set is small, this is much the faster route. See below.

Parameter sets#

A parameter set is a finite, explicit list of combinations, not a cross-join. It applies to Text-List parameters on cell-based reports only.

When a parameter set is worth it#

This is an advanced feature and it is not the everyday way to fan output out. Most reports want a replicating parameter, which is simpler and needs no combination list. But parameter sets earn their keep in one specific situation, and there they are the only good answer.

Reach for a parameter set when the combinations you want are sparse. Continuing the example above: a cross-join of business units and departments produces every pairing, but suppose only 30 of those 5,000 pairings actually exist in your organization. A cross-join would query and build all 5,000 and let you discard 4,970 by page suppression, which works and is slow. A parameter set lets you list the 30 real combinations, and only those 30 are produced.

So the decision is:

• Combinations are dense (most pairings are real, you want nearly all of them): use replicating parameters and let the cross-join do the work. Trim the few strays with page suppression.
• Combinations are sparse (a small, known set out of a large possible space): use a parameter set.
• You need per-file control over each output, or recipient-driven delivery: use a burst set on the Bursting step.

The other case for a parameter set is when no query can express the combinations you need. If the pairing of values is a business rule rather than something derivable from the data, listing it explicitly is clearer than contorting a query into producing it.

• Enter each combination as a pipe-delimited value line, and set a pipe-delimited Location. For example, a value line of East|Q1 with a Location of B3|B4 writes East into B3 and Q1 into B4.
• Each value line produces exactly one worksheet. Multiple lines fan out one worksheet per line.
• A part-count mismatch between the Location and the value (for example a 3-part Location with a 2-part value) is a silent no-op: nothing is written and the cells are left blank. No error is raised, so watch for this when debugging an unexpectedly empty output.

Limits#

• Parameter names must be unique within the report.
• Bursting is available only once a parameter is flagged Burstable.
• Parameter sets and cell-writing mappings work only for cell-based reports. Specification-based providers take the value directly and do not use a cell reference.
• Only Text List supports parameter sets, and the reason is structural: a set is a list of literal, pipe-delimited combination lines that you type. A query-driven type returns one value per row, so it cannot express a combination.

Related topics#

• Parameterize a report
• Advanced parameters
• Worksheet output options
• Job options