Published
Mar 06 2019 03:13 PM
10.2K
Views
First published on MSDN on Aug 02, 2017
I was very fortunate recently to get the chance to present at Microsoft Ready, an internal worldwide training event held for the first time in Las Vegas – after running for many years in Seattle. My session was on Microsoft Planner and I can’t repeat everything here as it was an internal event – but wanted to share part of the session that covered reporting in Planner. We don’t have any built in reporting yet, but using MSGraph and/or Flow and some Azure bits and pieces you can certainly report against your Planner information.
In Part 1 I will cover the basics of reporting in Planner, covering what makes sense when reporting, and what are the key data elements of Planner. In Part 2 I will look at Flow and how you could use that as a way to propagate your Planner information to a better reporting store, then I’ll finish with Part 3, where I pull all my Planner data out using Python and load up into Azure Cosmos DB before reporting against that data with Power BI.
But before getting into the technical stuff – what type of reports make sense for Planner? What are you looking for and what makes sense to report on? Unlike Project Online, in Planner there is no concept of ‘work’ or ‘effort’ – and although the tasks do have a percentComplete field this is used in Planner as being 0, 50 or 100 – meaning not started, in progress or finished. So when reporting in Planner, are you really comparing apples with apples – or are they pears? And if they are apples, are they the same apples? We need consistency.
If you’ve seen my previous blog posts on Planner you may have come across my PowerShell examples that allow you to clone plans by making a copy of the categories, buckets, tasks, checklists and assignments into a new plan. One win here is consistency of categories (the colored fly-outs) and buckets. This ensures that you can be consistent when reporting across all your plans – maybe having a regular count of how many tasks you have in each bucket – or how many are tagged with each category. Both buckets and categories are unique to a Plan – but being consistent across plans enables you to then report consistently. For example if you had a standard set of categories for most of your plans (or different sets of categories for sets of plans of the same type) then you could group these plans based on the category. More later, with an example in Part 3.
I’ve mentioned tasks, buckets, categories etc. but lets take a look at the various Graph calls that you’d find useful when working with Planner data along with the datasets returned. One challenge here is that some of the calls will only get your plans – so depending on your requirements you might need to ensure that your reporting ‘person’ or service account – is a member for all the groups of interest. We will likely be adding more APIs in Graph to facilitate reporting at some point.
First we can use the https://graph.microsoft.com/v1.0/me/Planner/Plans call to get all of the plans that we are subscribed to – and the concept of being subscribed needs a little explanation. If you create a plan then you are subscribed – and others can become subscribed too. However, if you create a plan programmatically, for example using the PowerShell script you do not become subscribed until you open the plan (I found this out the hard way). Also there is a limit to numbers of subscriptions – so you can find yourself unsubscribed in some cases.
You can follow along in the Graph Explorer - https://developer.microsoft.com/en-us/graph/graph-explorer and the response to the me/Planner/Plans call will look something like this, where this is just the first plan of many:
It matches up to the UI of the plan:
The title in this case is Template, and the other information is that this plan was created by user with id "cf091cb1-dc23-4e12-8f30-b26085eab810" – which happens to be me. This was created by the Planner web app – but we could see a different application id if for example we had used a PowerShell script and used a client id. The owner field is actually the id of the Group that this plan belongs to. For plans created through the Planner web app this will usually be a 1 to 1 relationship, but with the advent of Microsoft Teams you can now see multiple plans ‘owned’ by the same Group. The eTag value can be thought of as a version identifier. If the same eTag is found then nothing has changed. This is used more for making updates, where the current eTag must be passed in to any update call to ensure you are updating from the current version – and someone else hasn’t updated the record since you made the last read.
Another way to get all of our plans would be to get them from the Groups. With the https://graph.microsoft.com/v1.0/groups?$filter=groupTypes/any(c:c+eq+'Unified') call we can get all of our Office 365 Groups. If we omitted the filter for groupTypes we would also see any Exchange Groups (DLs) we might have. Once we have the Group – like this one:
we can use the id "6ff15978-94d4-414f-a497-295a245718bc" to get all the plans in the Group using the call to https://graph.microsoft.com/v1.0/groups/6ff15978-94d4-414f-a497-295a245718bc/Planner/Plans
Here we pull back the "Fall New Employee Orientation" plan, which was created using my PowerShell – which had the client id "3a32234d-52b2-4224-a4cd-aaa9819e66af".
In this example using the id of a Group that was created when adding a new Team, which also has two Planner tabs added – I can see multiple Plans in the group - https://graph.microsoft.com/v1.0/groups/400ea8d7-334b-4d26-b963-8fe400fc675a/Planner/Plans
I can also see these were created via Teams as the application id is different - "5e3ce6c0-2b1f-4285-8d4b-75ee78787346".
Using either of the approaches above to get the Plans, and their ids I can then use other Graph calls to get the rest of the items of interest.
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_ID>/Details will get me the Plan Details – notably the category descriptions:
.
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_id>/Buckets will get me the buckets, and even though I may have the same named buckets across my plans, particularly if I am using a template, each will have a unique id for the same named bucket in different plans. We need to use this to correctly match up the tasks to buckets – but we might want to do some transforming of the data to use the name instead for our reporting:
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_id>/Tasks gets us down to the meat in Planner – and the tasks. Points of interest here are the planId and bucketId so we know where these tasks fit – then the title, percentComplete and the assignments collection are the most commonly required fields. The first GUID in each element of the assignments collection is the identifier for the assigned resource – in this case Katie Jordan – which we will be getting from the Group Members call later.
https://graph.microsoft.com/v1.0/Planner/Tasks/<Task_id>/Details will return the task details – which contains any references, checklists (and their state) and any description – as well as the id to tie it back to the right task.
The final call we will make gets us the members. This is needed for each groupId – which we can get either from the Groups if we used that route, or from the Plan owner if we started with our Plans. https://graph.microsoft.com/v1.0/Groups/<Group_id (Plan owner)/Members For reporting purposes we may just need the id and displayName – and we would need to de-dupe after getting all our members back.
That is it for Part 1 – a quick run around the Graph Explorer to help you understand which data elements you would need as well as highlighting some of the challenges and considerations when you start a reporting journey with Planner. And it goes without saying, but I’ll say it anyway, that for any reporting journey you need to know the destination before setting out – so be sure you have a good understanding of the outputs so you’ll know if all the inputs are present that you need – and in a suitable format.
I was very fortunate recently to get the chance to present at Microsoft Ready, an internal worldwide training event held for the first time in Las Vegas – after running for many years in Seattle. My session was on Microsoft Planner and I can’t repeat everything here as it was an internal event – but wanted to share part of the session that covered reporting in Planner. We don’t have any built in reporting yet, but using MSGraph and/or Flow and some Azure bits and pieces you can certainly report against your Planner information.
In Part 1 I will cover the basics of reporting in Planner, covering what makes sense when reporting, and what are the key data elements of Planner. In Part 2 I will look at Flow and how you could use that as a way to propagate your Planner information to a better reporting store, then I’ll finish with Part 3, where I pull all my Planner data out using Python and load up into Azure Cosmos DB before reporting against that data with Power BI.
But before getting into the technical stuff – what type of reports make sense for Planner? What are you looking for and what makes sense to report on? Unlike Project Online, in Planner there is no concept of ‘work’ or ‘effort’ – and although the tasks do have a percentComplete field this is used in Planner as being 0, 50 or 100 – meaning not started, in progress or finished. So when reporting in Planner, are you really comparing apples with apples – or are they pears? And if they are apples, are they the same apples? We need consistency.
If you’ve seen my previous blog posts on Planner you may have come across my PowerShell examples that allow you to clone plans by making a copy of the categories, buckets, tasks, checklists and assignments into a new plan. One win here is consistency of categories (the colored fly-outs) and buckets. This ensures that you can be consistent when reporting across all your plans – maybe having a regular count of how many tasks you have in each bucket – or how many are tagged with each category. Both buckets and categories are unique to a Plan – but being consistent across plans enables you to then report consistently. For example if you had a standard set of categories for most of your plans (or different sets of categories for sets of plans of the same type) then you could group these plans based on the category. More later, with an example in Part 3.
I’ve mentioned tasks, buckets, categories etc. but lets take a look at the various Graph calls that you’d find useful when working with Planner data along with the datasets returned. One challenge here is that some of the calls will only get your plans – so depending on your requirements you might need to ensure that your reporting ‘person’ or service account – is a member for all the groups of interest. We will likely be adding more APIs in Graph to facilitate reporting at some point.
First we can use the https://graph.microsoft.com/v1.0/me/Planner/Plans call to get all of the plans that we are subscribed to – and the concept of being subscribed needs a little explanation. If you create a plan then you are subscribed – and others can become subscribed too. However, if you create a plan programmatically, for example using the PowerShell script you do not become subscribed until you open the plan (I found this out the hard way). Also there is a limit to numbers of subscriptions – so you can find yourself unsubscribed in some cases.
You can follow along in the Graph Explorer - https://developer.microsoft.com/en-us/graph/graph-explorer and the response to the me/Planner/Plans call will look something like this, where this is just the first plan of many:
It matches up to the UI of the plan:
The title in this case is Template, and the other information is that this plan was created by user with id "cf091cb1-dc23-4e12-8f30-b26085eab810" – which happens to be me. This was created by the Planner web app – but we could see a different application id if for example we had used a PowerShell script and used a client id. The owner field is actually the id of the Group that this plan belongs to. For plans created through the Planner web app this will usually be a 1 to 1 relationship, but with the advent of Microsoft Teams you can now see multiple plans ‘owned’ by the same Group. The eTag value can be thought of as a version identifier. If the same eTag is found then nothing has changed. This is used more for making updates, where the current eTag must be passed in to any update call to ensure you are updating from the current version – and someone else hasn’t updated the record since you made the last read.
Another way to get all of our plans would be to get them from the Groups. With the https://graph.microsoft.com/v1.0/groups?$filter=groupTypes/any(c:c+eq+'Unified') call we can get all of our Office 365 Groups. If we omitted the filter for groupTypes we would also see any Exchange Groups (DLs) we might have. Once we have the Group – like this one:
we can use the id "6ff15978-94d4-414f-a497-295a245718bc" to get all the plans in the Group using the call to https://graph.microsoft.com/v1.0/groups/6ff15978-94d4-414f-a497-295a245718bc/Planner/Plans
Here we pull back the "Fall New Employee Orientation" plan, which was created using my PowerShell – which had the client id "3a32234d-52b2-4224-a4cd-aaa9819e66af".
In this example using the id of a Group that was created when adding a new Team, which also has two Planner tabs added – I can see multiple Plans in the group - https://graph.microsoft.com/v1.0/groups/400ea8d7-334b-4d26-b963-8fe400fc675a/Planner/Plans
I can also see these were created via Teams as the application id is different - "5e3ce6c0-2b1f-4285-8d4b-75ee78787346".
Using either of the approaches above to get the Plans, and their ids I can then use other Graph calls to get the rest of the items of interest.
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_ID>/Details will get me the Plan Details – notably the category descriptions:
.
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_id>/Buckets will get me the buckets, and even though I may have the same named buckets across my plans, particularly if I am using a template, each will have a unique id for the same named bucket in different plans. We need to use this to correctly match up the tasks to buckets – but we might want to do some transforming of the data to use the name instead for our reporting:
https://graph.microsoft.com/v1.0/Planner/Plans/<Plan_id>/Tasks gets us down to the meat in Planner – and the tasks. Points of interest here are the planId and bucketId so we know where these tasks fit – then the title, percentComplete and the assignments collection are the most commonly required fields. The first GUID in each element of the assignments collection is the identifier for the assigned resource – in this case Katie Jordan – which we will be getting from the Group Members call later.
https://graph.microsoft.com/v1.0/Planner/Tasks/<Task_id>/Details will return the task details – which contains any references, checklists (and their state) and any description – as well as the id to tie it back to the right task.
The final call we will make gets us the members. This is needed for each groupId – which we can get either from the Groups if we used that route, or from the Plan owner if we started with our Plans. https://graph.microsoft.com/v1.0/Groups/<Group_id (Plan owner)/Members For reporting purposes we may just need the id and displayName – and we would need to de-dupe after getting all our members back.
That is it for Part 1 – a quick run around the Graph Explorer to help you understand which data elements you would need as well as highlighting some of the challenges and considerations when you start a reporting journey with Planner. And it goes without saying, but I’ll say it anyway, that for any reporting journey you need to know the destination before setting out – so be sure you have a good understanding of the outputs so you’ll know if all the inputs are present that you need – and in a suitable format.