Have a cool decision tree model whose content you want to share with folks who don't have SQL Server 2005 installed? Using this tip, you can deploy a report that they can simply use their web browser to view and interact with.

In this tip, we show you how to render a decision tree model created using SQL Server 2005 Data Mining as a SQL Server Reporting Services 2005 report. The rendering format is an indented table. For the rest of the tip, we'll use a decision tree model "Target Mail Tree" created from the AdventureWorks sample database. The model contains two predictable attributes, - "BikeBuyer" and "Gender".

You can download the sample Analysis Services and Report projects we create below here.

Setting up the Report Server Project

1. Open Visual Studio and create a new Report Server Project.
2. Add a new Shared Data Source to the Analysis Server database with the "Target Mail Tree" model.
3. Right Click on "Reports" and select the option "Add New Item". Select Report from the Templates and click Add.

Setting up the Report Parameter

In this section, we will create a report parameter that allows us to select the tree for display in the report. Each predictable attribute corresponds to a tree.

1. Navigate to the Data tab.  Select "New DataSet" from the drop down for dataset.
2. Enter Name="SelectTree", DataSource="Datasource created in step#2" and Command Type="Text". Click OK
3. In the DMX query designer, switch to Query Mode.
4. Enter the query SELECT ATTRIBUTE_NAME FROM [Target Mail Tree].CONTENT WHERE NODE_TYPE=2"
5. Click the "Save" icon to save the dataset.
6. Click on menu Report -> Report Parameters.
7. Click Add to add a new Parameter.
8. Enter Name="Tree", Data type="String", Prompt="Tree". Uncheck "Allow Blank Value" and "Multi-Value"
9. Select the "From query" option for available values.
10. Select the Dataset "SelectTree". Select "ATTRIBUTE_NAME" for the label and the value field.
11. Click OK to save the parameter.

Creating the Report Dataset

In this section, we'll create the dataset for displaying the tree in the report. This dataset is created using a DMX content query to the Analysis Server.

1. Navigate to the Data tab.  Select "New DataSet" from the drop down for dataset.
2. Enter Name="TreeData", DataSource="Datasource created in step#2" and Command Type="Text". Click OK
3. In the DMX query designer, select the icon for Report Parameter. Add a parameter "Tree" with value "Bike Buyer"
4. In the DMX query designer, switch to Query Mode.
5. Enter the query SELECT FLATTENED * FROM [Target Mail Tree].CONTENT WHERE ATTRIBUTE_NAME=@Tree
6. Click on the Save icon to save the dataset.

Designing the report to render the tree

In this section, we'll use the dataset created above to render the report using a tree format.

1. Open the toolbox and drag a Table into the report.
2. Delete the table Footer row.
3. Add four columns with Header "Node", "Distribution", "Support" and "Probability"
4. Open the "DataSet" tab in Visual Studio. You should see two datasets "SelectTree" and "TreeData" listed. Expand "Tree Data".
5. In the first row of the table, drag the columns "NODE_CAPTION", "NODE_SUPPORT" and "NODE_PROBABILITY" under the respective column headers.
6. Select the first row, right click and select the option "Insert Group"
7. For the Group On expression, select "=Fields!NODE_UNIQUE_NAME.Value"
8. For the Parent group option, select "=Fields!PARENT_UNIQUE_NAME.Value"
9. Navigate to the "Visibility" tab. Select "Expression" for Initial visibility and enter "=Level()>1". This will hide all nodes initially below level 1.
10. Check "Visibility can be toggled by another report item" and select NODE_CAPTION.
11. Click OK.
12. Select the cell for NODE_CAPTION and select Properties. In the format tab, change the Left indentation property to the formula "=CStr(2+Level()*14)+"pt". This will indent the cell based on their level in the tree.
13. At this point, clicking on the "Preview" tab will render the decision tree in a nested level format.

Adding node distribution

In this section, we'll add the node distribution property to for each node to the report:

1. In the layout tab, select the table row, right click and "Insert Group"
2. For the Group On expression, select "=Fields!NODE_UNIQUE_NAME.Value"
3. For the Parent group option, select "=Fields!PARENT_UNIQUE_NAME.Value"
4. Click OK.
5. You should see a table with three rows now. The first row is the row where we'll add the node columns.
6. Delete the fields added to the table above from the details row (second row)
7. Drag the columns "NODE_CAPTION", "NODE_SUPPORT" and "NODE_PROBABILITY" under the respective column headers on the first row of the table. Select the cell under the column "Distribution" and rename it to "NODE_DISTRIBUTION" using the textbox properties. Enter the Text "DISTRIBUTION" in the cell.
8. Select the first row, right click and select "Edit Group".
9. Navigate to the "Visibility" tab. Select "Expression" for Initial visibility and enter "=Level()>1". This will hide all nodes initially below level 1.
10. Check "Visibility can be toggled by another report item" and select NODE_CAPTION_1. (This is the new textbox ID which contains the node caption)
11. Click OK
12. Edit the cells in the second row. Drag the columns "NODE_DISTRIBUTION_SUPPORT" and "NODE_DISTRIBUTION_PROBABILITY" under the support and probability headers. Add the formula "=Cstr(Fields!NODE_DISTRIBUTION_ATTRIBUTE_NAME.Value) & "=" & Cstr(Fields!NODE_DISTRIBUTION_ATTRIBUTE_VALUE.Value)"  in the cell under the distribution column.
13. Select the second row, right click and select "Edit Group".   
14. Remove the expression for "Group On" and "Parent Group"
15. Select the Visibility tab. Set Initial Visibility to "Hidden".
16. Check "Visibility can be toggled by another report item" and select NODE_DISTRIBUTION.
17. Click OK

Voila, you now have a report that shows a nested level representation of your decision tree: