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, 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=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 (16)
  1. Dan Hillman says:

    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?

  2. Cliff says:

    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.

  3. Dan Hillman says:

    Hi Cliff,

    I probably misstated my question.  In my site definition, the provisioning code knows the url for the new site, so I know the url for the report rdl files.  The ReportViewerWebPart has a ReportPath property that I must update in the site provisioning code to reflect the path for the site being created but I haven’t found any way to get at that property and change it so I was looking for suggestions on how I might do that.  If I try to use the SPLimitedWebPartManager, it only sees the ReportViewerWebPart as an ErrorWebPart.  My hack right now is to update the onet.xml for the site definition before my provisioning code calls it.

  4. Cliff says:

    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

  5. Dan Hillman says:

    I’m using the SPWebProvisioningProvider suggested by Connell so that I can get the site constructed before doing any custom code. The basic code outline is

    public override void Provision(SPWebProvisioningProperties props)

    {

    Microsoft.Office.Server.Diagnostics.PortalLog.LogString(”Beginning CPDCMSiteDef provision code.”);

    SPWeb elevWeb = null;

    try

    {

    SPSecurity.RunWithElevatedPrivileges(delegate()

    {

    // Apply the actual Web template for the CPD CM site

    // get elevated web – sometimes sharepoint doesn’t like to do stuff inside the elevpriv block

    elevWeb = props.Web;

    elevWeb.ApplyWebTemplate(CPDCMSiteDef#0);

    });

    elevWeb.AllowUnsafeUpdates = true;

    elevWeb.Site.AllowUnsafeUpdates = true;

    … all of the site pieces are in the onet.xml of the CPDCMSiteDef#0 template. After the site has been created above, I run the custom code, which is where I have tried to use the SPLimitedWebPartManager to get at the ReportViewerWebPart. ListViewWebParts on the same page are accessible but the ReportViewerWebPart doesn’t seem to be.  Right now I’m experimenting with an awkward hack.  The page in question is provisioned in a module of the onet.xml. Before I call the code to create the site, I update the ReportPath property in the onet.xml file.  I’m probably breaking a million rules but it seems to work.

  6. Cliff says:

    Dan,

    I am not sure why the web part is reacting that way.  If I get a chance to investigate I’ll report back.

  7. Dan says:

    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

  8. Cliff says:

    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/920769

    In 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()]

  9. dchillman says:

    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?

  10. Cliff says:

    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.

  11. dchillman says:

    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.  Thanks

    using 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;

           }

       }

    }

  12. dchillman says:

    I’ve been banging my head against trying to use the Globals!ReportFolder in a call to a funtion in an external library.  In my simple test report I am using a expression

    =MyFunctions.GetRelativeUrl(Globals!ReportFolder)

    I get an error that implies the Globals!ReportFolder is empty.  Another text box with an expression

    =Globals!ReportFolder does show the correct value for the ReportFolder. If I hard code a value in the function call, the correct value is returned.  Is there a trick to using the Globals!ReportFolder in a function call?  thanks

  13. Cliff says:

    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

  14. 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.

  15. You are correct, report folder will give you this information.

Comments are closed.

Skip to main content