blogger counters

Wednesday, June 30, 2010

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:

 XML Data Source

  • 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:

<Query>
    <Method Name="GetData" Namespace="http://tempuri.org/">
        <Parameters>
            <Parameter Name="value">
                <DefaultValue>test</DefaultValue>
            </Parameter>
        </Parameters>
    </Method>
    <SoapAction>http://tempuri.org/GetData</SoapAction>
    <ElementPath IgnoreNamespaces="true">*</ElementPath>
</Query>

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.

Parameters

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:

DatasetParameters

The report will now use this value, instead of the default value specified in the “query” XML, when you run the report.