SharePoint Reporting Services and SharePoint Meeting Workspaces

In a post last week I discussed some headaches I had experienced when using SharePoint Reporting Services, based on SQL Server Reporting Services, to create a report based on SharePoint lists. In this post I wanted to discuss how the goal posts move when you are working on a report derived from data in a SharePoint Meeting Workspace as there are some subtleties worth noting.

Meeting Workspaces are a fantastic solution to the problem of coodinating and running a meeting. Their power is amplified with their ability to link a series of meetings to one workspace (Microsoft provides a great video on how to do this at http://office.microsoft.com/en-us/sharepointtechnology/HA102769611033.aspx?pid=CH100649471033). If you have created a series of meetings within a workspace, items in a list are handled differently to how they are handled in “normal” sites. Items are related to a meeting instance through a MeetingInstanceID.

Although it may appear that the workspace contains a site to handle each meeting instance, it really just handles the display and hiding of data based on the MeetingInstanceID set in the current session (not in SPContext.Current as we will see later). So how does this affect reports? I wanted to create a Meeting Minutes report and a Meeting Agenda report based of information held in lists within a Meeting Workspace. Below is an account of the challenges I faced. I hope it can help someone save some hours and sanity!

GetListItems, Meeting Instances and Reporting Services

The web service you connect to in order to get items from a list for display in a report is the Lists service (Lists.asmx). The GetListItems method returns information about items in the list based on the specified query… but from which meeting instance? Although the method takes a parameter which can set the meeting instance you wish to grab data from, it will not work in reporting services. Why?

Let’s look at the signature for GetListItems:

public XmlNode GetListItems (
    string listName,
    string viewName,
    XmlNode query,
    XmlNode viewFields,
    string rowLimit,
    XmlNode queryOptions,
    string webID
)

The parameter we are interested in is the queryOptions parameter. It takes an XML fragment that contains separate nodes for the various properties of the SPQuery object, one of which is MeetingInstanceID. So, in a normal situation, you would add the following XML node to an SPQuery to get data from a particular meeting instance:

<QueryOptions>
   <MeetingInstanceId>[your_meeting_instance_id]</MeetingInstanceId>
</QueryOptions>

where

[your_meeting_instance_id]

is the meeting instance ID of the meeting you wish to obtain the data from. You can get the Meeting Instance Id from a special list contained in every Meeting Workspace called “Meeting Series” (Sanket Shah discusses this “Meeting Series” list in his blog post titled “Challenges while working with object model to access recurring meeting workspace site information”).

For a report created in Visual Studio using the Report Designer, you would assume that you could paste the above XML in a value field of a parameter named queryOptions in your Dataset that connects to your list in the Meeting Workspace, however, this WILL NOT WORK.

Using a tool to inspect the packets that are being sent by Reporting Services (such as Fiddler) you will see that the XML gets its HTML special characters encoded prior to transport, resulting in the following being sent inside of the queryOptions XML node (parameter):

>QueryOptions<
   >MeetingInstanceId<[your_meeting_instance_id]>/MeetingInstanceId<
>/QueryOptions<

How frustrating!!! I guess it is to stop Cross Site Scripting attacks that could happen if a Dataset parameter took a Report parameter that contained malformed user data… Can you think of any other reasons?

As a result, the default behavior of the service is to return data from the list from the meeting instance that is next in the series based on the current system date (i.e. will only get data from the upcoming meeting in the series).

This is great if you are only interested in creating, say, an Agenda report that uses data on the upcoming meeting to build up the report, but what about if you want to create a Minutes report, taking data from a meeting that happened in the past… too bad so sad! You will have to create a new web service that replicates the GetListItems method but takes a Meeting Instance ID parameter as a string. This is the solution I ended up implementing.

In addition to the custom Web Service, I wanted to eliminate the need for users to select which meeting instance they wished to build the report on (my current solution had a Meeting Instance report parameter that the users had to fiddle with to generate the right report). I felt it was reasonable for the user to expect that the reports would be generated based on the meeting instance they were currently browsing within the workspace.

The solution was centred around the fact that the Report Viewer Web Part exposes report parameters for filtering via web part connections. Using this as the basis for a solution I was going to do the following:

1. Get the SPMeeting.InstanceID from the current SPContext using SPContext.Current and put it in into a custom Filter Web Part
2. Bind my MeetingInstanceId report parameter to the MeetingInstanceId exposed in the Filter Web Part using a connection to the Report Viewer Web Part
3. Use the MeetingInstanceId report parameter as a parameter to send over to my custom web service.

The thinking was that the SPMeeting.InstanceID from the current SPContext using SPContext.Current would give me the MeetingInstanceId of the meeting that the user was currently browsing in the Meeting Workspace. Disappointingly, once again, SharePoint failed me….

The SPMeeting.InstanceID from the current SPContext (using SPContext.Current) only ever provides the MeetingInstanceID of the meeting instance that is next in the series based on the current system date (same behavior as the Lists.GetListItems method) and NOT what the user is browsing, which is what you would expect… right?! At least that’s what I expected!

As a result you have to have a report parameter that is manually set by the user prior to building the report that sets the meeting instance from which you want to pull the data from. I populate this parameter with ows_InstanceId values from the “Meeting Series” list within the Meeting Workspace, and label it with the ows_EventDate field from the same list for usability.

Hope this helps someone out as there are many hours of research and fiddling around to work this solution.

Tags: Design Tips, Meeting Workspaces, MOSS 2007, Reporting Services