SharePoint Report Viewer Page Hacks

• Download source - 104.33 KB

Introduction

This article introduces an alternate way to view Microsoft Reporting Services reports in SharePoint. It actually contains a series of hacks I have done to the native SharePoint report viewer page in order to accomplish several requests from my clients. This applies to SharePoint Server 2007 and SQL Reporting Services 2005 (in SharePoint integration mode of course). Here is a list of them:

1. It accepts URL parameters for the report parameters.
2. The report itself is passed as a URL parameter.
3. It uses default.master page.
4. Drillthrough reports are opened in a new window rather than the same browser window.
5. A new entry is added to the edit drop down menu for report items, to open the report in the new page.

Why?

You may wonder why we need this and why the native SharePoint report viewer page, RSViewerPage.aspx, is not enough.

I'll go through each of the new hacks/features:

1. Accept URL parameters for the report - This is useful whenever you want to create a link to a report that contains parameters. You may want this link to be in the quick launch navigation or maybe in a document. Without this hack, you will have to build a page specific to this report and add a bunch of URL string filter web parts, which then need to be connected to the report.
2. The report itself is passed as a parameter - This is to accomplish the same goal stated in the first hack. If we cannot pass the report as a parameter, we would have to build a page for it.
3. It uses default.master - This is to have the report shown in a decent page with left and top navigation without having to build a page for every report I want to create a link to. This is not really a big enhancement. You can actually create your own master page and use it.
4. Drillthrough reports are opened in a new window rather than in the same browser window - This is consistently and obsessively requested by my clients. If I implement at least two reports for them, they would ask me to have drill-through reports opened in a new window.
5. A new entry is added to the edit drop down menu for report items, to open the report in the new page - This is not really a hack. I simply added an option to see the report with the new page included in this project.

The basic requirement for this to work is to have the reporting services already installed and work properly with your SharePoint site. The reporting services need to be in integration mode.

I know most of you are eager to try it out rather than care about what witty things I did in the code behind. So here it is.

How To Install It

This installs as a SharePoint package solution. Here are the command lines:

stsadm -o addSolution -filename DataQ.SharePoint.RSViewer.wsp
stsadm -o deploySolution -name DataQ.SharePoint.RSViewer.wsp 
	-immediate -allowgacdeployment
stsadm -o execadmsvcjobs

stsadm -o activatefeature -name DataQ.RS -url http://yoursite

Replace yoursite with your site. You will find the WSP package in ../DeploymentFiles/ folder. Alternatively you can go to Central Admin - > Operations and upload the solution there. Once installed, you should have a new feature called DataQ: Reporting Services Viewer Page at site collection level:

How To Use It

The package installs a report viewer page: ~site/_layouts/DataQ.RS/RSViewer.aspx.  It is available for all web sites in the site collection.

This is accessible through the reports item drop down menu (Edit Control Button or RCB) as you can see in this screenshot:

The real benefit of having this page is to create links to reports with parameters in the URL. This is the syntax to address such a report:

http://yoursite/_layouts/DataQ.RS/RSViewer.aspx?
report=/reports/YourReport.rdl&p_Parameter=10

These are the rules:

• You must specify the report through the report parameter. It should be the absolute path to the report.
• You must pass each report parameter through a URL parameter that has a p_ prefix to the name of the report parameter. I added this p_ prefix because something must indicate that I am dealing with a report parameter. You will see in the code comments later.

That is about all you need to know to install and use it. The next part is for those who want to know what is in the code or wish to modify it.

What's Going On in the Code

RSViewer.aspx is actually a SharePoint application page that uses default.master and has implanted sqlviewer web part as a user control. The native reporting services viewer page does not really use a master page. And yes, you can use a web part as a user control.

The report can be passed through a URL parameter as a relative path to the RDL, or through a ListID, ItemID pair of parameters. Here is the code sequence:

