Dimension Slicers

Introduction

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

Several display options are provided:

• 

  Tree view

• 

  Combo box

• 

  Listbox

• 

  Buttons

• 

  Member selector

• 

  Time slider

The member selector slicer type allows efficient access to more advanced functionality, for example searching. The Time Slider type gives simple access to date and time hierarchies. For more complex date requirements, see the Date Slicer type.

From v8.0, there are also Workbook Slicers, which allow you to have slicers in a task pane outside the worksheet area for easy access.

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 and then 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.

Members with no data / Non empty

By default XLCubed will not offer you members which have no data. You can change this behaviour so that all members are returned by turning off the remove empty button, show below:

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

Version 7.5

Version 7.5 allows multiple source cells for multi-select slicers and also includes a new option for when to apply the initial selection, these are:

• Selection becomes invalid: the default for new slicers, when the current member selection becomes invalid then the initial selection will be applied

• Report Opens: the default for existing pre-V7.5 slicers, the initial selection is only applied when the report first opens

• Available items change: the initial selection will be applied if the available members changes (even if the current selection is in the new list of slicers)

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 of cells instead of a single cell. One selected item from the slicer will be put into each output cell in the range.

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.

Disabling Slicers (new in v7.2)

If you show the title bar, you can optionally add a disable button to the slicer.

Clicking this will toggle whether or not the slicer filters objects it is linked to. This means a slicer can easily be turned off to return all results. If the slicer is outputs to a range then that range will be cleared.

Wait for Submit on Web (new in v7.5)

With XLCubed Version 7.5 there is a new option for "Wait for Submit on Web".

This allows several slicers to be changed and rather then the report refreshing for each change it will wait for the user to submit the selection either by click the Submit toolbar button or by using a "Submit" XL3Link or Picture Links.

This also removes the individual "Submit" button from multi-select slicers.

Use query restrictions for children (new in v7.6)

For tree slicers, the default behaviour is to show all children when expanding a member. By checking this option you can restrict the children using the same query used for the initial member list.

This is most useful when restricting the tree to show only members with data, as the non-empty will be applied to the children as the tree is expanded.

Alignment options (new in v8.0)

When Move/Size mode is enabled for Slicers, Small Multiples, TreeMaps and Maps, there is now a checkbox in the top-right corner of the control. If this box is checked on multiple items, when any one of them is moved, XLCubed will ask whether you would like to align all the other selected items with it. When an item is resized, all other selected items will be resized to the same size.

Slicer Title from Excel range (new in v8.1)

See Also

• Workbook Slicers
• SQL Slicers
• Excel Range Slicers
• Date Slicer
• Time Slider