Home > SSAS > Accessing the Slicer through VBA

Accessing the Slicer through VBA


There may be times when we want to programmatically control slicers through VBA. For example, we may want to default a date to the current date or set a cost centre depending on who has opened the book. This post looks how a slicer can be controlled through VBA.

In this example, we have added a slicer to a worksheet that uses a date hierarchy as its source. Because, we have included all levels of the hierarchy when the slicer was setup, we get three individual slicers for each level of the hierarchy.

If we look at the settings for the slicer (right click on the slicer and select slicer settings), we can see that the slicer has a name and each level of the slicer hierarchy maintains the hierarchy level name. For example, the Slicer_Dates_Hie below has a level Year, Month and Day. Although we can change the name (for example the name Year in the picture below), the slicer retains the mdx level that the slicer belongs to.

Accessing the Slicer

We can access the slicer through the SlicerCaches object. This is as simple as declaring the slicer cache and referencing it to the name of the slicer we want to use. For example;

Dim sC As SlicerCache
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

Navigating the Structure of the Slicer

Once we have a reference to the slicer we can navigate its structure using SlicerCacheLevels. For example we can determine the number of levels of the slicer and iterate over them with the following code.

Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)

For Each sL In sC.SlicerCacheLevels
Debug.Print “Level ” + CStr(sL.Ordinal) + ” –> ” + sL.Name
Next sL

Naturally, the level can be accessed through the cache level ordinal to produce the same result. The highest level (year) takes the value 1 which increments for each level from the first level. There is always a level (ie level 1) even if the slicer is based on a single attribute.

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
For i = 1 To sC.SlicerCacheLevels.Count
Debug.Print “Level ” + CStr(i) + ” –> ” + sC.SlicerCacheLevels(i).Name
Next i

Slicer Data Members

We can gain access to the data items through slicer items, as mdx attributes, they have a caption, value and a key (member unique name). For example the year 2011 in this slicer has a value of 2011 and a name (MDX unique name) of [Dates].[Dates Hie].[Year].&[2011]

Dim sC As SlicerCache
Dim SL As SlicerCacheLevel
Dim sI As SlicerItem

Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Dates_Hie”)
Set SL = sC.SlicerCacheLevels(1)
Debug.Print “——————————————————————————”

For Each sI In SL.SlicerItems
Debug.Print “Caption –> ” & sI.Caption
Debug.Print “Value –> ” + CStr(sI.Value)
Debug.Print “Unique Name –> ” + sI.Name
Debug.Print “——————————————————————————”

Next

Setting the Slicer Value

Slicer item selection must be set through the visible slicer items list and is specified using an array. For example, we could set the SlicerCache (selected items) to 2011 and 2012 with the following code;

sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]“, “[Dates].[Dates Hie].[Year].&[2012]“)

The name selected must be a data member of the level. If not a runtime error will occur (as below)

Once the values are set, connected pivots are updated immediately

Member Iteration

Members can be easily iterated using the following code;

Dim sC As SlicerCache 
Dim SL As SlicerCacheLevel 
Dim sI As SlicerItem
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Dates_Hie") 
Set SL = sC.SlicerCacheLevels(2)
For Each sI In SL.SlicerItems    
 sC.VisibleSlicerItemsList = Array(sI.Name) 
Next

Conclusion

The control of slicers through VBA could be used to provide some very nice personalisation to work books.

NB:  If you liked this post you might be interested in this one.  In it, I discuss setting slicers through cell association to pivot rows.

