Teun.ToString()

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: list=Issues; view=All Issues; 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. list=Risks; view=Top 10 Risks; striphtml=true

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: list=Defects; view=All Defects; grid=Daily 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 = Count(Fields!ID.Value) as the expression for the value, while using = Fields!GridDate.Value as the category group and = Fields!Status.Value or whatever you want to use to chart as the Series group. Valid values for the grid parameter are Daily, Weekly, Monthly and None.

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