| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Excel Range Slicers/Version 7.0"
(Created page with "<noinclude>{{VersionDisambiguation|pageVersion=|disambiguationPage=Excel Range Slicers/Versions}}</noinclude> In the scenario where a Slicer needs to be used to allow the selecti...") |
(→Creating a Range Slicer) |
||
| (8 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| − | <noinclude>{{VersionDisambiguation|pageVersion=|disambiguationPage=Excel Range Slicers/Versions}}</noinclude> | + | <noinclude>{{VersionDisambiguation|pageVersion=7.0|disambiguationPage=Excel Range Slicers/Versions}}</noinclude> |
| − | + | =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 {{Menu|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]]. | |
| − | + | ||
| + | ==Tree 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. | ||
| + | |||
| + | [[Image:ExcelRangeSlicerRange.png|centre]] | ||
| + | |||
| + | Create a slicer as follows: | ||
| + | |||
| + | <ol> | ||
| + | |||
| + | <li value="1"> Select the {{Menu|XLCubed|Slicer|Excel}} ribbon item. </li> | ||
| + | |||
| + | [[File:ExcelRangeSlicers0.png|350px|centre]] | ||
| + | |||
| + | |||
| + | <li value="2">Set the '''Slicer range''' to =$A$1:$C$7, and select the treeview-style slicer. </li> | ||
| + | |||
| + | [[Image:ExcelRangeSlicers1.png|350px|centre]] | ||
| + | |||
| + | |||
| + | <li value="3"> 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. </li> | ||
| + | |||
| + | [[Image:ExcelRangeSlicers2.png|350px|centre]] | ||
| + | |||
| + | </ol> | ||
| + | |||
| + | |||
| + | 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. | ||
| + | |||
| + | <gallery heights=200px widths=400px mode="nolines" class="center"> | ||
| + | File: ExcelRangeSlicerSlicer.png | ||
| + | File: ExcelRangeSlicers3.png | ||
| + | </gallery> | ||
| + | |||
| + | |||
| + | [[Category:Slicers]] | ||
Latest revision as of 10:57, 28 August 2018
 |
This article refers to XLCubed version 7.0. For other versions, see Excel Range Slicers/Versions. |
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.
Tree 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.
