Reporting on #ProjectOnline Resource Cost Rate Tables #Office365 #PPM #PowerBI #Excel #PowerQuery #MSProject

August 10, 2018 at 4:18 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

The resource cost rate table details are not available in the Project Online / Project Server OData Reporting API (_api/ProjectData) but they are accessible using OData but from the CSOM REST API (_api/ProjectServer). In this blog post, I will walkthrough getting this data into an example Power BI report. It wont look pretty, that’s not the idea of this post!

To get this data you need to use the _api/ProjectServer API as seen below in the example for cost rate table A:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Which gives the detail:

SNAGHTML5adc642

To get all of the resources different cost rate A details, you would need to dynamically pass in the RESGUID. In the steps below we look at doing this in Power Query so this would work for either Power BI or Excel but for the purpose of the blog post, I’m using Power BI.

In Power BI, create a new OData connection using the Get Data > OData option. Use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘A’)/CostRates

Update with the correct PWA URL and a valid resource GUID from that PWA instance. Edit the data so it loads the Power Query Editor:

image

I renamed this to fn_getResCostRateA as this will become a function. Open the advanced editor:

SNAGHTML581e6c0

The code needs to be updated to:

SNAGHTML5817fb9

Click done and you will see the following:

image

No need to do anything with the parameter or buttons. Now we need to add another data source in for the resource metadata. Add a new new OData data source in from the Power Query Editor window and use the following URL:

{PWAURL}/_api/ProjectServer/EnterpriseResources?$Select=Id,Name&$Filter=ResourceType ne 3

Update with the correct PWA URL. This will get the list of resource GUIDs to pass into the function and also the resource name to be used in the report. I renamed the connection to Resource Details – Cost Rate Table A:

image

Once you have edited the query as required a new custom column needs to be added to invoke the function created earlier. Click the Add Column tab then click Custom Column. Give the column a name such as GetCostRateADetails then enter the following: fn_getResCostRateA([Id]) as seen below:

image

When clicking OK, this might take a while depending on how many resources you have as this will invoke the function for each project and call the REST API, passing in the Id for that row and bring back the cost rate A table records. Once completed you will see the tables as below in the new custom column:

image

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the cost rate fields:

image

Click OK and the data will refresh / load then display the data for the cost rate fields:

image

Notice for those resources with multiple cost rate table entries there are multiple rows per resource. These are just resources from the Microsoft Project Online demo content with updated cost rate entries.

That’s it, now load into Power BI and create the report – a basic table example below:

image

For other cost rate tables, repeat the process but replace the A for the other cost rate tables such as:

{PWAURL}/_api/ProjectServer/EnterpriseResources(‘{RESGUID}’)/CostRateTables(‘B’)/CostRates

This dynamic function process is the same process I’ve used and detailed before in previous blog posts for Power Query such as this one: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

Advertisements

#ProjectOnline PWA Stats with Snapshot #JavaScript #jQuery #PPM #Office365 #PMOT #MSProject

July 2, 2018 at 10:55 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting | Leave a comment
Tags: , , , ,

Want to view simple PWA stats and capture the data to then build simple trend reports? This simple JavaScript and jQuery solution starter might be a good starting point. The output can be seen below:

image

Each PWA entity can be expanded to see the stats:

image

image

Then each week or month etc. you can take a snapshot of the data using the Snapshot button, this creates an item on the snapshot list:

image

The solution starter code has been published for download. The code expects the SharePoint list to already exist but that is covered in this blog post. The solution starter code can be downloaded from the Microsoft Gallery using the link here: https://gallery.technet.microsoft.com/Online-PWA-Stats-and-eb56e6bb

The code does make use of jQuery and jQuery UI, these are loaded from the jQuery CDN but you might want to download them and store them locally etc.:

image

The code expects a list called PWASnapshot in the root PWA site collection:

image

This can be updated to a different target list in the root PWA site collection, just change the listTitle variable as seen above. The following columns are required to already exist on the target SharePoint list in the PWA site collection:

image

They’re all default column settings apart from DateCaptured, this defaults to Today’s Date. If you do not need the snapshot capability, you could just comment out / remove the snapshot button from the code.

Create a new page on the PWA site to display the PWA Stats data, I created a new web part page in a library called “Pauls” in the root PWA site – this is on my test PWA site, hence a library called Pauls!

image

Download and update the solution starter as required – remember it is a solution starter so it could do with some code optimisations and better error handling etc. Upload the solution starter JavaScript code to the PWA site, in this example I uploaded it to the same library as the new PWAStats page. Edit the new page and add a Content Editor Web Part, update the Content Link to add the relative URL path for the JavaScript code as seen below in this example:

image

Update other web part settings as required then click Apply then click OK and stop editing the page.

As the data is loaded, the SharePoint modal dialog will appear:

image

This will close once all the projects are loaded as on my PWA dataset, the projects data is the largest.

Clicking the snapshot button will also load the SharePoint modal dialog:

image

This will close when the item is added to the list, then a message will display below the button to state the item has been added:

image

Trend reports could easily be created using Power BI consuming the snapshot list data to see how the data changes over time.

This could easily be extended to bring in additional PWA stats. I will probably write a blog post in the future to extend this to capture additional PWA stats.

The solution starter file contains HTML, CSS and JavaScript in the same file, for production you might want to split out the HTML, CSS and JavaScript into the separate files, reference the JavaScript and CSS files in the HTML file and link to the HTML file in the content editor web part but as this is so small having one file will be fine and is easier to manage.

Fully test on a DEV / TEST PWA instance first before using in Production. The script is provided "As is" with no warranties etc.

I hope you find it useful Smile

Getting Starting with #ProjectOnline and #PowerApps #PVC18 presentation links #PPM #PMOT #Apps #Office365 #MSProject

June 14, 2018 at 7:59 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Mobile | 1 Comment
Tags: , , ,

Last week I had the pleasure of presenting at the awesome Project Virtual Conference 2018. During my session I referenced existing blog posts and code samples that I had previously published on my blog. As promised in the presentation, here is a blog post containing all of the relevant links to help get you started using PowerApps for Project Online. A link to my session is here: http://projectvirtualconference.com/sessions/getting-started-with-project-online-and-powerapps/

Firstly here is a link to the official PowerApps site: https://powerapps.microsoft.com/en-us/build-powerapps/

The first example app we looked at was a navigation PowerApp for Project Online, this made use of the SharePoint Online connector in PowerApps. As discussed, you would need a process to get the required Project Online data into the target SharePoint list for this approach. Here a link to an example solution starter PowerShell script that will do just that: https://pwmather.wordpress.com/2018/03/01/projectonline-powershell-to-keep-ppm-data-in-sync-on-sharepoint-list-pmot-o365/

Once the data is available, here are the two links that walkthrough creating this example navigation app:

Part 1: https://pwmather.wordpress.com/2018/02/21/projectonline-powerapps-example-office365-ppm-pmot-apps-msproject-part1/

Part 2: https://pwmather.wordpress.com/2018/02/24/projectonline-powerapps-example-office365-ppm-pmot-apps-msproject-sharepoint-part2/

The next example PowerApp we looked at in the presentation made use of the Project Online connector in PowerApps to give examples of using some of the actions available in the connector. This works directly with Project Online so does not require any background process to get data into SharePoint. As mentioned in the presentation, the properties available are fairly limited, hence for the navigation app I had to get the data from Project Online into SharePoint list first as I needed the Project Site URL which is not in the Project data set in the Project Online connector for PowerApps. This example app did make use of the Project Online connector in PowerApps: https://docs.microsoft.com/en-us/connectors/projectonline/

Here are the three links that walkthrough creating this example app:

Part 1: https://pwmather.wordpress.com/2018/03/14/projectonline-powerapps-using-the-project-online-connector-ppm-apps-msproject-o365-part1/

Part 2: https://pwmather.wordpress.com/2018/04/11/projectonline-powerapps-using-the-project-online-connector-ppm-apps-msproject-o365-part2/

Part 3: https://pwmather.wordpress.com/2018/04/26/projectonline-powerapps-using-the-project-online-connector-ppm-apps-msproject-o365-part3/

As mentioned in the presentation, you can create a PowerApp that make use of both the SharePoint connector and Project Online or any number of connectors available for PowerApps – there are lots!

PowerApps are a great way to build business applications without having to write any code!

#ProjectOnline : Add related projects to a custom field #JavaScript #jQuery #PPM #Office365 #PMOT #MSProject

May 24, 2018 at 7:24 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information | 1 Comment
Tags: , , ,

This blog post follows on from my earlier blog post on updating Project Online project level custom fields from JavaScript using the REST API: https://pwmather.wordpress.com/2018/05/21/using-rest-in-javascript-to-update-projectonline-project-custom-fields-ppm-pmot-jquery-office365/

As mentioned in that post, I have published example code sample / solution starter that demonstrates updating a project level custom field using the REST API. This simple code sample runs from a Project Detail Page in the Project Web App site collection, it enables the user to add related projects to a project level custom field. As mentioned, it is a solution start to demonstrate the use of the Project Online REST APIs from JavaScript, so it wouldn’t be fit for production use as it would need some tweaks to manage things like displaying projects already added to the custom field pre-loaded in the related project list etc. The solution starter functionality can be seen below:

image

The solution starter code can be downloaded from the Microsoft Gallery here: https://gallery.technet.microsoft.com/Online-Add-Related-e6a69a02

Once downloaded, open the file to edit in your chosen editor (notepad will do if you have nothing else, I use Visual Studio Code or Notepad++). To get the solution starter code to work, you will need to update the custom field internal name for your target project level custom field, the code sample also assumes this target custom field is a text field. If the target field is a different type you will need to update the value type as mentioned in the first post.

Firstly, get the correct internal name for the custom field as seen below in this example:

image

This information is available using the CustomField API: {PWAUrl}/_api/ProjectServer/CustomFields

Now in the solution starter code, update the projectCFInternalName variable on line 61 as seen below with the correct guid:

image

Save the file and upload to a library in the PWA Site, for example mine is uploaded to the Site Assets library. Then add a Content Editor Web Part to the target Project Detail Page in PWA, edit that new web part:

image

In the content link, put a link into the JavaScript file, in my example the link is /sites/pwa/SiteAssets/PWARelatedProject.js. Expand Appearance, give it a title and change the Chrome Type to None. Click Apply then click OK and stop editing the page. Your page will then display the following (assuming you edited the web part from PWA Settings > Project Details Pages:

image

When accessed from a PDP linked to a project the following will display:

image

The list of projects are filtered to the list of projects the current user can access. Use the buttons to add the related project/s:

image

Then click Save:

image

Once the publish job has been sent to the queue for processing the modal will close. Viewing the custom field in the PDP will show the the projects added:

image

This is just a simple example to demonstrate updating project level custom fields using the REST API from a Project Detail Page. Update the solution starter to ensure the code is production ready and fully test on a DEV / TEST PWA instance first before using in Production. The script is provided "As is" with no warranties etc.

Using #REST in #JavaScript to update #ProjectOnline project custom fields #PPM #PMOT #jQuery #Office365

May 21, 2018 at 12:01 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information | 1 Comment
Tags: , , ,

This blog post provides example code for updating Project Online project level custom fields using the REST API ({PWASite}/_api/ProjectServer) in JavaScript on a PWA page. Following this blog post later this week will be a full working code sample that will be available for download.

When updating a project programmatically you will still need to following the same steps you do when updating a project manually, check out, update, publish and check in. The code snippets below demonstrate these actions using the REST API in JavaScript for updating a project level custom field.

Firstly check out the project:

image

Here we are passing in the project GUID into the URL that we will call to check out the project using the jQuery ajax HTTP request. The check out method is detailed here: https://msdn.microsoft.com/en-us/office/project/api/publishedproject#CheckOut__ If successful we then call the updateProjectCF function:

image

Again, in here we are passing in the same project GUID into the URL to update the custom fields using the jQuery ajax HTTP request. This call is slightly more involved as we have to inform the API call what is being changed. This is done by passing that data to the API in the HTTP call. To update custom fields you have to specify the key, the value and value type. The key is the internal custom field name, an example seen below:

image

This information is available using the CustomField API: {PWAUrl}/_api/ProjectServer/CustomFields

The value is the data you want to add to the custom field, this example is just updating a free text (single line of text) field. The value type specifies the custom field data type that you are updating. These are all prefixed with Edm (Entity Data Model) then the the type such as String, Int32 or DataTime etc. The update custom field method is detailed here: https://msdn.microsoft.com/en-us/office/project/api/draftproject#UpdateCustomFields_Collection_SP.KeyValue__customFieldDictionary_ If successful we then call the publishcheckInProject function:

image

Similar to the first API call, we just pass in the same project GUID to the URL used then call to publish and check in the project using the jQuery ajax HTTP call. Specifying true with the publish call will check in the project. The publish method is detailed here: https://msdn.microsoft.com/en-us/office/project/api/draftproject#Publish_Boolean_checkIn_

As mentioned earlier on in the post, there will be a full working example / solution started published later this week.

Change required for #SharePoint Online / #ProjectOnline REST API calls when using WebRequest #PowerShell #dotnet #office365dev

May 9, 2018 at 7:00 am | Posted in .Net, Administration, Customisation, Functionality, Information, Issue, PowerShell | 2 Comments
Tags: , , , ,

Just a quick blog post to highlight a change the is required when querying Project Online / SharePoint Online REST APIs in code when using the WebRequest class. Previously the PowerShell code sample below would work and authenticate with no issues:

#add SharePoint Online DLL - update the location if required
$programFiles = [environment]::getfolderpath("programfiles")
add-type -Path $programFiles'\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll'

#set the environment details
$PWAInstanceURL = "https://PWAURL"
$username = "username" 
$password = "password"
$securePass = ConvertTo-SecureString $password -AsPlainText -Force

#set the Odata URL with the correct project fields needed,
$url = $PWAInstanceURL + "/_api/ProjectData/Projects()?`$Filter=ProjectType ne 7&`$Select=ProjectId,ProjectName,ProjectPercentCompleted,ProjectOwnerName"

#get all of the data from the OData 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
$results.d.results 

There has been a change in Office365 and this would now generate a 401 unauthorized error as seen below:

image

It is now required to use the authentication cookie, not sure if this is a permanent change or a temporary issue. Adding the line below resolves the issue:

$webrequest.Headers["Cookie"] = $spocreds.GetAuthenticationCookie($url)

#get all of the data from the OData 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["Cookie"] = $spocreds.GetAuthenticationCookie($url)
$webrequest.Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f")
$response = $webrequest.GetResponse()

This change would be applicable to all of my PowerShell code samples that query the Project Online OData API found here: https://gallery.technet.microsoft.com/site/search?f%5B0%5D.Type=User&f%5B0%5D.Value=PWMather&sortBy=Date

Hope that helps

#ProjectOnline #PowerApps using the Project Online Connector #PPM #Apps #MSProject #O365 Part2

April 11, 2018 at 12:20 pm | Posted in Add-on, Administration, App, Configuration, Customisation | 2 Comments
Tags: , , , ,

In part two of this series of blog posts we will look at using the ListTasks action from the Project Online PowerApps Connector. For those of you that missed part 1, here is a link: https://pwmather.wordpress.com/2018/03/14/projectonline-powerapps-using-the-project-online-connector-ppm-apps-msproject-o365-part1/

The ListTasks action is: ProjectOnline.ListTasks("{ProjectGUID}","{"PWASiteURL}).value

