Reporting Services, Web Service Data Source, and Parameters
I wasted an entire day trying to figure out why I couldn’t call a web service that takes parameters. The problem turned out to be very simple to fix, but not at all obvious. I found some documentation on MSDN: Defining Report Datasets for XML Data, and also Reporting Services: Using XML and Web Service Data Sources. Both of these use examples that use the Reporting Services web services. But I couldn’t get it to work with my web service that I created with Visual Studio 2008.
Calling the Web Method
Here is how I set things up (the test web method is called GetData and takes a single string parameter). First, define the data source:
- Select XML in the Type field
- Enter the URL to the ASMX file in the Connection string field:
- Click Credentials and set it to Windows Authentication (that’s what I wanted, at least)
- Click OK
This will give you a data source that knows how to connect to the web service. Next you need to add a data set that calls a single web method, and this is where things went wrong.
- Create a new data set that uses the data source defined above
- Click the Query Designer… button
- Paste XML that defines what method to call, and what parameter values to use:
<Method Name="GetData" Namespace="http://tempuri.org/">
I’ve highlighted in bold and red the two changes I had to make over what I found in the documentation. By default, a new web services uses the namespace “http://tempuri.org/” that includes a trailing slash. So you have to add this in the Namespace attribute of the Method element. But when you do this, you get an extra slash in the default action: http://tempuri.org//GetData. To get around that, you have to explicitly provide the soap action using the SoapAction attribute.
Once you have this defined, you can click on the ! button to call your web method. The * in ElementPath tells Reporting Services to look for the element it finds that repeats to define the rows, and the next-level children as the column values.
Now about the parameters. You define the set of parameters using Parameter elements inside a Parameters element, as shown above. The DefaultValue element provides the value to use when it’s not provided. This will be the value that RS uses when you click the ! button to run the “query” in the Query Designer window.
The default value will also be used if you haven’t defined a query parameter with the same name as the parameter value. For example, if the parameter for you web service is called “value,” you could define the parameter like this:
The report will now use this value, instead of the default value specified in the “query” XML, when you run the report.