ThoughtFactory.CreateThought()

One of the features of Access Services, SharePoint 2010’s new “host Access databases on the web” service, involves the promotion of Access reports to SQL Server Reporting Services (SSRS) reports. Essentially the publishing feature of Access Services takes an Access report and generates an RDL file from it, allowing the SSRS Web control to display it inside SharePoint.

This feature requires that you have an SharePoint Integrated mode instance of Reporting Services and have configured Reporting Services Integration in SharePoint Central Administration. Assuming that you’ve done all this, you may expect that your reports just show up. Unfortunately this is probably not going to be the case, and you may find yourself being faced with an error similar to the following:

An attempt has been made to use a data extension ‘ADS’ that is either not registered for this report server or is not supported in this edition of Reporting Services

The following except from a TechNet article gives the solution:

Modify the C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config file on the Reporting Services server. Remove the comments from the ADS data extension. For example:

<!-- <Extension Name="ADS" Type="Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"/> -->TO

<Extension Name="ADS" Type="Microsoft.Office.Access.Reports.DataProcessing.AdsConnection, Microsoft.Office.Access.Server.DataServer, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=71e9bce111e9429c"/> -->

This allows Reporting Services to construct a data set out of a particular data source. You can find more information on Reporting Services Extensions here, and more specifically Data Processing Extensions here.

The interesting thing about all this is that Access Services actually promotes all of your Access tables to SharePoint lists, making me wonder why the generated SSRS reports need to know about Access at all. MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection already provides an implementation that talks to SharePoint lists, so why not use that? I figured that I’d do a little digging into the SharePoint site generated by Access Services to find out just why they needed their own Data Extension.

If you navigate to the _layouts/viewlsts.aspx page (type it into your address bar directly, so http://mysite/Applications/MyApplication/_layouts/viewlsts.aspx) of your Access Services application you should be able to explore the Report library and associated lists that make up your application. Inside the Report library you’ll notice that there is an "”AccessDataSource”, who’s connection string contains both the SiteId and WebId of your application, and whose Data Source Type is “ADS”. This tells us that, at the very least, the report knows that it’s communicating with a particular SharePoint Site.

If you download a copy of one of the report RDL files and check it out in a text or XML editor you’ll notice something interesting; inside the DataSet element there is a Query definition that looks something like the following:

<Query>
<DataSourceName>ADS</DataSourceName>
<CommandText>_sq_rContactList</CommandText>
<QueryParameters>
<QueryParameter Name="AccSrv_Where">
<Value>=Parameters!AccSrv_Where.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>

The important thing to note here is the CommandText element. Access Services stores most of your Access objects, including queries, as serialised text inside list items in a list called MSysASO. If you inspect that list you’ll find a list item matching up with the CommandText from your DataSet Query inside your RDL file.

If you look at the ServerObject field of this list item you’ll see something that initially looks somewhat like a CAML query but is actually a query from the Access Services namespace, http://schemas.microsoft.com/office/accessservices/2009/04/application.

Here’s an example of one of these queries:

<Query xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application" Name="_sq_rContactList"><References> <Reference Source="_sq_rContactList_Base" /></References><Results><Property Source="_sq_rContactList_Base" Name="Address" /><Property Source="_sq_rContactList_Base" Name="BusinessPhone" /><Property Source="_sq_rContactList_Base" Name="City" /><Property Source="_sq_rContactList_Base" Name="Company" /><Property Source="_sq_rContactList_Base" Name="ContactName" /><Property Source="_sq_rContactList_Base" Name="CountryRegion" /><Property Source="_sq_rContactList_Base" Name="EmailAddress" /><Property Source="_sq_rContactList_Base" Name="FaxNumber" /><Property Source="_sq_rContactList_Base" Name="HomePhone" /><Property Source="_sq_rContactList_Base" Name="JobTitle" /><Property Source="_sq_rContactList_Base" Name="LastName" /><Property Source="_sq_rContactList_Base" Name="MobilePhone" /><Property Source="_sq_rContactList_Base" Name="StateProvince" /><Property Source="_sq_rContactList_Base" Name="ZIPPostal" /></Results></Query>

At first glance this doesn’t tell us a whole lot; it just seems to be a collection of field names that we would want to fetch in our query, but it doesn’t say anything about where the data is coming from or whether there are any restrictions on what data we should bring back. You will notice, however, that it seems to reference another item in the MSysASO list through the Source attribute of the Property element. In this it is referencing the _sq_rContactList_Base list item, which looks like the following:

<Query xmlns="http://schemas.microsoft.com/office/accessservices/2009/04/application"> <References> <Reference Source="Contacts"/></References><Results><Property Source="Contacts" Name="Company"/><Property Source="Contacts" Name="ID"/><Property Source="Contacts" Name="LastName"/><Property Source="Contacts" Name="FirstName"/><Property Source="Contacts" Name="EmailAddress"/><Property Source="Contacts" Name="JobTitle"/><Property Source="Contacts" Name="BusinessPhone"/><Property Source="Contacts" Name="HomePhone"/><Property Source="Contacts" Name="MobilePhone"/><Property Source="Contacts" Name="FaxNumber"/><Property Source="Contacts" Name="Address"/><Property Source="Contacts" Name="City"/><Property Source="Contacts" Name="StateProvince"/><Property Source="Contacts" Name="ZIPPostal"/><Property Source="Contacts" Name="CountryRegion"/><Property Source="Contacts" Name="WebPage"/><Property Source="Contacts" Name="Notes"/><Property Source="Contacts" Name="_OldID"/><Property Source="Contacts" Name="ContactName"/><Property Source="Contacts" Name="FileAs"/><Property Source="Contacts" Name="Attachments"/><Property Source="Contacts" Name="SharePointEditor"/><Property Source="Contacts" Name="SharePointAuthor"/><Property Source="Contacts" Name="SharePointModifiedDate"/><Property Source="Contacts" Name="SharePointCreatedDate"/></Results><Restriction>=(((Contacts.Company)&lt;&gt;"Local Bank"))</Restriction></Query>

This second query contains the meat and potatoes of the query itself, including any query restrictions (which you can see in my example where I have restricted the data where Contacts.Company <> “Local Bank”) and the source tables and fields where the data is coming from.

As such, each query is actually made of of two serialised objects stored in two separate list items inside the MSysASO list;  _sq_r%ReportName% and _sq_r%ReportName%_Base. With these two objects, when combined with the ADS Data Extension and the SiteId/WebId referenced in the Reporting Services Data Source object previously discussed, Reporting Services now has enough information to fetch the appropriate data for the report.

So it is reasonable to infer from all this that the reason Microsoft don’t use the MIcrosoft.ReportingServices.DataExtensions.SharePointList.SPListConnection Data Extension is because they needed to be able to perform lookups into their shared resources list (MSysASO) to fetch the real query that they want to run, hence needing a custom Data Extension to do so. The use of this central repository makes total sense, especially when combined with Access’ shared query model where multiple reports could be referencing the same query. If they did not do this and simply relied upon the SharePoint List Data Extension then they would essentially have to generate CAML queries for each report, repeating them inside each RDL file as needed.