Download example #MicrosoftFlow for Syncing #MSProject #Roadmap Row Item Status with #ProjectOnline Task Status #CDS #PowerPlatform #MSFlow #REST #SharePoint #WorkManagement #Office365

June 19, 2019 at 11:17 pm | Posted in Add-on, Administration, App, Configuration, Customisation, Functionality, Information, Reporting, Workarounds | Leave a comment
Tags: , , , , , , ,

Following on from a recent blog post where I demonstrated an example Microsoft Flow for syncing the Roadmap row item status with the associated Office 365 Project Online Task status, I have now made this solution starter Flow available as a package that can be downloaded and imported. For those of you that missed the previous blog post, a link can be found below here: https://pwmather.wordpress.com/2019/05/27/sync-msproject-roadmap-row-item-status-with-projectonline-task-status-using-microsoftflow-cds-powerplatform-msflow-rest-sharepoint-workmanagement-office365/

FlowImage

The Flow package can be downloaded from the Microsoft Gallery here: https://gallery.technet.microsoft.com/Flow-to-Sync-Roadmap-item-44174a4b

Once downloaded the Flow can be imported, here is a Microsoft Flow blog post on exporting and importing Flow packages: https://flow.microsoft.com/en-us/blog/import-export-bap-packages/

Once imported and the connections all set – this will require a Flow P1 or P2 license as it uses the CDS connector, ensure the account has the correct access to Project Online and the CDS, open the flow and update the trigger and actions as these will currently point to one of my demo tenants:

  1. Update the “When a project is published” trigger with your PWA URL
  2. Update the “GetTaskHealth” action the correct site address for your PWA URL
  3. Update the “GetTaskHealth” action Uri to use the correct task level field, replace “RoadmapHealth” as needed
  4. Update the Switch action to use the correct task custom field – the expression would be items(‘Apply_to_each’)[‘FieldName’] – replace the field name with the correct task field
  5. Ensure the Case statements are updated to match the possible values in your custom field and map to the correct roadmap status value:
      • On Track = 0
      • Potential Problem = 1
      • At Risk = 2
      • Complete = 10
      • Not Set = 100
  6. Update the “List records” action to point to the correct environment
  7. Update the “Update a record” action to point to the correct environment

Now save the Flow and test it.

Hopefully you find this useful as a solution starter.

Advertisements

Sync #MSProject #Roadmap Row Item Status with #ProjectOnline Task Status using #MicrosoftFlow #CDS #PowerPlatform #MSFlow #REST #SharePoint #WorkManagement #Office365

May 27, 2019 at 9:36 pm | Posted in Administration, Configuration, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , ,

At the recent Microsoft PPM Summit in Prague last week, Chris Boyd from the Microsoft Project Product team demonstrated syncing the Roadmap row item status with the task status from the Project Online schedule. This was done using compiled code in a console application which worked well. I set myself a little challenge to do something similar but all from Microsoft Flow. Being a public holiday here in the UK, I found an hour spare today to tackle this. In this blog post I walkthrough the Flow actions required to do this. A summary image can be seen below:

image

As you can see, this flow is triggered on a Project Online Project Publish. I then execute a REST query on the Project Data API using the send an HTTP request to SharePoint action:

image

I pass in the published project ID and select the TaskID and the custom field I’m using to set the Roadmap row item status. I created a custom field called “Roadmap Health” that was a lookup with the same status values as Roadmap but you can use any field and values, just update the query and Flow Switch action as needed.

I then create a new variable called “Health” and set the Type to an Integer:

image

I then add an Apply to each action and pass in the body(‘GetTaskHealth’)[‘value’] expression to use the output from my REST call:

image

I then have a condition check action to check for null values in the Roadmap Health field, the expression for the field is items(‘Apply_to_each’)[‘RoadmapHealth’] then null is also added via an expression:

image

You could remove the need for the condition check by filtering out the nulls in the REST call. If this is false, nothing happens as there is no status to sync, if this true the next action is a  Switch, the field I’m using in the switch is referenced using the expression: items(‘Apply_to_each’)[‘RoadmapHealth’]

image

Then for each possible value from the task level custom field you are using, map this to one of the Roadmap status’ by setting the Health variable, for example, when the Roadmap Health task field value is equal to “On Track” I set the variable to 0:

image

The Roadmap status enumerations are below:

  • On Track = 0
  • Potential Problem = 1
  • At Risk = 2
  • Complete = 10
  • Not Set = 100

Once that is completed for all possible outcomes, the next action is the List records Common Data Service action:

image

Here the Flow returns the Roadmap Item Link record for the TaskId passed in, the TaskId is referenced using the following expression: items(‘Apply_to_each’)[‘TaskId’]

The next action is another Apply to each action, Flow does this automatically as the List records would typically return more than one record:

image

