#ProjectOnline reporting using #PowerBI Part2 #BI #Office365 #Reports #PPM #PMOT

This is the second post for the Project Online reporting with Power BI intro I created earlier this week. If you missed it, a link to the post can be found below:

http://pwmather.wordpress.com/2015/08/04/projectonline-reporting-using-powerbi-part1-bi-office365-reports-ppm-pmot/

In this post we will look at creating new reports using the Power BI Desktop tool then adding these to Power BI.

Firstly if you haven’t already, download the Power BI Desktop: https://powerbi.microsoft.com/desktop.

Once launched you will see a getting started type page:

Close this and you will see a blank canvas:

The first thing we need to do is get the data, to do this click the Get Data button on the ribbon then OData feed:

In the  next window, paste in your Project Online OData feed, in this example I am using:

https://cpsppmdemo4.sharepoint.com/sites/PWA2PSMODE/_api/ProjectData/Projects

Click OK and you will see your data:

At this point you can click OK to load the data but I would Edit the data to only select the fields you want. So in this example I will click the Edit button to load the query editor. The Query editor is very similar to the Power Query editor you see in Excel:

I only want certain columns so I will select the columns I want then remove the others. To select the columns just click the first one and hold down the Ctrl key then click the rest:

Or use the Choose Columns button on the ribbon:

Once all selected, click the Remove Columns > Remove Other Columns:

Notice in the Query Settings pane you see the Applied Steps, so you can easily undo a step if required:

 

That is the project data loaded, now I want to load the task data. In the Query Editor click New Source > OData feed:

Paste in the URL for the Tasks feed:

Click OK to add the new source. I then selected the columns I wanted and named the query:

There is also an advanced editor that I used to filter out the summary tasks by modifying the URL:

The advanced editor can be used to type the code to manipulate the data but use the UI where possible.

Now we have two individual datasets / queries, we need to merge the queries to create the join, click the Merge Queries button:

Chose the columns and table to join then the join type:

Click OK.

Once you are happy with the data click Close & Load > Close & Load:

This will load the dataset to the report, see the fields pane:

Then add your visualisations on, the first one I added is the Treemap:

Then drag the fields to the visualisation settings on the pane, in this example I use Project Name for the group property and % complete in the values property:

Add the other visualisations you need, the example I created looks like this:

I then added another page to visualise some task information:

A very basic report but that gives you the idea how easy it is to create visualisations of your Project Online data using the Power BI Desktop.

The next stage is to add this to Power BI. To do this I can either publish the report using the Publish button or from the Power BI site, upload the file. For this example I clicked the Publish button:

The report will then be available in Power BI. The manual way from the Power BI portal site is to click the Get Data button:

Then chose the type of data, for this example I will use Files:

Then click local file:

Locate the Power BI file (or can be Excel etc. but this was a Power BI file) and add the file:

Once loaded, Power BI will create the dataset, the report and create a dashboard with a link to the report (note, I removed everything from my Power BI portal so that is was clean for the screen shots :))

Clicking the link under the Reports heading will load my report:

Clicking on an element from a visualisation will filter the data in the other visualisations:

The first thing to do is set up the dataset to refresh, to do this click the ellipsis next to the dataset then click Schedule Refresh button on the fly out menu:

Expand Manage Data Sources:

Enter the credentials for both sources, click the Edit Credentials link, select the oAuth2 for the Authentication Method and click Sign in:

Enter the credentials for the Project Online tenant and click sign in. Repeat for the other data source.

Now expand the Schedule Refresh section and turn on the “Keep your data up-to-date”:

Click Apply. The data sources will update Daily now but you can also update it on demand using the Refresh Now option:

When the data is refreshing you will see a spinning icon next to the dataset:

Now lets look at the dashboard. I will create a new Dashboard called “Dashboard Example” using the + button next to the Dashboards heading:

Once created you will see a blank canvas:

Now I can pin visualisations to this dashboard. To start with access the report previous loaded, hover over a visualisation and click the pin icon:

Navigate back to the dashboard and you will see the visualisation:

Repeat this process until you have the dashboard you need, for example:

You can also create new visualisations from the dashboard using the natural language query “Ask a question…” field, start typing a question about the data, for example “show project work” will create a visualisation for the total project work in the dashboard:

This visualisation can then be pined using the pin too. You can also change the default visualisation for the data returned using the visualisation pane, see the example below for a different query:

Once finished, the visualisations can be been seen on the dashboard:

The properties of the tiles can be edited using the pencil icon:

This loads the Tile detail pane:

Clicking on a visualisation that was added from a report will navigate you to that report directly.

You can then share your dashboard out to other people in your organisation using the Share Dashboard option, this loads the following window:

Enter the email addresses and click Share.

This is just touching the surface of what you can do with Power BI, take a look today – it will be your favourite reporting tool!

#ProjectOnline / #ProjectServer reporting on auto / manually scheduled tasks #BI #Office365 #Excel #PowerQuery #PS2013

One issue or query that has been raised a few times is that when reporting on manually scheduled tasks and auto scheduled tasks the Start / Finish date fields do not show the same data that they do in Project or PWA for the manually scheduled tasks. For example, take a look at the example project plan below:

If I generate a reporting for this project using the equivalent fields from the OData API, see below:

As you can see all tasks have start and finish dates. The Manually scheduled tasks that are either blank or have text displayed in Project Pro contain dates in the report. These default to the Project Start date. The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskFinishDate,TaskIndex

This can be quite misleading from a reporting point of view. There are two options (probably more but two documented here!), these are detailed below in order of preference:

Option 1:

The preferred option would be to create a calculated field in the report, in this example, as I am using Excel I will create this in Power Query. I have my dataset in the Power Query Editor like below:

The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskStartDateString,TaskFinishDate,TaskFinishDateString,TaskIsManuallyScheduled,TaskIndex

Now click Add Column > Add Custom Column:

Give the column a name then create the formula:

Click OK to add the new custom column, repeat for the finish date:

After re-ordering and removing some columns my dataset now looks like this in the Query Editor:

The final change is to update the Data Types of the columns, in this example I updated the TaskStartDate and TaskFinishDate columns to Date/Time and my calculated DisplayedStartDate and DisplayedFinishDate columns to Text:

Now I am happy with the query I click the Close & Load button on the Home tab:

This will load my data into Excel:

As you can see the data displayed in my calculated columns on my report match the data in the fields on my Project Plan:

Option 2:

The other option is to create new Enterprise Custom Fields in Project Online that are calculated. Using Start date as an example, create a task level text field that is based on  formula, the formula would be =[Start]. I have called the field “DisplayedStartDate”. In Project Pro:

In my updated Excel Report:

The OData query used is below:

Tasks()?$Select=ProjectName,TaskName,TaskStartDate,TaskFinishDate,DisplayedStartDate,TaskIndex

As you can see this also gives the correct data but does introduce two additional calculated task level fields in your Project Online configuration. It is recommended to keep the task level calculated fields to a minimum – ideally below 5 for performance reasons hence this option being the least preferred.