SharePoint Windows SharePoint Service (WSS) has become well known in the corporate world.  Many corporations are putting lots of data in department SharePoint sites.  Some companies even use it as a basis for their custom applications.  Utilizing web parts and the SharePoint lists, one could create a mission critical application within the framework of SharePoint, utilizing the benefits of SharePoint's document management.

SharePoint Lists come with some standard list reports in the SharePoint site.  Many times, these simple reports cannot do the job needed for enterprise level applications.  To do more complex reports from SharePoint, there is more than one way to get this data.  Taking data from the lists and putting them into a data warehouse is one option.  By taking the data and massaging it into a data warehouse, you have more business intelligence options for reporting.  However, none of the data is real time.

Another option is to create reports directly from the data source using SharePoint's web service API's.  You can use the MSDN library to reference the different web service methods.  This option has the advantage of being real time.  This is the option we will be utilizing for this article

·         Crystal Reports XI

·         Visual Studio .NET 2003 or above

·         Windows Server 2003 with Windows SharePoint Service (WSS)

This article assumes that you have more than a passing familiarity with WSS, Crystal Reports, and creating Web Services with Visual Studio. 

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;
}

In the crystal report to create the report, we start out with the report wizard.  For a data connection, choose Create New Connection, ADO.NET.  Once you select that you will be prompted for the location of your class library wrapper.  Using the File Path ellipses prompt, find and put the location of your DLL into the connection information or you can just cut and paste it in (see Figure 1).

Figure 1

Also specify the Class name (this should show up in the drop down) and then specify using the dataset from class.  This should be the wrapper method you set up that returns a dataset.  The code would be something like in Listing 2.

Listing 2

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
 
namespace WrapperCrystalXIWebService
{
  public class Class1
  {
    public DataSet ReturnWS_Info(String strUrl)
    {
      DataSet dsToReturn = new DataSet("WebService");
      WS_MySharepointWS wsSP_WebService = new WS_MySharepointWS();
      // Populate the Dataset from Webservice Results here
      return dsToReturn;
     }
  }
}

This code in ReturnWS_Info should populate your dataset with the data returned from the web service.  Then simply return the dataset and Crystal XI will interpret the results into a familiar Grouping like you would see from a SQL query.

From here using the field explorer, you can place your fields on different bands in the designer create groupings and other features you have come to expect from Crystal.  You will want to designate a default parameter to your datasource or you will get prompted many different times when trying to view the data or the fields.

Because of the aforementioned bug you cannot directly connect to a web service.  Once that is resolved, it would be preferable to do that directly rather than create a wrapper class.  But in the meantime that is the workaround we have to use.

In this article we have shown how to connect to SharePoint using a wrapper class that connects a web service.  For this article I connected to my own web service, but you could code your wrapper class to connect directly to the web service.  In that class you would do the appropriate data manipulation, parsing parameters, etc.  Here we keep it in a web service in anticipation of being able to directly connect one day.

Hopefully this has given you an idea of how to use SharePoint lists as a data source for your Crystal reports.  As always… keep on coding.