Reporting over WSS lists
These are posts referring to the article on reporting over WSS lists from MS Reporting Services
Notice: a newer version of this web part has been published. Read and download here.
I promised a month ago that I would post a web part for charting WSS data into a Gantt chart. Well, here it is. It looks somewhat like this:
The part allows you to specify a list and view to render. You select the fields from the view that will indicate the start and end date of an item. The third required field is the title field. Because the part works with view fields, you can also use calculated fields to specify the start date, end date or title of the item.
Because I was too lazy to create a custom designer, you have to enter the names of the list, view and fields in a textbox. When you specify incorrect values, the part will show you the available options in it's error message.
So you want to use it? I put up a zip file containing both the sources and binaries. I haven't made an installer, but I suppose that most of you know what to do with a dll and dwp file. In your web.config you'll have to add a line for trusting the part like this:
‹SafeControl Assembly="Duynstee.GanttChart.WebPart, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9a66e9ace203d5f7" Namespace="Duynstee.GanttChart.WebPart" TypeName="*" Safe="True" />
The web part is actually more of a sample of using the underlying GanttCharting library. This will render VML that can be directly included into the HTML (IE only!). The charting library has some features that are not used by the web part, but that you may want to build on:
- aggregation: each GanttItem can have a collection of sub-items. Items with sub-items will be rendered accordingly (see sample in this post)
- links: each item has a LinkUrl property that will be placed on the item. You could have the item link to the edit page of the item
- color: you can color each item as you wish
Download here
I received a question from a reader asking how you can set a filter on the items returned from a list. Suppose you want to show only the Defects with status Active or Issues with Impact > 3? We cannot set the where clause, as we don't have a SQL statement. The data extension presented in the article always returns all rows.
Luckily, the Report Designer offers features to sort and filter the data returned before inserting it into the report. This is how to do it:
1. In Report Designer (in VS.NET) open your report on the Data tab. Select the ... button next to the list of datasets:
2. This will open the DataSet dialog. On the Filter tab, you can build expressions to filter the data. You can enter fixed values, but the expression builder will also help you to use global settings and passed parameters from the user in your filter.
This dialog allows you to do many more things to your data, like adding calculated columns. Check here.
This post refers to my article on reporting with Reporting Services over WSS Lists.
The extension presented in the article works fine in the Report Designer in Visual Studio, but it turns out that deploying it to the server requires some extra work. A problem you will encounter is that when you try to access your report through the Reporting Services website, it will give you an error like this:
Reporting Services Error
- An error has occurred during report processing. (rsProcessingAborted) Get Online Help
- Query execution failed for data set 'dataset_name'. (rsErrorExecutingCommand) Get Online Help
- Request for the permission of type System.Net.WebPermission, System, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
The problem here is that the Reporting Services engine does not trust our extension code. For more on Code Access Security in .NET, check out this MSDN article. We can tell it to trust our dll by editing the file rssrvpolicy.config that you will find in the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer directory. It contains XML defining a number of CodeGroups and their corresponding trust levels. We just add this code snippet at the end of the list of CodeGroups:
Note that we specifiy our exact dll to be trusted. For new extensions you may add, you'll have to add these to the list of CodeGroups as well.
The FullTrust level is rather crude of course. You could define another named permission set that has exactly the required permissions for this extension (network access).
Also: I chose to specify which code to trust using the file location (UrlMembershipCondition). Some people say you'd better sign your assembly with a strong name and trust the strong name (StrongNameMembershipCondition). I am not a very big fan of strong naming and I would suggest resorting to this way only if you really need it. After all: people who can replace your dll in the bin directory can probably also tamper with the rssrvpolicy.config file. More on what's wrong with strong naming.
I haven't got a clue what's taking Microsoft so long putting up the sample code for
my article on Reporting over SharePoint lists. For the time being, you can download it from my site.
Check it out.
The Microsoft site still doesn't offer the Dutch version online, but as the hard copy magazine has already been sent out, I suppose they do not mind me putting the English translation online now.
I just received the latest issue of .NET Magazine (which is a developer publication from Microsoft in Dutch). It contains an article by yours truly entitled "Rapporteren over SharePoint-lijsten met een Reporting Services Extension" (Reporting over SharePoint lists with MS Reporting Services). I am currently translating the article in English and will publish it through this blog soon.
A link to the electronic version and code download will follow shortly. I will also respond to any questions and remarks on the article through this blog.
UPDATE: Here it is