Security trim Project data in #ProjectOnline #PowerBI #Excel #PowerQuery reports part 2 #PPM #Odata #REST #BI

December 12, 2016 at 5:46 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

Further to my last post on an example method to security trim the Project data in a Power BI report for Microsoft’s Project Online PPM tool, another option is to merge the datasets / tables. If you didn’t see the first post, a link can be found below:

https://pwmather.wordpress.com/2016/12/09/security-trim-project-data-in-projectonline-powerbi-reports-ppm-odata-rest-bi/

For this post, I have the same two ODATA / REST queries used, the ODATA one for the Milestones (/_api/ProjectData/Tasks()?$Filter=TaskIsMilestone eq true) which is not security trimmed and the REST one for the Projects (/_api/ProjectServer/Projects) which is security trimmed. With both of these datasets loaded into the Power BI Query Editor, select the Milestone dataset and click Merge from the Home ribbon tab and set the following:

image

This is merging my two tables based on ProjectId from the Milestone dataset and Id from the Projects dataset using a Left Outer join. You can see from that screen shot that I don’t actually have access to the Agile Product Development project in PWA as it is not returned in the list of projects in the REST dataset. Clicking OK will show a NewColumn:

image

Click expand and select Name:

image

That returns nulls for the project names you do not have access to:

image

These rows should be filtered out, click the Name column heading dropdown and de-select null:

image

Click OK and the Milestone dataset table will match the list of projects you have access to in PWA – the rows with nulls in the Name column will be removed from the table. I then renamed the Milestones – ODATA dataset to Filtered Milestones:

image

Click Close & Apply then the load a couple of visualisations on to the report and you can see that now the data is security trimmed as part of the query and not in the report:

image

In this simple example there is no need for the report level filter to remove the nulls at the report level like there was in the previous post. The same applies as with the previous post, if your users know Power BI  / Power Query they can easily undo all of this and access all the data but this might be an option to explore.

Security trim Project data in #ProjectOnline #PowerBI reports #PPM #Odata #REST #BI

December 9, 2016 at 1:29 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: , , , , , , ,

The reporting API for Microsoft’s PPM solution – Project Online doesn’t filter the data based on the access model in PWA. For some organisations this can be problematic. In this blog post I show a simple report example to filter only projects and project milestones that I have access to in PWA.

In this example I have a very simple Power BI Report that is only using ODATA (_api/ProjectData), the Projects resource path to populate the Projects slicer / filter and the Tasks resource path to populate the Milestones for the selected Project:

image

I have access to a long list of projects (see the scroll bar on the slicer). Using the Audit Tracking project as an example, I can currently see this project in PWA as shown below:

image

I will change the access model so I get access denied to this project:

image

I only see these 6 projects projects in an unfiltered Project Center view:

image

Yet if I refresh my Power BI report I still see the full list as expected (the ODATA reporting API is not data security trimmed, you either access all data or none). One option is to use the REST (_api/ProjectServer) to filter the datasets that use the ODATA API as the source. The REST API is security trimmed to only show the data you have access to based on the access model in PWA. For example, I will get the list of Projects from REST to populate the Projects slicer / filter in Power BI, this only shows the projects I have access to in PWA. I have added a new data source to my simple Power BI report for the REST API:

image

The join is then set up on Id from the Projects REST dataset to the ProjectId in the Milestones OData dataset:

image

I now switch my slicer / filter to use the Projects – REST dataset

image

As you can see the list of projects matches what I see in the Project Center but there is a also a blank option there. The blank value is for rows it can’t join with on the Milestone ODATA dataset, these are the projects I no longer have permission to access in PWA! To resolve this, select the page and add a report level filter like below where Name is not blank:

image

This report filter will not show any blank values for the Name field on any visualisation in the report:

image

The blank value is now removed. My data is now security trimmed to match what I have access to in PWA! Obviously if you know what you are doing you can just undo all of this and see all data but for some it might be a workable solution! You can do the same with Resources too.

#Microsoft #Office365 Planner – #Task Management Tool #PMO #PMOT

September 23, 2015 at 8:49 am | Posted in Information | 1 Comment
Tags: , ,

A quick post to highlight a new tool that is going to be released soon called Office 365 Planner. This tool is great for organising team work with projects, tasks, assignments, documents in a visual way. For more details and screen shots see the link below:

https://blogs.office.com/2015/09/22/introducing-office-365-planner/

New Milestone Gantt #App for #ProjectOnline & #ProjectServer #PS2013 #Office365 #Project #PMO #BI

October 11, 2014 at 8:16 pm | Posted in Add-on, Administration, App, Customisation, Functionality, Information, Installation, Reporting | Leave a comment
Tags: , , , , , ,

CPS’ 4th SharePoint hosted app has been released into the App store. The app is called the “Milestone Gantt” app. The app can be downloaded here:

https://store.office.com/milestone-gantt-WA104379079.aspx

This app displays project milestones on a Gantt style chart, see the screen shots below:

image

image

image

image

The app has configuration options to enable the Project Web App administrators to configure the settings to meet the organisations requirements.

For more details see the user guide here.

For more CPS apps see the link below:

https://store.office.com/corporate-project-solutions-limited-PN104172071.aspx

CPS’ #Office #Project Task Auditor #App wins first place at #SPC14 App #Awards #SharePoint

March 4, 2014 at 1:15 pm | Posted in Add-on, Administration, App, Functionality, Information | 2 Comments
Tags: , , , , ,

The Task Auditor app from CPS won first place for the “Most Business Value Project” App at the SharePoint Conference 2014 in Las Vegas:

clip_image001

This is a free Project task pane app, go try it out and see what you think Smile

See the links below for details:

App store: http://office.microsoft.com/en-gb/store/task-auditor-WA104172076.aspx

Product site: http://www.projectserverplus.com/apps.html 

Blog post: https://pwmather.wordpress.com/2013/12/12/free-project-2013-app-from-cps-office365-office-apps-projectserver-projectonline/

#Microsoft Community Contributor Award 2011 – Thanks

August 31, 2011 at 2:57 pm | Posted in Personal | Leave a comment
Tags:

Just a quick post today, I am pleased to announce that yesterday I was awarded with the Microsoft Community Contributor Award 2011.

Blog at WordPress.com.
Entries and comments feeds.