#ProjectVConf session summary for #ProjectOnline #BI #PowerBI #Excel #SSRS #JavaScript #SSIS

November 3, 2015 at 3:13 am | Posted in Administration, Information, Personal | 1 Comment
Tags: , , , , , ,

It’s been a few weeks now since the Project Virtual Conference, for those of you that missed it, sign up here: http://projectvirtualconference.com/ and you can view the recordings.

This post will give a summary of my session on Project Online BI Made Easy:

http://event.projectvirtualconference.com/B002/

The aim of this session was to give an overview on some of the reporting technologies available to Project Online, this included:

  • Excel
  • Power BI
  • SSIS / SSRS
  • JavaScript / HTML

Firstly I demonstrated some example Excel dashboards I created for the session, screen shots of these can be seen below:

The project report below is from my Project Online report pack found here: https://gallery.technet.microsoft.com/Online-Reporting-Pack-431f075e

image

The report below is an example portfolio dashboard showing key metrics / data:

image

The report below is an example portfolio type report that can filter by programme:

image

I then demonstrated some example Power BI reports and dashboards that I created for the sessions:

The report below contains 4 pages, the first is another example portfolio type report:

image

The second page shows an example Treemap visualisation for the projects in the portfolio based on the % complete:

image

The third page shows an example project report:

image

The final page displays the drill down capability in Power BI, it shows the total cost per project initially:

image

Clicking a project drills into show the task cost for that project:

image

I also put together an example dashboard containing visualisations from the reports and natural language queries from the dashboard data:

image

image

The third technology demonstrated was SSIS / SSRS. The reporting technology was SSRS (SQL Server Reporting Services) but without SSIS (SQL Server Integration Services) an SSRS report would not be possible with Project Online data. Well that isn’t strictly true but you need to extract the data out of Project Online into another data source such as SQL – SSIS can do this but so could a .NET console application using CSOM for example.

Below is an example SSRS project status / highlight report that displays data from my Project Online PWA instance:

image

The fourth technology was JavaScript / HTML, I put together an simple project report that displayed project information – project level data, milestones / marked tasks, issues, risks and data from a custom list on my project site called benefits. This also demonstrated the capability of rendered the HTML tags in multiline fields and accessing custom SharePoint list data from the associated project site. The report can be seen below:

image

I also demonstrated a reporting add-in that we use for PS+ but this was just to give another example using JavaScript / HTML:

image

I then covered a bit around best practice when using the Odata API regarding filtering the data at source as much as possible to minimise the data being pulled down – this means using filters and selects to only pull the information you need.

The final part I walked through creating a new Excel based report from a blank workbook and also create a new Power BI report from a blank Power BI Desktop file.

Take a look at the session for more details.

To help get started with reporting in Project Online, take a look at some of the links below:

Excel Project Online Report Pack: https://gallery.technet.microsoft.com/projectserver/Online-Reporting-Pack-431f075e

https://pwmather.wordpress.com/2014/09/05/microsoft-projectonline-reporting-pack-msproject-sharepointonline-office365-ppm-excel-bi-data/

Blogs posts on Excel / Odata:

https://pwmather.wordpress.com/2013/09/16/projectserver-projectonline-example-report-ps2013-sp2013-office365-bi-excel-powerpivot/

https://pwmather.wordpress.com/2013/08/01/projectserver-projectonline-highlight-report-example-ps2013-sp2013-excel-bi-office/

https://pwmather.wordpress.com/2015/01/20/projectonline-excel-powerquery-report-office365-ps2013-bi-odata/

https://pwmather.wordpress.com/2015/04/13/projectonline-projectserver-reporting-on-auto-manually-scheduled-tasks-bi-office365-excel-powerquery-ps2013/

https://pwmather.wordpress.com/2015/10/09/removing-html-tags-from-projectonline-fields-powerquery-powerbi-excel/

https://pwmather.wordpress.com/2014/07/17/getting-started-with-projectonline-part-9-ps2013-office365-project-ppm-sharepointonline-pm-sp2013/

SSIS links:

https://pwmather.wordpress.com/2014/03/26/projectonline-data-via-odata-and-ssis-in-sql-database-table-on-premise-msproject-sharepointonline-bi-ssrs-office365-cloud/

https://msdn.microsoft.com/EN-US/library/office/dn794163.aspx

Power BI:

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

https://pwmather.wordpress.com/2015/08/07/projectonline-reporting-using-powerbi-part2-bi-office365-reports-ppm-pmot/

JavaScript examples:

https://pwmather.wordpress.com/2014/10/26/project-on-a-page-for-microsoft-projectonline-ps2013-javascript-jquery-office365-sharepoint/

https://pwmather.wordpress.com/2015/02/25/projectonline-projectserver-project-cost-and-work-report-javascript-jquery-office365-sharepoint-bi-ppm/

https://pwmather.wordpress.com/2015/05/01/projectonline-projectserver-view-resource-calendar-exceptions-javascript-jquery-office365-sharepoint/

There are plenty of details out there, just have a quick search and you will find lots of helpful articles for each technology!

Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: