How parameters work#

A parameter is a value the job pushes into a cell in the workbook before it calculates. Change the cell, the formulas recalculate, and you get a different report from the same template. Everything else about parameters is elaboration on that.

Timberline uses every value source and both replication modes, so it doubles as a reference.

Where a parameter's values come from#

The lesson in that table is the second column: prefer a source the model already owns. A text list is something you now have to maintain; a subset or an MDX query is not.

What replication does#

Every parameter has a replication mode, and it decides the shape of the output:

• One report per item: a separate output file, and a separate delivery, per value.
• One page per item: the worksheet repeats inside one output.

Board Packet sets Region to one report per item over four values, and produces four decks. Board Packet with Division Slides stays at total-company level and sets Market to one page per item over the same four values, and produces one deck whose sales revenue slide repeats once per division.

A job can use both at once. Divisional Packet gives each region its own file, containing a page per market.

Those last two are worth opening together, because they are the same idea pointed in different directions: the division-slides deck repeats divisions inside one output, and the divisional packet repeats the output itself per division, then repeats markets inside each one.

The pattern worth learning: a parameter inside a parameter#

This is Timberline's best trick. In Divisional Packet:

Region : {DrillDownMember({[THC_Market].[Total Company]}, {[THC_Market].[Total Company]})}
Market : {DrillDownMember({[THC_Market].[%param:Region%]},  {[THC_Market].[%param:Region%]})}

Region drills one level down from Total Company, giving North America, EMEA and APAC: one output each. Then Market drills down from %param:Region%, the value of the output currently being built. So the market list is re-evaluated for every region, and each divisional pack contains exactly its own markets.

Nothing is hard-coded. Add a region to the model and a pack appears for it.

Sales Rep Scorecard does the same over people:

Manager : FILTER( leaves of [THC_Employee], [THC_Employee].[Role]       = "Manager" )
Rep     : FILTER( leaves of [THC_Employee], [THC_Employee].[Manager ID] = "%param:Manager%" )

One report per manager, one page per direct report, and the org chart stays true because it is the org chart.

A third variant reads a single value out of a control cube rather than expanding a list:

Year : {StrToMember("[THC_Time].[THC_Time].[" & [THC_Control].([Controls].[Current Year],[Control_m].[Value]) & "]")}

Roll the year in the model, and every report that reads it follows.

Size before you schedule. Cascading multiplies. Three regions with four markets each is twelve pages; the same pattern over a large dimension fans out fast.

Getting the value into the workbook#

A parameter is only a value until a worksheet maps it to a place in the sheet. On the Reports step, each worksheet lists its parameters, and the value field on a row is where you set that place. Click it and a picker opens, listing everything on that sheet a parameter can point at:

Empty columns are hidden, so a sheet with no pivot simply shows its ranges. You can also just type a cell like F2, a range, or leave it blank if the value doesn't need to appear in the sheet.

Timberline shows the range styles:

There is also a lesser-known option the picker now surfaces: map a parameter to a pivot field (PivotTable.Field) instead of a cell. The value is applied as a pivot filter: a page field jumps to that item; a row or column field filters to it, so a single pivot report re-slices per value without a helper cell.

Two rules follow:

1. One target per worksheet. A parameter maps to a single place on each sheet. Needing the same scope on two sheets means two named ranges, which is exactly why the compensation form has Employee and Employee2.
2. Prefer named ranges to addresses. A named range survives someone inserting a row; F2 does not, which is why the picker marks sheet ranges preferred.

Reorder a worksheet's parameter rows by dragging the grip on the left of a row; remove one with the trash icon on the right.

Parameters do more than fill cells#

The same values drive the output around the report:

The destination-path one is the underrated feature. One job, and the output arrives filed by market and by year without anyone creating a folder.

Value-level security#

Timberline also carries two secured lists, which map values to groups so what a user may select depends on who they are:

• MarketControl: refreshed from the model by MDX. Leadership sees everything; each region group sees its own region.
• SalesTerritory: hand-maintained. Four territories, each mapped to its territory group, its region group and Leadership.

The pair is deliberate: one refreshes itself, one is maintained by hand, and that is the same choice a customer has to make.

Note: at the time of writing neither list is bound to a parameter on a running job, so they are demonstrable on the administration screen but you cannot yet show one filtering a report.

Related#

• What bursting looks like · Use a global parameter
• Customer guide: Advanced parameters