Excel Range Slicers

This article refers to the latest XLCubed version. For other versions, see Excel Range Slicers/Versions.

Slicer From a Range

Excel slicers allow the creation of a custom slicer, rather than one based on members in a cube. They are useful in the scenario where a slicer needs to be used to allow the selection of an item from a range of cells, then have the corresponding selection passed to another report as a parameter.

Creating an Excel Slicer

An Excel range slicer can be inserted from the XLCubed > Slicer > Excel ribbon item.

Select the range that the slicer is to be based on. A two column range should be used if you need to distinguish between keys and values - the slicer will display the second column and you can set the first column to be outputted.

Slicer type and other properties are very similar to that of Dimension Slicers.

Tree Slicers

The levels of a tree slicer based on an Excel range can be controlled with an additional column. For example, the screenshot below shows a selection list containing two continents and some of their children, each with a corresponding region code. This range of cells contains three columns: the region code, the caption, and the depth to be used for each item. When the user selects a region, its corresponding region code should be outputted.

Create a slicer as follows:

1. Select the XLCubed > Slicer > Excel ribbon item.





2. Set the Slicer range to =$A$1:$C$7, and select the treeview-style slicer.





3. In the Properties section, check Update range with selection, select First Column and enter the cell location where you want the country code to be displayed. In our example it is B11.

The Slicer is created using the selections that we have chosen, with Europe and North America at higher levels to their children countries. When the user clicks on a slicer option, in this case France, its corresponding country code is displayed in cell B11.

• 
• 

Alternative Layouts

If you need a tree slicer your data may not be in the format specified above. In this case XLCubed can also accept a number of other formats.

The following examples all produce the same result:

Parent Column

The data has three columns: Key, Text, ParentKey. Top level items have no parent, children refer to their direct parent. There can be multiple layers of nesting.

One Column Per Level

The data is in a table with a column for each level of the tree. Parents are repeated in the data when they have multiple children.

Key and Caption Column Per Level

Data is in a similar shape to the "one column" layout, but there are two columns per level of the tree, allowing for items to have a key that is stored but not displayed.