The output used for this action is the default List records value from the Dynamic Content option. Then the final action within the Flow is the Common Data Service Update a record:

image

This action updates the Roadmap Items, I then pass in the List records Roadmap Item value from the Dynamic content panel, this is the Roadmap Item Id value. I also pass in the Health variable in the “Health Status Value” field. The flow will loop through all Project tasks and update the equivalent Roadmap row item status, pretty simple for a no / low code solution using only Microsoft Flow!

Over the next few days I will publish a short video for this Flow on my YouTube channel and also probably provide a download link for this Flow template to help as a solution starter.

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

March 16, 2019 at 5:38 pm | Posted in Administration, App, Configuration, Customisation, Functionality, Information, Reporting | 1 Comment
Tags: , , , , , , , , , , ,

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!

#Project Roadmap #AzureDevOps #AzureBoards #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatform #CDS

March 15, 2019 at 6:26 am | Posted in Add-on, Administration, App, Configuration, Functionality, Information, Reporting | 7 Comments
Tags: , , , , , , , , ,

All of my previous posts on the new Roadmap service for Project has been based on Project Online projects, In this post we take a quick look at using Azure DevOps projects in Roadmap. This post wont go into much detail about the new Roadmap service, only how to use Azure DevOps projects in Roadmap. For details on the Roadmap service see this summary post: https://pwmather.wordpress.com/2019/03/01/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom-dynamics365/

Firstly ensure you have access to a project in Azure DevOps. This project will need work items (Epics, User Stories, Features, Tasks etc.) that have two fields used that might not be standard in your organisation depending on the process used in the project, these are Start Date and Target Date. To do this, for each type of work items that you want to sync in Roadmap, from the Work Items board in the Azure DevOps project, click “New Work Item” then the item such as Epic:

image

This list of work items might vary depending on which process your project uses, this example project just uses the basic process. When the Epic page loads, click Customize from the Actions menu seen below:

image

Click New Field:

image

Then set the option to “Use an existing field” and select “Start Date” and click Add Field:

image

Repeat this to add “Target Date” then update the layout to move the two new fields where you want them:

image

Repeat this process for other work items as needed such as Tasks, User Stories or Features depending on what board process your project uses. For example, if your project uses the default Agile process you will just need to update the User Story work item to add these fields. Now with some example Epic work items created in my test Azure DevOps project, each with a start date and target date, I can move over the Roadmap.

I’ve added a new row to my Roadmap for the Azure DevOps project, on the “Connect to a project” menu, I will select “Azure Boards”:

image

Ensure the Azure DevOps organization URL is correct and validated, then select your project:

image

Then the Flow connection details will appear:

image

Sign in if prompted. Then click Connect:

image

Then search for items to add:

image

Then click Add. Now the items will be added to the row:

image

This data will be kept in sync using Microsoft Flow just like the Project Online projects. The Project Online projects Flow runs every 5 minutes by default where as the Azure Board project Flow runs every hour.

In the next post we will look to combine the Azure DevOps Project Board data with the Roadmap data in Power BI.

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

March 8, 2019 at 11:04 pm | Posted in Add-on, Administration, App, Configuration, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , , , ,

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.

#Project Roadmap #CDS #App Overview #PPM #ProjectManagement #MSProject #ProjectOnline #Office365 #PowerPlatfom #Dynamics365

March 1, 2019 at 12:57 pm | Posted in Administration, App, Configuration, Customisation, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , ,

Have you used the new Roadmap feature from Microsoft yet? If not, why not! Over the last few months I posted several Roadmap related blog posts. In this blog post I have summarised all of the posts so that all of the post links are available in one place.

Roadmap is live – this post covered an overview for the end user: https://pwmather.wordpress.com/2018/12/19/project-roadmap-is-live-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom/

Roadmap CDS App over view – this covered the CDS details (entities etc.) and reporting:

Roadmap PWA Project Center integration: https://pwmather.wordpress.com/2019/01/16/projectonline-pwa-project-details-page-integration-with-project-roadmap-office365-ppm-powerplatform-msflow/

Then the Power BI Report pack for Roadmap: https://pwmather.wordpress.com/2019/01/30/project-roadmap-powerbi-report-pack-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-dynamics365/

image

Combining Project Online data with Roadmap data in the Power BI Report pack: https://pwmather.wordpress.com/2019/03/08/project-roadmap-powerbi-report-pack-with-projectonline-data-ppm-projectmanagement-msproject-office365-powerplatform-dynamics365-cds-odata/

Roadmap with Azure Board work items: https://pwmather.wordpress.com/2019/03/15/project-roadmap-azuredevops-azureboards-ppm-projectmanagement-msproject-projectonline-office365-powerplatform-cds/

