#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!

I am speaking – Project Virtual Conference #ProjectOnline #BI

October 2, 2015 at 12:57 pm | Posted in Information | Leave a comment
Tags: , , , , , ,

Quick post to reference my session for the Project Virtual Conference on 22nd October 2015:

http://projectvirtualconference.com/sessions/project-online-bi-made-easy/

See some of the great reporting options for Project Online.

For the full schedule see: http://projectvirtualconference.com/schedule/

To register for this great free event (no travel expenses either!): http://projectvirtualconference.com/register/

#ProjectOnline data via #ODATA and #SSIS in #SQL database table delta sync example #BI #Office365

August 11, 2014 at 6:49 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, T-SQL | Leave a comment
Tags: , , , , , , , ,

Microsoft recently released some great documentation and an example SSIS package on only pulling down the changes from Project Online to your custom SQL database – this will improve the efficiency of your Project Online SSIS packages. I recommend you take a look if you haven’t already:

http://msdn.microsoft.com/en-us/library/office/dn794163(v=office.15).aspx

http://www.microsoft.com/en-us/download/details.aspx?id=43736

For a walkthrough of an example SSIS package for Project Online see:

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/

Or a great example from Martin here:

http://nearbaseline.com/blog/2014/04/project-site-custom-list-reporting-using-ssis-odata-connector/

#ProjectOnline data via #ODATA and #SSIS in #SQL database table on-premise #MSProject #SharePointOnline #BI #SSRS #Office365 #Cloud

March 26, 2014 at 1:24 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Installation, Reporting, T-SQL | 10 Comments
Tags: , , , , , , , ,

Following on from my post earlier today regarding the SSIS component that enables you to extract data from ODATA feeds in Office 365, I have created a blog post that demonstrates this new functionality. See the steps below:

Firstly I installed the SQL Server data tools and Integration Services feature on my Test Project Server / SharePoint 2013 box (only server I had available at the time). Once you have access to SQL Server data tools, download and install the ODATA SSIS component from the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=42280

Also if this isn’t on a SharePoint / Project Server 2013 server – which in reality it wont be, you will also need the SharePoint client DLLs, these can be downloaded here:

http://www.microsoft.com/en-in/download/details.aspx?id=35585

When I tried the new SSIS component on the SQL 2012 Server the connection failed and it complained about the SharePoint.Client.Runtime.dll:

image

I didn’t test deploying the SharePoint client components to the SQL Server but I guess this should work.

Once everything is in place, launch SQL Server data tools for SQL Server 2012 and create a new Integration Services project:

image

Click OK and you will see the following screen:

image

On the control flow tab, at the bottom you will see Connection managers. Right click and create a new OLEDB Connection to the target database:

image

Before I created this, I did create a new empty SQL database:

image

Create another connection to the ODATA feed URL using the New Connection > ODATA option:

image

Create the ODATA connection to your ODATA tenant:

image

Specify an account that has access to the ODATA feed. Notice the Service document location is just the base ProjectData ODATA URL.

Click the All button and change the Microsoft Online Services Authentication to True:

image

Test the connection to confirm it is successful then click ok.

Add a Data Flow Task to the control flow:

image

Double click the data flow task and you will go to the Data flow tab:

image

Drag ODATA source from the toolbox to the data flow sheet:

image

Double click the ODATA source to see the properties:

image

Complete the details, selecting the ProjectOnlineODATAFeed connection and you will see the Collection property load the collection, in this case it is the tables on the ODATA feed:

image

Once loaded you will see the familiar ODATA entities:

image

In this example we will only use the Projects feed:

image

Click the Columns page on the left hand side and select the columns you want:

image

Click OK.

Drag the Destination Assistant from the SSIS toolbox to the Data Flow sheet and the new destination window will open:

image

Select the previously created SQL Server OLE DB connection:

image

Click OK.

image

Link the ODATA Source to the OLE DB Destination component using the blue data flow, then double click the OLEDB Destination component to load the editor – click yes to the warning / pop up:

image

Click the New button next to to the table details to create a new table, give the table a name, replace “OLE DB Destination” with the table name you want:

