PdfBookPages/Excel Range Slicers
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.
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:
- Select the XLCubed > Slicer > Excel ribbon item.
- Set the Slicer range to =$A$1:$C$7, and select the treeview-style slicer.
- 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.