Search Results

Keyword: ‘powerbi’

#Project Roadmap #PowerBI report pack with #AzureBoards data #PPM #ProjectManagement #MSProject #Office365 #PowerPlatform #Dynamics365 #CDS #Odata #AzureDevOps

March 16, 2019 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

This post follows on from my last post where I posted about using Azure DevOps Azure Boards in Project Roadmap, in case that you missed it here is the link: https://pwmather.wordpress.com/2019/03/15/project-roadmap-azuredevops-azureboards-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-cds/

In this post we will cover combining Azure Board data into the Roadmap Power BI report pack I released. Here is the blog on the default Roadmap Report pack if you haven’t seen that yet: https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

I covered a similar topic the other week but for combining Project Online data here: https://pwmather.wordpress.com/2019/03/08/project-roadmap-powerbi-report-pack-with-projectonline-data-ppm-projectmanagement-msproject-office365-powerplatform-dynamics365-cds-odata/

With the Power BI Roadmap report set up and loading data from your Roadmap service which includes linked items from Azure Boards, we will now edit that Power BI report to bring in Azure Boards data. Firstly click Get Data > Odata Feed and enter the Azure DevOpps OData API URL like below:

https://analytics.dev.azure.com/organizationName/_odata/v1.0/

image

For details on the Azure DevOps OData API in Power BI, see this article: https://docs.microsoft.com/en-us/azure/devops/report/powerbi/access-analytics-power-bi?view=azure-devops

Click OK and sign in as required. In the Navigator window select Projects and WorkItems plus other tables as required:

image

Click Edit to load the Power Query editor. Edit the queries as needed, such as removing columns, remaining columns etc. but ensure you leave the ProjectId and WorkItemId columns in Projects and WorkItems queries as these are required to join the Azure Boards data with the Roadmap data. Once finished you should have at least 9 queries like below:

image

Click Close and Apply in the Power Query editor. Set up the relationships between the Projects table and RoadmapRowLinks and WorkItems table and RoadmapItemLinks:

image

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and work item level data from my linked Azure Boards Projects and Work Items:

image

It’s that simple!

Advertisements
Categories: Paul Mather, Work Tags:

#Project Roadmap #PowerBI report pack with #ProjectOnline data #PPM #ProjectManagement #MSProject #Office365 #PowerPlatform #Dynamics365 #CDS #Odata

Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

As mentioned in previous posts, I said I will post on how to bring in Project Online data with the Roadmap service data in a Power BI Report. We will start off with the Power BI Roadmap report pack I published recently. If you missed it, it can be downloaded from the post below:

https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

With the Power BI Roadmap report set up and loading data from your Roadmap service, we will now edit that Power BI report to bring in Project Online data. Firstly click Get Data > Odata Feed and enter the Project Online Reporting API URL like below:

image 

Click OK and sign in as required. In the Navigator window select Projects and Tasks plus other tables as required:

image

Click Edit to load the Power Query editor. Edit the queries as needed, such as removing columns, remaining columns etc. but ensure you leave the ProjectId and TaskId columns in Projects and Tasks queries as these are required to join the Project Online data with the Roadmap data. Once finished you should have at least 9 queries like below:

image

Click Close and Apply in the Power Query editor. Set up the relationships between the Projects table and RoadmapRowLinks and Tasks table and RoadmapItemLinks:

image

Now update the Roadmap Detail page in the report as needed, as seen below outlined in red, I have included some project and task level data from my linked Project Online Projects and Tasks:

image

It’s that simple, take a look and see what you think.

Categories: Paul Mather, Work Tags:

#Project Roadmap #PowerBI report pack #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatform #Dynamics365

January 29, 2019 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

I have released a solution starter report pack for Project Roadmap, this follows on from a mini series of blog posts on the Roadmap backend CDS database / app. The final post in that series can be found here: https://pwmather.wordpress.com/2019/01/22/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365-powerbi-part-3/

As mentioned in that post, I would release the Power BI report pack I created. This report pack can be downloaded from the link below:

https://gallery.technet.microsoft.com/Roadmap-Power-BI-Report-8eaae91e

This report pack consists of 3 reports for Project Roadmap, these reports can be seen below:

Roadmap Summary page:

