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. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on Power BI Online course

    ReplyDelete

Post a Comment

Popular posts from this blog

Sharepoint 2013 - Fullscreen mode/hide quick launch

If you want to hide the quick launch on a SharePoint page or web part page in 2013 you could of cause use CSS scripts as in 2010.
But as a new thing in 2013 the user have the possibility to click on full screen mode. so why not load your page in this mode and then the user can choose to exit the mode if he prefers.

Add a content editor webpart to your page and click on Edit HTML from the ribbon.
Add the following code:

<script type="text/javascript">
window.onload = function()
{
  SetFullScreenMode(true);
}
</script>

SharePoint/Project Server - Firewall port open list

Firewall port open list Every time I have to install a new instance of Project Server I forget which ports have to be open.
The ports are divided into 3 lists, one list for the web front end servers, one list for the application server and one list for the SQL server.
These ports are based on a 2013 installation of SharePoint/Project Server. the 2010 installation defer a little bit.


Web Frontend ServerWhen a range is specified all ports between the range must be opened.
     Port(s)ProtocolBoundDescription
-80 TCPInhttp
-443 TCPInhttps/ssl
-25 TCPInSMTP for e-mail integration
-16500-16519TCPInPorts used by the search index component
-22233-22236TCPIn/OutPorts required for the AppFabric Caching Service
-32843-32845TCPInCommunication between Web servers and service applications
-32846TCPIn/OutSharePoint User Code Service
-808-809TCPInOffice Web Apps
-5725TCP