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.

For example, below is a report for sales by country. The report template is created once, and then repeated for each country.

The yellow cell shows where the country input goes, and then the formatted part of the report is repeated for each country on the right.

Adding a repeater

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

Next you need to pick three ranges:

• The repeat range
  • This is the entire area that needs to be repeated, including and work are where results are calculated
  • $B$2:$D$21 in the example above
• The render range
  • This is the area that is displayed in the final repeater
  • $B$16:$D$21 in the example above
• Input range
  • This is where the member parameters will be inserted
  • $C$2 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 e 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)