(upbeat music)
- Hi, welcome to the session.
I am Priyanka Sadana,
a PM with Microsoft Project Team.
Today, I'll be talking about reporting
in Project for the web.
I hope you enjoy this session.
So if your organization decides to use Project
for the web to manage your projects
how do your executives, the PM or your team get information
on all your plans, portfolios, or resources?
Power BI solves this problem for you?
So today the agenda is that I'll be talking about a Project
for the web Power BI templates that Microsoft Project Team
has provided along with Project for the web.
Then we'll see how we can extend these Power BI templates
and build your reporting solutions in our products.
I'll take you through a quick recap
of the Power BI templates.
The content pack has nine report pages in total.
The first three focus on roll up of projects
into a portfolio.
Project dashboards give a quick overview
of aggregated information on efforts
and task status across all your projects.
The portfolio timeline shows visually where
all your projects fall on timeline, including their duration
and progress to date and the portfolio milestone
report summarizes all milestones completed in past 30 days
or listed to be completed in past 30 days
as well as milestones planned for next 30 days.
The next two have resource focus.
The resource report includes dashboards
that show what people are working on.
A project executive might use these reports
to understand what everybody's doing.
And the last four drill down to task level
including work for individuals and their timelines.
So these reports provide a good starting point
for creating a set of reports on your own.
There is no one project management solution
that suits everybody nor do the reports.
Hence these reports have to be adapted
to respective processes and needs of your own organization.
So first we'll start with
how do you get started with these reports?
You need a Power BI desktop license
and Power BI template file.
You can find the template file at aka.ms/projectreports.
In the template pop-out window,
you can enter the URL for Dataverse environment
and selecting the information icon there will take you
to the help document that can help you determine your URL
for Dataverse environment.
The easiest way is to go
to your Project Home application hover over your project
for the all you want to report on,
and copy the URL from there and use it.
Next you'll be asked to sign in,
you follow the authorization process,
that's it, Power BI will load all your data
into your reports.
So you can see that status of all your projects
and your resources in your dashboards.
Let's take a new example.
In project management getting
an early indication of problems is really important
for a project manager.
It allows project manager to correct the problems even
before they start.
So using the Project for the web data,
let's build a variance report.
I can show you an example here.
So we'll take an example of schedule variance here.
From a variance report the team can see
if the work they are getting done is actually
the project's targets or
whether they are just spending time
without ticking off the project milestones
or project deliverables.
For calculating variance
I would need planned values and earned values per task.
The earned value is calculated on how much the task is done
multiplied by budget per task and planned value is
how much task was supposed to be done by today
multiplied by task budget.
So for this example, my assumption is
that my task budget is equal to fixed cost
into the number of resources that are assigned to the task.
So here I'm going to open transform data.
I'll make a new query here.
So I would be needing data
from these three tables, the task team staging table,
the project tasks staging table
and the project staging table.
I'll use one of the tables as my basic source first.
In the next step, I'll merge another table with this one.
So click on merge queries and here you can select the table.
I need the project staging table
and I'll join both these tables on the project ID.
So here we go.
You can see that we have joined all the tables.
I'll expand the second table.
Then following the same steps,
I'll add another table here.
That was the project task staging table from
which I would be needing duration information on the task.
These two tables I'll be joining on task IDs.
Here we go.
Similarly, I'll expand this table as well.
So there's a lot of information in these three
tables combined, which I won't be needing
for calculating my planned or earned values.
I'll clean this table and remove the columns
that I do not need.
For that, you have to go and click on choose columns
and you can select the columns that would be required.
Since I already have that in my SPI step one,
so this is the way you will get your data.
I only have the
data that I require here.
Now I'll start my calculation's orders.
I'll go to add a new column, highlight a custom column.
And as I said, I'll take a constant value for task budget.
Let's take,
a value of 100 here.
Now I'd be calculating my earned value.
Similarly, you can follow the same step.
As I told earlier earned value is your completed effort
or your total effort for a task,
into your task budget.
So this is how we calculate earned value.
Similarly, you can calculate planned value
and I have these values calculated in my SPA step two.
So I'll be removing these columns from here.
So you can see here that I have my calculate
all the values, the planned values and earned values.
So for planned values, I needed networking days
between today's date and the starting date.
For that I have written a function which gives returns
the value of number of days that have passed
from your starting date to your current date
removing all the weekends in between.
So I'll go and I'll close and apply.
Here I won't be doing that since I already have these values
in my tables.
Coming onto visual you can select a clustered column chart
from here and in a shared axis as you can drop
project title and your column values will be planned value,
earned value and to calculate variance
you can just go and create a quick measure.
You can see that you can add any kind of operation
from here, I have taken subtraction and planned values
and the earned value difference.
So this is my variance.
You can see that I have added variance
to this visual as well.
Moving onto next example,
let's say you wanted to see
your schedule variance by your departments
and not just for projects.
But Project for the web does not by default
give department values.
But since it's based on Power Platform
you can customize or extend your Project for the web.
I'll show an example on how to do that.
For that we'll open make.apps.com.
So here, if you scroll down
and open your Project application,
you can see that I've added apartment entity here.
How to do that, for that go to make.powerapps.com again,
click on data and tables,
find your projects here.
If you're not able to locate a project table
if it doesn't show up here, change this to all
from managed or custom.
Select your project.
And here you can add a column.
If you add a column, let's say department...
5
from this drop down onto the datatype as choices
if you want to provide choice for this,
and you can go and add new choices here.
Click on done.
And then you have to save table to save this entity.
Now you can see that your table has
the department field here.
Next up you have to move on to views,
select all project.
You have to keep the show only unused columns checked.
From there we take department 5 and we add it.
Here, to our view,
Yeah it's here.
I'll reorder it.
You can save and then publish.
Once it's published, move on to forms,
select information,
and you can drag the column here.
I won't be adding it since I already have
a department column here.
Similarly save and publish this.
Go back, now our table is updated.
We'll go ahead and create a new application.
We'll name it Project for the web.
Click on done.
And here in the site map,
you need to configure what all information
this application will have.
Let's call this Project.
We'll link this project to the project entity.
Click on save, and then publish.
Scroll down you can see that we have
Project for the web demo here
but since it's not published that's why it's not clickable.
Save it, and then publish.
If you open this application now,
you can see that,
your project will have a department information
linked with it, here.
And you can change it and select any of the values.
Now I want to add my Power BI dashboard here as well.
So what I'll do is I'll go back to my template
and then how do I add these departments?
I'll go to transfer data again.
Next you go to your project staging table.
You can see that the department data is already there.
In your SBI step one table
if your department data is not flowing, just open that,
click on the gear of removed other columns,
search for your department and you can select it.
So if we see here, if we have variances across departments
if we want to see how each of the projects in our department
is performing, you can drill down on the report,
click on any of the departments and you can see
the distribution of variances
across projects of that particular department.
Say our reports are ready.
You can just publish them for your team to consume.
Moving onto your Power BI service.
You can see here that your reports are published
and anyone in your organization can consume these reports.
So how do we indicate these reports back
into your Power app?
I'll show you an example here.
So if I click on Project for the web
you can see that I have these Power BI reports
for all my projects embedded here.
So how do you embed these reports
in your Power App application?
Here, I'll go to make.powerapps
Again, it's the homepage.
I'll open my application
and then I'll click on edit.
In your site map, you can another sub area,
another group, call them...
So, here we'll add a web resource,
and that web resource will be a Power BI application.
How do we create that?
Here I can show an example that I have
an embedded report created here.
How do you create?
Go and create this, open
makepowerapps.com.
Navigate to solutions, add a new solution here.
Let's call it,
embedded report demo.
Click on create,
now we edit it.
Add a new web resource here.
Select type as webpage and move on to your text editor.
For source you have to go back to your Power BI service,
click on share,
click on embeddable and then publish to web.
Copy the HTML via website.
We have it copied to clipboard.
And we'll place it here.
Change your iframe width 200%,
and height as well.
Click on this text to see if your report data
is flowing here.
We can see that our reports are loading just fine,
click on okay.
And then publish all customization.
Once it's published, go to app design designer again,
select your sub area, select web resource here,
and then a URL.
So it will take some time to sink inside
use embedded reports, which I made earlier,
you save it, and then you publish.
Close it and then save it again.
Lets move on, move back to our application.
You can see new sub area here, with the Power BI reports.
So that concludes the demo, go and try it for yourself.
Thanks for watching the session.
I hope you find it useful.
Do watch out our sessions
Extensibility in Project for the web
and Project Accelerator.
And for any feedback, please provide it to us
through our in-app feedback.
Or if you have any doubts please write
to priyanka.sadana@microsoft.com.
Thank you.
(upbeat music)