As you can see we need to pass in two parameters here, the Project GUID and the PWA Site URL. As this requires the Project GUID, we will use the PowerApp we started with in part 1 and pass in the Project GUID from the first screen. Add another screen to your PowerApp, in this example I have just added a blank screen and renamed it to Tasks:

image

Now add a data table to this new screen:

image

With the new data table selected, click the Advanced option in the right pane:

image

Now on the advanced settings pane, in DATA > Items enter:

ProjectOnline.ListTasks(Gallery2.Selected.Id,https://tenant.sharepoint.com/sites/pwa).value

Update the query as required, for example update to the correct gallery name and update to the correct PWA URL.

SNAGHTML2fcefca

Click “Choose the fields you want to add from the customization pane”, in this example I have selected Name and PercentComplete:

SNAGHTML2fefd2e

Now I will add a label at the top of the screen to display the Project Name. With the label added to the screen, select the label and click the advanced setting pane:

image

In DATA > Text starting typing the name of your gallery added to the first screen, select it then type “.” then click selected then “.” then click Name:

image

Now the project name for the first project in the gallery will display:

image

You could look to include an option here to add new tasks if you wanted, the action to use would be: https://docs.microsoft.com/en-us/connectors/projectonline/#creates-new-task

Now we need to add in a control to be able to navigate back to the projects screen, I have just added a left arrow icon then updated the OnSelect property in the advanced settings pane to navigate to the projects screen:

image

Now on the Projects screen we need to update the next icon on the gallery to navigate to the Tasks screen, select the icon and update the OnSelect property on the advanced pane:

image

Now you have an app that will display a list of all projects directly from Project Online, then access the tasks from that selected project.

Next up we will look at create new projects in Project Online using the CreateProject action.

#ProjectServer and #SharePoint 2013 / 2016 April 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject

April 11, 2018 at 6:26 am | Posted in Administration, Configuration, CU, Customisation, Fixes, Functionality, Information, Installation | Comments Off on #ProjectServer and #SharePoint 2013 / 2016 April 2018 Cumulative Update #PS2013 #SP2013 #PS2016 #SP2016 #MSProject
Tags: , , , , , , ,

The Office 2016 April 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4098622

Project 2016 April 2018 update:
https://support.microsoft.com/en-us/kb/4018320

SharePoint Server 2016 / Project Server 2016 April 2018 update: 
https://support.microsoft.com/en-us/kb/4018340 & https://support.microsoft.com/en-us/kb/4018336

The Office 2013 April 2018 updates and cumulative updates are now available, please see the links below:

https://support.microsoft.com/en-us/kb/4098622

Project Server 2013 April 2018 CU Server Roll up package:
https://support.microsoft.com/en-us/kb/4018346

Project Server 2013 April 2018 update:
https://support.microsoft.com/en-us/kb/4018352

Project 2013 April 2018 update:
https://support.microsoft.com/en-us/kb/4018335

Also worth noting, if you haven’t done so already, install Service Pack 1 http://support2.microsoft.com/kb/2880556 first if installing the April 2018 CU for 2013.

As always, fully test these updates on a replica test environment before deploying to production.

#ProjectOnline #MSProject #Agile updates #Kanban #Scrum #Tasks #PPM #PMOT

March 27, 2018 at 6:48 am | Posted in Administration, Configuration, Customisation, Functionality, Information | Comments Off on #ProjectOnline #MSProject #Agile updates #Kanban #Scrum #Tasks #PPM #PMOT
Tags: , , , ,

There are some updates / improvements to the Agile feature in Project Online Desktop client, these follow on from the first release in October last year: https://pwmather.wordpress.com/2017/10/25/agile-now-available-in-msproject-kanban-scrum-sprints-tasks/ 

These updates are available in the latest release of the Office Insider version of the Office click-to-run client.

Two new features in this release for updating the % complete on boards as you move tasks between a status and the ability to filter tasks on the boards using the summary tasks and resources. These are seen below.

% complete on boards

Opening a board view such as the Backlog Board or the Current Sprint Board you will now see a “Set % Complete” row. As seen on the screenshot below this can also be hidden from the view:

image

Clicking the “Set % Complete” enables you to type the desired % complete for that status:

image

As you move a task into that status column, the task % complete will update as per the % complete value for the column:

image

image

Filters

On the board views you have the ability to filter tasks using the summary tasks and resources:

image

image

You can select multiple values from the two filter menus, the tasks will then filter on the board based on your filters:

image

The filters are not persistent, as you change views the filters will be reset.

A great addition to the agile feature in Project Online Desktop client. If you’re on the Insider version of Office click-to-run, take a look and see what you think.

#ProjectOnline #PowerApps using the Project Online Connector #PPM #Apps #MSProject #O365 Part1

March 14, 2018 at 10:39 pm | Posted in Add-on, Administration, App, Configuration, Customisation | 2 Comments
Tags: , , , ,

I recently published two articles on creating a navigation type app for Project Online using PowerApps, here is a screen shot of the project details screen from that example app:

SNAGHTML28370a07

For those of you that missed those posts, the links are below:

https://pwmather.wordpress.com/2018/02/21/projectonline-powerapps-example-office365-ppm-pmot-apps-msproject-part1/

https://pwmather.wordpress.com/2018/02/24/projectonline-powerapps-example-office365-ppm-pmot-apps-msproject-sharepoint-part2/

That particular example used a SharePoint list as the data source as there were certain properties I needed such as the Project Site URL that are not available in the native Project Online connector for PowerApps. I also stored project images in that source SharePoint list and displayed those in my example app. I used a custom process to populate the SharePoint list with the Project Online data and kept that data in sync. In this series of blog posts we will look at how to use the native Project Online PowerApps connector: https://docs.microsoft.com/en-us/connectors/projectonline/.

This connector allows us to perform certain actions in PowerApps such as read the list of Projects directly from the PWA site collection: https://docs.microsoft.com/en-us/connectors/projectonline/#List_projects or creating a new project: https://docs.microsoft.com/en-us/connectors/projectonline/#Creates_new_project for example.

This series of blog posts wont build a full PowerApp using this connector but it will show snippets on how to use some of the Project Online PowerApps connector functions.

The first one we will look at is displaying a list of projects in PowerApps using the list projects function.

This series of posts assume that you have set up a Project Online Connection to Project Online:

SNAGHTML6a9d36

If you haven’t set up a Project Online connection yet, it is very simple, just click the + New Connection link and find Project Online then add.

Using the PowerApps studio, either desktop or web, we will start with a blank app:

SNAGHTML6d726e

The first thing to do is to add the Project Online data source, click “connect to data”. This will load all of your connections you have added to PowerApps / Flow, select the Project Online connection:

SNAGHTML6edae8

Then close the Data window. Now decide on the visual that you need to display the projects, I inserted a gallery:

image

Then set the layout to just Title as I only will just display the project name for the purpose of this blog post:

image

Click Advanced on the Gallery settings pane and you will notice it states “CustomGallerySample” in the DATA > Items field. Update this to:

ProjectOnline.ListProjects("{PWASiteURL}").value

SNAGHTML7a47c5

Now you can select a property to display on the gallery, in this example I will select Name:

SNAGHTML7d85ba

Now all of your projects will appear in the gallery directly from Project Online:

SNAGHTML9f2a46

In the next post we will look at how to use the ListTasks function in the Project Online PowerApps connector.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.