Combining Azure Board data with Roadmap data in the Power BI Report pack: https://pwmather.wordpress.com/2019/03/16/project-roadmap-powerbi-report-pack-with-azureboards-data-ppm-projectmanagement-msproject-office365-powerplatform-dynamics365-cds-odata-azuredevops/

If you haven’t used Roadmap yet, take a look when you can! Also, see what you think of my Roadmap Power BI Report pack to get you started on reporting.

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

January 30, 2019 at 12:19 am | Posted in Administration, Functionality, Information, Reporting | 3 Comments
Tags: , , , , , , , ,

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.

This does use the Power BI Common Data Service for Apps (Beta) connector – so this connector could change when released and the report might require some re-work.

I hope you like it and find it useful.

#ProjectOnline #Roadmap sync error – has the project been deleted? #MSFlow #PowerPlatform #PPM

January 24, 2019 at 10:37 pm | Posted in Administration, Fixes, Functionality, Information | Comments Off on #ProjectOnline #Roadmap sync error – has the project been deleted? #MSFlow #PowerPlatform #PPM
Tags: , , , ,

In the following scenario you may see an error that could be misleading. So you have set up a Roadmap with rows that are connected to Projects in Project Online – all good so far. Then your access changes in Project Online and you no longer have access to the linked Projects. You then later access your Roadmap (at least 5 minutes after your access changed so the Flow runs) and you will see:

image

Hover over the red cross and you see a tooltip for “Project has been deleted in Project Online”:

image

Then click the row to load the row details and you see another message stating: “We couldn’t sync updates to this project because it has been deleted in Project Online.”

image

If you try to add more rows you then get a status bar appear:

image

Click the Project Name under the connection to launch the Project Detail Page for that project and you will see this message stating that you don’t have access when the Project Detail Page loads for that project in PWA:

image

The List Project action in the Flow returns a 404:

image

The value in the response body is: "This project either does not exist, or has been deleted.  Only QueueJobs can be accessed from a deleted project." The Flow then continues and updates the roadmap data to mark the link as deleted.

For this scenario the error is misleading, the issue is that your account that was used to set up the link to the Project in Project Online now longer has access to that project. The List Project Flow Action which uses the CSOM API which is security trimmed. If your account is later then given access to the linked Project in Project Online, the next time the Flow runs, that row will update as expected in the Roadmap if the Project has been updated in Project Online within the 15 minute last project publish check in the Flow.

Just something to be aware of if you come across this error – your project might not have been deleted!

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

January 22, 2019 at 7:39 pm | Posted in Administration, Configuration, Functionality, Information, Reporting | 2 Comments
Tags: , , , , , , , ,

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.

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

January 7, 2019 at 6:03 pm | Posted in Administration, Configuration, Functionality, Information | 3 Comments
Tags: , , , , , , ,

This post follows on from part 1: https://pwmather.wordpress.com/2019/01/01/project-roadmap-cds-app-overview-ppm-projectmanagement-msproject-projectonline-office365-powerplatfom-dynamics365-part-1/ In part 1 we had a quick overview of the solutions used in the CDS app for the Roadmap service. In this post we will look at the fields used by the Roadmap service and take a look at some of the data in the Portfolio Service CDS database. As you know, this new feature is known as Roadmap to the end users, but the backend is known as the Portfolio Service, hence both names being used here.

Firstly we will have a look at some of the entities and field definitions from the Portfolio Service solution PowerApps admin interface that we briefly accessed at the end of part 1:

image

As you can see from the screen shot above, there are 6 entities deployed in the Portfolio Service solution. Each of these entities have various other artefacts such as views, keys, fields etc. For this post, we will just look at the fields. We wont explore all of the entities or all of the fields in each entity, but we will look at some of the key entities and fields used by the Roadmap service.

Firstly we will look at the Roadmap entity in the PowerApps portal:

image

You can scroll along the grid to see the properties such as description etc. This entity contains the roadmap details – the roadmap properties and rows added to the roadmap. Some of the key fields here for us as end users for reporting etc. are:

  • msdyn_name – this field will contain the Roadmap name and the row name
  • msdyn_type – this is used to determine the type – either a Roadmap which is type 0 or a row which is type 1
  • msdyn_roadmapid – this is the roadmap / row GUID
  • msdyn_parentroadmapid – this is contains the roadmap GUID for the rows added to a roadmap
  • msdyn_groupaadid – this is the associated Office 365 group ID

The next entity is the Roadmap Item:

image

This entity contains the roadmap item details – items added to rows on the roadmap and items added directly to the roadmap like key dates. Some key fields are:

  • msdyn_name – this field will contain item or key date name
  • msdyn_duedate – this field will contain the due date for row items
  • msdyn_startdate – this field will contain the start date for row items
  • msdyn_status – this is the status for the item such as At Risk, On Track etc.
  • msdyn_type – this is used to determine the type – either a key date which is type 0 or a phase which is type 1
  • msdyn_roadmapid – this is the roadmap / row GUID
  • msdyn_roadmapitemid – this contains the roadmap item GUID

