Skip to main content

Reporting on Microsoft Planner or Microsoft Staffhub!

Reporting on Planner or StaffHub!


For a long time I have been waiting for the reporting API for Planner and Staffhub. Today I got tired of waiting and found another way to do it.

I manage to create a PowerBI report based on all the planner plans I have access to and the same method will work for Staffhub and properly many other services such as Microsoft Tasks, Microsoft Forms, etc. 


How?

Easy, but requires some Azure, Flow and PowerBI knowledge.
These are the steps to I used:
1.       Create an Azure Blob to hold the data
2.       Create a Flow to save Planner data in the Blob
3.       Access the Blob from PowerBI

Azure – Blob!

So first, lets create a Blob.
No, not that kind of Blob.
Access the Azure portal - https://portal.azure.com/
Select New and search for “storage account”
Fill out the form and click create.
I kept all settings to their default
Access the new storage and locate/copy the Storage Account Name and Access Key – you will need this for connections to Flow and PowerBI.
Next let’s create a Blob Container and call it “planner”.
That’s it, now we are ready to store data.
Hint: the Microsoft Azure Storage Explorer is a nifty tool to see and manage files in the Storage, download it here:

Flow ->

Go to Flow and create a new blank Flow: https://emea.flow.microsoft.com/
Configure the following steps and attributes. It is pretty straight forward and I have added notes in the Flow to explain how it is created. The trick is the last step where data is saved to Azure, everything else is pretty standard.

Note: Remember the New Line/base64 expression when the variables are populated. if this expression is not inserted, PowerBI will not understand the file content and import all the data as one line.
That's it - Save and Run the Flow.

PowerBI :-)

Now to the fun stuff – Let's load the data into PowerBI.
Create a new connection and chose Azure Blob Storage.
Type in the storage account that you copied earlier and select the files that Flow have saved.
Load the data.

Now we have the Planner data in PowerBI and with some M skills we can create cool reports.

Other thoughts...
Even though we can extract data, Planner does not have the same rules as other planning tools - this makes it more difficult to create a good portfolio overview. Ex. Due date and start date are not mandatory so you might have a lot of tasks which that you do not know when is starting or ending.
Flow is limited to only extract certain data - one thing I really miss is the possibility to extract the assigned resources. it might be possible through the "Body" element of the task (which is base64 encoded.


Technical data:
PowerBI formula:

Plans:
let
    Source = AzureStorage.Blobs("projectonlinefudev"),
    planner1 = Source{[Name="planner"]}[Data],
    #"https://projectonlinefudev blob core windows net/planner/_plans" = planner1{[#"Folder Path"="https://projectonlinefudev.blob.core.windows.net/planner/",Name="plans"]}[Content],
    #"Imported CSV" = Csv.Document(#"https://projectonlinefudev blob core windows net/planner/_plans",[Delimiter=";", Columns=13, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type datetime}, {"Column4", type datetime}, {"Column5", Int64.Type}, {"Column6", type logical}, {"Column7", type datetime}, {"Column8", type text}, {"Column9", type datetime}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Task Id"}, {"Column2", "Task Title"}, {"Column3", "Start date"}, {"Column4", "Due date"}, {"Column5", "Percent complete"}, {"Column6", "Has Description"}, {"Column7", "Created date"}, {"Column8", "Created By"}, {"Column9", "Completed date"}, {"Column10", "Plan Id"}, {"Column11", "Plan Title"}, {"Column12", "Bucket Id"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Bucket Id"},Buckets,{"Column1"},"Buckets",JoinKind.LeftOuter),
    #"Expanded Buckets" = Table.ExpandTableColumn(#"Merged Queries", "Buckets", {"Column3", "Phase"}, {"Buckets.Column3", "Buckets.Phase"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Buckets",{{"Buckets.Column3", "Bucket"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Task Id", "Bucket Id", "Column13"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Duration", each if Number.From([Due date]-[Created date]) <= 0 or [Due date] = null or [Created date] = null  then 1 else Number.From([Due date]-[Created date])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type number}})
in
    #"Changed Type1"

Buckets:
let
    Source = AzureStorage.Blobs("projectonlinefudev"),
    planner1 = Source{[Name="planner"]}[Data],
    #"https://projectonlinefudev blob core windows net/planner/_buckets" = planner1{[#"Folder Path"="https://projectonlinefudev.blob.core.windows.net/planner/",Name="buckets"]}[Content],
    #"Imported CSV" = Csv.Document(#"https://projectonlinefudev blob core windows net/planner/_buckets",[Delimiter=";", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Lower([Column3])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Bucket"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Phase", each if
Text.Contains([Bucket], "opgave") or
Text.Contains([Bucket], "%") or
Text.Contains([Bucket], "progress") or
[Bucket] = "started" or
Text.Contains([Bucket], "doing")
then "In Progress"

else if
Text.Contains([Bucket], "completed") or
Text.Contains([Bucket], "done") or
Text.Contains([Bucket], "finished") or
Text.Contains([Bucket], "solved")  or
Text.Contains([Bucket], "fã¦rdig") or
Text.Contains([Bucket], "klar til test")
then "Completed"
else "Not Started")
in
    #"Added Custom1"

