Class SlicerCacheLevel (Excel VBA)

The class SlicerCacheLevel represents a level of a hierarchy in an OLAP data source being filtered by a slicer. To use a SlicerCacheLevel class variable it first needs to be instantiated, for example

Dim slccl as SlicerCacheLevel
Set slccl = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel

For Each

Here is an example of processing the SlicerCacheLevel items in a collection.

Dim slccl As SlicerCacheLevel
For Each slccl In ActiveWorkbook.SlicerCacheLevels
	
Next slccl

Count

Returns the number of SlicerItem objects in the specified SlicerCacheLevel object.

Dim lngCount As Long
lngCount = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.Count

CrossFilterType

Returns or sets whether a slicer is participating in cross filtering with other slicers that share the same slicer cache, and how cross filtering is displayed. Possible return values are xlSlicerCrossFilterHideButtonsWithNoData - Cross filtering is turned on for this slicer cache, any tile with no data for a filtering selection in other slicers connected to the same data source will be dimmed. Additionally, buttons will be hidden, xlSlicerCrossFilterShowItemsWithDataAtTop - Cross filtering is turned on for this slicer cache, any tile with no data for a filtering selection in other slicers connected to the same data source will be dimmed. Additionally, tiles with data are moved to the top in the slicer. , xlSlicerCrossFilterShowItemsWithNoData - Cross filtering is turned on for this slicer cache, any tile with no data for a filtering selection in other slicers connected to the same data source will be dimmed, xlSlicerNoCrossFilter - Cross filtering is turned off entirely, so all tiles are displayed and active (not dimmed) regardless of filtering selections in other slicers.

If more than one slicer is associated with the same PivotTable, by default, if the item or items that you filter by in one slicer have no corresponding data in another slicer, those items will be dimmed. For example, if you have a Country slicer and a State slicer, and you choose a country in the Country slicer, all states that are not in that country will be dimmed. This feature is referred to as cross filtering. The user interface settings that correspond to the setting of the CrossFilterType property are the Visually indicate items with no data and Show items with no data last check boxes in the Slicer Settings dialog box. Setting the CrossFilterType property to xlSlicerCrossFilterShowItemsWithDataAtTop corresponds to selecting both the Visually indicate items with no data and Show items with no data last check boxes. Setting the CrossFilterType property to xlSlicerCrossFilterShowItemsWithNoData corresponds to selecting only the Visually indicate items with no data check box. Clearing both check boxes corresponds to setting the CrossFilterType property to xlSlicerNoCrossFilter. Slicers filtering non-OLAP data sources (SlicerCache.OLAP = False) are not supported by the CrossFilterType property of the SlicerCacheLevel object. For slicers filtering PivotTables based on ranges, lists, and non-OLAP relational data sources, use the CrossFilterType property of the SlicerCache object instead.

ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.CrossFilterType = xlSlicerCrossFilterHideButtonsWithNoData

Name

Returns the MDX unique name of the OLAP hierarchy level represented by the object.

Dim strName As String
strName = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.Name

Ordinal

Returns the one-based ordinal value of the hierarchy level represented by the specified SlicerCacheLevel object.

Dim lngOrdinal As Long
lngOrdinal = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.Ordinal

SlicerItems

Returns a SlicerItems collection that contains the collection of all slicer items for the specified level.

Dim sisSlicerItems As SlicerItems
Set sisSlicerItems = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.SlicerItems

SortItems

Returns or sets the sort order of the items in the slicer. Possible return values are xlSlicerSortAscending - Slicer items are sorted in ascending order by item captions, xlSlicerSortDataSourceOrder - Slicer items are displayed in the order provided by the data source, xlSlicerSortDescending - Slicer items are sorted in descending order by item captions.

The default setting of this property is xlDataSourceOrder (1).

ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.SortItems = xlSlicerSortAscending

VisibleSlicerItemsList

Returns the list of slicer items that are currently included in the slicer filter.

The list of slicer items are returned as MDX unique name strings. If this list is empty, the slicer is not filtering the data source and all slicer tiles are displayed as selected.

Dim varVisibleSlicerItemsList As Variant
varVisibleSlicerItemsList = ActiveWorkbook.ActiveSlicer.SlicerCacheLevel.VisibleSlicerItemsList