Let’s create and access a simple but effective Resource Demand Dashboard using:
Project Online Engagements data;
Power Query
Office Apps
Pivot Tables in Excel
Power BI
First of all, you must have a Project Online account and some Engagements data.
Engagement is the new feature in Project Online to support a full Resource Management capability. I’m talking about a model and giving a full demo in the video below.
Let’s get started then!
Make sure you have Resource Engagements data
Open Excel
I’m using the Excel 2016, which contains Power Query built in
In case you are using Excel 2013, you just need to download the Power Query here: Microsoft Power Query for Excel
Create a blank workbook
Access the tab Data (Power Query in Excel 2013)
Create a New Query from Other Sources
Then select From OData Feed
Access your Project Online OData feed by typing the following URL:
https://[TenantName].sharepoint.com/sites/pwa/_api/ProjectData
in the Navigator window
select the table: Engagements
Click on the arrow next to Load
Select Load To…
In the Load To window
Select Only Create Connection
Check Add this data to the Data Model
Click Load
You will see the Workbook Queries panel
Create your own layout, but remember the Excel Online limitations.
Select any cell
Click on Insert à Pivot Table
In the Create PivotTable windows select Use this Workbook´s Data Model
Select Existing Worksheet
Click OK
Drag and drop the fields below to the following areas:
ProjectName à ROWS
ProposedWork à VALUES (Sum)
Access the Insert tab and click on People Graph
People Graph Is a cool Office App, which will allow us visualize this data better
In the People Graph’s Data settings, click on Select your data
Select our Pivot Table area, like below
Click on Create
Repeat the steps below and create a new Pivot Table
Replace ProjectName by ResourceName (Role/RBS/Skills)
I’m using only TOP 10 most demanded Roles, but it’s up to you
You can also add some slicers to make your analysis even better
Feel free to add as many slicers as you want!
You can also be creative and try different layouts!
Upload your file to SharePoint Online or OneDrive for Business
You will have a great report with full Excel Online functionalities
Below you can see when we are using the slicers
I want to check all the demand only for one specific project
Import your file to Power BI
Open the report directly in Power BI
If you have the Power BI Pro license you can schedule data refresh hourly
But even with the FREE version of Power BI you can schedule data refresh daily
Hope you like!
Comments