Flow:
{"$schema":"https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#","contentVersion":"1.0.0.0","parameters":{"logicAppName":{"type":"String","metadata":{"description":"Name of the logic app."}},"logicAppLocation":{"defaultValue":"[resourceGroup().location]","allowedValues":["eastasia","southeastasia","centralus","eastus","eastus2","westus","northcentralus","southcentralus","northeurope","westeurope","japanwest","japaneast","brazilsouth","australiaeast","australiasoutheast","southindia","centralindia","westindia","canadacentral","canadaeast","westcentralus","westus2","[resourceGroup().location]"],"type":"String","metadata":{"description":"Location of the logic app."}},"planner_Connection_Name":{"defaultValue":"planner","type":"String","metadata":{"description":"Name of the connection."}},"azureblob_Connection_Name":{"defaultValue":"azureblob","type":"String","metadata":{"description":"Name of the connection."}}},"resources":[{"type":"Microsoft.Logic/workflows","name":"[parameters('logicAppName')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"state":"Disabled","definition":{"$schema":"https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#","contentVersion":"1.0.0.0","parameters":{"$connections":{"defaultValue":{},"type":"Object"},"$authentication":{"defaultValue":{},"type":"SecureObject"}},"triggers":{"Recurrence":{"recurrence":{"frequency":"Hour","interval":1},"type":"Recurrence","description":"Set the recurrency of the Flow, you can also play around with other triggers - ex. Plan Created."}},"actions":{"List_my_plans":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListMyPlans_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/me/planner/plans","authentication":"@parameters('$authentication')"}},"Apply_to_each":{"foreach":"@body('List_my_plans')?['value']","actions":{"List_tasks":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListTasks_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/plans/@{encodeURIComponent(items('Apply_to_each')?['id'])}/tasks","authentication":"@parameters('$authentication')"},"description":"List all the task for the current plan. Note the UI could not find the Id at first, but after typing \"Id\" in the field and then searching for Id it appeared."},"Apply_to_each_2":{"foreach":"@body('List_tasks')?['value']","actions":{"Get_task_details":{"runAfter":{},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"GetTaskDetails_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/tasks/@{encodeURIComponent(items('Apply_to_each_2')?['id'])}/details","authentication":"@parameters('$authentication')"}},"Append_to_string_variable_2":{"runAfter":{"Get_task_details":["Succeeded"]},"type":"AppendToStringVariable","inputs":{"name":"plans","value":"@{items('Apply_to_each_2')?['id']};@{items('Apply_to_each_2')?['title']};@{items('Apply_to_each_2')?['startDateTime']};@{items('Apply_to_each_2')?['dueDateTime']};@{items('Apply_to_each_2')?['percentComplete']};@{items('Apply_to_each_2')?['hasDescription']};@{items('Apply_to_each_2')?['createdDateTime']};@{items('Apply_to_each_2')?['createdBy']?['user']?['displayName']};@{items('Apply_to_each_2')?['completedDateTime']};@{items('Apply_to_each')?['id']};@{items('Apply_to_each')?['title']};@{items('Apply_to_each_2')?['bucketId']};@{base64ToString()}"},"description":"Saving task + plan data to a variable. Notice the \"base64ToString('DQo=')\" in the end. This is an Expression under the Conversion category that adds a new line. It is needed for PowerBI to understand the where a new row starts."}},"runAfter":{"List_tasks":["Succeeded"]},"type":"Foreach"},"List_buckets":{"runAfter":{"Apply_to_each_2":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"ListBuckets_V2"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['planner']['connectionId']"}},"method":"get","path":"/v1.0/planner/plans/@{encodeURIComponent(items('Apply_to_each')?['id'])}/buckets","authentication":"@parameters('$authentication')"},"description":"Buckets are the headings above the tasks. ex. To-Do, In Progress, Completed, ..."},"Apply_to_each_3":{"foreach":"@body('List_buckets')?['value']","actions":{"Append_to_string_variable":{"runAfter":{},"type":"AppendToStringVariable","inputs":{"name":"buckets","value":"@{items('Apply_to_each_3')?['id']};@{items('Apply_to_each_3')?['planId']};@{items('Apply_to_each_3')?['name']};@{base64ToString(base64ToString())}"},"description":"Again, remember to add the New Line character."}},"runAfter":{"List_buckets":["Succeeded"]},"type":"Foreach"}},"runAfter":{"Initialize_variable_2":["Succeeded"]},"type":"Foreach","description":"For each plan we need to load all tasks and buckets. \"Value\" is the plan."},"Initialize_variable":{"runAfter":{"List_my_plans":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"plans","type":"String"}]},"description":"For faster and easier text handling i use a variable to store plan and task information in."},"Create_blob":{"runAfter":{"Apply_to_each":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"CreateFile"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['azureblob']['connectionId']"}},"method":"post","path":"/datasets/default/files","queries":{"folderPath":"/planner","name":"plans"},"body":"@variables('plans')","authentication":"@parameters('$authentication')"},"description":"Lastly, save the plan variable to a file in Azure Blub.","runtimeConfiguration":{"contentTransfer":{"transferMode":"Chunked"}}},"Initialize_variable_2":{"runAfter":{"Initialize_variable":["Succeeded"]},"type":"InitializeVariable","inputs":{"variables":[{"name":"buckets","type":"String"}]},"description":"I also have a variable for the Buckets"},"Create_blob_2":{"runAfter":{"Create_blob":["Succeeded"]},"metadata":{"flowSystemMetadata":{"swaggerOperationId":"CreateFile"}},"type":"ApiConnection","inputs":{"host":{"connection":{"name":"@parameters('$connections')['azureblob']['connectionId']"}},"method":"post","path":"/datasets/default/files","queries":{"folderPath":"/planner","name":"buckets"},"body":"@variables('buckets')","authentication":"@parameters('$authentication')"},"description":"And save the buckets to a different file.","runtimeConfiguration":{"contentTransfer":{"transferMode":"Chunked"}}}},"outputs":{}},"parameters":{"$connections":{"value":{"planner":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'planner')]","connectionId":"[resourceId('Microsoft.Web/connections', parameters('planner_Connection_Name'))]","connectionName":"[parameters('planner_Connection_Name')]"},"azureblob":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'azureblob')]","connectionId":"[resourceId('Microsoft.Web/connections', parameters('azureblob_Connection_Name'))]","connectionName":"[parameters('azureblob_Connection_Name')]"}}}}},"dependsOn":["[resourceId('Microsoft.Web/connections', parameters('planner_Connection_Name'))]","[resourceId('Microsoft.Web/connections', parameters('azureblob_Connection_Name'))]"]},{"type":"Microsoft.Web/connections","name":"[parameters('planner_Connection_Name')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"api":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'planner')]"},"displayName":"[parameters('planner_Connection_Name')]"}},{"type":"Microsoft.Web/connections","name":"[parameters('azureblob_Connection_Name')]","apiVersion":"2016-06-01","location":"[parameters('logicAppLocation')]","properties":{"api":{"id":"[concat('/subscriptions/', subscription().subscriptionId, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'azureblob')]"},"displayName":"[parameters('azureblob_Connection_Name')]"}}]}

Comments

  1. As always a world class post before any other figured it out!

    ReplyDelete
  2. How to download an Android mobile phone and iOS gaming
    Step 성남 출장마사지 1: Download an Android mobile app from the 시흥 출장안마 Google Play Store and start gambling on your 포항 출장안마 favorite casino games and 김제 출장샵 sports games. 삼척 출장안마 · Step 2: Go to the

    ReplyDelete

Post a Comment

Popular posts from this blog

Azure DevOps - Gantt Chart

It's been a while since my last post - in the past couple of weeks I have played around with some videos of topics I find interesting. One of these topics are a very cool way of displaying a Gantt Chart upon your Azure DevOps board's. Check it out here!

Project Server - Change field name on PDP for standard fields (like the Owner field)

Project Server - Change owner field name on PDP The field names on the PDPs (Project Detail Pages) has been preselected on the standard fields for a project. If you want to change the Owner to something else it is quite difficult. In the following i will explain how we can change this field through the Content Editor webpart. To change the owner field add a Content Editor webpart to the PDP page where the field is inserted. Select the webpart and from the ribbon select HTML->Edit HTML Source. Copy/Paste the following code into the Content Editor webpart. < script type ="text/javascript">     var old_name = "Owner" ;     var new_name = "Ansvarlig" ;     var ttnA = document.getElementsByTagName( "div" );     for ( var j = 0; j < ttnA.length; j++) {         var orig = ttnA[j].innerHTML;         var stripped = orig.replace( /^\s*/ , "" ).replace( /\s*$/ , &quo

PowerShell results size unlimited/truncated - $FormatEnumerationLimit/Width

Ever experienced the problem where you run a Powershell command and you cannot see the whole result because the result is truncated. Problem: If you for example run the Test-SPsite command you might see something like the following: Site : SPSite Url=http://atlas/pwa Results : { SPSiteHealthResult Status=Passed RuleName="Conflicting Content Types" RuleId=befe203b-a8c0-48c2-b5f0-27c10f9e1622, SPSiteHealthResult Status=FailedWarning RuleName="Customized Files" RuleId=cd839b0d-9707-4950-8fac-f306cb920f6c, SPSiteHealthResult Status=Passed RuleName="Missing Galleries" RuleId=ee967197-ccbe-4c00-88e4-e6fab81145e1, SPSiteHealthResult Status=Passed RuleName="Missing Parent Content Types" RuleId=a9a6769f-7289-4b9f-ae7f-5db4b997d284, SPSiteHealthResult Status=FailedError RuleName="Missing Site Templates" RuleId=5258ccf5-e7d6-4df7-b8ae-12fcc0513ebd,