Calling Web Services from Reporting Services

I wanted to do this because I didn't like the idea that we needed SQL procs in a database for reports that have to do the same thing as business logic dlls did for the web app pages. This duplication not only made things harder (being done twice) but allowed the possibility of breaking one when the other was changed and the fact that SQL is so different from code that one was often much easier to change than the other. I found out that you can call web service functions from reports so that the same code can populate reports AND web pages. It is not massively easy to work it out and my example uses Reporting Services 2005 and .Net 2.0. It also uses the Reporting Services XML Data Provider for the dataset (since the web service returns xml in the form of soap).

Create your web service as per a normal web service. In order to be generic and not need changing or growing for every different sort of function I would need to call, I made some generic functions that use reflection to call whatever function I want. You can probably work out what I did if I show you the code for the generic function:
```
[WebMethod()]
public ArrayList ExecuteArrayMethod(String typeName, String method, object[] ParamArray)
{
 ArrayList returnObject = null;
 Type type = AccessType(typeName);

 try
 {
  if (type != null && type.GetMethod(method).GetCustomAttributes(typeof(ExportedMethod), false).Length > 0)
  {
   returnObject = (ArrayList)type.InvokeMember(method, BindingFlags.Default | BindingFlags.InvokeMethod, null, null, ParamArray);
  }
 }
 catch (Exception)
 {
  throw;
 }
 return returnObject;
}
```
There are a couple of points of note. The ExportedMethod attribute was something I added to control what functions were allowed to be exported to the reports and what couldn't, it is optional. Also, the arraylist allows collections of any type to be exported via the function. I have created a dataset flavour which I haven't tested yet and which I don't think I'll need.

Obviously the functions that actually generate your report data must be callable from the parameters you have available in your web service. If the function needs a load more information like connection strings, arguments etc, then either pass them in via the web service function or if they are private, create a parent function that can obtain these values and pass them to the worker function. The function you call from the web service must be static (I think!).

You will need to declare the types of any exported classes (i.e. types put into the ArrayList) at the top above the web service class definition even though you never need to reference them by name in the report. This forces the webservice to include the property data in the xml schema used by callers of the functions. You do this:
```
[XmlInclude(typeof(ClassName1)), XmlInclude(typeof(ClassName2))]
```

Build and deploy the web service to a url somewhere, such as http://myhost/webservices/myservice.asmx

Navigate to the asmx file in your browser and click on the function that you will be calling from your report. You should get an invoke section at the top which you can use to ensure that your code is returning the expected data. Do not go straight to the report without checking this, the report syntax is hard enough without the underlying data actually not working! Also note the RESPONSE syntax under the soap 1.2 section (the second of the two brown boxes). It will contain some soap bits (ignore these) and then a response tag, a result tag and then data depending on what you are returning. These are the important bits.

Open up your reporting services project (or create a new one) and add a datasource of type XML. In the connection string field, put the path to the asmx web service including the http and the asmx filename itself. Under credentials, you will need to put whatever is needed to call functions on this web service. In my case, it is a private intranet so I use windows credentials.

Create/edit a report and add a new dataset that uses this XML datasource, set the type to text and this is where the fun begins. You need to use a query syntax similar to XPath but not quite (search the web for details, there are various MSDN articles about it). This names the method, the parameters and the expected return values (and optional types). I will show mine so hopefully it makes more sense:


<Query>
  <Method Name="ExecuteArrayMethod" Namespace="Namespace.Of.The.Webservice">
      <Parameters>
       <Parameter Name="typeName">
       <Parameter Name="method">
       <Parameter Name="QuoteReference">
   </Parameters>
  </Method> 
   <ElementPath IgnoreNamespaces="true">
           ExecuteArrayMethodResponse{}/ExecuteArrayMethodResult{}/anyType{Location,Description,Schedule,Item_Code,Quantity(Integer),OriginalSale(Decimal),RevisedSale(Decimal)}
</ElementPath>
</Query>

Note that the anyType is the type that is returned in my soap response because I used an arraylist, it might be something else depending on your data. I have also typed some of the return values so that they can be treated as numbers rather than the string defaults. This XPath relates to the following web service xml response:


<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
  <soap12:Body>
    <ExecuteArrayMethodResponse xmlns="Namespace.Of.The.Webservice">
      <ExecuteArrayMethodResult>
        <anyType />
        <anyType />
      </ExecuteArrayMethodResult>
    </ExecuteArrayMethodResponse>
  </soap12:Body>
</soap12:Envelope>

Every time you save the xml, it will parse it and give you an error if there is a syntax problem, it does not understand line breaks so you might get a line1, character 300 error or similar, unfortunately, you have to try and build up the element path if your syntax is wrong and hope for the best.

If your web method requires parameters (as mine does), click on the ellipses next to the dataset (...) and click the parameters tab. You need to define any parameters passed to the method here so either a) use static values (in my case the method parameter is static for any given report) or to use a previously created report parameter if it will be different for each instance of the report (like my QuoteReference is).

Exit the edit pages and click the ! button to call the method. You might be prompted for parameters and you should see your expected table of data or you might get an error. If you get data but it is incorrect, check your element path. If you get no data, your element path might be wrong or there might be no data being returned, remove the element path and leave it blank to see if there is actually data present, if there is, your element path is still wrong. if you get an error, click the little red X icon and make the dialog bigger, the errors are often hidden but usually useful "no permission", "could not instantitate type" etc

Once you get data in the right format, your dataset on the left should now show the field names which can be used on the report in the normal way.

One of the big benefits for us is that the workload is shifted from the database server which also seems to be chugging away to the web server which is fairly lightly loaded since the work is now mostly done in a web service not a view/proc.

When you deploy your report to another server, you might get other errors and these are sometimes related to the permissions with which the report server is attempting to access your web service with. When you go to the web service in your browser, you are usually logging in as you and you might have access, the report server however logs in to the webservice as the report server (and not the person running the report). I got round this by modifying the datasource on the live server to use specific login credentials rather than windows credentials.