Question:
“If I’m using SQL Server Analysis Services, can I URL filter my published report on Tableau Server using a dimension member? How about with a hierarchy?”
Answer:
Yes, you can! However, doing so is a bit more complex than a simple ?Region=East string.
Here’s a handy-dandy guide on how to approach this task. I’m using the latest version of Microsoft’s sample cube, “AdventureWorks” as my data source.
Filtering on an Dimension Attribute Member
Task: Filter on the Business Type attribute of the Resellers dimension. We want to see only those with a Business Type of Specialty Bike Shop
Approach: In your filter statement, refer to the attribute hierarchy on one side of the filter, and the specific attribute member on the other.
After you URLencode the & symbol above, your filter will look like this:
?[Reseller].[Business Type].[Business Type]=[Reseller].[Business Type].%26[Specialty Bike Shop]
..and the URL itself:
http://s/views/w/s?[Reseller].[Business Type].[Business Type]=[Reseller].[Business Type].%26[Specialty Bike Shop]
Filtering on a Hierarchy
Filtering via a hierarchy is much more complex. The trick is that you must refer not only the level of the hierarchy you are filtering on, but every level above it, as well.
Task: Using the Promotions hierarchy in the Promotion dimension, select Discontinued Product from the Type level:
Approach: In order to correctly specify "Discontinued Product", refer to both the Type and Category levels of the hierarchy in the filter you build:
// URL encoding the & symbol to %26
[Promotion].[Promotions].[Category]=[Promotion].[Promotions].[Category].%26[Reseller]
[Promotion].[Promotions].[Type]=[Promotion].[Promotions].[Type].%26[Discontinued Product]
The complete filter looks like this:
http://t/views/w/s?[Promotion].[Promotions].[Category]=[Promotion].[Promotions].[Category].%26[Reseller]&[Promotion].[Promotions].[Type]=[Promotion].[Promotions].[Type].%26[Discontinued Product]
Task: Using the Calendar hierarchy of the Date dimension, choose Q2, CY 2006:
Approach: Refer to the Calendar Year, Calendar Semester, and Calendar Quarter levels of the hierarchy as you write your filter:
[Date].[Calendar].[Calendar Year]=[Date].[Calendar].[Calendar Year].%26[2006] // CY 2006
[Date].[Calendar].[Calendar Semester]=[Date].[Calendar].[Calendar Semester].%26[2006]%26[1] // Semester 1 of 2006
[Date].[Calendar].[Calendar Quarter]=[Date].[Calendar].[Calendar Quarter].%26[2006]%26[2] // Q2 of 2006
Your filter looks like this:
http://t/views/w/s?[Date].[Calendar].[Calendar%20Year]=[Date].[Calendar].[Calendar%20Year].%26[2006]&[Date].[Calendar].[Calendar%20Semester]=[Date].[Calendar].[Calendar%20Semester].%26[2006]%26[1]&[Date].[Calendar].[Calendar%20Quarter]=[Date].[Calendar].[Calendar%20Quarter].%26[2006]%26[2]
