Function to get selected slicer options.

[ncalenti]

I have a report that changes dynamically based on various slicer options chosen on the dashboard. I would like to update the report header based on the selected slicers.
For example, say in the "state" slicer, the user selected AZ, and SD. Then the report header would read "States: AZ, SD". If the user chose not to filter on a specific slicer, it would either be left out of the header, or just say all. Is this possible to do?

 

[DonkeyOte]

It's possible but without more info. it's hard to provide a truly meaningful example...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim PTS As Slicer, PTSC As SlicerCache, PTSCI As SlicerItem
    Dim lngPTS As Long
    Dim vResults() As Variant
    Dim strTemp As String
    With Target
        ReDim vResults(1 To .Slicers.Count, 1 To 1)
        For Each PTS In .Slicers
            lngPTS = lngPTS + 1
            With PTS
                strTemp = .Caption & ": "
                Set PTSC = .SlicerCache
                With PTSC
                    For Each PTSCI In .SlicerItems
                        With PTSCI
                            If .Selected Then
                                strTemp = strTemp & ", " & .Caption
                            End If
                        End With
                    Next PTSCI
                End With
                Set PTSC = Nothing
                vResults(lngPTS, 1) = Replace(strTemp, ", ", "", 1, 1)
            End With
        Next PTS
    End With
    Cells(1, "A").Resize(UBound(vResults, 1)) = vResults
End Sub

The above code would reside within the appropriate Sheet Object in VBE and would fire whenever the Pivot was being amended (via Slicer or directly).
The code itself would return the relevant strings to A1:An (where n determined by number of Slicers attributed to given Pivot) - modify to meet own requirements etc...

 

[Waqar]

Hi Donkey

Can u explain more what does ur code do..........It seem like Awesome.

I have a dashboard where different slicer are placed..

REgards

 

[ncalenti]

I actually implemented this solution a few months ago. I can't recall if I used the above code sample or not. I will post how I did it with code a bit later when I boot up my work computer

 

[Waqar]

Thanks ncalenti

I will wait for ur response, as this code seems to be good for Dashboard Presentation.

Waqar

 

[ncalenti]

Sorry for the delay. Here is the code. It works on a slicer called mandate_state and outputs the slicer options selected as a string. I use it on the dashboard and cover sheet of a dynamic report. I wrote 1 function for each slicer. Also, in the cell where i wanted the string output I had '=getstatefilters(State!A7:A1510)' I did this because the function only updates when the cell you point it to updates, so i pointed it to a range i knew would update when you change around the mandate_state slicer.
Let me know if you have questions:

'All the following fuctions just get the parameters of the common slicers so that we can display them on the cover

Function GetStateFilters(rng As Range) As String
    Dim slcr As SlicerCache, itm As SlicerItem
    Dim strOut As String

    Set slcr = ActiveWorkbook.SlicerCaches("Slicer_mandate_state")
    If slcr.VisibleSlicerItems.Count = slcr.SlicerItems.Count Then
        GetStateFilters = "All States"
    Else
        For Each itm In slcr.VisibleSlicerItems
            strOut = strOut & "," & itm.name
        Next itm
        GetStateFilters = "States: " & Mid$(strOut, 2)
    End If
End Function

 

[Waqar]

Thanks Alot ncalenti

I have a Dashboard where defferent Slicers use and defferent Pivot Table are used.

What should i do for using ur Code in my Dashboard.

Regards

 

[Waqar]

hi ncalenti

I think u r busy now a days.

If u guide me i will update my Dashboard.

Regards