Discover SharePoint Context within an Integrated SSRS report
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, https://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 https://cliffgre-mossvm. When I request a report it runs in a reference to https://cliffgre-mossvm:8000/ReportServer, so it runs completely out of the SharePoint environment. The Url request in the report looks something like this:
https://cliffgre-mossvm:8000/ReportServer/Reserved.ReportServer?https://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=12.0.0.0, 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.
1: public string GetInfo() {
2: string siteInfo = "";
3: try {
4:
5: string siteCollectionUrl = "";
6: string queryString = HttpContext.Current.Request.Url.ToString();
7: int indexStart = (queryString.IndexOf("?")+1);
8: int indexEnd = queryString.IndexOf( "&" );
9: string reportUrl = queryString.Substring( indexStart, (indexEnd-indexStart) );
10: siteCollectionUrl = reportUrl.Substring( 0, reportUrl.LastIndexOf("/") );
11:
12: SharePointPermission sharepointPerm = new SharePointPermission( PermissionState.Unrestricted );
13: sharepointPerm.Assert();
14:
15: using( SPSite siteCollection = new SPSite( siteCollectionUrl ) ) {
16: siteInfo = siteCollection.PortalName + ": "+ siteCollection.Url;
17: }
18:
19: sharepointPerm.Deny();
20:
21: } catch( Exception ex ) {
22: siteInfo = ex.Message + ex.StackTrace;
23: }
24: return siteInfo;
25: }
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.
Comments
- Anonymous
December 14, 2008
PingBack from http://stevepietrek.com/2008/12/14/links-12142008/ - Anonymous
May 12, 2009
In looking at your code, I have a related question, kind of. In using the Sharepoint ReportViewerWebPart it is easy to set the ReportPath in the Sharepoint User Interface, but I haven't been able to figure out how to set the ReportPath in code. I have a requirement to do this in a site definition solution I am developing. Can you point me in the right direction? - Anonymous
May 12, 2009
I am not sure what you starting point is in code, but can determine the report path by using SPContext, which gives you the context of the current site or site collection that you are in. From there you can iterate the lists or items in the list and use the SPListItem.RelativeUrl property (I think) to get the url of the item you are interested in. - Anonymous
May 12, 2009
The comment has been removed - Anonymous
May 12, 2009
Are you using a featurereceiver to do this work in site provisioning? It may be a timing issue as far as when the feature receiver is called. Does SPLimitedWebPartManager allow you to see the properties once the site has been provisioned? If so, you may want to look at a solution like the one linked below where your master page or default.aspx has a control on it that runs the first time someone comes to the site and sets the properties then.http://blogs.msdn.com/sharepoint/archive/2007/03/22/customizing-moss-2007-my-sites-within-the-enterprise.aspx - Anonymous
May 13, 2009
The comment has been removed - Anonymous
May 13, 2009
Dan,I am not sure why the web part is reacting that way. If I get a chance to investigate I'll report back. - Anonymous
June 03, 2009
I am now trying to follow your example to create a custom assembly that I can use within an RDL. In my case I have modified the code to return the ServerRelativeUrl, which I am using as the default value for a parameter in a RDL. when the RDL is loaded, it returns the error "Failed to load expression host assembly. Details: That assembly does not allow partially trusted callers". the assembly is in the GAC but I haven't done anything with the report server config file (it wasn't clear to me if I needed to when the assembly is in the GAC, or what to add to the config). Do you have any examples of how you've deployed and called a custom assembly in a RDL? thanks - Anonymous
June 03, 2009
After I posted this I realized that you can also use the Globals!ReportFolder expression to provide the location of the report in SharePoint.For using a custom assembly in RDL follow this guidance:http://support.microsoft.com/kb/920769In your case you may just need to open your AssemblyInfo.cs file in your project (located in the properties folder). Add a reference to using System.Security;at the bottom of the AssemblyInfo file add [assembly: AllowPartiallyTrustedCallers()] - Anonymous
June 04, 2009
thanks for the link - it was very helpful. The change to the AssemblyInfo.cs cleared up my security issue but it raised another. My test report attempts to use the custom assembly to get the default value for a parameter. When I deployed it to my sharepoint site and tried to test the report, it would never render. In debugging the queryString is "http://win2k3mossdev:1234/reportserver/ReportExecution2005.asmx" rather than the string with embedded rdl path in your example. I'm guessing that this is because my reporting services is running with "integrated" mode rather than "native" mode? I poked around in the HttpContext while debugging but didn't see anything with the actual report path. any ideas? - Anonymous
June 04, 2009
What is your assembly doing? What parameter value default is it trying to provide? Where did you get the HttpContext? The example shown in the blog post is running in integrated mode. I used HttpContext embedded in the report, if I remember, to inspect the url by adding a reference to System.Web.HttpContext in the report properties code section and having it output the current request information.The report path can be gathered by using the Globals!ReportFolder expression in the report whether in integrated or native mode. - Anonymous
June 04, 2009
I had just mimicked your code in its own dll, added it to the report references, then called it in an expression. Sounds like I don't have it quite right. I've just started looking at the Globals, which seems to make it even easier. Thanksusing System;using System.Collections.Generic;using System.Text;using System.Web;using Microsoft.SharePoint;using Microsoft.SharePoint.Security;using System.Security;namespace CPDCMSiteDef{ public class ReportFunctions { public static string GetRelativeSiteUrl() { string relativeSiteUrl = ""; try { string siteCollectionUrl = ""; string webUrl = ""; string queryString = HttpContext.Current.Request.Url.ToString(); int indexStart = (queryString.IndexOf("?") + 1); int indexEnd = queryString.IndexOf("&"); string reportUrl = queryString.Substring(indexStart, (indexEnd - indexStart)); siteCollectionUrl = reportUrl.Substring(0, reportUrl.LastIndexOf("/")); SharePointPermission sharePointPerm = new SharePointPermission(System.Security.Permissions.PermissionState.Unrestricted); sharePointPerm.Assert(); using (SPSite siteCollection = new SPSite(siteCollectionUrl)) { int i = siteCollection.Url.Length; int j = siteCollectionUrl.Length - i; webUrl = siteCollectionUrl.Substring(i + 1, j - 1); int x = webUrl.IndexOf("Project Reports"); webUrl = webUrl.Substring(0, x - 1); using (SPWeb myWeb = siteCollection.OpenWeb(webUrl)) { relativeSiteUrl = myWeb.ServerRelativeUrl; } } sharePointPerm.Deny(); } catch (Exception ex) { relativeSiteUrl = ex.Message + ex.StackTrace; } return relativeSiteUrl; } }} - Anonymous
June 05, 2009
The comment has been removed - Anonymous
June 07, 2009
When I put this example together I called an embedded expression and called my custom assembly from there.=Code.GetPath(Globals!ReportFolder)Function GetPath(path As String) As String Myassembly.GetPath(path)End Function - Anonymous
December 28, 2009
Rather than doing the string operations on querystring parameter, we can pass the report folder URL as an parameter to SharePoint method. and then we can use this url to form the SPWeb object as follows:SPSite Site = new SPSite("ReportFolderURL");SPWeb Web = Site.OpenWeb();Report folder Url can be obtained using Global parameters in report. - Anonymous
December 29, 2009
You are correct, report folder will give you this information.