SharePoint Reporting Services Headaches

Merry Christmas and Happy New Year!

A poorly documented area of SharePoint 2007 is the use of the SQL Server Reporting Services (in SharePoint Integrated mode of course) to create reports. I have been working with the Lists web service (find it at http://your_moss_server_name/[sites/][site_name /]_vti_bin/Lists.asmx) to create my reports. Here are my “Best SharePoint Reporting Services Headaches” you need to be aware of:

1. Issues with GetListItemsChanged (well… sort of)

The GetListItemsChanged method returns changes made to the list since a specified date and time. When you are defining your parameters for the query from the Visual Studio interface, it gives you an option to add an Expression to the value field. Sounds handy if you would like to do something like “Get me all the items that have changed in the last month” – you would need an expression like =DateAdd(“M”, -1, Today()) in the value of the ’since’ parameter so that your users don’t have to enter the correct date every time.

Unfortunately what they don’t tell you is that you cant use VB functions in dataset queries.  This goes for any query, not just ones used on the GetListItemsChanged method. The result: to achieve something like the aforementioned you would have to create a view in the list in SharePoint (bad news… see why in a minute) or enter the date manually each time (and yes, can’t put VB functions in Report Parameters either).

I also found that the GetListItemsChanged method returned extra rows with blank data in them. Equally annoying!

2. Issues with GetListItems

You can’t return the “last modified”/”modified by” dates. The only way to achieve this is to write a custom web service.

3. Queries can’t span more than one list

If you want to run a CAML query that gets data from more than one list then you better put your coding hat on. Once again, the only way to achieve this is to write a custom web service.

4. Annoying format of some data

Sometimes you will get data that is in the format “X;#some_data” where X is a number. This number is one of two things, dependant on the column it is in:

1. The UserID of the user who created the item. This would relate to the record with the same UserID in the User information List in the Site Collection you are calling your query from.
2. The ID of the item.

David Wise’s post on ShareConnecting SQL Reporting Services to a SharePoint List provides a bit of code that will help you get rid of this.

5. Cannot get data from a view using its display name

On just about every SharePoint Reporting Services blog entry I have read there is a mention on whether to pass a GUID or a Display Name for the listName parameter in the GetListItems method. Unfortunately you don’t get a choice when it comes to the GetListItems method viewName parameter – you have to enter a GUID. This presents a design issue given if find yourself in one of two scenarios:

1. You have multiple SharePoint environments (i.e. dev, test, pre-prod, prod) and you need to progress the report through the environments; or
2. You wish to deploy the report in several locations within your farm, running it off different lists created with the same list template.

If you fall in to one of these categories BE CAREFUL! The query’s viewName GUID will need to be changed before you run your report off anything other than what it was developed off, regardless whether the view is named the same. You can modify the .rdl file (the report file – really just an XML file) to get it to work without reopening Visual Studio:

1. Manually open the .rdl file with notepad or your favorite text editor
2. Find the dataset where you call the GetListItems method with the viewName parameter.
3. Modify the viewName query parameter so that it contains the GUID of the view on the different list. The quickest way to get the GUID is to navigate to the list and modify the view that you want to use in the query. You will find the GUID for the view in the URL of the Modify View page (don’t forget to change the HTML encoded characters, %7B = “{“, %2D = “-”, %7D = “}”).
4. Save the file.
5. Upload it to your SharePoint Report Library.

Have you had troubles with Reporting Services? I’m happy to help you out as it can be really frustrating stuff! Just leave a comment below and I will get straight on to it!

Next post I will highlight some issues with creating Reports using data from lists within a SharePoint Meeting Workspace.

Tags: Development, MOSS 2007, Reporting Services