8 Steps to Filter Your SSRS Report on a Project Site

Posted By Posted by: EPM Partners on July 24, 2014

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

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

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

 

1) Prepare your SSRS report with a parameter which accepts Project Workspace URL as a 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 your 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 the Text Filter web part, you will need to find the text filter control ID (inspect element is your friend here).

7) The JavaScript code (provided at the end of this blog) injected via the Hidden Content Editor web part will determine the Project Site URL, Populate Text Filter control, and enforce the post back event for the Text Filter control so that the 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>

See more at: http://epmadvice.blogspot.com.au/2014/06/filter-ssrs-report-on-project-site.html#sthash.2bx4OziT.dpuf

 

 

 


Blog Posted In 

Leave a Reply

Your email address will not be published. Required fields are marked *