XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Dimension Slicers

Introduction

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

Several display options are provided:


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.

Excel Only Slicers

Please see Excel Range Slicers.

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, shown below:

SlicerRemoveMembers.PNG

Cascading and Advanced Slicers

View the video on cascading slicers here.

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 Categories which is being filtered on Geography based on what has been selected in the slicer named 'Location'.

CascadingSlicer1.PNG

In this example, the selections made by the user in the Geography slicer will cascade into the available selections within this Product 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.

CascadingSlicer2.PNG

In this case the cascade is achieved by selecting the initial (Country level) slicer within the Geography hierarchy, and then choosing Descendants at State.

Slicer selections can also be defined as a dynamic filter or ranking in exactly the same way we might rank or filter hierarchy members in a grid (read more here). To achieve this, the slicer content is defined as an advanced selection, and the ranking, filter or sort is defined here, as shown below.

CascadingSlicer3.PNG

In this case the Product 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.

Behaviour

Initial Value

When you create an XLCubed slicer, under the Settings tab, you are 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


SlicerBehaviour1.PNG


There are various options 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.

MultiSelect and Parent Child Checkboxes

The "multi-select members" options allows users to select one or more members from the slicer.

Turning on the "Parent/child checkboxes are related" option reverts to behaviour of slicers in older versions of XLCubed, i.e. selecting a parent member will include all of its children. This makes it easier to select an entire level and then exclude members where desired. This option is off by default to allow users to select the children members in addition to higher levels.

SlicerCheckboxes RelatedOn.PNG SlicerCheckboxes RelatedOff.PNG

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

The option to "Wait for Submit on Web" 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.

This option is also available for Member Selector slicers from v9.

Activate XL3Link

Select a cell containing an XL3Link to activate the link after a slicer selection has been made.

Example - Page Navigation

Create an Excel slicer with the names of the workbook sheets. Output the selected member to a cell.

SlicerSheetNav1.PNG

Write a simple formula to get the address of the sheet to link to e.g. =B6 & "!A1".

Create an XL3Link which references the address formula as its link location.

SlicerSheetNav2.PNG

Edit the slicer so that it activates the link.

SlicerSheetNav3.PNG

Appearance

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 outputs to a range then that range will be cleared.

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

You can give a slicer a name and this will be its default title if the "Show title bar" option is enabled.

However, you can choose to use the content of an Excel cell as the slicer title (the slicer name as it appears in Workbook Objects etc. will remain the same).

SlicerAppearance2.PNG

Custom Query

As of Version 9.1 it is possible to set up a custom query on slicers, whether they are based on a cube or SQL database. The query can even be driven from a range so that changes in the report update the available items.

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.

SlicerSizing.png


See Also