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 Dimension Slicer’. Note that grid slicers can be positioned where required (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 or / 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’. T’. 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.