image

Click OK. At this point if we look at the SQL database we will see the new table and columns:

image

Back in the SSIS package click the Mappings page, here you can see the field mappings from the ODATA feed to the SQL table:

image

Click OK and you will see the following flow:

image

Run the package using the green arrow:

image

Once run successfully you will see the following:

image

If we switch back to the SQL server and see what is stored in the table you will see the project data from your Project online tenant:

image

Now the data is local you can use SQL Server Reporting Services to create reports, you can integrate this data into other LOB systems or create a custom OLAP cube etc. Once you are happy with the package you can set the package to run on a schedule to periodically copy the data down. There are plenty of references on the web for creating SSIS packages so have a search.

This is a basic example but hopefully gives you the idea of what is now possible Smile

#Odata #SQL SSIS component now available for SQL Server 2012 #ProjectOnline #SSRS #SharePointOnline #SharePoint #BI

March 26, 2014 at 9:16 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , ,

Quick post to let you know that the SSIS component to export data via ODATA that was mentioned here is now available for download, see the link below:

http://www.microsoft.com/en-us/download/details.aspx?id=42280

This will enable you to copy data from Project Online into a custom SQL database, from there you can easily create SSRS reports, custom OLAP cubes, integrate into other LOB systems etc. Look out for more details soon.

#SSRS native report render issue in #SharePoint #SP2013 with #IE 10 or later #PS2013 #BI #SQL

January 15, 2014 at 6:03 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Issue, Reporting | 1 Comment
Tags: , , , , ,

This post covers an the details around an issue I came across the other day and I wanted to make you aware to help in your deployments / system design. The issue is with displaying SSRS Native mode reports on a SharePoint 2013 page using a page viewer web part when using IE 10 or later. Our preference and recommendation is to usually use SSRS Integrated mode but on occasions some of our clients use SSRS 2008 R2 / 2012 Native mode. This issue doesn’t exist for SSRS Integrated mode.

****** Update ************

This issue is resolved by updating the document mode for the ReportViewer.aspx pages, updating this from <meta http-equiv=”X-UA-Compatible” content=”IE=5″>   to  <meta http-equiv=”X-UA-Compatible” content=”IE=8″> or later did resolve this issue for us. The file can be found on the report server in the following location: C:\Program Files\Microsoft SQL Server\MSRSx.InstanceName\Reporting Services\ReportServer\Pages. Please note, this will impact all the reports on that report server so test on a test server before a production server.

**************************

The issue is that the SSRS reports are not displayed correctly on the page, they are truncated:

image

Other standards-compliant browsers are ok, Chrome is:

image

IE 8 and 9 also work fine.

The page viewer web part with SSRS Native reports worked fine in SharePoint 2010 in any browser.

I have tested with the SSRS Report viewer web part (the 2008 R2 version as the SQL 2012 version doesn’t deploy to SharePoint 2013) from the RSWebParts.cab file, this has the same issue.

One of our devs had a quick look at this and it stated it was because the web part uses a table that is 3 cells wide. 2 of the cells are related to the document map while the 3rd contains the report itself. The document map cells are hidden by default.

In older versions of IE, a hidden cell in a table counted towards with width of the table, this was against the standard. Now with more standards compliant browsers, hidden cells do not count towards the width of the table.

This means that the report cell is the only cell defined for the row, so the browser forces it into the left most cell space. The end result of this is the SSRS report is truncated to the right as that is the limit of the size of that column.

So the answer going forward if your client wants to embed SSRS reports in SharePoint 2013 pages and they use IE, recommend (and use) SSRS 2012 Integrated mode.

Update #SharePoint list item author and editor #SP2010 #PowerShell #PS2010 #SSRS

May 1, 2013 at 7:52 am | Posted in Administration, Configuration, Fixes, Functionality, Information, PowerShell, Workarounds | 1 Comment
Tags: , , ,

