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.
IIn most of scenarios I realized projects that report Data Sources connect to MS SQL database or to Analysis Services cubes. But reporting services provide you with possibility to connect to more data source types. In this post I would like to show you how to connect with report to Web Service and visualise data. We do not need to developed Web Service for this sample we will use the Reporting Services Web Service. Let’s suppose that we would like to visualise data in Reporting Services Repository.
It can be implemented in few ways. We can use for this task Reporting Services database for example but for our purposes we will use Reporting Services Web Service.
Goal
Create dataset returning data regarding Reporting Services Report Manager directories and reports.
Open Report Builder and create new file or Microsoft Visual Studio with Data Tools, create new project and add new Reporting Services report.
Go to Report Data pane, Data Sources section, right click and select Add Data Source.
Picture 1 – Add Data Source
When Data Source Properties window appears, choose XML in Select connection type select box.
Picture 2 – Connection Type
In Data Source Properties dialog box put Connection string with URL address of your Reporting Services Web Service Endpoint. In my case I will use ReportService2005.asmx.
Picture 3 – Data Source Properties
Create New dataset, select your created Data source DataSource1.
If we look at the code above, we can see that we need to specify method name and its parameters. Everything you need you can find you put URL of We Service Endpoint to your browser. There you can find all methods the web service supports. The code mentioned above is syntax used for Report XML Data Sources. Find more info on the web https://bit.ly/2XvdCWB.
In our case we use Listchildren method which expects two parameters. Item – parent item, the method starts to list, and Recursive specifying you would like to list all its parents recursively. Dataset Properties pane should look like on picture bellow.
Picture 5 – Add Dataset Properties
Voila and we can see items in our dataset. You can link the dataset with the Tablix of your report. As you can see web service gives us information regarding Name and Path of a report or when and by whom was the report created or modified.
Picture 6 – Dataset Fields
In next post I will implement recursive Tablix report with the dataset created in this post. Stay tuned.