if (!Page.IsPostBack)
{
    SPWeb web = SPContext.Current.Web;
    SPList list;
    SPListItem report = null;
    if (Request["ItemId"] != null && Request["ListId"] != null)
    {
        list = web.Lists[new Guid(Request["ListId"].ToString())];
        if (list != null)
        {
            report = list.Items.GetItemById(Convert.ToInt32(Request["ItemId"])); 
        }
    }
    else if (Request["report"] != null)
    {
        report = web.GetListItem(Request["report"].ToString());
    }
 
    if (report != null)
    {
        //set the window title
        LiteralPageTitle.Text = report.Title;

        //set the title in the page
        LabelPageTitleInTitleArea.Text = report.Title;
                 
        //set the path to the report
        m_sqlRsWebPart.ReportPath = 
	SPContext.Current.Site.MakeFullUrl(report.File.ServerRelativeUrl);
                
        ReportParameterDefaultCollection 
		parameters; // = new ReportParameterDefaultCollection();
        parameters = m_sqlRsWebPart.OverrideParameters;

        foreach (string urlParam in Request.QueryString.AllKeys)
        {
            //we have to prefix report params with something so we know 
            //they are report parameters, and they are 
            //destined to be passed to the report
            if (urlParam != null && urlParam.StartsWith("p_"))
            {
                string paramName = urlParam.Substring(2);
                string paramValue = Request[urlParam].ToString();
                Microsoft.Reporting.WebForms.ReportParameter p = 
	       new Microsoft.Reporting.WebForms.ReportParameter(paramName, paramValue);
                parameters.Add(p);
            }
        } //foreach (string urlParam in Request.QueryString.AllKeys)
    } //if (report != null)
} //if (!Page.IsPostBack)

First important thing is to set m_sqlRsWebPart.ReportPath correctly to the report document. The second is to overwrite m_sqlRsWebPart.OverrideParameters collection in case you pass any of them through the URL.  I choose to figure out which URL parameter is meant to be a report parameter by prefixing it with p_. An alternative way would be to query the reporting services web service for the available parameters of this report and see if they are present in the URL. I'm not doing this here. I want to keep it simple.

Another feature introduced by this application page is to open all drill-through reports in a new window. It is not a trivial change: it cannot be accomplished through HyperlinkTarget. The reason is all links to drill-through reports are actually POST calls. My solution is to use a JavaScript sequence that changes the target attribute of the FORM tag of the RSViewer.aspx page:

<script type="text/javascript" language="javascript">

    function ChangeTarget() {

        document.getElementById("aspnetForm").target = "_self";
        try {
            //this is first argument of function ControllerActionHandler
            //(actionType, actionParam)
            //if is Drillthrough then should make the target = _blank
            var c = arguments.callee.caller.caller.arguments[0];
            if (c == "Drillthrough") {
                    
                //needed for SP, see OWS.JS , definition of _spFormOnSubmitWrapper()
                _spSuppressFormOnSubmitWrapper = true;                    
                document.getElementById("aspnetForm").target = "_blank";
            }
        }
        catch (err) {
        };

        return _spFormOnSubmitWrapper();
    }

	/*
	...
	*/
	
    document.getElementById("aspnetForm").onsubmit = ChangeTarget;
    
	/*
	...
	*/
	
</script>

The trick here is to change the target of the form submission from _self to _blank, but only when the user clicks on a drillthrough type of link. I do this by examining arguments.callee.caller.caller.arguments[0], which must evaluate to Drillthrough (I know... long story, lots of trial and error). Finally, I rewrite the onsubmit body page event with my function, so it will be executed whenever user submits something such as when he clicks on a drillthrough. Am I repeating myself here? Please note, I still call the SharePoint original function _spFormOnSubmitWrapper() which is used for the onsubmit event, so I will not disturb any other native actions performed by SharePoint itself.

Conclusion

The conclusion is we can freely hack SharePoint if we cannot wait until the next version. I included in this article a few small solutions for a few small problems that I found with SQL Reporting Services report viewer page in SharePoint. I hope this will help you.

History

• September 22^nd, 2009: Published