II will extend report created in post and show you how you can implement formatting for recursive group with the Level function.
Open report from the mentioned post or use another one with recursive Tablix group. Choose any of an item in the Tablix. I chose the Path text box since it will be more illustrative. Click on the Textbox and go to Properties Pane to Padding section, Left, Expression and put following formula.
Picture 1 – Level function
You should see similar settings in Properties Pane as on the picture bellow.
Picture 2 – Level function Properties Pane
Look at the report on picture bellow. You can see that the Path Textbox has dynamic padding based on how many recursive child’s current item has. As states in short description, Level function Returns a zero-based integer representing the current depth level of a recursive hierarchy. It helps in our scenario to change format of Padding property based on which depth level of hierarchy is current item placed.
Picture 3 – Recursive Tablix group with dynamic padding format
IIn this post I will continue with implementation of Report which gets data form web service. I would like to demonstrate Recursive group functionality of Tablix Report.
In short:
Add field with Parent member
Set Recursive group
Create dataset connecting to Reporting Services We Service according to the SSRS connect to web service. Put Tablix component, Insert tab in Report Builder, Table component and Insert Table option. Using Microsoft Visual Studio with Data Tools, go to Toolbox-bar and drag and drop Tablix component.
Map created dataset to inserted Tablix. Right click above the Tablix and choose Tablix Properties from popup menu.
Picture 1 – Tablix properties
Select dataset from Dataset Name select box.
Picture 2 – Tablix Properties
Firstly, we have to prepare parent member which means parent directory of currently listed item. We will use Path field we get from Web Service.
Go to Report Data pane, Datasets section, right click to get Dataset Properties window. Click on Add button and from popup menu select Calculated Field. New item is added to our dataset, name it Parent and go to the Expression icon.
Picture 3 – Add calculated field
Put pattern mentioned bellow to Expression box. The pattern splits Path field by SPLIT function and because SPLIT function returns array data type we point to the last member of array by other usage of SPLIT function together with Length attribute. It gives us parent directory of current member. This solution is not the best one. For example, when you set same name of two directories, the report may not behave the expected way. There are other ways how to achieve this but for our purposes it is enough.
Now we can get to Report Tablix to change Tablix group from detail to Recursive. Firstly, we group data by child member which is current item of repository (Report or Directory). Group Properties -> Group on Name.
Picture 5 – Group properties
Now in Group Properties pane go to Advanced section and set Recursive parent. Select Parent field from select box.
Picture 6 – Recursive parent
Set Group Properties to Expand/Collapse functionality, hide group by default and set Type TextBox as Toggle item (picture below). You can get more info regarding Expand/Collapse functionality in this Expand collapse groups.
Picture 7 – Expand / Collapse group
When you finish previous steps, you should see report similar on picture bellow. From left to right we can see item Type, Path and current item Name. Expand/Collapse is available in case that item has child members.
