With SQL Reporting Services integration with SharePoint there may be a need to be contextually aware of the site or list where your report resides. With the ability to add code or reference custom assemblies in our report we have a starting point to gather this information and leverage information that may be available to us in a site. The first thing I thought of when thinking through this issue was that I could leverage the SPContext object. This would provide the ability to get the current site Url and reference information that way. Unfortunately, the reference to SPContext within the report is null because it is being rendered in an IFRAME. So we can't use that mechanism to do that. So the next idea was to get a reference to HttpContext and leverage that in order to find out where we are in SharePoint. When a report runs from a SharePoint document library it runs within the RSViewerPage.aspx page. The actual Url request in my environment is, http://cliffgre-mossvm/SiteDirectory/reports/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/SiteDirectory/reports/RDL/TestReport.rdl&Source=http%3A%2F%2Fcliffgre%2Dmossvm%2FSiteDirectory%2Freports%2FRDL%2FForms%2FAllItems%2Easpx&DefaultItemOpen=0. The report, however, is run using a pointer to the Report Server. In my environment the Web Application lives in http://cliffgre-mossvm. When I request a report it runs in a reference to http://cliffgre-mossvm:8000/ReportServer, so it runs completely out of the SharePoint environment. The Url request in the report looks something like this:
http://cliffgre-mossvm:8000/ReportServer/Reserved.ReportServer?http://cliffgre-mossvm/SiteDirectory/reports/RDL/TestReport.rdl &rs:SessionID=l1ahe1yas2icsrjuiar0na55&rs:command=Render&rs:Format=HTML4.0&rc:HTMLFragment=true&rc:Section=1 &rc:StreamRoot=/SiteDirectory/reports/Reserved.ReportViewerWebPart.axd?ReportSession=l1ahe1yas2icsrjuiar0na55& ControlID=05e0aa50c74646f3858bf6847b08f5f2&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportImage&StreamID= &rc:ResourceStreamRoot=/SiteDirectory/reports/Reserved.ReportViewerWebPart.axd?ReportSession=l1ahe1yas2icsrjuiar0na55 &ControlID=05e0aa50c74646f3858bf6847b08f5f2&Culture=1033&UICulture=1033&ReportStack=1&OpType=ReportImage&ResourceStreamID= &rc:ActionScript=ClientReport05e0aa50c74646f3858bf6847b08f5f2.ActionHandler&rc:StyleStream=true&rc:LinkTarget=_top &rc:UserAgent=Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.2;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30;+InfoPath.2;+.NET+CLR+3.0.04506.648;+.NET+CLR+3.5.21022) &rc:Toolbar=false&rs:ErrorResponseAsXml=true&rs:AllowNewSessions=false
One of the things that gets passed as part of the query string is the path to the report. Notice the first line above after the question mark (?). We can leverage that information and parse the Url in order to find out the site and list where the report lives. So now we have the Url to the report and we can leverage the SharePoint object model to get a reference to SPSite, SPWeb and down to SPList. Because we are running a custom assembly in reporting services it will have to be marked to AllowPartiallyTrustedCallers using the [assembly: AllowPartiallyTrustedCallers] attribute.
If only things were that simple. Once we add the code to our custom assembly and install it in the GAC we will get the error message below.
SecurityException: Request for the permission of type 'Microsoft.SharePoint.Security.SharePointPermission, Microsoft.SharePoint.Security, Version=126.96.36.199, Culture=neutral, PublicKeyToken=71e9bce111e9429c' failed.
In order to get around this problem we have to assert the required SharePoint permissions in order to have access to the SharePoint object model. By surrounding our code with an Assert() for SharePointPermission our code will run and allow us to navigate the SharePoint object model as we see fit. The full coding example is shown below. Although it only simply returns the .PortalName and .Url properties, it can be extended to meet your needs.
Now that we have done this we have a report that can leverage the SharePoint object model as needed. Of course this won't work within Visual Studio so the report has to be deployed to SharePoint in order for the code to run properly.