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
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.
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>" +
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'/>" +
XmlNode ndListItems = listService.GetListItems("List_Name", null, ndQuery,
ndViewFields, null, ndQueryOptions);
DataSet dsReturn = new DataSet();