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.

There are 4 different query layouts you can choose from depending on your database schema. In each case the result set must be in the order you wish to display the resulting tree. The modes are:

• Depth column
  • Result set has 3 columns for key, description and depth of the node.
• Parent column
  • Result set has 3 columns for key, description and parent key
  • Key and Parent key must not be null, you can use -1 for top level nodes
• One column per level
  • Each column is a level in the tree. High level node values are repeated on each row
• Key and Caption per level
  • Each key and caption column pair is a level in the tree. High level node values are repeated on each row.

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.

This uses the Depth column query 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.

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
   	,	3 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
   	,	2 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
   	,	1 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)

Example result sets for different query styles

Depth Column

Parent Column

One Column per Level

Key and Caption Column per Level

See Also

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