Constructing a Tree Slicer For SQL

As for Excel Range Slicers, SQL Slicers can also allow users to select items in a familiar tree style. The key is to have three columns returned by the SQL query:

1. ID column
2. Caption column
3. Tree level column

Any additional columns will be ignored.

Example

In this example, we will use Microsoft's Adventure Works demo relational database, and the DimProduct, DimProductSubcategory and DimProductCategory tables, which will be familiar to users of the Adventure Works cube as the Product dimension sources.

1. After making a connection to the Adventure Works relational database, you should enter a query to bring back the three columns listed above:
   
2. This query can be used for this case:

   SELECT
   	'c' + CONVERT(varchar(10), p3.ProductKey) AS Id
   	,	p3.EnglishProductName AS Caption
   	,	4 AS TreeLevel
   	,	p1.EnglishProductCategoryName + p2.EnglishProductSubcategoryName + p3.EnglishProductName AS Ordering
   FROM dbo.DimProduct p3
   JOIN dbo.DimProductSubcategory p2
   	ON p3.ProductSubcategoryKey = p2.ProductSubcategoryKey
   JOIN dbo.DimProductCategory p1
   	ON p1.ProductCategoryKey = p2.ProductCategoryKey
   UNION ALL
   SELECT
   	'b' + CONVERT(varchar(10), p2.ProductSubcategoryKey) AS Id
   	,	p2.EnglishProductSubcategoryName AS Caption
   	,	3 AS TreeLevel
   	,	p1.EnglishProductCategoryName + p2.EnglishProductSubcategoryName AS Ordering
   FROM dbo.DimProductSubcategory p2
   JOIN dbo.DimProductCategory p1
   	ON p1.ProductCategoryKey = p2.ProductCategoryKey
   UNION ALL
   SELECT
   	'a' + CONVERT(varchar(10), p1.ProductCategoryKey) AS Id
   	,	p1.EnglishProductCategoryName AS Caption
   	,	2 AS TreeLevel
   	,	p1.EnglishProductCategoryName AS Ordering
   FROM dbo.DimProductCategory p1
   ORDER BY Ordering

   There are a few points to note about this particular query:

   • The three columns mentioned above are the first three columns to appear in the query
   • There is a fourth column that doesn't affect the slicer output, but is used by the query to order its output
   • The key column gives an initial letter (a, b or c) to indicate the level selected, followed by a number giving the key
3. The tree type should then be selected in the Insert Slicer dialog:
   
4. This gives the following output:
   
5. If the Update range with selection property is selected, and the First column option chosen, then the composite key that was mentioned above will be output to the cell. This can then be simply parsed with Excel formulae, for example (assuming the output cell is A1):

   Level

   =IF(LEFT(A1,1)="a","Category",IF(LEFT(A1,1)="b","Subcategory",IF(LEFT(A1,1)="c","Product","Unknown")))

   Id

   =MID(A1,2,LEN(A1)-1)

See Also

• SQL Slicers (Standard Users)
• SQL Slicers (Administrators)
• Excel Range Slicers