Dimension Slicers

Introduction

Dimension slicers simplify the process of creating cube dynamic drop downs.

Four display options are provided:

Tree view	Combo box
Listbox	Buttons

Video demonstration

Click here for videos showing dimension slicers.

Grid Slicers

To add a slicer to a grid, right-click on the relevant dimension member, and choose XLCubed – Add Slicer.

Note that grid slicers can be positioned where required, and are not restricted to the grid area.

The default will be an in-cell treeview with the last active member selected. Once inserted, right click on the slicer and choose Edit for configuration and design options. Choose the required slicer display type, and then, using the member selector on the right, choose the set of members to enable selection from. These can be either static lists of members, or dynamic selections such as children, descendants at level, Level x or even ranked lists etc.

Note that the slicer content can also be driven from an Excel cell (e.g. Children of E11). A slicer which has been added to an individual grid can be used as the selection mechanism for multiple grids through the Grid Links Dialog. It can also update a specified range with the uniquename of the member chosen, which could for example be used as the argument for XL3Lookup formulae.

Standalone Slicers

A standalone slicer can also be added using the slicer button on the Ribbon. Here the required hierarchy is chosen from the list of available hierarchies, and dragged into the ‘Slicer Hierarchy’ control. From that point the setup is the same as with Grid Slicers.

Standalone slicers will need to be manually linked into grids as required. This is done by selecting the slicer in the member selector form:

Slicer In Member Selector

Alternatively you could use them to drive other XLCubed or Excel formulae and then other reports/query tables.

Cascading and Advanced Slicers

Slicers have a filter area in their definition, which can be used to restrict the members returned based on the presence of data. The example below shows the design for a slicer for Product Model Categories which is being filtered on Geography based on what has been selected in the slicer at Sheet1!$C$3.

In this example, the selections made by the user in the Geography slicer will cascade into the available selections within this Product Model Categories slicer, and create an intelligent linked and data-dynamic set of available report selections for the user.

Cascading slicers can also be configured within one hierarchy, where a user choice made at a higher level restricts the secondary slicer selections to children of or descendants at level x. This is shown in the example below for the Geography hierarchy.

In this case the cascade is achieved by selecting the initial (country Level) slicer within the Geography hierarchy, and then choosing ‘Descendants at City’.

Slicers can also be defined as a dynamic filter or ranking. To achieve this, the slicer content is defined as an advanced selection, and the ranking, filter or sort is defined here, as shown below.

In this case the ‘Product Model Categories’ filter content is restricted by Geography in the filter area, which is in turn based on another slicer selection. The members to display are then defined in the advanced dialog as the lowest level members where sales are over 400 units and sorted descending.

Initial Value

When you create an XLCubed slicer in v6.5, under the Settings tab, you are now able to include an initial value which can be any of the following:

• Current Selection – sets the value of the slicer as when the report was published

• First Selection – sets the value of the slicer to be the first in the list of possible selections

• Last Selection - sets the value of the slicer to be the last in the list of possible selections

• Default Member – sets the value of the slicer to be the default member – usually the All member

• Range – sets the value of the slicer to be whatever the cell value is in the range location defined

Updating Excel Ranges

You can set up a slicer to output the selected item(s) to an Excel range which can then be used to drive formula reports.

If you have multi-select turned on then you should select a range instead of a single cell. One selected item will be put into each output cell.

You can the use the XL3Member formula to reference the output range, and run XL3Lookup formulae off of this.

Setting two or more slicers to output to the same range will keep the slicers "in-sync" with each other, so updating one of the slicers will also update the others.

Excel Only Slicers

Please see Excel Range Slicers.