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.
