Filter SSRS Report on Project Site

[Populate SSRS Report Parameters via JavaScript]

 

Requirement

Display a Project specific data on a Project Site using an SSRS Report.

 

Solution

SSRS Report can be displayed on a SharePoint web part page using Report Viewer Control. The Challenge was to populate the parameter with Project Name so that only that Project specific data gets loaded in SSRS Report.

 

Our familiar hero, JavaScript came to the rescue again, though it took some help from Text Filter Web Part this time. Following are the steps

 

1) Prepare your SSRS report with a parameter which accepts Project Workspace URL as Parameter value and loads Project Data accordingly

 

2) Display this report using Report Viewer web part (available in SQL Server Reporting category) on your desired SharePoint web part page in Project Site.

 

3) Add a “Text Filter” web part (available in Filters category) to your page ,

 

4) Connect Text filter web part with Report viewer web part so that the value of text filter web part gets passed as a parameter

 

5) To test your report, type the Project Site URL (e.g. http://pwaurl/pwa/siteUrl ) in the Text Filter web part text box and see if the report loads correctly

 

6) Now to automatically populate Text Filter web part, you need to find text filter control ID (inspect element is your friend) and a bit of JavaScript magic.

 

7) The JavaScript code (provided at the end of this blog) injected via Hidden Content Editor web part will determine Project Site URL, Populate Text Filter control, and enforce the post back event for the Text Filter control so that Report gets refreshed according the the passed parameter value.

 

8) Once you are happy with the results, you can hide the Text Filter control by minimizing it and setting the Chrome Type to none.

 

Script

 

Note: Use inspect element to find your text filter control ID and parameter id for post back function (slightly different from control ID) and replace at places highlighted as bold

 

<script>
    function submitSiteURL()
    {

 

//  find the control by ID

        var ttnA = document.getElementById('ctl00_ctl40_g_b619b608_692c_47f9_ad64_39b27e905477_SPTextSlicerValueTextControl');

 

// Find the Project Site URL

        var url = window.location.protocol + "//" + window.location.host + _spPageContextInfo.webServerRelativeUrl;

 

// If the Control is not already populated (to avoid infinite refresh loop)

        if (ttnA.value != url)
        {
                ttnA.value=url;

// DO Post back ID parameter should be updated (it is slightly different from Control ID) 

        __doPostBack('ctl00$ctl40$g_b619b608_692c_47f9_ad64_39b27e905477$SPTextSlicerValueTextControl','');
        }

    }

    _spBodyOnLoadFunctionNames.push("submitSiteURL");

    </script>