The next entity is the Roadmap Item Link:

image

This entity contains the roadmap item link details – items added to rows on the roadmap that are linked to external projects (Project Online or Azure Boards currently). Some key fields are:

  • msdyn_externalname – this field will contain the external task name
  • msdyn_externalduedate – this field will contain the external due date for row items
  • msdyn_externalprojecttaskid – this is the external task GUID from the external project
  • msdyn_externalstartdate – this field will contain the external start date for row items
  • msdyn_externaltype – this is used to determine the type – either a key date which is type 0 or a phase which is type 1
  • msdyn_externalurl – this is the link to the external project
  • msdyn_roadmapid – this is the roadmap row GUID
  • msdyn_roadmapitemid – this contains the roadmap item GUID
  • msdyn_roadmapitemlinkid – this contains the roadmap item link GUID

The final entity that we will look at is the Roadmap Row Link:

image

This entity contains the roadmap row link details – rows on the roadmap that are linked to external projects (Project Online or Azure Boards currently). Some key fields are:

  • msdyn_externalname – this field will contain the external project name
  • msdyn_externalprojectid – this is the project GUID from the external project
  • msdyn_externalurl – this is the link to the external project
  • msdyn_refreshenddate – this is the finish time the external project last synchronised with the CDS data via the Flow
  • msdyn_refreshstartdate – this is the start time the external project last synchronised with the CDS data via the Flow
  • msdyn_roadmapid – this is the roadmap row GUID
  • msdyn_roadmaprowlinkid – this contains the roadmap row link GUID

Lets explore the data in the Roadmap entity. There are several ways in which we can do this, in code using the Organization Service or the Web API: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/work-with-data-cds or there is a Connector for Power BI: https://powerapps.microsoft.com/en-us/blog/cds-for-apps-powerbi/ For this I will use Power Query in Power BI but also show you how to access the Web API too. To access the Roadmap data in Power BI you will need the server URL. There are at least two ways (probably more) to get the correct server URL for the CDS Roadmap uses. Firstly via the Office 365 Admin Center using the steps below:

Access the Office 365 Admin Center > Admin Centers > Dynamics 365:

image

Then click open on the correct instance and you will see the server URL in the URL bar:

image

The other way, if you do not have access to the Admin Center is by using the browser dev tools. Access Project Home > Press F12 / open the dev tools > Access a Roadmap > In the Network tab, find the network call GetCdsEndpoint then check the Response, you will see a cdsurl property:

image

Once you have the server URL you can either use the Power BI connector or the Web API. The the simplest way to demo viewing this data using the Web API is via the Browser. In this example. Add the following to the end of the server URL: /api/data/v9.1 – you can use a different version, at the time of writing 9.1 was the latest on my tenant. Going to the root Web API URL will return the JSON for all of the entities available. Append the entity that you want to access to the end of the URL and you will see all of the data available in the entity:

image

As the Web API implements the OData protocol you can use the standard query options such as $select or $filter. Here I’m just selecting the name and type from the roadmaps entity:

https://org05724544.crm11.dynamics.com/api/data/v9.1/msdyn_roadmaps()?$select=msdyn_name,msdyn_type

image

Here I’m filtering for roadmaps only as the roadmaps entity contains roadmaps and rows:

https://org05724544.crm11.dynamics.com/api/data/v9.1/msdyn_roadmaps()?$filter=msdyn_type eq 0

image

To easily make sense of the data you need to format the JSON. Power BI is easier to visualise the data.

In Power BI click Get Data > More > Online Services > Common Data Service for App (Beta):

image

Select it then press connect. Accept the warning about being a preview connector (something to be aware of!). Now enter the server URL:

image

Now you can see all of the tables / entities that are in this CDS service, for the purpose of this blog post I will just expand Entities then select Roadmap, Roadmap Item, Roadmap Item Link and Roadmap Row Link:

image

Then click Edit to load these in the query editor. I’ve removed some of the default fields to clean up the data. Here is one of my roadmaps from the msdyn_roadmap table via Power Query in Power BI:

image

Here is a row from my roadmap:

image

Here is a key date added to my roadmap from the msdyn_roadmapitem table:

image

Here is a task (known as a phase in Roadmap) added to a row in the roadmap:

image

Here is a milestone (known as a key date in Roadmap) added to a row in the roadmap:

image

Here is an item link for a task linked to a row in Roadmap from msdyn_roadmapitemlink table:

image

Here is an row link for a project linked to a row in Roadmap from msdyn_roadmaprowlink table:

image

In the final part of this mini series, we will look at following on from this and creating a simple report in Power BI for the Roadmap data and bring in some Project Online data.

Next Page »

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