image

Roadmap Detail page:

image

Roadmap Sync Admin page:

image

Once downloaded, the report pack CDS data source will need to be updated to point to your target Project Roadmap environment. 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 PWMatherRoadmapReport.pbit template file in Power BI Desktop and follow the steps below to point the CDS data source to your Project Roadmap environment:

  • When prompted, enter the correct CDS URL for the Project Roadmap environment:

image

image

    • I created a new role in the Dynamics 365 instance where Roadmap is deployed – access the Dynamics 365 admin center from the Office 365 Admin center using the Global Admin account. Open the default Dynamics 365 instance (this is where Roadmap is deployed to) then click Settings > Security > Security Roles > New. I gave the new role Read access at the business unit level to the 4 Roadmap entities used in the report:

image

    • I then accessed the user account from the Dynamics Security admin in the Users page then assigned the new role to this account using the Manage Roles option. Other roles and role assignments are as per the default settings:

image 

    • Now the report will be able to access and load the data.
  • The report will update with the data from your Project Roadmap environment – this might take a few minutes.
  • Save the report.
  • Publish the report to the Power BI service and distribute / share as required.

Your Office 365 administrator / Dynamics 365 administrator will be able to help you out with the correct user account to use as they will probably have a preferred approach to granting access that might be different to the way I have done it here. Or they might want to set up this report, publish to Power BI and give you access via the Power BI service.

I hope you like it and find it useful.

Categories: Paul Mather, Work Tags:

#Project Roadmap #CDS #App Overview #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatform #Dynamics365 #PowerBI Part 3

January 22, 2019 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

This post follows on from part 2: https://pwmather.wordpress.com/2019/01/07/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365-part-2/ In Part 2 we reviewed the fields and looks looked at the data used by the Roadmap application. In this post we will continue with the Power BI report that was started in part 2. I have made some changes following on from the last post if you have been following and creating the Power BI report. The queries I have can be seen below:

image

Here are the query connection details:

  • Roadmaps queries msdyn_roadmaps but filter to only return roadmaps
  • RoadmapItems queries msdyn_roadmapitems but filtered to only return roadmap items
  • RoadmapRows queries msdyn_roadmaps but filter to only return rows
  • RoadmapRowItems queries msdyn_roadmapitems but filtered to only return row items
  • RoadmapItemLinks queries msdyn_roadmapitemlinks
  • RoadmapRowLinks queries msdyn_roadmaprowlinks

I have also used the Power Query editor options to remove fields I do not need, renamed fields etc. but that is standard Power BI functionality.

Then the following relationships have been set up between these tables:

image

Or the visual view:

image

Now this Power BI file is ready to start creating the reports. Here are some screen shots of example reports:

image

image

I will be releasing the Power BI file to download later this week that can be used as a solution starter for your Project Roadmap reporting. I will then create a blog post on how you can bring in other data from Project Online into the Power BI file.

Categories: Paul Mather, Work Tags:

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

October 22, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

This is a supporting blog post for a new Project Online Power BI Report Pack that I have published. This report pack provides examples for a project compliance / audit type check to ensure your projects follow certain planning standards. This follows on from the previous report packs that I published: https://pwmather.wordpress.com/2017/10/31/projectonline-ppm-powerbi-report-pack-v2-bi-reporting-powerquery-dax-office365/ This new report pack follows the same theme / styling. The compliance report pack can be downloaded from the Microsoft Gallery, the link to download the report is here: https://gallery.technet.microsoft.com/Online-Power-BI-Compliance-b45b657c

The report pack consists of two reports, a summary report for project level checks and a detailed report for tasks, risks and issues checks. These can be seen below:

Summary Page:

image

Project Details (Select a Project from the filter):

image

Same report but with a different project selected:

image

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 installed. This can be downloaded here: https://powerbi.microsoft.com/en-us/desktop

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

  • In the parameter window that opens, enter the full Project Online PWA URL without the /default.asp – such as https://tenant.sharepoint.com/sites/pwa
  • Click Load
  • The data will now start to load and you will be prompted to connect
  • On the OData feed window, click Organizational account and click Sign in and enter credentials as required
  • Click Connect
  • On the Privacy levels window set the privacy as required
  • Click Save
  • The data will load – this may take a few minutes depending on the dataset size in Project Online
  • Access the Project Details page and select a project from the project filter
  • Save the report