About these ads
Categories: SSAS Tags: , ,
  1. July 16, 2012 at 12:40 am

    Hi! Please
    can I get different format of a measure in a PowerPivot table based on Slicer Item value
    something like this. I did try but no luck. I can send a file if it needed

    thank you!!!

    If SlicerItem = “abs” Then

    With ActiveSheet.PivotTables(“PP002″).PivotFields( _
    “[Measures].[sum]“)
    .Calculation = xlNormal
    .NumberFormat = “# ### ##0_.”
    End With
    End If

    If SlicerItem = “ratio” Then

    With ActiveSheet.PivotTables(“PP002″).PivotFields( _
    “[Measures].[sum]“)
    .Calculation = xlPercentOfColumn
    .NumberFormat = “0.0%”
    End With
    End If

    • July 16, 2012 at 3:35 am

      Hi 100sky,

      You can specify the format string for the pivot columns with code like this;

      Dim x As PivotTable
      Set x = Sheet2.PivotTables(1)
      x.DataBodyRange.Cells.NumberFormat = “#,##0″
      x.PivotFields(“[Measures].[End of Day Rate]“).NumberFormat = “#,##0.000″

      Note i can set the entire data area or only a particular field.

      Is this what you want?

  2. July 16, 2012 at 8:20 am

    thank you!
    let’s we’ve got 2 store (facts)

    A 100
    B 200

    we can do a measure Sum:=sum(FT) and we can make 2 PP tables like
    A 100
    B 200
    or
    A 33.3%
    B 66.7%

    What I want, if it possible:
    To create dimension and not link it to fact table, like

    absolute_value
    ratio

    and in One PP table after clicking by slicer item “ABS” getting format “100” but after clicking by slicer item “RATIO” getting format “33.3%” all in the same column

    I can get result what I need only by using Buttons&VBA and want to know if it’s possible by SlicerItems&VBA

    Thank you! Very much!

  3. July 16, 2012 at 9:40 am

    I think what you want to do is change the way that the pivot table operates so that you can set the ‘value’ to be a percent of the column or the native value.

    So, I have two subs that specify raw values or percent values. You can then fire these how you like (not the only difference is in the calculation and number format)

    Sub RawValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1″)

    With x.PivotFields(“[Measures].[Sum of Value]“)
    .Calculation = xlNormal
    .NumberFormat = “#,##0″
    End With

    End Sub

    Sub PercentValues()

    Dim x As PivotTable
    Set x = Sheet3.PivotTables(“PivotTable1″)

    With x.PivotFields(“[Measures].[Sum of Value]“)
    .Calculation = xlPercentOfColumn
    .NumberFormat = “#,##0%”
    End With

    End Sub

  4. July 17, 2012 at 12:05 am

    Paul, thank you!

    Your macro is what I need.

    If it’s possible what when I have Slicer consist of 2 Slicer Items (“abs” and “ratio”) and clicking “abs” I’ve got macro “RawValues” execution and clicking “ratio” I’ve got macro “PercentValues” execution

    Thank you from Russia

    • July 17, 2012 at 12:14 am

      I am not sure if you can attach the marco(s) to slicer items so that the events are fired with a change of a slicer value. What you may have to do is create a pivot table with the filter showing the ‘slicer selection’ and attach the slicer to it. Then, you can attach your macro to the on_change event on the worksheet so that you can check what the selected value of the slicer is and apply a check against the pivot values.

  5. July 17, 2012 at 1:42 am

    Yes, I can return name(value) any of slicer items due to your article but can’t attach the macro to slicer item clicking directly. Now I know it definitely

    Your blog is a great help
    Thank you

  6. September 25, 2012 at 12:47 pm

    Hi, I just want to know how many data are inside of the slicer, like a dinamic seach to get the variable data. Can you help me with that?
    Pleaseee

    • September 25, 2012 at 8:11 pm

      Hi Vanessa, I am a little unsure of exactly what you are after. But doesn’t the following help?

      Sub MemberInfo()

      Dim sC As SlicerCache
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Month”)
      Debug.Print “There are ” + CStr(sC.SlicerCacheLevels(1).Count) + ” elements in your slicer”

      Dim sCL As SlicerCacheLevel
      Set sCL = sC.SlicerCacheLevels(1)

      Dim i As Integer
      For i = 1 To sCL.SlicerItems.Count

      Debug.Print “Slicer Caption -> ” + sCL.SlicerItems(i).Caption
      Debug.Print “Slicer Value -> ” + sCL.SlicerItems(i).Value
      Debug.Print “Unique Name –> ” + sCL.SlicerItems(i).Name
      Debug.Print “—-”

      Next i

      End Sub

  7. Emilio
    December 12, 2012 at 11:47 am

    Hello!

    I was trying to set the Slicer Value as you said before:

    “sC.VisibleSlicerItemsList = Array(“[Dates].[Dates Hie].[Year].&[2011]“, “[Dates].[Dates Hie].[Year].&[2012]“)”

    but i found VisibleSlicerItemsList is read-only.

    http://msdn.microsoft.com/en-us/library/ff822312.aspx

    My data source is OLAP and when you are working with OLAP, I think there are some properties that are read-only. Do u know another way to set the slicer value?

    Thanks in advance and regards!
    Emilio

    • December 12, 2012 at 8:37 pm

      Hi Emilio,

      I can set the slicer (against a cube) values with these snippets;

      ‘ finally set it to 2005
      Sheet1.Cells(2, 2) = “[Date].[Calendar Year].&[2005]”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]“)

      ‘ multiple set
      Sheet1.Cells(2, 2) = “Multiple”
      sC.VisibleSlicerItemsList = Array(“[Date].[Calendar Year].&[2005]“, “[Date].[Calendar Year].&[2006]“)

      I’ll also flick you through the workbook.

      • Emilio
        December 12, 2012 at 10:05 pm

        Hello Paul, I got it!

        I tried your solution and it works fine. The problem was I was setting de VisibleSlicerItemList of the SlicerCacheLevel instead of the SlicerCache.

        Thank you very much and kind regards,
        Emilio

  8. January 2, 2013 at 7:34 pm

    Does your site have a contact page? I’m having trouble locating it but, I’d like to shoot
    you an e-mail. I’ve got some suggestions for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it expand over time.

  9. August 7, 2013 at 11:02 pm

    I was very happy to find this site. I need to to thank you for ones time just for this fantastic read!

    ! I definitely really liked every bit of it and I have you
    book-marked to look at new information in your web site.

  10. JHN
    October 18, 2013 at 1:17 pm

    Hi,
    I do have a VBA giving me a list of slicers left, according any selection done in related slicers:
    Sub GetSlicerValues01()
    Dim lCt As Long
    Dim oSi As SlicerItem
    Worksheets.Add
    With ActiveSheet.Range(“A1″)
    .Value = “Slicer value”
    .Offset(, 1).Value = “Selected”
    .Offset(, 2).Value = “Available”
    For Each oSi In ActiveWorkbook.SlicerCaches(“Slicer_Belt_series”).SlicerItems
    lCt = lCt + 1
    .Offset(lCt).Value = oSi.Value
    .Offset(lCt, 1).Value = oSi.Selected
    .Offset(lCt, 2).Value = oSi.HasData
    Next
    End With
    End Sub
    But I actually just want a number back, telling me the amount of slicer left. It should be easy just to count where Selected = true AND HasData = true.

    Any suggestions how such code could look like?

    Thanks in advance JHN

    • October 29, 2013 at 9:00 pm

      Hi JHN,

      You could create a function to return the count of selected items. This should be ok.

      Function SelectedCount() As Integer

      Dim sc As SlicerCache
      Dim sl As SlicerCacheLevel
      Set sc = ActiveWorkbook.SlicerCaches(“Slicer_Country”)
      Set sl = sc.SlicerCacheLevels(1)

      For i = 1 To sl.SlicerItems.Count
      If sl.SlicerItems(i).Selected = True Then SelectedCount = SelectedCount + 1
      Next

      End Function

      Regards,

      Paul

  11. June 11, 2014 at 7:30 am

    Hi, I would like to pose another question on the same subkect of Data Slicers. I have searched around and have not been able to find out if any Slicer Items have been clicked or changed.

    I basically have a complex pivot and correspoding chart, but the chart series keep chaging to default. I have worked out the VBA code t set the series type and colour but this only works when I click a button.

    I need to run this reset macro code when the user click any of the items in the two or 3 slicers I have.

    Could any one help. Thanks Kuldip

    • June 12, 2014 at 8:29 pm

      Hi Kuldip,

      I think the easiest way is to have your slicers put their data to different worksheet (almost like a control sheet you might say). On that worksheet, you can then listen for the worksheet_Change event to update the charts, names etc. There won’t be any need for a different button.

      HTH, Paul

  12. June 13, 2014 at 6:21 am

    Hi Paul,
    Many Thanks for the suggestion. The issue is that the contents of the data slicer and pivot keep changing so not to static. Much appreciated, however, I managed to find a good solution :

    I used Worksheet_PivotTableUpdate
    So when ever the pivot changed I used this as a trigger to update the series type and colour and works great.

    The following is what I used to set the series attributes.
    I = 1
    Set seriesCol = ActiveSheet.ChartObjects(1).Chart.SeriesCollection
    For Each mySeries In seriesCol
    Set mySeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(I)
    mySeries.Select
    With mySeries
    ‘MsgBox “Series Name : ” & .Name
    Select Case UCase(.Name)
    Case Is = “ACTUAL HEAD BOOKING”
    .ChartType = xlColumnStacked
    With Selection.Format.Fill
    .Visible = msoTrue
    .ForeColor.RGB = RGB(102, 153, 255) ‘ACTUAL HEAD BOOKING (Blue)
    .Transparency = 0
    .Solid
    End With
    Case Is = “EQUIVALENCE”
    ‘Etc.. Etc… Etc…
    End Select
    End With
    I = I + 1

    Next

    Thanks again. Regards Kuldip.

  13. June 20, 2014 at 9:29 am

    Hi Paul, I have another question that I am struggling with.

    Hi I having difficulty trying to cycle through the slicers to see if they are filtered or not.

    My goal is to get all the selected Slicer into a worksheet so that I can apply a (High to Low) filter to the underlying pivot data so that I can pick the “Top 5 over budget” based upon the selections made in the Data Slicers.

    I have the following code but get see error:
    ‘Run Time Error 438’ Object doesn’t support this method’
    Can someone advise how I can achieve this.

    Public Sub top_over_under_booked()
    Dim oSi As SlicerItem
    Dim oSlicercache As SlicerCache
    Dim oSl As SlicerCacheLevel
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    Set target_ws = ThisWorkbook.Worksheets(“Get Slicer Selections”)
    For Each oSlicercache In ThisWorkbook.SlicerCaches

    For Each oPt In oSlicercache.PivotTables

    oPt.Parent.Activate ‘Slice Name
    worksheet_name = UCase(oPt.Parent.Name)
    If worksheet_name = UCase(“Chart Analysis 5 Years”) Then
    column_no = 0
    slicer_name = UCase(oSlicercache.Name)
    Select Case UCase(oSlicercache.Name)
    Case Is = “SLICER_FY1″
    column_no = 1
    Case Is = “SLicer_REPORT_PT_DEPT1″
    column_no = 2
    End Select
    If column_no 0 Then
    For Each oSl In ActiveWorkbook.SlicerCaches(oSlicercache.Name) ‘ <—– Error
    For Each oSi In oSl.SlicerItems
    'oSi.Selected = True
    check_slicer_string = oSi.Value
    'target_ws.Cells(ource_ws.Cells(65000, column_no).End(xlUp).Row + 1, column_no) = oSlicercache.Value
    Next
    Next
    End If
    oPT.Parent.Name
    End If
    Next
    Next
    End Sub

    • June 21, 2014 at 9:50 pm

      Hi Kuldip – I’ve attached a sample of how this could be done. We just create a function to iterate over the level, then if any items are found to be not selected we know that all are not.

      HTH,
      Paul

      Sub MyControl()

      Debug.Print AllSelected(“Slicer_Customer_Geography”, 1)

      End Sub

      Private Function AllSelected(ByVal SlicerCacheName, ByVal SlicerCacheLevel) as Boolean

      Dim sC As SlicerCache
      Dim sL As SlicerCacheLevel
      Set sC = ActiveWorkbook.SlicerCaches(“Slicer_Customer_Geography”)
      Set sL = sC.SlicerCacheLevels(SlicerCacheLevel)

      ‘ Lets assume that all are selected
      For i = 1 To sL.SlicerItems.Count
      If sL.SlicerItems(i).Selected = False Then
      AllSelected = False
      Exit Function
      End If
      Next

      AllSelected = True
      End Function

  14. July 9, 2014 at 6:38 am

    Hi Paul,

    I have so nailed a great solution and works 95% of the time. As mentioned in my original question, I created the Pivot chart and through VBA reset the series colours, type etc. I subsequently used :

    Worksheet_PivotTableUpdate

    to trigger the reset_colours_series_type etcBUT the problem is that these are instances when the associated PIVIT Data changes but the inbuilt function Worksheet_PivotTableUpdate DOES NOT trigger the code to be run and the chart remain in bad configuration. Wierd indeed.

    Does anyone have any ideas why this triiger doesn’t trigger (so to speak).

    Thanks in advance Kuldip Mond.

  1. January 25, 2013 at 3:30 am
  2. February 15, 2013 at 10:24 pm
  3. February 15, 2013 at 10:30 pm
  4. February 16, 2013 at 5:01 am
  5. February 16, 2013 at 11:55 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 228 other followers

%d bloggers like this: