SSRS connect to web service

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.

When Data Source Properties window appears, choose XML in Select connection type select box.

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.

Create New dataset, select your created Data source DataSource1.

Put following code to Query text area.

<Query>
<Method Name="ListChildren"
Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">
<Parameters>
<Parameter Name="Item" Type="String">
     <DefaultValue>/</DefaultValue>
</Parameter>
<Parameter Name="Recursive" Type="Boolean">
<DefaultValue>1</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

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.

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.

In next post I will implement recursive Tablix report with the dataset created in this post. Stay tuned.