One of the missing features in SSAS we found is filtering by list of values. In most of clients (Excel, Panorama) you are able to set a filter for one or several items.  But filter cube by 300 items could be tricky task.   Let say we have a bussines task like: filter a Adventure Works cube by 300 different products. These 300 products spread in all Products categories hierarchy and depend to different product categories. To select 300 products you have to open Product hierarchy and check each item. To check 300 different items could be a tricky tasks, isn’t it? The solution – to find a way  programically select items.
Using Excel 2007 you can filter Pivot Table items using “VisibleItemsList” property. Look for VBA code below.

     Dim iTmp As Integer
    Dim ProductKey As String
    Dim ProductKeyArray() As String
    iTmp = 2
   
    ProductKey = Excel.Worksheets(“Filter”).Cells(iTmp, 1).Value
    While ProductKey <> Empty
        ReDim Preserve ProductKeyArray(iTmp – 2)
        ProductKeyArray(iTmp – 2) = “[Product].[Product Key].&[" & ProductKey & "]”
        iTmp = iTmp + 1
        ProductKey = Excel.Worksheets(“Filter”).Cells(iTmp, 1).Value
    Wend
   
    On Error Resume Next
    Excel.Worksheets(“Data”).PivotTables(Excel.Worksheets(“Data”).PivotTables.Count).PivotFields( _
        “[Product].[Product Key].[Product Key]“).VisibleItemsList = _
        ProductKeyArray()

 To look how this works, download file  FilterPTSample.xlsm (rename zip to FilterPTSample.xlsm). Enable macros and data connection, change data connection to Adventure Works, put product keys on sheet “Filter”, down from “A2″ and then press “Filter by product key”.  After filter was applied, in 1′st sheet (“Data”) , attribute Product Key and hierarchy “Product Categories” you have to see only products from sheet “Filter”. To clear filter, choose “Clear” from Data\Sort & Filter box.

PTBeforeFilter

ProductKeys

PTAfterFilter