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 | #1

    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 | #2

      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 | #3

    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 | #4

    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 | #5

    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 | #6

      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 | #7

    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 | #8

    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 | #9

      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 | #10

    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 | #11

      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 | #12

        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 | #13

    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 | #15

    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 | #16

    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 | #17

      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

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

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 158 other followers

%d bloggers like this: