Construct a tree slicer for SQL

As for Excel Range Slicers, SQL Slicers can also enable 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
  • The result set has 3 columns for the key, description, and depth of the node.
• Parent column
  • Result set has 3 columns for key, description, and parent key
  • Key and Parent key mustn't 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.

In this example, you 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:

• ID column
• Caption column
• Tree level column

Any additional columns will be ignored.

1. After making a connection to the Adventure Works relational database, enter a query to bring back the three columns listed above. 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's 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
2. Select the tree type in the Insert Slicer dialog:
3. This gives this output:
   

4. If the Update range with selection property is selected, and the First column option chosen, then the composite key will be output to the cell. This can then be ‌ parsed with Excel formulas, 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)