Repeaters

Repeaters are a visualisation introduced in Version 9.1.

They allow you to select a range in Excel, parameterise the range, and then repeat the range for each input parameter. They are useful when you want to repeat a formatted section of a report by one variable, without having to recreate the section multiple times manually. An example of the concept is shown below.

The image below shows how the source sheet for a repeater is set up.

Adding a repeater

When adding a repeater, you must first pick the members that will be used as input to the report.

Next you need to pick the three ranges shown in the image above:

• The repeat range (green border)
  • This is the entire area that needs to be repeated. Note that this must include any source data and calculation formulae required.
  • $A$2:$P$39 in the example above
• The render range (blue border)
  • This is the area that you want to be displayed in the final repeater control
  • $B$7:$E$29 in the example above
• Input range
  • This is where the member parameters will be inserted by the repeater. Typically any grids or formulae in the repeat range will need to reference this cell.
  • $N$6 in the example above

Supported features

The following are supported

• Excel formulae
• Standard formatting
• Conditional formatting
• In-Cell charts
• Excel Charts
• Grids
• Tables
• Pictures
  • Including the use of XL3DynamicPicture

The following are not supported

• Slicers
• Small multiples
• Treemaps
• Viewports

Advanced usage options

Display if True

You can select a cell in the repeat range to control if the block is displayed. For example, you could check if a value falls below a threshold and supress the block in that case. This cell should contain a boolean, e.g. comparison (=, >, <) or If().

Drilling

You can use XL3Link formulae to allow drilling into a repeater. These formulae must be inside the repeater area, and should follow these examples:

• =XL3Link(,"Drill Down", 5)
• =XL3Link(,"Drill Up", 6)

If you enable the Back/Forward button on a repeater then these can be used to easily undo any drills.

Multiple hierarchies

You can pick multiple hierarchies in the member selector, and then an output range of multiple cells to output each combination. This can be used with non-empty to remove blocks with no data. E.g. Picking years and countries, and put Sales measure on filters will mean any combinations with no sales are removed.

Multiple data sources

You can add and edit data sources in the Repeater editor. If you have multiple data sources then combinations of them will be formed for each block. E.g. 3 Products and 4 Countries will produce 12 blocks. You can use the Grid layout to put the blocks into a table.

Dynamic Pictures

(docs todo)