The Crystal Report will use an XML Data Source to do a report.  Instead of going directly against the SharePoint web service, we will create our own web service to go against our SharePoint list.  There are two main reasons to use your own web service as the datasource instead of going directly against the SharePoint web service.  First, most of the SharePoint web services use complex data type parameters (mainly xml) which you cannot easily pass through using Crystal directly.  Second, if you want to report on lists in more than one "subsite," your web service can point to the correct site for the data. 

To clarify this, here is how the SharePoint web services work.  The SharePoint web service works per SharePoint site.  So for instance, say I have a SharePoint site with the URL http://SharePoint server/sites/topsite/sitewithlists.  To use the web service to get data from the lists for that site, I would use the following URL for the lists web service: http://SharePoint server/sites/topsite/sitewithlists/_vti_bin/lists.asmx.

So our web service will return a DataSet to the Crystal report.  Because of an outstanding KB issue we cannot directly access the web service, but we can create a wrapper class that returns a dataset to Crystal.

Our custom web service will take the URL as the parameter.  This example is simple, but you can expand it to have a parameter that contains more meaningful information for your user, yet return the URL.  For instance, have a table that contains a description of a site and the URL and then bind that to the parameter.  Now pass the URL as the value to the wrapper class.  See listing 1 to see a sample snippet of the web service code the wrapper calls.

Listing 1

XmlDocument xmlDoc = new System.Xml.XmlDocument();
XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element,"Query","");
XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element,"ViewFields","");
XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element,"QueryOptions","");
 
ndQueryOptions.InnerXml = "<IncludeMandatoryColumns>FALSE</IncludeMandatoryColumns>" +
    "<DateInUtc>TRUE</DateInUtc>";
ndViewFields.InnerXml = "<FieldRef Name='Field1' /><FieldRef Name='Field2'/>";
ndQuery.InnerXml = "<Where><And><Gt><FieldRef Name='Field1'/>" +
    "<Value Type='Number'>5000</Value></Gt><Gt><FieldRef Name='Field2'/>" +
    "<Value Type='DateTime'>2003-07-03T00:00:00</Value></Gt></And></Where>";
try
{
  XmlNode ndListItems = listService.GetListItems("List_Name", null, ndQuery,
  ndViewFields, null, ndQueryOptions);
  DataSet dsReturn = new DataSet();
  dsReturn. ReadXml(ndListItems.InnerXml);
  return dsReturn;
}