#Projectfortheweb example #PowerBI report to show planned work day by day #WorkManagement #MSProject #PPM #PowerPlatform #MSDyn365 #CDS

A quick blog post to give an example of reporting on resource assignment planned work at the day level. For those of you familiar with Project Online or Project Server, this would be the Assignment Timephased data where you have the TimeByDay detail. The daily details for resource assignments in Project for the Web are held in the msdyn_plannedwork column found in the Resource Assignments entity, as seen here in the Power Query editor:

PlannedWork1

This data is stored in a JSON array of objects, a string of data. Below I copied the data in the Visual Studio Code and formatted it as it is easier to read:

PlannedWork2

As you can see from the image above there are various properties there – End, Hours and Start. End and Start are JavaScript dates, these are milliseconds from midnight 1970/01/01. I’ve put together a simple Power BI report to show how to get this data – the report is not visual – it is functional Smile

I wont cover this step by step as reporting on Project for the Web data in the Common Data Service (CDS) has been covered before on my blog and others. To set the scene I will use the simple test project below:

PlannedWork3

The data in the Resource Assignment entity without any transformation of the JSON data looks like this:

PlannedWork4

As you can see, you can easily get the total hours for the assignments but not the hours per day. To get the hours per day you need to get this from the msdyn_plannedwork column. In this example Power BI report where the JSON is transformed the output is below for the same data as seen above:

PlannedWork5

These two tables show the same data, the top table is a matrix table where as the bottom table is a normal flat table. Let’s now take a look at the Power BI Power Query steps:

PlannedWork6

As the data is in the Resource Assignments entity, the steps here are all on the resource assignment data. The first thing to do is remove the columns that are not required, the columns needed here are msdyn_bookableresourceid, msdyn_plannedwork, msdyn_projectid, msdyn_resourceassignmentid, msdyn_taskid. Next filter out any msdyn_plannedwork rows where there is a null using the filter option:

Filter

Next transform the msdyn_plannedwork data to JSON:

Transform

Next expand the List data in msdyn_plannedwork to New rows:

Expand to rows

The next step is to expand data in the records (End, Hours and Start) – those JSON properties we looked at earlier:

image

This results in these three columns being added:

3 new columns

We now have the property values in separate columns. Next is to remove the “/Date(“ from the End and Start columns using the Replace Values function:

Replace1

With the same two End and Start columns selected, add another Replace Values step to remove the “)/” from the end of the value:

Replace2

With the End and Start columns still selected, convert the data to Whole Number:

Covert to number

Now add a new custom column to calculate the End date from the milliseconds value:

End Date

Repeat for the Start column:

Start Date

Then change the data type of the two new column added to be DateTime columns:

Change to DateTime

That is it, you will now have the data in a format where you can see the resource assignment data in day by day format.

I have made this simple example report available as a template to download here: https://gallery.technet.microsoft.com/Work-by-Day-Power-BI-6d1b71f4

One thought on “#Projectfortheweb example #PowerBI report to show planned work day by day #WorkManagement #MSProject #PPM #PowerPlatform #MSDyn365 #CDS

Comments are closed.

Blog at WordPress.com.

Up ↑

%d bloggers like this: