Wednesday, April 19, 2006
Allowing blanks in the parameters of a report
How to allow blanks for a parameter
To allow blanks for a parameter, open the report in Visual Studio and go to Report|Report Parameters dialog. This is the place where you can select the option "Allow blank value". You might also choose to hide the parameter if you don't want to bother the report users with this feature.
Why adding this parameter anyway?
Sometimes you want to report over data in one and only one team site. Think of a central site that is used to track current job applications. There, it is fine to have the URL of the site as part of the connection string. But in some cases, you want to have one report, but you want to use it on data from several sites. These will typically be sites that are created from a common Site Template.In our organization, we create a standard teamsite to track information about the progress of projects. This teamsite includes numerous web parts that display a SSRS report using data from the current site. This means that these web parts will have to pass the URL of their source team site to the report server on the URL of the report. The most obvious way to do this is by having the extension automatically insert a parameter into the report to override the default site URL.
Labels: reporting services
Using the SharePoint Lists extension on Reporting Services 2005
So, let's start with something that has been requested a few times during my vacation: how about using the Reporting Services extension for SharePoint Lists on SSRS 2005? I received some reports from people that it didn't "just work", so I installed SSRS 2005 myself and gave it a try.
The bad news is that it seems to be impossible to use the old version of the extension on the new SSRS version. I don't know why, but I had to recompile the sources against the new framework and new interfaces library. Mind you: I did not change one line of code, just recompiled. The new binaries are available for download:
Old version for SQL2000 with source code
New version for SQL2005, only binaries (but the source is the same)
Installation instructions
In SQL2005, some of the configuration details have changed, so here follows a brand new installation manual for the 2005 version.Copying the binaries
First step is to copy the compiled binaries to the folders where the reporting server and report designer expect to find them. For the report designer (the functionality you use inside Visual Studio) this is the folder:C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\bin
Note the folder MSSQL.4: the 4 indicates the SqlServer instance that is used by reporting services. With SQL2005, you will often have multiple instances. Look for the one containing the Reporting Services folder.
For the reporting server, you copy the binaries to:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
This is a different location from the SQL2000 setup. In many development environments, you will have both the designer and the reporting server running on one machine. In these cases, you copy the binaries to both places.
Registering the extension
Now we have to let the designer and server know about the extension. For the designer, you edit the file:C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\RSReportDesigner.config
You have to add two lines to this file, one to the <Data> block and one to the <Designer> block. Here we show the result, but you have to add only the last lines of each block. For the server, you'll have to make and edit to the file
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\rsreportserver.config
In this file, only the <Data> block needs to be modified (indentical to the change described above). The resulting block looks like this (the last line has been added):
Trusting the extension
For using the extension from the report designer in Visual Studio, you are ready now, but to run the extension from deployed reports on the server, we need one more step: the extension needs to be trusted. This is done by adding an XML block in the file:C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\rssrvpolicy.config
With these setup steps, you should be ready to use SharePoint lists as the datasource for your reporting services reports.
Labels: reporting services
Thursday, March 02, 2006
Update: Reporting over Sharepoint lists from MS Reporting Services
About 6 months ago, I published an article about writing a reporting services data extension to query lists in Sharepoint sites. The article focussed mainly on "how to extend MS Reporting Services", but from the many responses I received, I reckon that many people are building production solutions around the sample presented in that article. As a matter of fact: I did so myself. I created a number of reports for the team sites we use in our projects at Macaw. Every now and then, I had the need for extra functionality in the extension itself.
I decided to publish this enhanced version to the public. A few warnings in advance: this is free software, it comes with source code, but I do not take any responsibility for the quality of the software oor for any damage caused by the use of it. This is still a sample and may not be fit for production environments.
First of all: the code and binaries of the Macaw.ReportingServices.SharepointListExtension can be downloaded here. Installation and configuration procedures are exactly identical to the former version of the extension.
Now on to the new functionality. All of the new features can be configured throught the query string box of the (the box that you would normally use to enter the SQL query of your report)..
Reporting on views
In the old version, the query string of the dataset could only specify a list name. Filtering and sorting would be specified by the default view of that list. In the new version, you can use the query string to specify an existing view for the list. The sorting and filtering of this view will be reflected in the dataset in the report. Specifying a view for your query is easy as this: Note that the different settings are separated by semicolons as you would normally see in a database connection string. In the previous version, the list would be identified by entering the list name as the query string (without the list= part). You can still do this for backward compatibility, but the "right" format is now list=ListName.If you specify no view at all, instead of getting the default view, you will now get the full table: all fields and all rows.
Stripping HTML from multiline fields
Many people use multi line texts in their lists. Even if you do not allow the addition of links and markup (bold/italic), SharePoint stores the content of these fields as HTML. It will contain <div> tags. In a normal Reporting Services report, these tags will show up visibly. With the new striphtml parameter, you can have the extension remove any HTML markup from the multi-line fields before returning the data for rendering the report.Charting changes in Issue lists over time
In Sharepoint, when you base your list on the Issues templates, the list will keep track of historic data. This allows you to see which changes have occurred to the list item and when (and by whom). When you want to create a report on the current state of the list, that's fine, but sometimes it is interesting to get insights in the history of the list, answering questions like "How did the number of active issues with severity high change over time?". To do this, I created a new parameter that you can set: When you do this, the resulting dataset get an extra field (GridDate). For each day, the dataset is filled with copies of all items that existed at that date (with the values they had on that date). This means that the dataset can grow very large if the changes span a long period. But it also allows you to create charts like this:
Just create an Area chart, use
The siteUrl parameter
You still specify the Url of the WSS site containing your data in the connection string of your datasource. However, if you want to use the same report for many sites (with identical lists and views), you may want to specify the site Url as a parameter. This also allows you to make a web part that shows a specific report querying the current site. The parameter will automatically show up when you use the data extension in your report. When you leave the parameter blank, the value from the datasource connection string will be used.Labels: reporting services
Saturday, September 10, 2005
Reporting over SharePoint lists with MS Reporting Services
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 |
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:
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.
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.
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:
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:
The GetDataTableFromWSS is the workhorse of the solution. The following code fragment shows the full source of this method.
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:
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:
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:
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.
This CurrentRow property is then used by all methods defined in the IDataReader interface, like GetFieldType, GetValue, etcetera.
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:
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 |
 |
| Image 3 |
 |
| 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 extensionLabels: reporting services
