In an organization, there are multiple projects and for each project there are multiple reports generated. For effective tracking and monitoring, various out of the box reports are available in the Project Server. Quite often it happens that project managers and team members demand for report view that corresponds only to their projects and not the entire organization.

To meet this requirement, here is a solution that can filter out specific project data from the excel report, so that the user does not have to go through the complete list of projects to find data related to his project on the Project Site.

Below is the excel report showing the count of completed tasks for all projects.

Now to filter out the specific list, below are the steps to be followed:

  1. Add Slicer to the excel report.
  2. Add a web part to add the excel report.
  3. Add Content Editor web part to refer to the JavaScript file that will filter out specific project in the excel reports.

Note: Pre-Requisite: SQL Server analysis services in Power Pivot mode must be installed.

 Detailed steps:

    1. Add Slicer to the Excel Report :
      1. Open the excel report in edit mode in excel from the SharePoint document libraryBusiness Intelligence.
      2. Select the Pivot Table. Under the ANALYSE tab, select Insert Slicer. On the Slicer window, select the project name (which will be used for filtering).

      3. Add Slicer as a parameter: Select Info of the excel -> select Browser View Options->a pop up window will appear -> Select Parameter tab -> Add the desired slicer so that it can be used as filter parameter in the web browser.
      4. Save and upload the excel report.
  1. Add a web part to add the excel report.  
      1. Navigate to the project site page where we need to add the excel reports (For example – Project Q 003).
      2. Open the page in edit mode
        .
      3. Click on Add a Web Part

      4. A selection window will open. From the Business Data category select Excel Web Access web part and click Add.
      5. The web part will be added.


    1. Click on link Click here to open the tool pane to edit the web part and set properties
    2. We need to fill out the workbook field with the URL of the workbook. Browse to the location of the excel report and add it.
    3. Add Content Editor web part to refer to the JavaScript file that will filter out specific project in the excel reports.
      1. Upload the JQuery file named “jquery-1.11.2.min.js” into a SharePoint shared document library present on the root site.
      2. Upload the Java script file named “QueryFilterData.txt” into a SharePoint shared document library present on the root site.
      3. Before uploading the custom  QueryFilterData.txt file, we need to update the Project Server URL. In this case – “http://tfspsdemo2013/PWA” is our development machine URL.
      4. Now add the content editor web part to refer to the JavaScript file. Click on Add a Web Part.
      5. A selection window will open. From Media and Content category, select Content Editor web part and add it.
      6. Edit the web part and enter the path of the JavaScript file (Name: QueryFilterData) added in Step 2 and click on Test Link. If the file opens in another tab then the link is correct. Click Apply and then click OK.

      7. Once all the steps are done, click Stop Editing and there you go! You will see the report data showing only filtered specific project data.

You may also want to refer to the below Java script:

Java Script Code:

 

To find the project name:

function getProjectNameCallback() {

projectUID = spWebProps.get_fieldValues()[“MSPWAPROJUID”];

projContext = PS.ProjectContext.get_current();

projects = projContext.get_projects();

projContext.load(projects, ‘Include(Name, Id)’);

projContext.executeQueryAsync(

iterateThroughProjects,

errorCallbac);

}

function iterateThroughProjects(response) {

var enumerator = projects.getEnumerator();

while (enumerator.moveNext()) {

var project = enumerator.get_current();

var id = project.get_id();

if (id == projectUID) {

projectdetail = project.get_name();

if (typeof(projectdetail) != undefined) {

// alert(projectdetail);

PageLoad();   }

break;

}   }  };

Set Project Name to the Slicer:

Each EwaControl is associated with only one workbook. The EwaControl.getActiveWorkbook method returns the workbook associated with the specified EwaControl object. (Link)

ewaInstance =Ewa.EwaControl.getInstances().getItem(index); (In Case there is single excel report then use Index as 0, in case of multi excel report we need to iterate through it).

var objCollection = {}; (Array)

objCollection[‘Slicer_ProjectName’] = [projectdetail];  (Slicer_ProjectName is the Excel Slicer Name)

ewaInstance.getActiveWorkbook().setParametersAsync(objCollection, SetParamsAsyncCallback, null);

(This will set the Slicer parameter on the report).

 

Posted by Advaiya

    Let's connect to make technology work for you.





    Please tick the options most relevant to your business challenges
    Decision makingBusiness productivityCustomer experienceTechnology-led innovationDigital transformation


    [By using this form you agree with the storage and handling of your data by this website.]