Recently while working on a client deployment we had an issue relinking the SSRS reports to the data sources within the same library – SSRS was in SharePoint integrated mode. We had developed the Project Server solution internally and migrated it to the clients environment at a later date. All worked as expected apart from trying to relink the SSRS Reports to the updated data sources. The SSRS data sources had been edited to use the correct databases / PSI web services but when we tried to link the reports to the databases from the Manage Data Sources menu, SharePoint threw an error when trying to browse to the data sources. The error from the ULS log can be seen below:

Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: , Microsoft.ReportingServices.Diagnostics.Utilities.SharePointException: Report Server has encountered a SharePoint error. —> System.NullReferenceException: Object reference not set to an instance of an object.  
at Microsoft.ReportingServices.SharePoint.Objects.RSSPImpUser.get_LoginName()  
at Microsoft.ReportingServices.SharePoint.Utilities.CatalogItemUtilities.GetListItemUserLoginName(RSSPListItem item, String fieldName)

The cause of this error was that the user who created the SSRS objects had been removed from SharePoint when the system was cleaned up to take on to the clients environment.

There is a simple fix, that is to update the Author (Created By) and Editor (Modified By) fields with a user that exists on the SharePoint farm. The Author field can’t be updated via the UI but it can be update via the object model.

I created a simple PowerShell script that will update the author and editor for all SharePoint list items in the chosen list. To download the PowerShell script please see the link below:

http://gallery.technet.microsoft.com/scriptcenter/Update-SharePoint-list-4e6272ab

There are some variables / settings that need to be updated before running the script, these can be found below:

$newuser = "support\paulmather"  This needs to be updated with the new author and editor’s user account.

$site = get-SPWeb http://vm353/pwa/ProjectBICenter This needs to be updated with the correct URL for the site that contains the list / library.

$list = $site.Lists["Test"] This needs to be updated with the name of the correct library / lists that contains the list items that need to be updated, in this example the SSRS items.

That is it, the script is then ready to run.

Please fully test on a replica test farm before running on Production.

#ProjectServer #SSRS Report with multivalued parameters #SQL #PS2010 #SP2010

March 17, 2013 at 6:03 pm | Posted in Configuration, Customisation, Functionality, Reporting, T-SQL | 1 Comment
Tags: , , , ,

A quick blog post to highlight the use of one of the Project Server Reporting database functions to resolve an issue when using an SSRS multi value parameter.

There are several methods to get multi value parameters working in SQL Server Reporting Services (SSRS) including dataset filters, joining parameters and custom SQL functions – other blogs / forum posts detail these. This post demonstrates using a function that is available in the Project Server Reporting database. The function is called MSP_FN_Utility_ConvertStringListToTable. An example SQL Stored Procedure that will allow multi values can be seen below:

CREATE PROCEDURE [dbo].[SP_ProjectData] (
        @ProjUID NVARCHAR (max)
)AS
BEGIN
select            P.ProjectName
           ,    T.TaskName
from            MSP_EPMProject_UserView P
INNER JOIN        MSP_EPMTask_UserView T
ON                P.ProjectUID = T.ProjectUID
INNER JOIN        MSP_FN_Utility_ConvertStringListToTable (@ProjUID) AS PU
On              P.ProjectUID IS NULL or P.ProjectUID like PU.TokenVal
END

Create the SQL query as normal but instead of using a where clause to filter the Project UIDs join on to the function as shown above.

#SharePoint 2007 / 2010 and #ProjectServer 2007/ 2010 #BI Reporting made easy #SP2010 #PS2010 #businessintelligence #SQL

August 22, 2012 at 12:36 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , ,

Following from my previous post where I briefly introduced a new product from CPS called SharePoint ReportLink, as promised, this post demonstrates the ease of use and ease of reporting on SharePoint data when using SharePoint ReportLink.

For the purpose of this blog post, I have already deployed the solution to my test VM. Worth noting at this point, the version I have installed / used for this blog post is not the final release so you will see CPS DataMart referenced as this was the original product name. Functionally the version I have installed on my VM is probably 99% identical. Firstly I need to activate the feature on the site collection, in this case it is one of my PWA site collections. Once the feature is activated a new heading / menu appears on the Site Settings page for that site collection:

image

You will see CPS DataMart with four links, Application Settings, List Configuration, List Column Mappings and Logging. We will take a look at each page in turn, firstly the Application Settings page:

image

I didn’t set any database server or database name, as this is has been activated against a PWA site collection the feature automatically populates the database server with the Project Server Reporting SQL Server name and the name of the Project Server reporting database – pretty cool! You can of course update the settings to use a different database server and custom database. If this feature is activated against a normal SharePoint site collection you will see the following on the Application Settings page:

image

As you can see there is also a logging level, by default this is set to Information but can be updated:

image

The SharePoint ReportLink will log events to a custom list to ease diagnostics.

Let’s take a look at the next two page pages together, List Configuration and List Column Mappings.

List Configuration:

image

List Column Mappings:

image

As you can see these are just standard SharePoint lists, at this point you need to know what lists and what columns on those list you wish to report on. For the purpose of this blog post I will report on the Issues, Risks and Lessons Learned from my projects sites:

image

See below the populated / configured List Configuration and List Column Mapping lists:

image

image

Once the CPS SharePoint ReportLink is activated and configured on the site collection, any items on the lists configured will be synchronised to the SharePoint ReportLink database tables in real time. This includes, new items created, existing items modified and items deleted from those configured lists across all webs within that site collection.

After setting the List Column Mappings, custom tables will be created in the specified SharePoint ReportLink database, in this case the Project Server Reporting database:

Tables:

image

Issues list columns:

image

If those lists already contain items, after setting the column mappings the existing SharePoint items will be created in the SharePoint ReportLink tables.

I then created a few new items on the lists (on PM 1 and PM 2 sites, other items already existed), as you can see from below, the data is available in the custom tables in the reporting database:

Issues:

image

Lessons Learned:

image

As you can see, you can quickly and easily access the SharePoint list data from multiple sites using T-SQL. Also notice the Project_UID, this enables you to easily link the SharePoint ReportLink data to your Project Server data. If SharePoint ReportLink reads data from a non Project Server project site, the Project_UID column will contain an empty GUID.

The final menu we haven’t seen yet is the logging list, this can be seen below:

image

The warnings are logged above as the new custom list, Lessons Learned, doesn’t exist on the majority of my test project sites.

Now that the SharePoint data is easily accessible using T-SQL, reporting could not be simpler using your preferred reporting tool, SSRS, Excel etc. SharePoint BI made easy!

In case you didn’t see the first post, I have included links to the product below:

http://www.cps.co.uk/What-We-Do/Pages/SharePoint-ReportLink.aspx

http://www.cps.co.uk/What-We-Do/Documents/SharePoint%20ReportLink.pdf

I will follow up with a third post and create an SSRS report including the SharePoint ReportLink data in the near future.

#SharePoint 2007 / 2010 Reporting #SP2010 #PS2010 #ProjectServer #BI #businessintelligence #SQL

August 21, 2012 at 11:00 am | Posted in Add-on, Customisation, Functionality, Information, T-SQL | 5 Comments
Tags: , , , , , ,

A quick blog post to highlight a new product that CPS have released. The product is called SharePoint ReportLink. Details for the product can be found below:

image

http://www.cps.co.uk/What-We-Do/Pages/SharePoint-ReportLink.aspx

http://www.cps.co.uk/What-We-Do/Documents/SharePoint%20ReportLink.pdf

This product enables end users to easily create reports on SharePoint data using their favourite reporting technology without having to use the SharePoint web services. The product is an easily installable SharePoint solution (WSP file). Once the solution is installed, configuration is very simple too, users specify what lists and fields in those lists they wish to report on. The data is then available in real time in a specified reporting database. This enables users to create reports on SharePoint data using T-SQL to the custom reporting database rather having to use the SharePoint web services to access the data. This is particularly useful for Project Server programme level reports where you want to read data from multiple project sites in the one report.

Look out for a full blog post very soon where I will demo the configuration and usage to show you how powerful and simple to use this product is.

Next Page »

Blog at WordPress.com.
Entries and comments feeds.