Please note, some of the steps above might not be seen if you have connected to the Project Online instance from Power BI Desktop previously. 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 create a Power BI app workspace and give users access: https://docs.microsoft.com/en-us/power-bi/service-create-workspaces

The checks in this pack are just examples and might not be applicable to your organisation but it will give you a good starting point it you do not have any compliance / assurance type reports today.

I will plan to update this in the future, so feel free to add comments for any suggested project compliance checks, provided they are generic enough and possible using only intrinsic fields, I will look to add these in a later release.

I hope you like it Smile

Categories: Paul Mather, Work Tags:

#ProjectOnline reporting on task Predecessors and Successors #O365 #MSProject #PPM #PMOT # Excel #PowerBI #OData

October 13, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

A few times I have heard this topic come up so I thought it was worth a quick blog post to give two examples for getting access to this detail. Firstly a quick look at my sample project to see the data and task links:

image

As we can see, all tasks are linked. The predecessor and successor details are not available in the OData reporting API by default: ({PWASiteURL}/_api/ProjectData).

The first option we will explore is using the REST CSOM API ({PWAURL}/_api/ProjectServer). To access this is not a simple read from one endpoint like it would be in the OData reporting API if the data was there. When using the CSOM REST API you have to first get the project then from there you can get the task details and task link details. Below we walkthrough this process and view the results. I am just using the browser to return the data for ease. Let’s have a look at this Project data using: {PWASiteURL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’) where the GUID is the project GUID for the project seen above. This returns:

SNAGHTML1271759a

Here you can see all of the related endpoints and then the project properties below. I have outlined in red the two related endpoints that are useful to us, the TaskLinks and Tasks.

Lets have a look at the TaskLinks first – we have 4 links in the simple plan displayed above, this matches what we see in the TaskLinks endpoint:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks

SNAGHTML127510a3

For each link we can then access two other endpoints /End and /Start and see two properties for the link, Id and DependencyType. Id is the TaskLink Id and DependencyType is the internal dependency type value, the enumerations for the dependency type can be found here: https://msdn.microsoft.com/en-us/library/microsoft.projectserver.client.dependencytype_di_pj14mref.aspx. Looking at the data returned, I have 3 links with a dependency type of 1 (Finish to Start) and 1 link with a dependency type of 3 (Start to Start). Now for one of those TaskLinks, we will look at what the /End and /Start endpoints provide. I will use the TaskLink with a Start to Start dependency type for this. Firstly the /Start endpoint:

{PWASiteUL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks(‘0d7da2b3-2dcb-e811-9328-1002b5489337’)/Start – where the 2nd GUID is the TaskLink GUID

SNAGHTML1283a2ae

This returns all of the data for the starting task, in this example it is task T2 (I’ve updated the REST call to just return the task name:

SNAGHTML12872358

Task T2 is the task starting the link as seen in the project plan:

image

The /End endpoint, as you can guess will return the same details but for the task ending the link:

{PWASiteUL}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/TaskLinks(‘0d7da2b3-2dcb-e811-9328-1002b5489337’)/End – where the 2nd GUID is the TaskLink GUID – I’ve update the REST call to just return the task name:

SNAGHTML128b4ce6

This returns T3 from the example project:

image

As you can see, using the TaskLinks endpoint once we have the project, we can then navigate to find the task details for the linked tasks.

Now lets look at what the /Tasks endpoint can do for us to find the linked tasks. Accessing the {PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks endpoint will return all of the tasks in the project (based on the project GUID used in the REST call):

SNAGHTML128ffcaa

For each task in the project we can see the task properties but also navigate to another endpoint to view more related data for that one task. For example, we can then navigate and view the /Predecessors and /Successors. I will use task T3 for this walkthrough by passing in the Task GUID for T3. Accessing the predecessors data for task T3:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks(‘b3433ba7-2dcb-e811-9328-1002b5489337’)/Predecessors – where I have passed in the task GUID for T3:

SNAGHTML12964d6d

This returns the TaskLink details for the predecessor task – from that point we can then use the /End and /Start related queries to get the linked task details. The same goes for the /Successors endpoint for the example task T3:

{PWASiteUrl}/_api/ProjectServer/Projects(‘a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks(‘b3433ba7-2dcb-e811-9328-1002b5489337’)/Successors – where I have passed in the task GUID for T3:

SNAGHTML129abb66

This returns the TaskLink details for the successor task – from that point we can then use the /End and /Start related queries to get the linked task details.

As you can see, trying the get that data for all linked tasks in a report using Power Query wouldn’t be a simple query to one endpoint but it is possible to follow it through to get the data needed.

The next option to look at is creating two task level calculated fields so that you can get the predecessor and successor details in the /Tasks endpoint in the OData reporting API ({PWASiteURL}/_api/ProjectData/Tasks). Whilst this is simplifies the reporting experience there is a performance cost to this – certainly for large projects with many tasks. Also this will use 2 of the recommended maximum 5 task level calculated fields! In PWA Settings > Enterprise Custom Fields and Lookup Tables, create two new Task level text fields that use formulas, one field will be for predecessors and one for successors. In the predecessors field formula use [Predecessors] and in the successors field formula use [Successors]. The predecessors custom field can be seen below:

image

The next time you publish your project/s you will then see the data available in the OData Reporting API:

{PWASiteUrl}/_api/ProjectData/Projects(guid’a28bf087-2acb-e811-afb0-00155d143a0e’)/Tasks?$Select=TaskName,TaskPredecessors,TaskSuccessors

SNAGHTML12a6e5c7

Hope that helps!

Categories: Paul Mather, Work Tags:

#ProjectOnline Supporting Projects and Programs Part 3 #PPM #MSProject #Office365 #PMOT #PMO #SharePoint #PowerBI

October 1, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

In part 3 of this mini series of blog posts we will look at a basic report example to support projects and programs making use of the configuration changes in part 1 and 2. For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/09/19/projectonline-supporting-projects-and-programs-part-1-ppm-msproject-office365-pmot-pmo/ and part 2 here: https://pwmather.wordpress.com/2018/09/21/projectonline-supporting-projects-and-programs-part-2-ppm-msproject-office365-pmot-pmo-sharepoint/

Now that we have done some very simple configuration changes in PWA and the Project Sites and then populated some example test data in the PWA instance we can look at example reports. We won’t cover creating these reports from start to end as this isn’t the purpose of the post, it is purely to highlight how to make use of the configuration changes to give to the program level reporting. These reports are also not engaging or showing casing Power BI, so you will want to create much better looking reports as these are just used to show examples of the data!

Firstly, lets look at the Project Center so you get an idea of the Project data I have in this test instance:

image

Notice I have two projects tagged and 1_Program projects but one in each program. These are the projects that will provide the data in the first page of my Program report:

image

The slicer is using the Program custom field:

image

To limit the data on this page, I have added page filter using the Project Plan Type field and filtered to “1_Program” projects:

image

So this page shows data for the project tagged with “1_Program” in the Project Plan Type field and in this case, the project tagged with “IT Transformation” which in my data set is the “IT Change Program” project. I don’t have much data on this page but this is just to show the data is for the program level project.

The next two pages show similar details for the program, one shows the details and the other shows some charts (just to add some colour!) but they both work the same way in filtering data that is only relevant at the program level:

image

image

On these pages there are no page level filters set, the tasks, risks and issues visualisations all have a filter applied to only display tasks, risks or issues that are requiring attention at the program level. On the tasks visuals we are using the task level “Escalation Level” field and filtering to only include tasks tagged with “1_Program”:

image

On the risks and issues visuals, we do the the same but use the “Category” field and filter to only include risks or issues tagged with “1_Program”:

image

This provides quick access to data relevant to the program. As we can see, these are very simple examples but the concept can be applied to larger datasets with more fields and data but the first page / report example will only work providing you one have 1 project plan per “program” value tagged with “1_Program” in the “Project Plan Type” Project level field.

That’s it for this short series – I hope that you found it useful!

Categories: Paul Mather, Work Tags:

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

August 10, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

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/

Categories: Paul Mather, Work Tags:

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 3

January 16, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

Following on from my 2nd post in this mini series on reporting including HTML formatting in Power BI, in this post we will look at a couple more options that will refresh in the Power BI App Service. If you missed the previous posts, the links are below:

Part 1: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

Part 2: https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

The options we will look at in this post require a process to get the Project HTML data into a source that can be queried from Power BI with one call. Firstly I will demonstrate a simple PowerShell script that will get the data and write this to a SharePoint list on the PWA site. This is process is very similar to a Project Online snapshot solution starter script I published back in August 2016: https://pwmather.wordpress.com/2016/08/26/projectonline-data-capture-snapshot-capability-with-powershell-sharepoint-office365-ppm-bi/ Once you have a script running to capture the data on the defined scheduled you will see something similar to the screen shot seen below:

image

Here you can see my process has run twice, once back in August when I first wrote this script and just now when I ran it again. As this is based on sales demo data, you can see in the two expanded examples the data has not changed but in a real world usage I’d like to think the data would have changed / been updated! Having the data in one list enables a SharePoint OData call from Power BI, as I have included the ProjectId in the data on the list, this can easily be joined with the data from the main Project OData Reporting API. As this data is in a SharePoint list you might need to consider the user permissions / access to the list. If this was running on a schedule, either from a Windows Scheduled task if on-prem or maybe a scheduled Azure Function if you wanted to make use of Azure PaaS, set the schedule to run before the reports were due allowing time for this process to complete. I won’t cover the PowerShell script in detail here as I will create a dedicated post for that in a week or two, but I will highlight the changes required if you were to start with the OData snapshot example.

  • The first API called was updated in this example to change the select query to just return the ProjectId:

image

  • After the while statement, the script will start a foreach loop and set the ProjectId to a variable:

image

  • Then the REST URL is constructed and the ProjectId is passed in. The select query includes the Project Name, Project ID and the Multiline custom fields that I want to include. I then make the various REST calls in a try / catch block, firstly to get the data:

image

  • Then to write the data to the SharePoint list:

image

Once that runs successfully with an account that has full access to all projects and edit access the the SharePoint list, your target list will contain all of the projects along with the selected fields. As mentioned, I will post this full script in a week or two once I get a chance to tidy a few bits up in the code sample but hopefully the screenshots of the changes along with the snapshot example PowerShell script, there will be enough pointers to get started. Now the data is in a single source, it is very simple to use in Power BI.

In Power BI Desktop add a new OData feed, in the URL field enter the SharePoint list REST URL for the source list, for example the REST URL I used is: https://tenant.sharepoint.com/sites/PWA/_api/web/lists/getbytitle(‘ProjectMutliLineFields’)/Items    where ProjectMutliLineFields is the name of my SharePoint list. Edit the query to launch the Power BI query editor. In this example, my source SharePoint list contains duplicate projects but in my report I want to only see the latest. The steps below will transform the data so that the report only has the latest version for each project record. Rename the query to IDandDate then remove all columns except for the ProjectId and Created columns:

image

Now group by ProjectId and get the Max Created value, I called this column “Latest”:

image

That will give you a list of unique Project IDs using the latest record. Now add a 2nd OData feed and use the same SharePoint list REST URL as in the previous step. Remove columns that are not required, I removed all expect for Title, ProjectId, Created and the multiline fields. Then rename the columns to meaningful names if required:

image

This query will currently contain the duplicate project records based on my example list, next I will merge this query with the IDandDate query using the ProjectId column and the Created/Latest column:

image

Hold down the Ctrl key to select more than one column per table for the merge.

This will add the new column into the table:

image

Click the double arrow on the column heading to expand the column then select the aggregate radio button. On the dropdown menu next to Latest select Maximum:

image

This will show a date value for the latest records, where a null is displayed, there is a duplicate record with a later date:

image

Filter out the null records from the Max of Latest date column and that is it. For the purpose of this blog post, I also added a 3rd query to the Project OData API to show data from the two sources. Close and Apply the data then ensure the relationships are correct, I also set the IDandDate query to be hidden in the report view:

image

Then design your report as needed making use of the same HTML Viewer custom visual:

image

As you can see, this is just a simple example like the others just to highlight the HTML formatting being rendered in Power BI.

Another option without having to write and maintain any custom code or write the data to a SharePoint list does make use of a 3rd party tool that extracts the Project Online data into an Azure SQL database as the data changes in Project Online. This particular tool is developed by the Product Dev team I lead at CPS and is called DataStore. This product is part of our edison365 product suite but is available on its own. This isn’t sales pitch so I won’t go into details here but I just wanted to give another option as some people prefer no code solutions. There are also other software vendors that do similar products for Project Online but I’m not sure if they include the multiline project level fields with the HTML. So using this tool or similar (check they include the HTML fields), you can get all of your Project Online data into an Azure SQL database, as mentioned, the DataStore tool will also include the HTML data as displayed below in the example SQL query below:

image

Power BI can get data from the Azure SQL Server and this data will also refresh in the Power BI App Service.

Feel free to contact me if you have any queries or questions but hopefully that gives you some ideas on including the HTML formatting in your Project Online reports using Power BI!

Categories: Paul Mather, Work Tags:

#ProjectOnline #PowerBI Report – Include #HTML formatting #PPM #PMOT #PowerQuery #OData #REST Part 2

January 5, 2018 Leave a comment
Paul Mather
I am a Project Server and SharePoint consultant but my main focus currently is around Project Server.
I have been working with Project Server for nearly five years since 2007 for a Microsoft Gold Certified Partner in the UK, I have also been awared with the Microsoft Community Contributor Award 2011.
I am also a certified Prince2 Practitioner.

This article has been cross posted from pwmather.wordpress.com (original article)

Following on from my first post discussing including HTML formatting for Project Online Power BI Reports, in this post we will look at a summary of options to get the correct data into Power BI then walkthrough one of those options. In part 3, the final part, we will look at one of the other options to get the data.

For those of you that missed part 1, see the post here: https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

As per the first post, it is very simple to have the data rendered in Power BI to include the HTML formatting, the slightly more tricky part is to get the Project Online data into Power BI with the HTML included.

First, a bit of background on where in Project Online you can access the enterprise project multiline custom fields with the HTML included. As per the first post, you need to access the REST API ({PWAURL}/_api/ProjectServer) to get the data with the HTML included as the OData Reporting API ({PWAURL}/_api/ProjectData) has had the HTML removed. Using the REST API we can view the endpoints at the root:

SNAGHTML59e8e263

This is the REST API to programmatically interact with the Project Online data, you can create, read, update and delete data using this API depending on your access. For this reporting post we only need to read data, carryout the steps with an account that has access to all projects in the PWA instance like an Admin account.

The endpoint we need is /Projects:

SNAGHTML59ef8e46

This will detail all of the projects the logged on user has access to – it is recommended to carry out these steps with an account that has access to all projects in the PWA instance otherwise you might / will see errors in later steps. For each project detailed you will see a few key project level properties including things like Name, Description, Created Date, ID to name a few. It is also possible to navigate from there using the Project ID to get more details for that project. For example you can get the project tasks using the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/Tasks or get the project team using this URL: ProjectServer/Projects(‘{ProjectGUID}’)/ProjectResources. To get the enterprise project level multiline custom fields we need to use the following URL: ProjectServer/Projects(‘{ProjectGUID}’)/IncludeCustomFields. Accessing this URL for the specified Project GUID (replace the placeholder with an actual project GUID) you will see more properties for that project including the multiline custom fields we need:

SNAGHTML59fcff6f

Notice the HTML in the custom field outlined in red in the image above. You would need to do this call for all projects but using the correct Project GUIDs. Also worth pointing out, in this API the custom fields are referenced using the internal names, for example Custom_x005f_4d0daaaba6ade21193f900155d153dd4 rather than the display names. You can use the custom fields endpoint to map the internal names to the display names: /ProjectServer/CustomFields.

So that covers the background on how you access the multiline custom field data that includes the HTML using the REST API, next we look at how to do this from Power BI. What makes it slightly more tricky than just using the normal OData Reporting API is that you have to make a call dynamically for each project GUID if you are using the REST API directly. In this series of posts we will look at calling this API dynamically straight from Power BI (covered later on in this post) but that has a limitation and also another method to get this data from one call / endpoint but that requires a bit of custom code / a 3rd party tool but does remove the limitation / issue. I will cover off the latter option in the 3rd blog post including a code sample / snippet.

Moving on to Power BI and getting this data dynamically and explaining the limitation. This process with follow the same approach I documented a while back to report on project site data using the the SharePoint list REST API: https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-projectonline-projectserver-powerbi-powerquery-bi-office365-excel-ppm/ As per the post above, this will require a custom function and a custom column to call the function. The limitation of this approach is that it works fine in the Power BI Desktop client but the data will not currently refresh in the Power BI App service. There might be workarounds to this limitation but that is beyond the scope of this blog post.

Firstly get a REST URL for one project that includes custom fields, for example I have used this: https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4 – replace the parts highlighted in yellow with details from your PWA instance. In this example I have included just one of my multiline custom fields but include as many multiline fields as required, just separate them using a comma. As mentioned before, use the /CustomFields endpoint to identify the correct custom fields to include in the select statement. You can see below, the example multiline field I have used is called “Status Summary”

SNAGHTML5cb3b4d5

Now add this URL as a data source in Power BI using the Get Data > OData feed option. That will open the Query Editor and show the record:

image

Update the Query Name to something like projectHTMLCFsFunction as this query will be turned into a function. In the Query Editor, on the View tab access the Advanced Editor and you will see your query:

SNAGHTML5cc54192

The full query will be similar to this:

let
    Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘ad641588-f34b-e511-89e3-00059a3c7a00’)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
in
    Source

This needs to be modified to turn this into a parameterised function like below, parts highlighted in yellow are added / edited:

let loadHTMLCFs = (GUID as text) =>
    let
        Source = OData.Feed("https://tenant.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘"&GUID&"‘)/IncludeCustomFields?$Select=Id,Name,Custom_x005f_4d0daaaba6ade21193f900155d153dd4")
    in
        Source
in  loadHTMLCFs

A screen shot below to show the completed query in the Query Editor as the formatting is clearer, bits added / edited are outlined in red:

SNAGHTML5cc4e299

Click Done in the Query Editor 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 other data feeds required in the report, for the purpose of this blog I will just add in the minimum required and that is the default OData Reporting API /Projects endpoint to get the other project fields into the report. In the Query Editor on the Home tab click New Source > OData feed and add in the OData Reporting API URL: https://tenant.sharepoint.com/sites/pwa/_api/ProjectData then select the tables required. For this blog post I have just selected Projects. Using the Query Editor, remove unwanted columns, rename columns etc. You will need to keep at least ProjectId and ProjectType, they are required. For the purpose of the blog post I have just selected ProjectId, ProjectType, ProjectName and ProjectOwnerName. Using ProjectType, filter out ProjectType 7 as this is the Timesheet Project record. Keeping this in the dataset will cause errors later on.

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 GetProjectHTMLCFs then enter the following: projectHTMLCFsFunction([ProjectId]) as seen below:

image

projectHTMLCFsFunction is the name of the function we created earlier and we are passing in the ProjectId. When clicking OK, this might take a while depending on how many projects you have as this will invoke the function for each project and call the REST API, passing in the ProjectId for that row and bring back the records. Once completed you will see the records as below in the new custom column:

SNAGHTML5cdd9337

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the multiline custom fields, in this example I just have one:

image

Click OK and the data will refresh / load then display the data for the multiline columns:

SNAGHTML5ce22300

Notice we have the HTML in the data! Rename the columns for the correct display names then when completed, click Close & Apply. The changes will now be applied to the Power BI Report and load the data. Add in the HTML Viewer custom visual as detailed in blog post 1 then add the data on the the report canvas as you would normally. Ensure that the multiline custom fields use the HTML Viewer custom visual:

image

An example with a normal table visual and the HTML Viewer visual:

image

That’s it, design your Project Status reports to now include the HTML formatting your users have added. Just remember though, this will only refresh in the Power BI Desktop client. It can be published to the Power BI App service but the data will be static and will not update, you would need to open the report in the Power BI Desktop client, refresh it then publish it back into the Power BI service.

Next up in part 3 we will look at a slightly different approach to get the data in Power BI that does enable the report / data to refresh in the Power BI App service.

Categories: Paul Mather, Work Tags:
%d bloggers like this: