#ProjectOnline #PowerBI content pack 2 available #BI #Office365 #PPM update

October 19, 2017 at 11:56 am | Posted in Add-on, Administration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , ,

Following on from my previous blog post regarding the updated Project Online Power BI content pack from Microsoft -  see below if you missed it:


That was shortly removed from the Power BI service and hasn’t been updated there yet but the template file has since been made available to download from GitHub:


You can now have the default reports provided here and extend to your own requirements for example a quick change would be to change the currency symbol used if you are not using US dollars:


Make the changes as required then publish to your own organisation.


#SharePoint item count from all lists on all sub webs in SharePoint / #ProjectOnline #PPM #PowerShell

September 28, 2017 at 11:29 am | Posted in Add-on, Administration, Customisation, Functionality, Information, PowerShell, Reporting | Leave a comment
Tags: , , , ,

This is a supporting blog post for an example PowerShell script I quickly wrote for Microsoft’s Office 365 SharePoint Online. It was created after a query was posted on the Project Online TechNet forums asking how to easily check what sub sites were being used in PWA.

The code sample can be downloaded from here: https://gallery.technet.microsoft.com/Get-item-count-from-all-026a6db2

To get the script to work, there will need to be some environment variables set and a DLL available, these are detailed below.

Update the environment details:


Add the SharePoint Online / PWA URL, username and password for an account that is a site collection admin on the target site collection.

To get the script to work you will need to reference the DLL as seen in the image below:


This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

Below you can see the output from the PowerShell ISE when running against my test SharePoint / Project Online site collection:


There are probably easier ways to view this information but I thought I would just try with a simple PowerShell script.

#ProjectOnline #PowerBI content pack 2 available #BI #Office365 #PPM

September 26, 2017 at 4:14 pm | Posted in Add-on, Administration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: , , , ,

2 years ago Microsoft released the first Project Online Power BI Content Pack, this week they have released another updated Project Online content pack! This is available now. For details on how to get the content pack see my original post below:


The steps are the same to get the new Project Online Content pack. This is version 2.3 as seen below:


Once the data is imported access the Report and Dashboard from the Power BI App > My Workspace navigation. I have set this up against our sales demo instance for Project Online. There are default reports for:

Portfolio Dashboard:image

Portfolio Timeline:image

Portfolio Costs:image

Portfolio Milestones:image

Portfolio Risks:image

Portfolio Issues:image

Resource Availability:image

Resource Overview:image

Resource Assignments:image

Resource Details – you will need to select a resource from the Resource Name filter:image

Resource Demand Forecast:image

Project Status – you will need to select a project from the Project filter:image

Project Risks & Issues – you will need to select a resource from the Project filter:image

Report Dashboard:image

Together with this content pack and the example report pack I built earlier this year, there are plenty of examples of reports to make Project Online reporting a simple task! A link to my report pack can be seen below:


Running #ProjectOnline #PowerShell in #Azure using #AzureFunctions #PPM #Cloud #Flow #LogicApp Part2

August 1, 2017 at 4:32 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, PowerShell, Workflow | Leave a comment
Tags: , , , , , , ,

Following on from part 1 where I introduced the idea of automating certain Microsoft 365 PPM Project Online customisations using PowerShell, Microsoft Flow / Azure Logic Apps and Azure Functions, in part 2 I will set up an example automation for creating a Project Online event driven snapshot application on project published without having to set up any server or write any complied code! If you missed part 1 where this concept was introduced, see the link below:


Firstly I created an Azure Function app in my Azure subscription then created a new function based on the HttpTrigger – PowerShell template:


Give the function a name and set the Authorisation level – set the authorisation level to the correct setting for your function. Click Create. For details on Azure Functions, start here: https://docs.microsoft.com/en-us/azure/azure-functions/

You will then be presented with the function and sample code:


We will now create the PowerShell script to create the snapshot. This is based on a script I published a while back: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/

The script has been updated to work in an Azure Function but also modified to use a parameter so that it only captures data for the published project, the PowerShell script can be seen further on in the post.

Firstly upload the SharePoint CSOM DLLs using the upload button:


I used the SharePoint CSOM DLLs from the SharePoint Online Management Shell:


Then enter the PowerShell code – screen shots below and code pasted below the images:




Code sample used in function:

# POST method: $req
$requestBody = Get-Content $req -Raw | ConvertFrom-Json
$projID = $requestBody.projID

# GET method: each querystring parameter is its own variable
if ($req_query_name) 
    $projID = $req_query_name 

#add SharePoint Online DLL - update the location if required
Import-Module "D:\home\site\wwwroot\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.dll"
Import-Module "D:\home\site\wwwroot\ProjectSiteUserSyncHTTPTrigger\Microsoft.SharePoint.Client.Runtime.dll"

#set the environment details
$PWAInstanceURL = "https://mod497254.sharepoint.com/sites/PWA2"
$username = "admin@MOD497254.onmicrosoft.com" 
$password = "password"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force
#create the SharePoint list on the PWA site and add the correct columns based on the data required
$listname = "ProjectSnapShots"
$results1 = @()

#set the Odata URL with the correct project fields needed
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectId eq GUID'$projID'&`$Select=ProjectId,ProjectName,ProjectPercentCompleted"

#get all of the data from the OData URL
while ($url){
    [Microsoft.SharePoint.Client.SharePointOnlineCredentials]$spocreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass);    
    $webrequest = [System.Net.WebRequest]::Create($url)
    $webrequest.Credentials = $spocreds
    $webrequest.Accept = "application/json;odata=verbose"
    $webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
    $response = $webrequest.GetResponse()
    $reader = New-Object System.IO.StreamReader $response.GetResponseStream()
    $data = $reader.ReadToEnd()
    $results = ConvertFrom-Json -InputObject $data
    $results1 += $results.d.results
        if ($results.d.__next){
    else {

#add data to snapshot list
#get PWA site client context
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($PWAInstanceURL) 
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePass) 
$ctx.Credentials = $credentials 
#get the target list 
$List = $ctx.Web.Lists.GetByTitle($listname) 

#for each project, create the list item - update the newitem with the correct list columns and project data
foreach ($projectrow in $results1) 
   $itemcreationInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation 
   $newitem = $List.AddItem($itemcreationInfo) 
   $newitem["Title"] = $projectrow.ProjectName
   $newitem["ProjectId"] = $projectrow.ProjectId
   $newitem["PercentCompleted"] = $projectrow.ProjectPercentCompleted

The PowerShell code would need to be updated with your environment details: (PWAInstanceUrl, username, password and listname variables). Also the OData URL will need to be updated to include the project level fields that you want to snapshot.The target SharePoint list will also need to be set up in the PWA site collection for the project fields the script uses. This is the list I set up for this example:


SnapshotDate is set to Todays date so we don’t need to set that in the code.

The code is simple to follow but in summary the first part will get the projID from request body – we will pass in the ProjectID for the published project from the Flow / Logic App trigger. Then the SharePoint Online CSOM DLLs are imported in. Then the specific PWA environment details are set for the variables. The OData URL is then added to the url variable. Here notice we are filtering for the ProjectID and passing in the $projID variable we get from the request body. The Select part of the query will need to be updated for your project level fields. Next the code gets the data from the OData feed using the web request and adds the data into the results array. Once we have the data, we connect to the SharePoint list, in the example it is the ProjectSnapShots as set in the $listname variable. Lastly the new item is created in the list using the data from the results array.

Now the Azure Function is ready to be used. It can be tested using the Test option in the right hand panel, update the Request body:


Update it for a valid project ID. Then click Run above the function code:


The Logs window below will help you debug any errors etc.:


Then check the SharePoint list in the PWA site and the new item should have been created:


We now know the Azure Function is working as expected, now we need to call the Azure function when a project is published. All we need from the Azure Function is the URL to use, use the </>Get function URL button:


Select the correct Key, in this example I used the default function key. Copy the URL as it will be needed later.

To call the Azure Function when a project is published, the choice here for a no code option would be Microsoft Flow or Azure Logic App. For this I will use Microsoft Flow but the same steps (triggers , actions etc.) would be used in the Azure Logic App. Create a new Flow and search for Project Online:


Then select the Project Online – When a project is published trigger.

Enter the PWA URL:


Then click the ellipsis and set the connection for the PWA URL or create a new connection if needed:


Click + New step then Add an Action and search Http:


Select HTTP – HTTP:


Complete the HTTP action:

Method is POST, the Uri is the URL for the function that we copied earlier, Headers are not required. The Body is where we pass in the project ID from the published project trigger:


The Flow is now completed:


Now click Save flow.

In PWA, Publish a project or projects and see the snapshot data created on the configured snapshot list once the Flow has run:

Flow run:


Data added to the list for the project I published – in this example it was the Office 2016 rollout project:


This just shows a simple example and the some of the possibilities for extending the Project Online capability when making use of simple PowerShell scripts and other Microsoft 365 / Azure services for cloud / serverless solutions. Look out for more examples in the future.

Running #ProjectOnline #PowerShell in #Azure using #AzureFunctions #PPM #Cloud #Flow #LogicApp Part1

July 28, 2017 at 4:50 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Fixes, Functionality, Information, PowerShell, Workflow | 4 Comments
Tags: , , , , , ,

Following on from my last post where I published an example solution starter PowerShell script for adding project team users to the Project Site, here I mentioned about running the script in an Azure Function and even running this sync from a Project Online event. The blog post can be seen below if you missed that:


Whilst I will use that example PowerShell script from my last blog post as an example, the concept will work for any PowerShell script.

I wont cover the details in setting up the Azure Function in part 1 as there is plenty of support out there for this – for this example I created an Http Trigger – PowerShell function.


I then uploaded the SharePoint DLLs and copied in the PowerShell script into the editor:


The project site user sync script didn’t work as is as I had to make some minor changes to get this to run from the Azure Function. This included change the way the SharePoint CSOM DLLs where loaded in. In the example script I used Add-Type to load the DLLs but in the Azure Function I had to switch this out to use Import-Module:


The next change I had to make was to comment out all of the feedback to the console, so all of the write-hosts. I also had to remove the functions within the script so that is was one block. After these changes I could execute the PowerShell script to add the project team members from my example project into the associated project site from the Azure Function. As this was an HTTP Trigger Azure Function, you can get the URL to the function and access that URL to execute the function.

This opens up lots of possibilities to easily execute this Azure Function from other applications that can make the HTTP call. For example you build easily execute this script once the project has been published either using a remote event receiver (RER) or a Microsoft Flow / Azure Logic App. The example script would need to be made generic and pass in a variable into the Azure Function for it to be a workable solution.

In part 2 of this blog post we will look at make a full event driven solution that is fired on project publish then executing the Azure Function and passing in a variable.

#ProjectOnline project user sync to project sites #PPM #O365 #PowerShell #SharePoint

July 7, 2017 at 4:13 pm | Posted in Add-on, Administration, App, Customisation, Functionality, Information, PowerShell, Workarounds | 1 Comment
Tags: , , , ,

This is a supporting blog post for an example solution starter PowerShell script I wrote for Microsoft’s Office 365 PPM tool Project Online. With the great improvement from Microsoft to now support up to 30,000 Projects and Project Sites in one Project Online Project Web App site collection, there are some limitations such as not being able to sync the users to project sites outside of the PWA site collection. For details on this change see the post here:


This solution starter script just demonstrates one method to sync the project team members into the associated Project Site-  the concept would also be useful when using the SharePoint permission mode in PWA. The example script is fixed to one example project / project site and only adds the team members to the members SharePoint group on that site. It doesn’t loop through all projects  or remove users from the project sites etc. This could all easily be done but is beyond the scope of the code sample / solution starter.

The code sample / solution starter can be downloaded from here: https://gallery.technet.microsoft.com/Online-User-Sync-to-7a75ef77

To get the script to work, there will need to be some environment variables set and a DLL available, these are detailed below.

To get the script to work you will need to reference the DLL as seen in the image below:


This can be installed from the SharePoint Online Client components / management shell. I used the dll from the SharePoint Online Management Shell in this example.

You will then need to update the environment detail placeholders as seen below:


Add the Full PWA URL, username and password for an account that is a full admin on the PWA site collection. Select an example project to test with on a non-production PWA project / project site. Use a project that is not syncing the users to the project site automatically – for example a project site outside of the PWA site collection. Copy the Project GUID – it will be similar to this: 4b75dfb8-4051-e711-80c9-00155da85037 and update the script with the project GUID. An easy way to get the Project GUID is from the PWA PDP URL when accessing the project. For the same project, copy the full project site URL and update the script then find the name of the correct SharePoint group to add the users to. For example it could be the members group. The groups can be found from the project site > Settings cog > Site Settings > Site Permissions.

Once these variable placeholders are set, save the script. Run the script using PowerShell and you will see the users added to the specified group on the project site. Fully test this on a non-production PWA instance first. The code should really be updated to production standard code before using on a production PWA instance and handle removing users, adding users to different permissions groups based on role and dynamically getting the projects and project site urls etc.

Below you can see the output from the PowerShell ISE when running against my example project  / project site on my test PWA instance:


Looking at the project site you can see the users here:


A production version of the PowerShell script could be set to run each day / night etc. as required. The code could also easily be run using an Azure Function and executed from a URL – you could then build is an event driven type user sync – more on that in my next blog post.

The script is provided "As is" with no warranties etc. – this is just a free solution starter so is use at your own risk.

Want to report across #ProjectOnline Project Sites for #SharePoint list data? #PPM #JavaScript #Office365 #REST #OData

May 5, 2017 at 5:13 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 5 Comments
Tags: , , , ,

For those of you that are familiar with Project Server or Project Online, only the default lists and default columns from those lists are synchronised to the database reporting schema. So Project API’s like the OData API (_api/ProjectData) only have data for Risks / Issues and the default columns etc. I have published an example solution starter script that will allow data from any list on the project site to be used.

This is a supporting blog post for the JavaScript solution starter I have published to the Microsoft Gallery, it can be downloaded here:


This solution starter should be updated before production use to include the correct data you want, improve error handling, support the REST API pagination etc. This example uses the Issues list and some example default columns from the Issues list but it can easily be updated to use a custom list on the Project Sites.

There are comments in the script to help update the script for your target Project Site list and columns. Once the script is updated, upload it to a library on the target PWA site. Create a new page on the PWA site to host the JavaScript file. Then add a content editor web part on the new page and reference the JavaScript file, for example:


Once added it will look like this (this in on one of my MOD demo tenants):


The select menu will contain a list of EPTs:


Changing the selection to another EPT will load a SharePoint modal pop up whilst the data loads:


If an EPT is selected that doesn’t contain any list items the following will be displayed:


There is example conditional formatting on the table:


The list item title is a clickable hyper link that will open the list item in a new window:


Fully test this on a non-production PWA instance before using in Production, the script is provided As Is with no warranties etc. Try it out and let me know what you think.

#ProjectOnline #PPM #PowerBI Report Pack Publish #BI #Reporting #PowerQuery #DAX #Office365

February 10, 2017 at 8:33 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , ,

Following on from my recent Project Online Power BI report pack post / release, this post looks at publishing the Report pack to the Power BI service, creating an example Dashboard and then finally creating an organisational content pack. For those of you that missed the previous post, please find a link below:


Open up the report pack in Power BI Desktop as seen below:


Click the Publish button on the Home ribbon or Click File > Publish > Publish to Power BI:


Sign in to Power BI if prompted to do so. If you have more than workspace in the Power BI Service it will prompt you to select the destination. Select the destination “My Workspace” as required and click Select, you will then see the following:


Once completed you will see:


Click Got it to close the window. Open up the browser and navigate to https://app.powerbi.com. You will then see the published report under the Reports heading in My Workspace and also the report dataset under the Datasets heading. Click the report name under the reports heading and the published report will load:


You can then schedule a refresh of the data. Click the ellipsis next to the report under the Datasets heading:


Click Schedule Refresh. You will then see that you need to enter the credentials used for each OData endpoint used in the reports:


Click Edit credentials and select OAuth2 on the window that opens:


Click Sign In then enter the credentials / select the account as required. Repeat this for all of the OData connections.

**********NOTE: There is currently an issue with updating the credentials in Power BI when the Project Online OData URL contains [], you will probably receive the following message “Failed to update data source credentials.”


I’m not sure when this will be resolved. Alternatively, if your PWA site default language is English you could update all of the report queries to remove the /[en-US] from the OData URL. To do this, click Edit Queries > Edit Queries to open the Query Editor. Select a query from the Queries menu on the left then click Advanced Editor. This will open the Advanced Editor window. In Source remove /[en-US] from the OData URL and click Done. Repeat this for all queries that contain the PWA OData URL, once completed click Close & Apply > Close & Apply. Then follow the steps to publish the report to the Power BI service. **********

Once the credentials are all updated and correct, expand Schedule Refresh and enable this as required:


Click Apply.

Now click the report name under the Reports heading. From here you can pin visualizations on to a dashboard. See an example below:


Click the pin in the top right corner of the visualization then click New Dashboard and give the dashboard a name:


Click Pin.

Repeat this for other visualizations on other pages in the report as required but on the Pin to dashboard menu select Existing dashboard and select the dashboard you just created:


Once you have added all the visualizations you want, click on the new dashboard from the Dashboards heading:


Now you could create the organisational content pack to make this report and dashboard available to others in the organisation. This does require the Power BI Pro license.

Click the Settings Cog in the top right corner:


Click Create content pack:


Complete the form as required and upload an image if needed then select your dashboard – this will automatically include the correct report and dataset. Once completed, click Publish. You will then see a success message in the top right corner:


Other users with a Power BI Pro license will then be able to consume the content pack once logged into their Power BI workspace by clicking Get Data > My Organization > Get:


Click Get it now. It will then add the content pack to their workspace.

Before creating the content pack it would be best to wait until the data source credentials issue is resolved in the Power BI service so that the data in the content pack will update on the schedule. Or alternatively, if your PWA site default language is English, remove the /[en-US] from each dataset query as described in this post.

#ProjectOnline #PPM #PowerBI Report Pack #BI #Reporting #PowerQuery #DAX #Office365

January 3, 2017 at 3:03 pm | Posted in Add-on, Administration, Customisation, Functionality, Information, Reporting | 7 Comments
Tags: , , , ,

Over the holiday period I started to build a Power BI Report Pack for Microsoft’s Office 365 PPM tool Project Online. I have made this publically available to download using the link below:


This Report Pack currently consists of 5 reports but will be extended in the near future with additional reports and / or changes to existing reports. In this release there are 5 reports, these can be seen below:

Portfolio Report:


Issues Report:


Risks Report:


Project Report:


Resource Demand Report:


These reports only use default intrinsic fields so it should work for all Project Online deployments.

Once downloaded, the report pack data sources will need to be updated to point to your target Project Online PWA instance. To do this you will need the Power BI desktop tool which is a free download here: https://powerbi.microsoft.com/en-us/desktop/

Open the downloaded PWMatherProjectOnlinePowerBIReportPack.pbix file in Power BI Desktop and follow the steps below to point the data sources to your Project Online PWA instance:

  • Home > Edit Queries > Edit Parameters
  • On the “Enter Parameters” window update the PWAURLParameter value with the correct PWA URL and click OK.
  • Click Apply Changes on the yellow status bar . You might be prompted to sign in if you haven’t before, if so, see the details below
  • If prompt with the authentication options. Select Organizational account then click Sign In.
  • Enter credentials for an account that has access to the OData Reporting API on the target PWA instance
  • Then click Connect
  • A Privacy levels window may appear, select Organisational then click Save.
  • The Apply Query Changes window will appear as the data is refreshed.
  • Once the refresh window disappears the reports will update to show the data from the target PWA instance. Click the Project Report tab and change the project filter from Project 1 to a project from your PWA instance. Save the file.

This file can either be emailed around to colleagues with details on how to update the credentials to their own or what would be better is to publish the report to your Power BI workspace can create an organisational content pack that others can add to their Power BI workspace. If the Power BI organisational content pack is the chosen option, you might want to create a Dashboard first.

In a follow up blog post to this one, I will touch on creating a dashboard once the report is published to my Power BI workspace then create an organisation content pack.

Enjoy, I hope you like it Smile

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:


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:


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:


Click expand and select Name:


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


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


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:


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:


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.

Next Page »

Create a free website or blog at WordPress.com.
Entries and comments feeds.