SSRS:Enabling Tree View

Enabling Tree View and customizing it to pass parameters to data region on user selection

Requirement: To enable a tree view in the report to browse the different hierarchies. On selection of the value at any hierarchy the corresponding data shall be shown in the tablix.

The following sample was tried using the Adventure Works data in SQL Server 2008 Reporting Services.

My first challenge was to establish the tree structure .

I did that in a matter of seconds ,but I was told to have the data in one column only . LI did some browsing and I came up with the following solution.

Right click on the last hierarchy level

Then select Insert Rows -> Outside GroupAbove

You will get a new texbox above .Copy the value from the previous column (towards the left ) in to this texbox.

The empty column is where u get the toggle button so leave it as it is. To enable the toggling

Select the group properties. Make Visibility as hidden and select the Checkbox Display can be toggled by the report item and choose the previous level grouping in the item.

Make the columns border as white in the Tablix properties and change the width of the columns which has the toggle button.

Now to the main part, passing in parameters to the data region.

I created 2 parameters @ProductCategory and @ProductSubCategory.

Now to enable user selection in the tree view , click on ProductCategory in the tree structure and choose properties.

Modify the actions as follows

Enable as hyperlink – go to report

We will call the same report PurchasingSummary

Add the parameter ProductCategory( u can choose this from the dropdownlist)

Assign the value as Fields!ProductCategory.Value which is nothing but the value selected by the user(on click)

Similary click on ProductSubCategory in the tree structure and enable the action.

Using the parameters , I could use the filter conditions in the Tablix Properties.But I chose to do it in the query in the dataset .(sticking on to my best practice J)

I modified the where conditions with isnull function. The query is as follows

SELECT

YEAR(poh.OrderDate) * 100 + MONTH(poh.OrderDate) AS OrderMonth

,pc.Name AS ProductCategory

,psc.Name AS ProductSubCategory

,p.Name

,SUM(pod.OrderQty) AS OrderQty

,SUM(pod.LineTotal) AS Amount

FROM Production.ProductCategory AS pc

INNER JOIN Production.ProductSubcategory AS psc

ON pc.ProductCategoryID = psc.ProductCategoryID

INNER JOIN Production.Product AS p

ON psc.ProductSubcategoryID = p.ProductSubcategoryID

INNER JOIN Purchasing.PurchaseOrderDetail AS pod

ON p.ProductID = pod.ProductID

AND p.ProductID = pod.ProductID

INNER JOIN Purchasing.PurchaseOrderHeader AS poh

ON pod.PurchaseOrderID = poh.PurchaseOrderID

AND pod.PurchaseOrderID = poh.PurchaseOrderID

where pc.Name = isnull(@ProductCategory,pc.Name)

and psc.Name = ISNULL(@ProductSubCategory , psc.Name)

GROUP BY pc.Name

,psc.Name

,p.Name

,YEAR(poh.OrderDate) * 100 + MONTH(poh.OrderDate)

ORDER BY ProductCategory, ProductSubCategory, p.Name, OrderMonth;

If the @ProductCategory is selected by the user then the query filters the data else if it is passed null where nothing really happens.

Now preview the report, you should notice the following things else you have missed something

o The Product categories and the subcategories in the tree structure are clickable.

o On click of the productSubCategory or ProductCategory values the corresponding data is shown in the Matrix.

o On click of the productSubCategory or ProductCategory values the entire report is refreshed and the toggle button in the tree structure collapses everytime

Everything looks good except the tree structure collapsing every time the report being called. DAMN!!!

After some head breaking idea I came up with a roundabout idea to achieve this.

I created 2 more report parameters SubCategoryShow (Boolean) and Categoryname (text).This will be hidden in the report and used internally to store the value of the user selection , indirectly controlling the tree structure’s initial state.

Select the action property of the ProductSubCategory textbox and assign the values for the parameters SubCategoryShow and Categoryname while calling the report.

Since Product Category was the outer hierarchy there was no change there.

Select the textbox where the togglebutton is present (the first column) and open the expression for the Initial Toggle state property.I used the following expression there.

=iif((Parameters!SubCategoryShow.Value)And Parameters!CategoryName.Value =Fields!ProductCategory.Value ,true,false)

Explanation:If user has selected a subcategory and if the product name matches the item selected by the user than keep it open .The initial toggle state will be + or – based on the value selected.Remember InitialToggle state false means (+) and true means (-)

Now select the ProductSubCategory Group and select the visibility property.Choose the expression in show or hide based on an expression. I used the following expression there (similar to previous one)

=iif((Parameters!SubCategoryShow.Value)And Parameters!CategoryName.Value =Fields!ProductCategory.Value ,false,true)

This could be done for any number of hierarchies with appropriate parameters.

Now Build the Report, you could see the state of the tree structure remains the same each time on User Selection.