Teun.ToString()

Update: I have written an extended version of the example described in this article. If you plan to work with the solution described here, check out the newer version.

This article is a translation of my article in Dutch that appeared in issue #9 of .NET Magazine. The PDF version of the Dutch article can be found at the Microsoft site. All code can also be downloaded from the Microsoft site or here. If you want to copy the code in this article, be aware that some characters may not be what they look like.

As part of Microsoft's work to become a major player in the Business Intelligence marketplace, it introduced Microsoft Reporting Services in 2004.. This reporting engine is freely available for owners of a SQL Server 2000 license. It allows organisations to create WYSIWYG reports combiining several sources like OLEDB databases and OLAP cubes. Reporting Services offers a broad range of target formats (such as HTML, PDF and MS Excel) and several delivery methods (like a web interface or scheduled e-mails). Designing and testing of reports is done within Visual Studio. All this allows the typical Microsoft developer to use Reporting Services to quickly create impressive reports in a well-known and comfortable environment.

Of course, most organisation posess more information than that stored in relational databases. For example: many organisations use SharePoint lists to keep track of critical business processes. Think of project teams administering status and priority of all tasks and subtasks within the project in a Tasks list. One would like to include that data into reports as well to allow the management to get an overview of the progress presented in a graph. Luckily, the architecture of Microsoft Reporting Services has several extension points, so we can create a solution for this requirement by building a Reporting Services Extension. How to do that is the subject of this article.

SharePoint Extensibility

An extension to Reporting Services that can report about data in SharePoint lists should at least be able to get access to this data in SharePoint. This turns out to be relatively easy, as Windows SharePoint Services (WSS) exposes a broad range of its functionality as SOAP web services. In our case, we are only interested in the SOAP service Lists.asmx. This services exists for every WSS site at the URL http://servername/sites/sitename/_vti_bin/Lists.asmx. The service exposes methods such as GetListCollection (returns all available lists in the site), GetList (returns detailed information about the list and its columns) and GetListItems (returns the full content of the list). The result of all of these methods is an XmlNode object (representing the type from the WSDL). Getting the right information from this XML structure can be tiresome, but all of the available information on the list can be retrieved through this interface.

Reporting Services Extensibility

The Reporting Services architecture provides extension point at several places. This is all done through a plug-in model. The extension is an assembly containing .NET types implementing specific interfaces. Reporting Services works only through these interfaces. Which concrete type is instantiated depends on configuration and options selected by the user.

• Reporting Services knows four types of plug-ins: Data Processing Extension to report over custom data sources; this is the type of plug-in we will build in the article.
• Delivery Extension to create custom ways to deliver scheduled reports; standard options are e-mail and fileshare.
• Rendering Extension to generate reports in custom document formats. Renderers are available for HTML 3.2, HTML 4.0, TIFF images, PDF documents, Excel documents and XML. Some organisation may have a need for additional formats. RTF and Microsoft Word are conspicuous absents.
• Security Extension to connect Reporting Services' Role Based Security to a custom system for authenticatie/autorisatie, like an LDAP service. By default, Reporting Services validates all user credentials against a windows domain. Obviously, home-growing you own security extension can cause its own security risks. Be careful.

How it works

To report over the SharePoint lists, we will write a Data Processing Extension. But before we delve into the code, let's have a short look at the way the Reporting Services engine will use our extension. The pattern used is very much like that of DataReaders in ADO.NET. A Data Processing Extension contains classes implementing at least the following interfaces (all from the namespace Microsoft.ReportingServices.DataProcessing): IDbConnection, IDbCommand and IDataReader. There are sevral other interfaces that Data Processing Extension can implement when appropriate, but these three are the core.

Image 1: Collaboration diag for RS, IDbConnection, IDbCommand en IDatareader

As can be seen in image 1, the Reporting Services engine first creates an instance of IDbConnection. On this instance, it sets a number of properties (eg. ConnectionString) and then calls the methods Open and CreateCommand. The Connection object then returns an instance implementing IDbCommand. This Command object features CommandType and CommandText properties as well as an ExecuteReader method. This method returns an object implementing IDataReader. This object is very much like an ADO.NET DataReader. The forward-only reader is used by reporting services to pull out all the data it needs for the report.

We will now have a look at some relevant code snippets from the central classes of our extension. The full working source code can be downloaded from www.microsoft.nl/netmagazine9.

Connection

The Connection class implements IDbConnection and is Reporting Sevrices' starting point when using our extension. Of the properties and methods from IDbConnection, a number is not supported bij our extension. That's why for example BeginTransaction will always throw NotImplementedException and the ConnectionTimeout property always returns 0. Transactions and timeouts are not really relevant for our extension, because we do not have a "real" database connection in use. That is why so little happens in the Open and Close methods:

