SSRS, Reporting Services, SQL Server, Business Inteligence, Pentaho, T-SQL
To make your report Tablix looking better it could be very useful feature. As you can join Cells in excel you can join merge Textboxes in your Report Tablix.
Bellow you can see the result.
For the opposite action:
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.
You should see similar settings in Properties Pane as on the picture bellow.
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.
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:
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.
Select dataset from Dataset Name select box.
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.
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.
=SPLIT(Fields!Path.Value ,”/”)(Split(Fields!Path.Value ,”/”).Length – 2 )
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.
Now in Group Properties pane go to Advanced section and set Recursive parent. Select Parent field from select box.
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.
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.