private System.Data.ConnectionState _state = System.Data.ConnectionState.Closed; public System.Data.ConnectionState State { get {return _state;} } public void Open() { _state = System.Data.ConnectionState.Open; return; } public void Close() { _state = System.Data.ConnectionState.Closed; return; }

The one property in Connection that is important is ConnectionString. The connection string for this extension can contain only one item: the URL for the WSS site containing the data. To make later elaboration possible, we will still use the rather complex of the OLEDB connection strings: NAME1=VALUE1;NAME2=VALUE2. The different fragments in the connection string are stored in a Hashtable for later retrieval. The WssSiteUrl property is used to extract the correct fragment from the hashtable.

private string _connString; private Hashtable _connStringParts = new Hashtable(); public string ConnectionString { get {return _connString;} set { _connString = value; string[] parts = _connString.Split(';'); _connStringParts = new Hashtable(); foreach(string part in parts) { if(part.IndexOf("=") < site =" (string)_connStringParts[" site =" site"> if(part.IndexOf("=") < 1) { throw new ArgumentException(String.Format("The part {0} is not a valid part for a connection string", part)); } _connStringParts.Add(part.Substring(0, part.IndexOf("=")).ToLower(), part.Substring(part.IndexOf("=") + 1)); } } }

Besides implementing the IDbConnection-interface, the Connection class also implements the IDbConnectionExtension interface. This interface contains four properties the deal with security and login credentials: Impersonate, IntegratedSecurity, UserName and Password. By implementing this interface, we signal that our extension is capable of using this information in building an appropriate connection to out data source. In our case, we will be calling web services and pass the ligin information to the WSS webserver. The Connection class itself does nothing with the properties. The information they hold will later be used by the Command object. The CreateCommand method does nothing but returning an instance of the correct Command type.

public IDbCommand CreateCommand() { Command result = new Command(this); return result; }

The LocalizedName property allows us to specify how our extension will show up in the list of available source types. We always return the text "SharePoint Lists".

Command

The Command object, which is returned by the CreateCommand method, is the real core of our solution. The object can, based upon the Connection object and the name of a SharePoint list, fill an ADO.NET DataTable with all of the items in the list. Then it can instantiate a DataReader object that feeds the data from this DataTable to the Reporting Services engine.

The most important properties in the IDbCommand interface are CommandText and CommandType. When reporting on relational databases, CommandText will normally contain an SQL query.In out extension, we use the CommandText property to pass the name of the SharePoint list. Of the three existing CommandType values, we only support CommandType.Text:

private CommandType _type = CommandType.Text; public CommandType CommandType { get { return _type; } set { if (value != CommandType.Text) throw new NotSupportedException(); _type = value; } }

Our solution does not support parameters and transactions. So, the Parameters property always returns an empty collection and the Transaction property is always null, while the CreateParameter method throws a NotImplementedException. The CommandTimeout property in this solution always returns 30 (for 30 seconds).

The real work of Command happens when the ExecuteReader method is called. This in turn calls GetDataTableFromWSS and passes the resulting DataTable to a new DataReader:

public IDataReader ExecuteReader(CommandBehavior behavior) { data.DataTable table = GetDataTableFromWSS((behavior == CommandBehavior.SchemaOnly)); return new DataReader(this._connection, table, _parameters); }

The GetDataTableFromWSS is the workhorse of the solution. The following code fragment shows the full source of this method.

private data.DataTable GetDataTableFromWSS(bool onlySchema) { WssSite.Lists.Lists lists = new WssSite.Lists.Lists(); lists.Url = _connection.WssSiteUrl + "_vti_bin/Lists.asmx"; lists.Credentials = GetCredential(_connection); lists.PreAuthenticate = true; lists.Timeout = this.CommandTimeout; XmlNode ListCollectionNode = lists.GetListCollection(); XmlElement List = (XmlElement)ListCollectionNode.SelectSingleNode( String.Format("wss:List[@Title='{0}']", this.CommandText), NameSpaceMgr); if(List == null) { throw new ArgumentException( String.Format("The list {0} could not be found in the site {1}", this.CommandText, _connection.WssSiteUrl)); } string TechListName = List.GetAttribute("Name"); data.DataTable result = new data.DataTable("list"); XmlNode ListInfoNode = lists.GetList(TechListName); StringBuilder fieldRefs = new StringBuilder(); Hashtable DisplayNames = new Hashtable(); foreach(XmlElement Field in ListInfoNode.SelectNodes("wss:Fields/wss:Field", NameSpaceMgr)) { string FieldName = Field.GetAttribute("Name"); string FieldDisplayName = Field.GetAttribute("DisplayName"); if(result.Columns.Contains(FieldDisplayName)) { FieldDisplayName = FieldDisplayName + " (" + FieldName + ")"; } result.Columns.Add(FieldDisplayName, TypeFromField(Field)); fieldRefs.AppendFormat("", FieldName); DisplayNames.Add(FieldDisplayName, FieldName); } if(onlySchema)return result; XmlElement fields = ListInfoNode.OwnerDocument.CreateElement("ViewFields"); fields.InnerXml = fieldRefs.ToString(); XmlNode ItemsNode = lists.GetListItems(TechListName, "", null, fields, "1000000", null); // Lookup fields always start with the numeric ID, then ;# and then the string representation. // We are normally only interested in the name, so we strip the ID. System.Text.RegularExpressions.Regex CheckLookup = new System.Text.RegularExpressions.Regex("^\\d+;#"); foreach(XmlElement Item in ItemsNode.SelectNodes("rs:data/z:row", NameSpaceMgr)) { data.DataRow newRow = result.NewRow(); foreach(data.DataColumn col in result.Columns) { if(Item.HasAttribute("ows_" + (string)DisplayNames[col.ColumnName])) { string val = Item.GetAttribute("ows_" + (string)DisplayNames[col.ColumnName]); if(CheckLookup.IsMatch((string)val)) { string valString = val as String; val = valString.Substring(valString.IndexOf("#") + 1); } // Assigning a string to a field that expects numbers or // datetime values will implicitly convert them newRow[col] = val; } } result.Rows.Add(newRow); } return result; }

So what happens in this code? It first instantiates a proxy class for the web service. It then sets some properties based upon proerties of the Connection object. Then is calls the web method GetListCollection. This method returns us an XML structure of information about all lists. The only thing we are after is the Name of the list, which is a Guid we will need in all of the web service calls later on. The XML returned by GetListCollection looks like this:

<lists xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <list defaultviewurl="/sites/kd/work/Lists/Announcements/AllItems.aspx" id="{FB328912-52A1-4E00-88C8-5388559437E8}" title="Announcements" description="Use the Announcements list to ..." imageurl="/_layouts/images/itann.gif" name="{FB328912-52A1-4E00-88C8-5388559437E8}" many="" more=""> </list>

Now that we know the unique name of the list, we call the GetList method. The resulting XML contains information about names and types of all columns in the list:

<list> <fields> <field type="Text" name="Title" displayname="Title" required="TRUE" frombasetype="TRUE" colname="nvarchar3"> <field name="Case" frombasetype="FALSE" type="Lookup" list="{BE7960D6-D604-4799-B34F-8FAA67A8392C}" showfield="Case_x0020_Number" displayname="Requirement" description="Case this workitem belongs to" required="TRUE" colname="int4"> ... </fields> </list>

For each column in the WSS list, we add a new field to our DataTable with the same name. There is one nasty detail: WSS uses both a Name and a DisplayName property. When a column in WSS is created, these are identical, but DisplayName can be changed, while Name is fixed. SharePoint shows only the DisplayName in its UI, so it is important that we follow these names in the report designer (people working with the WSS list will be blissfully unaware of the real immutable names). So we use the DisplayName as column name in our DataTable and have to keep track of which Name belongs to which column name (we do this in a hashtable). Also, we build an XML fragment containing a reference to each column in a StringBuilder. We will use this XML string later to indicate that we want to retrieve all available fields from the list.

When we only have to return the schema - this happens when the extension is used by the report designer - we are ready now, but normally, we also need the data itself. We will now load this data into the DataTable using the web method GetListItems. When you call this method, it returns an XML document like this:

<listitems s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" rs="urn:schemas-microsoft-com:rowset" z="#RowsetSchema" xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <rs:data itemcount="4"> <z:row ows_id="1" ows_title="Interesting task" ows_modified="2004-02-25 00:14:40" ows_created="2004-02-25 00:14:40" ows_author="36;#Teun Duynstee" ows_editor="36;#Teun Duynstee" ows_owshiddenversion="1" ows_attachments="0" ows__moderationstatus="0" ows_status="Not Started" ows_assignedto="36;#Teun Duynstee"> <z:row> </rs:data> </listitems>

Note that all fields in the list have been converted to attributes by prefixing their names with "ows_". We loop through all z:row elements and for each of the columns in our DataTable, we check if a matching attribute on the row is available. We find the name of the attribute by combining the fixed prefix with the Name we stored for each column in a hashtable. The string returned from the GetAttribute method can be stored in the DataTable without converting or casting, because DataTable internally always performs a Convert on the data entered.

For readability of the reports, we do change the values of fields of type Choice and of references to other SharePoint lists. For these fields the value returned by the web method is of the form "36;#Teun Duynstee". It featurs first the numeric ID, followed by ; and # and then the human readable presentation. In reports, we are rarely interested in ID's at all, so we use a regular expression to strip ID and separator from the value before placing it in the DataTable. The DataTable with content is then passed to a DataReader which is then returned as the result of ExecuteReader.

DataReader

The DataReader is a rather simple class. It is not much more than a wrapper around the DataTable created and filled by the Command object. Internally, an Enumerator is instatiated and used by the CurrentRow property to return the current row.

IEnumerator TableEnum = null; public bool Read() { if(TableEnum == null) { TableEnum = _table.Rows.GetEnumerator(); } return TableEnum.MoveNext(); } protected DataRow CurrentRow { get { return (DataRow)TableEnum.Current; } }

This CurrentRow property is then used by all methods defined in the IDataReader interface, like GetFieldType, GetValue, etcetera.

public int FieldCount { get { return _table.Columns.Count; } } public string GetName(int i) { return _table.Columns[i].ColumnName; } public Type GetFieldType(int i) { // Return the actual Type class for the data type. return _table.Columns[i].DataType; } public Object GetValue(int i) { return CurrentRow[i]; } public int GetOrdinal(string name) { foreach(DataColumn col in _table.Columns) { if(col.ColumnName == name) { return col.Ordinal; } } throw new IndexOutOfRangeException("There is no field with the name " + name); }

Deploying the solution

Once the plugins are built, they can easily be registered by adding a reference in a config file. This must happen in two separate places: one for the report designer on the machine where reports will be build and one for the service on the machine that will eventually produce the reports. The dll file we built must also be copied to two different directories. The steps for deployment are (the directory paths assume a default install):

• Copy the dll to the folders C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer en C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.
• Open (with Notepad) the config files C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config and C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\RSReportServer.config. Both contain an XML block specifying data extensions. It will already contain references for SQL, OLEDB, ORACLE en ODBC. We add our extension to this list. The resulting XML should look like this: <extensions> <Data> <Extension name="SQL" type="Microsoft.ReportingServices... "/> <Extension name="OLEDB" type="Microsoft.ReportingServices..."/> <Extension name="ORACLE" type="Microsoft.ReportingServices... "/> <Extension name="ODBC" type="Microsoft.ReportingServices..."/> <Extension name="SPSLIST" type="Macaw.ReportingServices.SharepointListExtension.Connection, Macaw.ReportingServices.SharepointListExtension"/> </Data> </extensions>

On a machine that does not have Visual Studio.NET installed, the report designer is of course not available.

Results

Now that we installed the extension, we will try to create a report based upon some information in a team site. In this case, we will take the Tasks list, which is available by default in any project team site. We create a new project in Visual Studio.NET 2003 using the Report Project Wizard.

Image 2

In the wizard, we see the new extension appear right away: Image 2 displays the wizard step that configures the data source. In the list of data source types, the option "SharePoint Lists" has appeared (this is the value returned by Connection.LocalizedName). Our connection string contains only one element: the team site URL. Using the Credentials button we can set the account that should be used to retrieve the data from WSS. In the next wizard step, the Command text can be entered. We enter "Tasks", the name of the list we want to query. Next we will see the screen in Image 3.

Image 3

In the list of available fields, we see all of the Fields the Task list features. There are actually more fields than you would normally see in SharePoint. The list also contains the invisible and calculated fields. If you want to, you can use these in your reports as well. Then, we just create a simple report by selecting the fields and adding them to the report and we can easily create a report as in Image 4.

Image 4

Conclusion

As both Windows SharePoint Services and MS Reporting Services have been designed with extensibility in mind, it is not very hard to connect the two into one solution. Web parts are available to show reports from Reporting Services inside WSS sites. Using the technique shown in this article, you cn present the information available in your WSS sites through Reporting Services. As organisations increasingly organize some of their primary processes through WSS team sites, the importance of reporting over the progress of these processes will only grow.

Postscripts

Some readers have sent me questions. I have encountered limitations myself. I have (and will in the future) written about these on my blog. These are the most relevant posts:

How to filter the results from WSS in your report Setting CAS to use our Reporting Services data extension

Labels: reporting services